Re: Index non-usage problem in 8.2.9

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(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