MaXX <bs139412@xxxxxxxxx> writes: > 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? Everything up to the "find rows faster" is pretty much true. "find rows faster" depends on exactly how you define your indexes, what your queries look like, and what the distribution of both the queries and the data look like. Where it really helps is when you're processing a whole bunch of records and using the partial index expression in addition the key column effectively lets you combine two constraints on your query. To get the same effect without the partial index you would either need a compound key which would take a lot more space and cause more i/o or you would need two separate indexes that postgres would combine with a bitmap index scan but that wouldn't be as effective. So for example if there are a million packets to a given host but only 100k that were TCP then a partial index on <host where proto = TCP> would let you scan only the 100k instead of having to scan the million and look at each one to discard it. And it would let you do that without having to create a much larger index on <proto,host> or combine two indexes one on <proto> and one on <host> either of which would be much slower and take more space. But if you're just looking up a single record I wouldn't expect it to be much faster to look it up in the smaller partial index than in the larger index. Indexes find records in log(n) time and log() grows awfully slowly. At best you're basically skipping a single tree level in favour of earlier query planning which is probably not going to be noticeable. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
