Re: When can postgresql use a partial (NOT NULL) index? Seems to depend on size of IN clause (even with enable seqscan = off)

Adding the is not null clause does allow the query to use the index again
(and is a much cleaner workaround in that I don't have to change the indexes
or rely on any magic number for splitting the in clauses). Also makes sense
since it more exactly matches the partial indexing condition.

Thanks Tom!

Tim

=> SELECT * FROM scm WHERE ((bid in
(1000071082,1558141,1261493,1558137,1558166,1622957,1261535,1558191,1885437,2025548,1558144,1261485,1261536,1261539,1261541,1000067964,1558183,1789348,1000090512,1558150,1000096731,1261533,2056107,1875527,1177541,1535923,1558184,1558155,1261538,1261537,1558140,1159311,1558188,1558185,1261529,1558158,1000021460,1558517,1000090515,1558194,1558143,1558153,1261484,1261542,1558156,1201225,1261481,1558157,1891458,1200735,1285621,1702779,1558135,1261540,1579615,1558189,1558154,2053227,1261531,1261488,1558139,1261527,1558192,1261530,1261528,1159310,1558136,1558138,1558164,1261543,1000015605,2053214,1558187,1183258,1184576,1558145,1558159,1208646,1622955,1558161,1558160,1208046,1000060938,1000067963,1000067965,1261487,1828875,1541699,1261491,1210589,1558162,1558151,1558152,1558163,1181201,1186001,1197776,1200734,1208043,1208044,1208045,1261486,1558142,1558146,1000096733,1000091036,1190958,1261532,1178300,1544212,1000015606,1637671,1261482,1261489,1261483,1875455,1000015596,1558165,1000152148,1000152147,1000152146,1000141594,1000141133,1000172483,1000191484,1000191485,1000196236,1000236337,1000241756,1000242921,1000256842,1000257993,1000270323,1000272820,1000281535,1000297033,1000297039,1000297446,1000301868,1000307196,1000316101,1000331822,1000334293,1000342550,1000352078,1000367699,1000372920,1000373959,1000383317,1000400498,1000405863,1000412281,1000420780,1000430861)))
AND bid IS NOT NULL ORDER BY m DESC LIMIT 100 OFFSET 0;
Limit (cost=80925.25..80925.50 rows=100 width=229)
-> Sort (cost=80925.25..80979.66 rows=21765 width=229)
Sort Key: m
-> Bitmap Heap Scan on scm (cost=825.19..80093.41 rows=21765
width=229)
Recheck Cond: ((bid = ANY
('{1000071082,1558141,1261493,1558137,1558166,1622957,1261535,1558191,1885437,2025548,1558144,1261485,1261536,1261539,1261541,1000067964,1558183,1789348,1000090512,1558150,1000096731,1261533,2056107,1875527,117541,1535923,1558184,1558155,1261538,1261537,1558140,1159311,1558188,1558185,1261529,1558158,1000021460,1558517,1000090515,1558194,1558143,1558153,1261484,1261542,1558156,1201225,1261481,1558157,1891458,1200735,1285621,1702779,1558135,1261540,1579615,155889,1558154,2053227,1261531,1261488,1558139,1261527,1558192,1261530,1261528,1159310,1558136,1558138,1558164,1261543,1000015605,2053214,1558187,1183258,1184576,1558145,1558159,1208646,1622955,1558161,1558160,1208046,1000060938,1000067963,1000067965,1261487,828875,1541699,1261491,1210589,1558162,1558151,1558152,1558163,1181201,1186001,1197776,1200734,1208043,1208044,1208045,1261486,1558142,1558146,1000096733,1000091036,1190958,1261532,1178300,1544212,1000015606,1637671,1261482,1261489,1261483,1875455,100001596,1558165,1000152148,1000152147,1000152146,1000141594,1000141133,1000172483,1000191484,1000191485,1000196236,1000236337,1000241756,1000242921,1000256842,1000257993,1000270323,1000272820,1000281535,1000297033,1000297039,1000297446,1000301868,1000307196,100316101,1000331822,1000334293,1000342550,1000352078,1000367699,1000372920,1000373959,1000383317,1000400498,1000405863,1000412281,1000420780,1000430861}'::integer[]))
AND (bid IS NOT NULL))
-> Bitmap Index Scan on index_scm_on_bid (cost=0.00..819.75
rows=21765 width=0)
Index Cond: (bid = ANY
('{1000071082,1558141,1261493,1558137,1558166,1622957,1261535,1558191,1885437,2025548,1558144,1261485,1261536,1261539,1261541,1000067964,1558183,1789348,1000090512,1558150,1000096731,1261533,2056107,18755271177541,1535923,1558184,1558155,1261538,1261537,1558140,1159311,1558188,1558185,1261529,1558158,1000021460,1558517,1000090515,1558194,1558143,1558153,1261484,1261542,1558156,1201225,1261481,1558157,1891458,1200735,1285621,1702779,1558135,1261540,1579615,158189,1558154,2053227,1261531,1261488,1558139,1261527,1558192,1261530,1261528,1159310,1558136,1558138,1558164,1261543,1000015605,2053214,1558187,1183258,1184576,1558145,1558159,1208646,1622955,1558161,1558160,1208046,1000060938,1000067963,1000067965,126147,1828875,1541699,1261491,1210589,1558162,1558151,1558152,1558163,1181201,1186001,1197776,1200734,1208043,1208044,1208045,1261486,1558142,1558146,1000096733,1000091036,1190958,1261532,1178300,1544212,1000015606,1637671,1261482,1261489,1261483,1875455,100015596,1558165,1000152148,1000152147,1000152146,1000141594,1000141133,1000172483,1000191484,1000191485,1000196236,1000236337,1000241756,1000242921,1000256842,1000257993,1000270323,1000272820,1000281535,1000297033,1000297039,1000297446,1000301868,10003071961000316101,1000331822,1000334293,1000342550,1000352078,1000367699,1000372920,1000373959,1000383317,1000400498,1000405863,1000412281,1000420780,1000430861}'::integer[]))
(7 rows)
Total runtime: 47.137 ms

On Tue, Aug 3, 2010 at 5:01 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Timothy Garnett <tgarnett(at)panjiva(dot)com> writes:
> > ... My first thought was that there was a problem with the
> > statistics/estimation in the planner, but using "set enable
> seq_scan=off;"
> > still does not use the index when there's over 100 bid's in the IN
> clause.
> > Breaking the IN clause into 2 < 100 element groups does however rescue
> the
> > use of the index and the fast performance as does creating a new
> non-partial
> > index on bid (i.e. an index "index_scm_on_bid2" btree (bid) WITH
> > (fillfactor=100) will be used with over 100 bid's).
>
> I think you're hitting the code that abandons attempts to prove
> constraints true when the expressions get too large (to avoid O(N^2)
> or worse behavior). Could you just add an explicit AND bid IS NOT NULL
> when you know none of the items in the IN clause will be null?
>
> regards, tom lane
>