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@xxxxxxxxxxxxxxx> 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
