Re: Understanding how partial indexes work?

"Chris Velevitch" <chris(dot)velevitch(at)gmail(dot)com> writes:
> I have a query on a table:-
> X between k1 and k2 or X < k1 and Y <> k3

> where k1, k2, k3 are constants.

> How would this query work, if I created an index on X and a partial
> index on X where Y <> k3?

Is it worth the trouble? You didn't mention the statistics involved,
but ordinarily I'd think a non-equal condition is too nonselective
to justify the cost of maintaining an extra index.

The real problem here is that "X < k1" is probably too nonselective
as well, which will likely lead the planner to decide that a plain
seqscan is the cheapest solution. In principle the above could be
done with a BitmapOr of two indexscans on X, but unless the constants
are such that only a small fraction of rows are going to be selected,
it's likely that a seqscan is the way to go.

regards, tom lane