Re: conditional indexes

Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> The system can recognize simple inequality implications, for example "x <
> 1" implies "x < 2"; otherwise the predicate condition must exactly match
> the query's WHERE condition or the index will not be recognized to be
> usable.

The reason it understands that example, but not that foo = 'bar' implies
foo <> '', is that the implication rules are built to work with btree
index operators. The presence of an operator in a btree opclass is what
gives us enough confidence that we understand its semantics (including
its relationships to other operators) to make these sorts of deductions.

As an example, we understand that foo < 42 (in WHERE) implies foo <= 42
(a possible partial index condition) only if the < and <= operators
involved can be found in the same index opclass. It is their roles in
the opclass, *not* their names, that we use to understand their
relationship.

The problem with <> is that it is not a btree-indexable operator (simply
because an index would hardly ever be useful for searching for rows that
do not match a key). And so there are no implication rules for it.

It might be possible to teach the planner to recognize that foo = 'bar'
implies an index predicate that's written like NOT (foo = ''), but it
doesn't look like that would work today (there's no special handling for
NOT clauses...)

regards, tom lane