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@selectacast.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