From: | "Cestmir Hybl" <cestmirl(at)freeside(dot)sk> |
---|---|
To: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Ignoring index on (A is null), (A is not null) conditions |
Date: | 2003-10-28 18:57:24 |
Message-ID: | 017201c39d85$533eed70$0200a8c0@stratos |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | John K. Herreshoff | 2003-10-28 19:04:57 | Re: Adding foreign key performance |
Previous Message | Bruce Momjian | 2003-10-28 18:54:03 | Re: Adding foreign key performance |