Re: Partial index does not make query faster - Mailing list pgsql-general

Ruben Blanco <rubenblan@gmail.com> writes:
> I'm trying to reduce execution time on a query using a partial index,
> but Postgres doesn't make a significant improvement, even when the
> partial index is 30 times smaller than the index used currently.

That doesn't really matter that much.  The part of the index a given
query will actually access is about the same size either way, ie same
number of leaf tuples of the same size.  If you're lucky, you might save
one level of btree descent to reach the leaf pages; but considering that
btree fanout for integer-size keys is several hundred to one, you need a
size ratio of several hundred even to be assured of that.

The planner does have a small correction to favor smaller indexes over
larger, but it's so small that it's often lost in the noise.  In this
case I think it's probably getting swamped by rounding off the estimate
of the number of leaf pages accessed to the nearest number of pages.
So it doesn't see the partial index as being any cheaper to use than the
full index.

>     Indexes:
>         "calls_201109_index_2" btree (company, call_date, caller_cli)
>         "calls_201109_index_partial" btree (company, call_date, caller_cli) WHERE call_date = '2011-09-01'::date

In this case you could have made the partial index smaller in a useful
way (ie, reducing the number of leaf pages touched) by omitting the
call_date column, which is quite redundant given the WHERE clause.
I experimented a bit with that, but found that there actually is a
planner bug in that case --- it misestimates the number of index tuples
to be read because of failing to account for the partial index predicate
in one place.  I'll see about fixing that, but in the meantime I don't
think you are really going to get any win with the above line of
thought, for a couple reasons:

First, is there some reason why 2011-09-01 is such a special date that
it deserves its own index, or are you just showing us a fragment of a
grand plan to manually partition the index through creating a large set
of partial indexes?  That sort of approach is almost certainly going to
be a dead loss once you consider the extra overhead of updating the
indexes and the extra planning costs.  Basically, while partitioning a
table can be useful when it comes time to drop one partition, it doesn't
save anything for routine queries except in very special cases; and
since there's no equivalent administrative need at the index level,
partitioning an index is not going to be a win.

Second, even if you can omit the call_date column, that's not going to
make this index much smaller, perhaps even not at all smaller depending
on alignment considerations.  You need to reduce the size of an index
entry by probably a factor of 2 before it's worth the extra complexity.

            regards, tom lane