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
