Creating an index without the IS NOT NULL did not help. The complete
version:
PostgreSQL 8.2.9 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 4.1.1 20070105 (Red Hat 4.1.1-52)
Starting with a fresh database I got the same results you did, but not
with my production table.
Tom Lane wrote:
Joseph S <jks@xxxxxxxxxxxxxxx> writes:
It seems that postgres can't figure out that it can use the index on
sacode unless I put "d2.sacode > 0" in my where clause.
Works for me ...
regression=# create table d2(sgcode int, sacode int);
CREATE TABLE
regression=# create index d2i on d2 (sgcode, sacode) WHERE sacode IS NOT NULL AND sacode > 0;
CREATE INDEX
regression=# explain select count(*) from d2 where d2.sgcode = 156 AND d2.sacode IN(2,1);
QUERY PLAN
------------------------------------------------------------------------------------
Aggregate (cost=8.54..8.55 rows=1 width=0)
-> Bitmap Heap Scan on d2 (cost=4.52..8.54 rows=1 width=0)
Recheck Cond: ((sgcode = 156) AND (sacode = ANY ('{2,1}'::integer[])))
-> Bitmap Index Scan on d2i (cost=0.00..4.52 rows=1 width=0)
Index Cond: ((sgcode = 156) AND (sacode = ANY ('{2,1}'::integer[])))
(5 rows)
You sure the server is 8.2.9? Awhile ago there were some bug fixes
around the handling of IS NULL/IS NOT NULL in predicates.
One thought is that the IS NOT NULL is really redundant, since it's
implied by the sacode > 0 test anyway. Does it work better if you
make the index just "WHERE sacode > 0" ?
regards, tom lane
