BUG #1470: Boolean expression index not used when it could be

From: "Sergey Koshcheyev" <sergey(dot)p(dot)k(at)hotmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #1470: Boolean expression index not used when it could be
Date: 2005-02-09 10:41:41
Message-ID: 20050209104141.1C84EF0B0C@svr2.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


The following bug has been logged online:

Bug reference: 1470
Logged by: Sergey Koshcheyev
Email address: sergey(dot)p(dot)k(at)hotmail(dot)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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Sergey Koshcheyev 2005-02-09 10:44:41 BUG #1471: Corrected e-mail address - bug 1470
Previous Message rob 2005-02-09 07:59:35 Re: SELECT returning too many rows (?)