I have a table, d2, that has a field sacode that is almost always null.
In fact the stanullfrac in pg_statistic for this column is 1. I have
this index on my table:
"d2_sgcode_sacode_idx" btree (sgcode, sacode) WHERE sacode IS NOT NULL
AND sacode > 0
The first version of my query wasn't using that index for some reason:
p10:owl=# explain select count(*) from d2 where d2.sgcode = 156 AND
d2.sacode IN(2,1);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Aggregate (cost=208074.99..208075.00 rows=1 width=0)
-> Bitmap Heap Scan on d2 (cost=175091.29..208074.99 rows=1 width=0)
Recheck Cond: (sgcode = 156)
Filter: (sacode = ANY ('{2,1}'::integer[]))
-> Bitmap Index Scan on d2_lower_username_sgcode_key
(cost=0.00..175091.29 rows=9431 width=0)
Index Cond: (sgcode = 156)
(6 rows)
Time: 0.531 ms
I accidentally stumbled upon the solution:
p10:owl=# explain select count(*) from d2 where d2.sgcode = 156 AND
d2.sacode IN(2,1) and d2.sacode > 0;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Aggregate (cost=16.33..16.34 rows=1 width=0)
-> Index Scan using d2_sgcode_sacode_idx on d2 (cost=0.00..16.33
rows=1 width=0)
Index Cond: ((sgcode = 156) AND (sacode > 0))
Filter: (sacode = ANY ('{2,1}'::integer[]))
(4 rows)
Time: 0.710 ms
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. It won't use
the index if I use "d2.sacode >= 1", for example.
