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 по дате отправления: