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. ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
