Re: AW: partial index

"Zeugswetter Andreas SB SD" <ZeugswetterA(at)spardat(dot)at> writes:
>> Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp> writes:
> test=# create index myindex on accounts(aid) where bid <> 0;

> Hmm ? Am I reading correctly ? a restriction that is on a field, that
> is not in the index ? Does that make sense ?

Yes it does, and in fact it's one of the more important applications of
partial indexes. It's the only way that a partial index can be cheaper
to scan than a full index. Consider:

create index foofull on foo (f1);

create index foopartial on foo (f1) where f1 < 100;

create index foopartial2 on foo (f1) where f2 > 100;

Now

select * from foo where f1 < 200;

cannot use either of the partial indexes, it will have to use foofull
or a seqscan.

select * from foo where f1 < 50;

can use foopartial, but the number of rows retrieved using the index
will be just the same as if it used foofull. Cost savings will be
marginal at best.

select * from foo where f1 < 50 and f2 > 200;

can use foopartial2, and since some of the rows have already been
filtered from the index on the basis of f2, this will be cheaper than
using either of the other indexes.

When I was testing the partial-index additions awhile back, at first
I thought it was a bug that the planner didn't show a preference for the
partial index in a case like #2. But it was right; the indexscan will
cover the same number of rows and indexentries with either index. If
the partial index is much smaller than the full index, you might save
one or two disk reads during the initial btree descent --- but that's
all. So a partial index constructed along the lines of foopartial might
save work at insert/update time (if it's much smaller than a full index)
but it's no better for selecting. The only way that having both full
and partial indexes on a column could make sense is if the partial
index's predicate mentions another column.

See also the previous discussion about using predicates with UNIQUE
indexes.

regards, tom lane