Re: Partial indexes Vs standard indexes : Insert performance

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?

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