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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html

Reply via email to