Re: When can postgresql use a partial (NOT NULL) index? Seems to depend on size of IN clause (even with enable seqscan = off) - Mailing list pgsql-general
| From | Timothy Garnett |
|---|---|
| Subject | Re: When can postgresql use a partial (NOT NULL) index? Seems to depend on size of IN clause (even with enable seqscan = off) |
| Date | |
| Msg-id | AANLkTintMPO8KxLknfJ+TkaJmgDj7eauJ-YxGSFjEdwP@mail.gmail.com Whole thread Raw |
| In response to | Re: When can postgresql use a partial (NOT NULL) index? Seems to depend on size of IN clause (even with enable seqscan = off) (Tom Lane <tgl@sss.pgh.pa.us>) |
| List | pgsql-general |
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
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@sss.pgh.pa.us> wrote:
Timothy Garnett <tgarnett@panjiva.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;"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
pgsql-general by date: