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
