Re: Partial indexes Vs standard indexes : Insert

Jeff Davis wrote:
> On Tue, 2006-08-15 at 13:13 +0200, MaXX wrote:
[snip]
>> I have a table in which I store log from my firewall.
>> For the protocol column (3 distinct values: TCP ~82%, UDP ~17%, ICMP
>> ~1%, the table contains 1.7M rows), I use a partial index to find ICMP
>> packets faster.
It's ICMP ~0.1%
>> In my understanding, a partial index is only touched when a matching row
>> is inserted/updated/deleted (index constraint is true), so if I create a
>> partial index for each protocol, I will slow down my machine as if I had
>> created a single "normal" index, but it will find rows faster (the
>> distribution is not uniform)...
>> Is this correct?
> That should work. Keep in mind that the main idea of an index is to
> reduce the number of pages that have to be fetched from disk. If the
> record size is small, you may have at least one ICMP packet on 50% (or
> more) of the disk pages even if ICMP packets only make up 1% of the
> total records. Even if they aren't inserted randomly, updates/deletes
> may randomize the distribution somewhat. If you have an ICMP packet on
> every other page, you might not be impressed with the performance versus
> a sequential scan. However, it could be a big win if you have other
> WHERE conditions aside from just the packet type.
OK, so that works well for queries where there is a very few rows in the
index in regard of the table size, and as long as this still true.

> The planner tries to take all of these things into consideration to some
> degree. The best test is to try EXPLAIN or EXPLAIN ANALYZE to see what
> plan it makes. Also, try forcing different types of plans to see if the
> planner is making the right choice.
I did some test and with both your reply and the one of Gregory Stark, I
was able identify what are good indexes and speed up the thing...

Thanks a lot,
--
MaXX