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@xxxxxxxxxxxxx> wrote:
Timothy Garnett <tgarnett@xxxxxxxxxxx> writes:
> ... My first thought was that there was a problem with the
> statistics/estimation in the planner, but using "set enable seq_scan=off;"I think you're hitting the code that abandons attempts to prove
> 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).
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
