Re: Partial indexes Vs standard indexes : Insert performance

From: MaXX <bs139412(at)skynet(dot)be>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Partial indexes Vs standard indexes : Insert performance
Date: 2006-08-16 10:15:57
Message-ID: ebur8u$1elp$1@talisker.lacave.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Graeme Hinchliffe 2006-08-16 10:17:57 Massive slowdown when LIMIT applied
Previous Message Alban Hertroys 2006-08-16 09:37:17 Re: How to add days to date