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