Thread: conditional indexes

Hello,

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!

    isbs=# explain select * from person where login='user' and login<>'';
                                     QUERY PLAN

-----------------------------------------------------------------------------
     Index Scan using person_login on person  (cost=0.00..5.97 rows=1
width=167)

Postgres start to use conditional index only when I also pass index'
condition:
    login='user' AND login<>'' ...


isbs=# select version();
                               version
---------------------------------------------------------------------
 PostgreSQL 7.3.3 on i386-unknown-freebsd4.7, compiled by GCC 2.95.4


--
 best regards,
Ruslan A Dautkhanov  rusland@scn.ru

On Wed, 25 Jun 2003 17:33:27 +0800
Ruslan A Dautkhanov <rusland@scn.ru> wrote:

> Postgres start to use conditional index only when I also pass index'
> condition:
>     login='user' AND login<>'' ...

i belive it's pretty rational when you'll think that you can have your own operators which does something else, and
allowingpostgres to treat conditional indices the way you'd like them to behave, would require to put lot of really
nastyworkarounds. 

just my point of view, though.

depesz

--

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.

Stephan Szabo <sszabo@megazone23.bigpanda.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