Ignoring index on (A is null), (A is not null) conditions

Поиск
Список
Период
Сортировка
От Cestmir Hybl
Тема Ignoring index on (A is null), (A is not null) conditions
Дата
Msg-id 017201c39d85$533eed70$0200a8c0@stratos
обсуждение исходный текст
Список pgsql-performance
Hi,

suppose, for simplicity, there is a table with index like this:

create table TABLE1 (
  A integer
);
create index TABLE1_A on TABLE1 (A);

My question is: why psql (7.3.3) does not use index when filtering by A IS
NULL, A IS NOT
NULL expressions?

In fact, I need to filter by expression ((A is null) or (A > const)).

Is there a way to filter by this expression using index?

Functional index cannot be used (except strange solution with CASE-ing and
converting NULL values into some integer constant)



----------------------------------------------------------------------------
--
 Index Scan using table1_a on table1  (cost=0.00..437.14 rows=29164 width=4)
   Index Cond: (a > 1000)
----------------------------------------------------------------------------
--
 Seq Scan on table1  (cost=0.00..448.22 rows=1 width=4)
   Filter: (a IS NULL)
--------------------------------------------------------
 Seq Scan on table1  (cost=0.00..448.22 rows=30222 width=4)
   Filter: (a IS NOT NULL)
------------------------------------------------------------
 Seq Scan on table1  (cost=0.00..523.77 rows=29164 width=4)
   Filter: ((a IS NULL) OR (a > 1000))
------------------------------------------------------------


CH


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

Предыдущее
От: "John K. Herreshoff"
Дата:
Сообщение: Re: Adding foreign key performance
Следующее
От: Bruce Momjian
Дата:
Сообщение: Re: Adding foreign key performance