Gregory Stark wrote:
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?
[snip]
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.
OK. I made some test with the queries actually run by my app and I found
a new usefull indexes to replace another.
I can see a real improvement from 112ms to 4ms in the query to find ICMP
pkts.
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.
I'm taking good note of this.
Thanks a lot,
--
MaXX
