Re: Partial indexes Vs standard indexes : Insert performance

Gregory Stark wrote:
> MaXX <bs139412(at)skynet(dot)be> 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