conditional indexes

Ruslan A Dautkhanov <rusland@scn.ru>

From: Ruslan A Dautkhanov <rusland@scn.ru>
To: pgsql-bugs@postgresql.org
Date: 2003-06-25T09:33:27Z
Lists: pgsql-bugs
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