Re: conditional indexes

On Wed, 25 Jun 2003, Ruslan A Dautkhanov wrote:

Bugs is not the right place for this, -general would be better.

> I think that conditional indexes not so clever as can.. Just little one
> example:
>
>     isbs=# create unique index person_login on person (login) where
> login<>'';
>     CREATE INDEX
>
>     isbs=# explain select * from person where login='user';
>                            QUERY PLAN
>     ---------------------------------------------------------
>      Seq Scan on person  (cost=0.00..53.34 rows=1 width=167)
>
> Why it does not use person_login index - predicate login='user' definitely
> also mean (login<>'') - indexes' predicate!

You have to be able to show that however, correctness needs to be first.
The general belief (what I get from the docs and past discussions) is that
doing the proofs would be expensive in general even for cases where the
index doesn't end up getting used.

From the docs (at least the cvs ones):

However, keep in mind that the predicate must match the conditions used in
the queries that are supposed to benefit from the index. To be precise, a
partial index can be used in a query only if the system can recognize that
the WHERE condition of the query mathematically implies the predicate of
the index. PostgreSQL does not have a sophisticated theorem prover that
can recognize mathematically equivalent expressions that are written in
different forms. (Not only is such a general theorem prover extremely
difficult to create, it would probably be too slow to be of any real use.)
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.