Re: Partial indexes Vs standard indexes : Insert

On Tue, 2006-08-15 at 13:13 +0200, MaXX wrote:
> Hi,
>
> I just want to verify if I'm understanding this correctly:
>
> 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.
>
> 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.

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.

Regards,
Jeff Davis