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.
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 (?) |