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

Поиск
Список
Период
Сортировка
От Sergey Koshcheyev
Тема BUG #1470: Boolean expression index not used when it could be
Дата
Msg-id 20050209104141.1C84EF0B0C@svr2.postgresql.org
обсуждение исходный текст
Ответы Re: BUG #1470: Boolean expression index not used when it could be  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
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.

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Andreas Pflug
Дата:
Сообщение: Re: Minor bug in pgAdmin III
Следующее
От: "Sergey Koshcheyev"
Дата:
Сообщение: BUG #1471: Corrected e-mail address - bug 1470