Re: Index non-usage problem in 8.2.9

Actually sacode is an int2.  I didn't mention it before because I
already tried explicit casts and that didn't do anything.  Now I just
realized that in your testcase you use int instead of int2.  I just retried:

[local]:playpen=#  create table d2(sgcode int, sacode int2);
CREATE TABLE
Time: 13.748 ms
[local]:playpen=#  create index d2i on d2 (sgcode, sacode) WHERE sacode
IS NOT NULL AND sacode > 0;
CREATE INDEX
Time: 30.734 ms
[local]:playpen=#  explain select count(*) from d2 where  d2.sgcode =
156 AND d2.sacode IN(2,1);
                                 QUERY PLAN
--------------------------------------------------------------------------
  Aggregate  (cost=40.61..40.62 rows=1 width=0)
    ->  Seq Scan on d2  (cost=0.00..40.60 rows=1 width=0)
          Filter: ((sgcode = 156) AND (sacode = ANY ('{2,1}'::integer[])))
(3 rows)
[local]:playpen=#  explain select count(*) from d2 where  d2.sgcode =
156 AND d2.sacode IN(2::int2,1::int2);
                                 QUERY PLAN
---------------------------------------------------------------------------
  Aggregate  (cost=40.61..40.62 rows=1 width=0)
    ->  Seq Scan on d2  (cost=0.00..40.60 rows=1 width=0)
          Filter: ((sgcode = 156) AND (sacode = ANY ('{2,1}'::smallint[])))
(3 rows)

Time: 0.986 ms



Tom Lane wrote:
> Joseph S <jks@selectacast.net> writes:
>> Tom Lane wrote:
>>> Can you force it to use the partial index by dropping the other index?
>>> (Use begin; drop index ...; explain ...; rollback; to avoid dropping
>>> the index for real.)  It's quite unclear at this point whether it
>
>> I tried, and it ends up using a seqscan.
>
> Just to be sure, what if you set enable_seqscan = off?
>
> If still not, then there must be something about the table or index
> declaration that you didn't tell us.  In the past, issues like use of
> a domain instead of a bare datatype have been relevant ...
>
>             regards, tom lane