The following bug has been logged online:
Bug reference: 1470
Logged by: Sergey Koshcheyev
Email address: sergey.p.k@hotmail.com
PostgreSQL version: 7.4.6
Operating system: Linux (Debian)
Description: Boolean expression index not used when it could be
Details:
I'm trying to optimize "is null" queries, since PgSQL doesn't index null
values. I have found that creating an expression index on (column is null)
could work, but it doesn't get used unless the index expression is part of a
comparison. Could this be improved, so that (a boolean expression) is taken
as equivalent to (a boolean expression = true)?
Here's an example:
office=> create table tbl1 (abc int);
CREATE TABLE
office=> create index tbl1_abc on tbl1 ((abc is null));
CREATE INDEX
office=> explain select * from tbl1 where (abc is null) = true;
QUERY PLAN
----------------------------------------------------------------------
Index Scan using tbl1_abc on tbl1 (cost=0.00..17.07 rows=6 width=4)
Index Cond: ((abc IS NULL) = true)
(2 rows)
office=> explain select * from tbl1 where (abc is null);
QUERY PLAN
-----------------------------------------------------
Seq Scan on tbl1 (cost=0.00..20.00 rows=6 width=4)
Filter: (abc IS NULL)
(2 rows)
I would like the second select to pick up the index too.
