Re: Index non-usage problem in 8.2.9

Joseph S <jks(at)selectacast(dot)net> 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