Scanning all partition when more than 100 items in "where id in ()" clause
Hello All, I think I found bug here. Postgres 9.6.9 on Red Hat Enterprise
Linux Server release 7.5 (Maipo).
explain analyze select * from attachment where entity_id in
(189740255,190007498,190419209,190454048,190498132,190589677,190741801,190757628,190792101,190806372,190808350,190823698,190897323,
190900401,190900785,190901387,190905482,190907623,190908303,190909243,190910238,190910853,190912027,190912553,190913738,190915283,
190915471,190915715,190916292,190917756,190921775,190924593,190924867,190937455,190939665,190941432,190946157,190946578,190947077,
190948243,190950786,190951360,190951819,190952605,190953382,190965451,190966957,190968387,191015655,191015973,191024345,191027103,
191034716,191035208,191037555,191043749,191044743,191044833,191045116,191045389,191045513,191071499,191072143,191073777,191076136,
191078249,191078327,191079035,191080060,191082010,191082170,191082475,191088364,191090035,191094751,191095227,191095960,191096543,
191097152,191097484,191104018,191116600,191128391,191128416,191128441,191128442,191128458,191128498,191128501,191128562,191128563,
191128580,191128591,191139565,191145651,191152454,191152613,191155619,191156217,191156687);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..3062.89 rows=24943 width=134) (actual
time=0.101..6.938 rows=258 loops=1)
-> Seq Scan on attachment (cost=0.00..0.00 rows=1 width=880) (actual
time=0.005..0.005 rows=0 loops=1)
Filter: (entity_id = ANY
('{189740255,190007498,190419209,190454048,190498132,190589677,190741801,190757628,190792101,190806372,190808350,190823698,190897323,190900401,190900785,
190901387,190905482,190907623,190908303,190909243,190910238,190910853,190912027,190912553,190913738,190915283,190915471,190915715,190916292,190917756,190921775,190924593,190924867,1909374
55,190939665,190941432,190946157,190946578,190947077,190948243,190950786,190951360,190951819,190952605,190953382,190965451,190966957,190968387,191015655,191015973,191024345,191027103,1910
34716,191035208,191037555,191043749,191044743,191044833,191045116,191045389,191045513,191071499,191072143,191073777,191076136,191078249,191078327,191079035,191080060,191082010,191082170,1
91082475,191088364,191090035,191094751,191095227,191095960,191096543,191097152,191097484,191104018,191116600,191128391,191128416,191128441,191128442,191128458,191128498,191128501,19112856
2,191128563,191128580,191128591,191139565,191145651,191152454,191152613,191155619,191156217,191156687}'::integer[]))
-> Index Scan using attachment_entity_id_180m_195m_by_entity_id on
attachment_entity_id_180m_195m (cost=0.57..3062.89 rows=24942 width=134)
(actual time=0.095..6.900 rows=258 loops=1
)
Index Cond: (entity_id = ANY
('{189740255,190007498,190419209,190454048,190498132,190589677,190741801,190757628,190792101,190806372,190808350,190823698,190897323,190900401,190900
785,190901387,190905482,190907623,190908303,190909243,190910238,190910853,190912027,190912553,190913738,190915283,190915471,190915715,190916292,190917756,190921775,190924593,190924867,190
937455,190939665,190941432,190946157,190946578,190947077,190948243,190950786,190951360,190951819,190952605,190953382,190965451,190966957,190968387,191015655,191015973,191024345,191027103,
191034716,191035208,191037555,191043749,191044743,191044833,191045116,191045389,191045513,191071499,191072143,191073777,191076136,191078249,191078327,191079035,191080060,191082010,1910821
70,191082475,191088364,191090035,191094751,191095227,191095960,191096543,191097152,191097484,191104018,191116600,191128391,191128416,191128441,191128442,191128458,191128498,191128501,1911
28562,191128563,191128580,191128591,191139565,191145651,191152454,191152613,191155619,191156217,191156687}'::integer[]))
Planning time: 12.969 ms
Execution time: 7.062 ms
(7 rows)
Adding only one more item in the "where id in ()" clause, query will do
index scan on all partitions
explain analyze select * from attachment where entity_id in
(189740255,190007498,190419209,190454048,190498132,190589677,190741801,190757628,190792101,190806372,190808350,190823698,190897323,
190900401,190900785,190901387,190905482,190907623,190908303,190909243,190910238,190910853,190912027,190912553,190913738,190915283,
190915471,190915715,190916292,190917756,190921775,190924593,190924867,190937455,190939665,190941432,190946157,190946578,190947077,
190948243,190950786,190951360,190951819,190952605,190953382,190965451,190966957,190968387,191015655,191015973,191024345,191027103,
191034716,191035208,191037555,191043749,191044743,191044833,191045116,191045389,191045513,191071499,191072143,191073777,191076136,
191078249,191078327,191079035,191080060,191082010,191082170,191082475,191088364,191090035,191094751,191095227,191095960,191096543,
191097152,191097484,191104018,191116600,191128391,191128416,191128441,191128442,191128458,191128498,191128501,191128562,191128563,
191128580,191128591,191139565,191145651,191152454,191152613,191155619,191156217,191156687,191156685);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..26203.63 rows=221704 width=131) (actual
time=1.412..50.966 rows=258 loops=1)
-> Seq Scan on attachment (cost=0.00..0.00 rows=1 width=880) (actual
time=0.002..0.002 rows=0 loops=1)
Filter: (entity_id = ANY
('{189740255,190007498,190419209,190454048,190498132,190589677,190741801,190757628,190792101,190806372,190808350,190823698,190897323,190900401,190900785,
190901387,190905482,190907623,190908303,190909243,190910238,190910853,190912027,190912553,190913738,190915283,190915471,190915715,190916292,190917756,190921775,190924593,190924867,1909374
55,190939665,190941432,190946157,190946578,190947077,190948243,190950786,190951360,190951819,190952605,190953382,190965451,190966957,190968387,191015655,191015973,191024345,191027103,1910
34716,191035208,191037555,191043749,191044743,191044833,191045116,191045389,191045513,191071499,191072143,191073777,191076136,191078249,191078327,191079035,191080060,191082010,191082170,1
91082475,191088364,191090035,191094751,191095227,191095960,191096543,191097152,191097484,191104018,191116600,191128391,191128416,191128441,191128442,191128458,191128498,191128501,19112856
2,191128563,191128580,191128591,191139565,191145651,191152454,191152613,191155619,191156217,191156687,191156685}'::integer[]))
-> Index Scan using attachment_entity_id_null_by_entity_id on
attachment_entity_id_null (cost=0.42..443.69 rows=1 width=172) (actual
time=0.089..0.089 rows=0 loops=1)
Index Cond: (entity_id = ANY
('{189740255,190007498,190419209,190454048,190498132,190589677,190741801,190757628,190792101,190806372,190808350,190823698,190897323,190900401,190900
785,190901387,190905482,190907623,190908303,190909243,190910238,190910853,190912027,190912553,190913738,190915283,190915471,190915715,190916292,190917756,190921775,190924593,190924867,190
937455,190939665,190941432,190946157,190946578,190947077,190948243,190950786,190951360,190951819,190952605,190953382,190965451,190966957,190968387,191015655,191015973,191024345,191027103,
191034716,191035208,191037555,191043749,191044743,191044833,191045116,191045389,191045513,191071499,191072143,191073777,191076136,191078249,191078327,191079035,191080060,191082010,1910821
70,191082475,191088364,191090035,191094751,191095227,191095960,191096543,191097152,191097484,191104018,191116600,191128391,191128416,191128441,191128442,191128458,191128498,191128501,1911
28562,191128563,191128580,191128591,191139565,191145651,191152454,191152613,191155619,191156217,191156687,191156685}'::integer[]))
-> Bitmap Heap Scan on attachment_entity_id_less_45m
(cost=277.83..599.33 rows=143 width=132) (actual time=0.070..0.070 rows=0
loops=1)
Recheck Cond: (entity_id = ANY
('{189740255,190007498,190419209,190454048,190498132,190589677,190741801,190757628,190792101,190806372,190808350,190823698,190897323,190900401,1909
00785,190901387,190905482,190907623,190908303,190909243,190910238,190910853,190912027,190912553,190913738,190915283,190915471,190915715,190916292,190917756,190921775,190924593,190924867,1
90937455,190939665,190941432,190946157,190946578,190947077,190948243,190950786,190951360,190951819,190952605,190953382,190965451,190966957,190968387,191015655,191015973,191024345,19102710
3,191034716,191035208,191037555,191043749,191044743,191044833,191045116,191045389,191045513,191071499,191072143,191073777,191076136,191078249,191078327,191079035,191080060,191082010,19108
2170,191082475,191088364,191090035,191094751,191095227,191095960,191096543,191097152,191097484,191104018,191116600,191128391,191128416,191128441,191128442,191128458,191128498,191128501,19
1128562,191128563,191128580,191128591,191139565,191145651,191152454,191152613,191155619,191156217,191156687,191156685}'::integer[]))
-> Bitmap Index Scan on
attachment_entity_id_less_45m_by_entity_id (cost=0.00..277.79 rows=143
width=0) (actual time=0.070..0.070 rows=0 loops=1)
Index Cond: (entity_id = ANY
('{189740255,190007498,190419209,190454048,190498132,190589677,190741801,190757628,190792101,190806372,190808350,190823698,190897323,190900401,
190900785,190901387,190905482,190907623,190908303,190909243,190910238,190910853,190912027,190912553,190913738,190915283,190915471,190915715,190916292,190917756,190921775,190924593,1909248
67,190937455,190939665,190941432,190946157,190946578,190947077,190948243,190950786,190951360,190951819,190952605,190953382,190965451,190966957,190968387,191015655,191015973,191024345,1910
27103,191034716,191035208,191037555,191043749,191044743,191044833,191045116,191045389,191045513,191071499,191072143,191073777,191076136,191078249,191078327,191079035,191080060,191082010,1
91082170,191082475,191088364,191090035,191094751,191095227,191095960,191096543,191097152,191097484,191104018,191116600,191128391,191128416,191128441,191128442,191128458,191128498,19112850
1,191128562,191128563,191128580,191128591,191139565,191145651,191152454,191152613,191155619,191156217,191156687,191156685}'::integer[]))
-> Index Scan using attachment_entity_id_45m_60m_by_entity_id on
attachment_entity_id_45m_60m (cost=0.57..1885.49 rows=20391 width=129)
(actual time=0.121..0.121 rows=0 loops=1)
Index Cond: (entity_id = ANY
('{189740255,190007498,190419209,190454048,190498132,190589677,190741801,190757628,190792101,190806372,190808350,190823698,190897323,190900401,190900
785,190901387,190905482,190907623,190908303,190909243,190910238,190910853,190912027,190912553,190913738,190915283,190915471,190915715,190916292,190917756,190921775,190924593,190924867,190
937455,190939665,190941432,190946157,190946578,190947077,190948243,190950786,190951360,190951819,190952605,190953382,190965451,190966957,190968387,191015655,191015973,191024345,191027103,
191034716,191035208,191037555,191043749,191044743,191044833,191045116,191045389,191045513,191071499,191072143,191073777,191076136,191078249,191078327,191079035,191080060,191082010,1910821
70,191082475,191088364,191090035,191094751,191095227,191095960,191096543,191097152,191097484,191104018,191116600,191128391,191128416,191128441,191128442,191128458,191128498,191128501,1911
28562,191128563,191128580,191128591,191139565,191145651,191152454,191152613,191155619,191156217,191156687,191156685}'::integer[]))
-> Index Scan using attachment_entity_id_60m_75m_by_entity_id on
attachment_entity_id_60m_75m (cost=0.57..1704.44 rows=24498 width=130)
(actual time=0.126..0.126 rows=0 loops=1)
Index Cond: (entity_id = ANY
('{189740255,190007498,190419209,190454048,190498132,190589677,190741801,190757628,190792101,190806372,190808350,190823698,190897323,190900401,190900
785,190901387,190905482,190907623,190908303,190909243,190910238,190910853,190912027,190912553,190913738,190915283,190915471,190915715,190916292,190917756,190921775,190924593,190924867,190
937455,190939665,190941432,190946157,190946578,190947077,190948243,190950786,190951360,190951819,190952605,190953382,190965451,190966957,190968387,191015655,191015973,191024345,191027103,
191034716,191035208,191037555,191043749,191044743,191044833,191045116,191045389,191045513,191071499,191072143,191073777,191076136,191078249,191078327,191079035,191080060,191082010,1910821
70,191082475,191088364,191090035,191094751,191095227,191095960,191096543,191097152,191097484,191104018,191116600,191128391,191128416,191128441,191128442,191128458,191128498,191128501,1911
28562,191128563,191128580,191128591,191139565,191145651,191152454,191152613,191155619,191156217,191156687,191156685}'::integer[]))
-> Index Scan using attachment_entity_id_75m_90m_by_entity_id on
attachment_entity_id_75m_90m (cost=0.57..1570.17 rows=13695 width=131)
(actual time=0.126..0.126 rows=0 loops=1)
Index Cond: (entity_id = ANY
('{189740255,190007498,190419209,190454048,190498132,190589677,190741801,190757628,190792101,190806372,190808350,190823698,190897323,190900401,190900
785,190901387,190905482,190907623,190908303,190909243,190910238,190910853,190912027,190912553,190913738,190915283,190915471,190915715,190916292,190917756,190921775,190924593,190924867,190
937455,190939665,190941432,190946157,190946578,190947077,190948243,190950786,190951360,190951819,190952605,190953382,190965451,190966957,190968387,191015655,191015973,191024345,191027103,
191034716,191035208,191037555,191043749,191044743,191044833,191045116,191045389,191045513,191071499,191072143,191073777,191076136,191078249,191078327,191079035,191080060,191082010,1910821
70,191082475,191088364,191090035,191094751,191095227,191095960,191096543,191097152,191097484,191104018,191116600,191128391,191128416,191128441,191128442,191128458,191128498,191128501,1911
28562,191128563,191128580,191128591,191139565,191145651,191152454,191152613,191155619,191156217,191156687,191156685}'::integer[]))
-> Index Scan using attachment_entity_id_90m_105m_by_entity_id on
attachment_entity_id_90m_105m (cost=0.57..2390.10 rows=13579 width=133)
(actual time=0.123..0.123 rows=0 loops=1)
Index Cond: (entity_id = ANY
('{189740255,190007498,190419209,190454048,190498132,190589677,190741801,190757628,190792101,190806372,190808350,190823698,190897323,190900401,190900
785,190901387,190905482,190907623,190908303,190909243,190910238,190910853,190912027,190912553,190913738,190915283,190915471,190915715,190916292,190917756,190921775,190924593,190924867,190
937455,190939665,190941432,190946157,190946578,190947077,190948243,190950786,190951360,190951819,190952605,190953382,190965451,190966957,190968387,191015655,191015973,191024345,191027103,
191034716,191035208,191037555,191043749,191044743,191044833,191045116,191045389,191045513,191071499,191072143,191073777,191076136,191078249,191078327,191079035,191080060,191082010,1910821
70,191082475,191088364,191090035,191094751,191095227,191095960,191096543,191097152,191097484,191104018,191116600,191128391,191128416,191128441,191128442,191128458,191128498,191128501,1911
28562,191128563,191128580,191128591,191139565,191145651,191152454,191152613,191155619,191156217,191156687,191156685}'::integer[]))
-> Index Scan using attachment_entity_id_105m_120m_by_entity_id on
attachment_entity_id_105m_120m (cost=0.57..1824.95 rows=20334 width=133)
(actual time=0.134..0.134 rows=0 loops=1)
Index Cond: (entity_id = ANY
('{189740255,190007498,190419209,190454048,190498132,190589677,190741801,190757628,190792101,190806372,190808350,190823698,190897323,190900401,190900
785,190901387,190905482,190907623,190908303,190909243,190910238,190910853,190912027,190912553,190913738,190915283,190915471,190915715,190916292,190917756,190921775,190924593,190924867,190
937455,190939665,190941432,190946157,190946578,190947077,190948243,190950786,190951360,190951819,190952605,190953382,190965451,190966957,190968387,191015655,191015973,191024345,191027103,
191034716,191035208,191037555,191043749,191044743,191044833,191045116,191045389,191045513,191071499,191072143,191073777,191076136,191078249,191078327,191079035,191080060,191082010,1910821
70,191082475,191088364,191090035,191094751,191095227,191095960,191096543,191097152,191097484,191104018,191116600,191128391,191128416,191128441,191128442,191128458,191128498,191128501,1911
28562,191128563,191128580,191128591,191139565,191145651,191152454,191152613,191155619,191156217,191156687,191156685}'::integer[]))
-> Index Scan using attachment_entity_id_120m_135m_by_entity_id on
attachment_entity_id_120m_135m (cost=0.57..2474.61 rows=23011 width=131)
(actual time=0.124..0.124 rows=0 loops=1)
Index Cond: (entity_id = ANY
('{189740255,190007498,190419209,190454048,190498132,190589677,190741801,190757628,190792101,190806372,190808350,190823698,190897323,190900401,190900
785,190901387,190905482,190907623,190908303,190909243,190910238,190910853,190912027,190912553,190913738,190915283,190915471,190915715,190916292,190917756,190921775,190924593,190924867,190
937455,190939665,190941432,190946157,190946578,190947077,190948243,190950786,190951360,190951819,190952605,190953382,190965451,190966957,190968387,191015655,191015973,191024345,191027103,
191034716,191035208,191037555,191043749,191044743,191044833,191045116,191045389,191045513,191071499,191072143,191073777,191076136,191078249,191078327,191079035,191080060,191082010,1910821
70,191082475,191088364,191090035,191094751,191095227,191095960,191096543,191097152,191097484,191104018,191116600,191128391,191128416,191128441,191128442,191128458,191128498,191128501,1911
28562,191128563,191128580,191128591,191139565,191145651,191152454,191152613,191155619,191156217,191156687,191156685}'::integer[]))
-> Index Scan using attachment_entity_id_135m_150m_by_entity_id on
attachment_entity_id_135m_150m (cost=0.57..2019.61 rows=23681 width=132)
(actual time=0.125..0.125 rows=0 loops=1)
Index Cond: (entity_id = ANY
('{189740255,190007498,190419209,190454048,190498132,190589677,190741801,190757628,190792101,190806372,190808350,190823698,190897323,190900401,190900
785,190901387,190905482,190907623,190908303,190909243,190910238,190910853,190912027,190912553,190913738,190915283,190915471,190915715,190916292,190917756,190921775,190924593,190924867,190
937455,190939665,190941432,190946157,190946578,190947077,190948243,190950786,190951360,190951819,190952605,190953382,190965451,190966957,190968387,191015655,191015973,191024345,191027103,
191034716,191035208,191037555,191043749,191044743,191044833,191045116,191045389,191045513,191071499,191072143,191073777,191076136,191078249,191078327,191079035,191080060,191082010,1910821
70,191082475,191088364,191090035,191094751,191095227,191095960,191096543,191097152,191097484,191104018,191116600,191128391,191128416,191128441,191128442,191128458,191128498,191128501,1911
28562,191128563,191128580,191128591,191139565,191145651,191152454,191152613,191155619,191156217,191156687,191156685}'::integer[]))
-> Index Scan using attachment_entity_id_150m_165m_by_entity_id on
attachment_entity_id_150m_165m (cost=0.57..5090.89 rows=27380 width=133)
(actual time=0.172..0.172 rows=0 loops=1)
Index Cond: (entity_id = ANY
('{189740255,190007498,190419209,190454048,190498132,190589677,190741801,190757628,190792101,190806372,190808350,190823698,190897323,190900401,190900
785,190901387,190905482,190907623,190908303,190909243,190910238,190910853,190912027,190912553,190913738,190915283,190915471,190915715,190916292,190917756,190921775,190924593,190924867,190
937455,190939665,190941432,190946157,190946578,190947077,190948243,190950786,190951360,190951819,190952605,190953382,190965451,190966957,190968387,191015655,191015973,191024345,191027103,
191034716,191035208,191037555,191043749,191044743,191044833,191045116,191045389,191045513,191071499,191072143,191073777,191076136,191078249,191078327,191079035,191080060,191082010,1910821
70,191082475,191088364,191090035,191094751,191095227,191095960,191096543,191097152,191097484,191104018,191116600,191128391,191128416,191128441,191128442,191128458,191128498,191128501,1911
28562,191128563,191128580,191128591,191139565,191145651,191152454,191152613,191155619,191156217,191156687,191156685}'::integer[]))
-> Index Scan using attachment_entity_id_165m_180m_by_entity_id on
attachment_entity_id_165m_180m (cost=0.57..3104.96 rows=29790 width=132)
(actual time=0.134..0.134 rows=0 loops=1)
Index Cond: (entity_id = ANY
('{189740255,190007498,190419209,190454048,190498132,190589677,190741801,190757628,190792101,190806372,190808350,190823698,190897323,190900401,190900
785,190901387,190905482,190907623,190908303,190909243,190910238,190910853,190912027,190912553,190913738,190915283,190915471,190915715,190916292,190917756,190921775,190924593,190924867,190
937455,190939665,190941432,190946157,190946578,190947077,190948243,190950786,190951360,190951819,190952605,190953382,190965451,190966957,190968387,191015655,191015973,191024345,191027103,
191034716,191035208,191037555,191043749,191044743,191044833,191045116,191045389,191045513,191071499,191072143,191073777,191076136,191078249,191078327,191079035,191080060,191082010,1910821
70,191082475,191088364,191090035,191094751,191095227,191095960,191096543,191097152,191097484,191104018,191116600,191128391,191128416,191128441,191128442,191128458,191128498,191128501,1911
28562,191128563,191128580,191128591,191139565,191145651,191152454,191152613,191155619,191156217,191156687,191156685}'::integer[]))
-> Index Scan using attachment_entity_id_180m_195m_by_entity_id on
attachment_entity_id_180m_195m (cost=0.57..3094.11 rows=25198 width=134)
(actual time=0.063..49.565 rows=258 loops=
1)
Index Cond: (entity_id = ANY
('{189740255,190007498,190419209,190454048,190498132,190589677,190741801,190757628,190792101,190806372,190808350,190823698,190897323,190900401,190900
785,190901387,190905482,190907623,190908303,190909243,190910238,190910853,190912027,190912553,190913738,190915283,190915471,190915715,190916292,190917756,190921775,190924593,190924867,190
937455,190939665,190941432,190946157,190946578,190947077,190948243,190950786,190951360,190951819,190952605,190953382,190965451,190966957,190968387,191015655,191015973,191024345,191027103,
191034716,191035208,191037555,191043749,191044743,191044833,191045116,191045389,191045513,191071499,191072143,191073777,191076136,191078249,191078327,191079035,191080060,191082010,1910821
70,191082475,191088364,191090035,191094751,191095227,191095960,191096543,191097152,191097484,191104018,191116600,191128391,191128416,191128441,191128442,191128458,191128498,191128501,1911
28562,191128563,191128580,191128591,191139565,191145651,191152454,191152613,191155619,191156217,191156687,191156685}'::integer[]))
-> Seq Scan on attachment_entity_id_885m_900m (cost=0.00..1.27 rows=2
width=164) (actual time=0.013..0.013 rows=0 loops=1)
Filter: (entity_id = ANY
('{189740255,190007498,190419209,190454048,190498132,190589677,190741801,190757628,190792101,190806372,190808350,190823698,190897323,190900401,190900785,
190901387,190905482,190907623,190908303,190909243,190910238,190910853,190912027,190912553,190913738,190915283,190915471,190915715,190916292,190917756,190921775,190924593,190924867,1909374
55,190939665,190941432,190946157,190946578,190947077,190948243,190950786,190951360,190951819,190952605,190953382,190965451,190966957,190968387,191015655,191015973,191024345,191027103,1910
34716,191035208,191037555,191043749,191044743,191044833,191045116,191045389,191045513,191071499,191072143,191073777,191076136,191078249,191078327,191079035,191080060,191082010,191082170,1
91082475,191088364,191090035,191094751,191095227,191095960,191096543,191097152,191097484,191104018,191116600,191128391,191128416,191128441,191128442,191128458,191128498,191128501,19112856
2,191128563,191128580,191128591,191139565,191145651,191152454,191152613,191155619,191156217,191156687,191156685}'::integer[]))
Rows Removed by Filter: 2
Planning time: 60.537 ms
Execution time: 51.401 ms
(34 rows)
--
Regards,
Soni Maula Harriz
Soni M <diptatapa@gmail.com> writes:
Hello All, I think I found bug here. Postgres 9.6.9 on Red Hat Enterprise
Linux Server release 7.5 (Maipo).
I think it's a consequence of the limitation in predtest.c that it won't
try to do predicate proofs using ScalarArrayOps with more than 100 array
entries:
/*
* Proof attempts involving large arrays in ScalarArrayOpExpr nodes are
* likely to require O(N^2) time, and more often than not fail anyway.
* So we set an arbitrary limit on the number of array elements that
* we will allow to be treated as an AND or OR clause.
* XXX is it worth exposing this as a GUC knob?
*/
#define MAX_SAOP_ARRAY_SIZE 100
Not a bug, but a tradeoff. You'd be unhappy if the planner spent longer
devising the plan than it saved to eliminate the extra partitions.
regards, tom lane
Greetings,
* Soni M (diptatapa@gmail.com) wrote:
Hello All, I think I found bug here. Postgres 9.6.9 on Red Hat Enterprise
Linux Server release 7.5 (Maipo).
[... the 99 item query ...]
Planning time: 12.969 ms
Execution time: 7.062 ms
[... the 100 item query ...]
Planning time: 60.537 ms
Execution time: 51.401 ms
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
I think it's a consequence of the limitation in predtest.c that it won't
try to do predicate proofs using ScalarArrayOps with more than 100 array
entries:
This is far from the first time we (or, at least, I) have seen
complaints about that particular constant.
/*
* Proof attempts involving large arrays in ScalarArrayOpExpr nodes are
* likely to require O(N^2) time, and more often than not fail anyway.
* So we set an arbitrary limit on the number of array elements that
* we will allow to be treated as an AND or OR clause.
* XXX is it worth exposing this as a GUC knob?
*/
#define MAX_SAOP_ARRAY_SIZE 100
Which certainly makes me think that comment in there might be worth
something- perhaps we should make this a GUC and let users see just what
would end up happening with a different choice. There could certainly
be cases where it'd be better to work it out.
Not a bug, but a tradeoff. You'd be unhappy if the planner spent longer
devising the plan than it saved to eliminate the extra partitions.
While I agree in concept, I'm awful curious how the "simpler" approach
used when we hit the limit resulted in a 5x increase in planning time.
Looks a bit like the extra time required to perform that elimination for
at least a few more items would be saving us cycles somewhere else that
are apparently pretty expensive.
Soni, any chance that this query was the first time all of those
partitions were hit in this backend, meaning there was a lot of time
required to load the relation information for them? What happens if you
prime the backend by running the 100-item case once, and then do 5 runs
of the 99-item and then 5 of the 100-item case?
Thanks!
Stephen
Import Notes
Reply to msg id not found: 19851.1532642641@sss.pgh.pa.usCAAMgDXna_6gdaWKaj8Jc=oP6yAdM+HHktbRbJ28JXPNzbhrw2w@mail.gmail.com | Resolved by subject fallback
After loading the relation information to cache, the best time for 100
items is around Planning time: 2.789 ms, and the best time for the 101 item
is around Planning time: 3.159 ms.
On Fri, Jul 27, 2018 at 9:23 AM, Stephen Frost <sfrost@snowman.net> wrote:
Greetings,
* Soni M (diptatapa@gmail.com) wrote:
Hello All, I think I found bug here. Postgres 9.6.9 on Red Hat Enterprise
Linux Server release 7.5 (Maipo).[... the 99 item query ...]
Planning time: 12.969 ms
Execution time: 7.062 ms[... the 100 item query ...]
Planning time: 60.537 ms
Execution time: 51.401 ms* Tom Lane (tgl@sss.pgh.pa.us) wrote:
I think it's a consequence of the limitation in predtest.c that it won't
try to do predicate proofs using ScalarArrayOps with more than 100 array
entries:This is far from the first time we (or, at least, I) have seen
complaints about that particular constant./*
* Proof attempts involving large arrays in ScalarArrayOpExpr nodes are
* likely to require O(N^2) time, and more often than not fail anyway.
* So we set an arbitrary limit on the number of array elements that
* we will allow to be treated as an AND or OR clause.
* XXX is it worth exposing this as a GUC knob?
*/
#define MAX_SAOP_ARRAY_SIZE 100Which certainly makes me think that comment in there might be worth
something- perhaps we should make this a GUC and let users see just what
would end up happening with a different choice. There could certainly
be cases where it'd be better to work it out.Not a bug, but a tradeoff. You'd be unhappy if the planner spent longer
devising the plan than it saved to eliminate the extra partitions.While I agree in concept, I'm awful curious how the "simpler" approach
used when we hit the limit resulted in a 5x increase in planning time.
Looks a bit like the extra time required to perform that elimination for
at least a few more items would be saving us cycles somewhere else that
are apparently pretty expensive.Soni, any chance that this query was the first time all of those
partitions were hit in this backend, meaning there was a lot of time
required to load the relation information for them? What happens if you
prime the backend by running the 100-item case once, and then do 5 runs
of the 99-item and then 5 of the 100-item case?Thanks!
Stephen
--
Regards,
Soni Maula Harriz
On 2018/07/27 11:23, Stephen Frost wrote:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
/*
* Proof attempts involving large arrays in ScalarArrayOpExpr nodes are
* likely to require O(N^2) time, and more often than not fail anyway.
* So we set an arbitrary limit on the number of array elements that
* we will allow to be treated as an AND or OR clause.
* XXX is it worth exposing this as a GUC knob?
*/
#define MAX_SAOP_ARRAY_SIZE 100Which certainly makes me think that comment in there might be worth
something- perhaps we should make this a GUC and let users see just what
would end up happening with a different choice. There could certainly
be cases where it'd be better to work it out.Not a bug, but a tradeoff. You'd be unhappy if the planner spent longer
devising the plan than it saved to eliminate the extra partitions.While I agree in concept, I'm awful curious how the "simpler" approach
used when we hit the limit resulted in a 5x increase in planning time.
Looks a bit like the extra time required to perform that elimination for
at least a few more items would be saving us cycles somewhere else that
are apparently pretty expensive.
The "simpler" approach in this case is predtest.c not pruning partitions
at all, which results in the planner creating a scan plan for every
partition, instead of just one in the case where the IN(..) list was
within the limit for the pruning to occur.
Fwiw, on the tiny machine I work on, attempting pruning with 100-item list
takes way longer than it takes the planner to just forget about pruning
and create a plan for all partitions. But that's just about the planning
time.
\d+ lt
Partition key: LIST (b)
Partitions: lt_1 FOR VALUES IN (1),
lt_10 FOR VALUES IN (10),
lt_2 FOR VALUES IN (2),
lt_3 FOR VALUES IN (3),
lt_4 FOR VALUES IN (4),
lt_5 FOR VALUES IN (5),
lt_6 FOR VALUES IN (6),
lt_7 FOR VALUES IN (7),
lt_8 FOR VALUES IN (8),
lt_9 FOR VALUES IN (9)
-- 100-item list allowing pruning to occur
explain analyze select * from lt where b in
(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1);
QUERY PLAN
─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
───────────────────────────────────────────────────────────────────────────────────
Append (cost=0.29..437.60 rows=5085 width=8) (actual time=0.096..138.388
rows=10000 loops=1)
-> Index Scan using lt_1_b_idx on lt_1 (cost=0.29..437.60 rows=5085
width=8) (actual time=0.087..56.177 rows=10000 loops=1)
Index Cond: (b = ANY
('{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::integer[]))
Planning time: 24.349 ms
Execution time: 179.944 ms
(5 rows)
-- 101-item list disabling pruning
explain analyze select * from lt where b in
(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1);
QUERY PLAN
─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
─────────────────────────────────────────────────────────────────────────────────────
Append (cost=0.29..4463.78 rows=51360 width=8) (actual
time=0.172..141.214 rows=10000 loops=1)
-> Index Scan using lt_1_b_idx on lt_1 (cost=0.29..438.78 rows=5136
width=8) (actual time=0.153..55.516 rows=10000 loops=1)
Index Cond: (b = ANY
('{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::integer[]))
-> Index Scan using lt_2_b_idx on lt_2 (cost=0.29..442.78 rows=5136
width=8) (actual time=0.091..0.091 rows=0 loops=1)
Index Cond: (b = ANY
('{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::integer[]))
-> Index Scan using lt_3_b_idx on lt_3 (cost=0.29..446.78 rows=5136
width=8) (actual time=0.090..0.090 rows=0 loops=1)
Index Cond: (b = ANY
('{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::integer[]))
[ Index Scan nodes of remaining patitions ]
Planning time: 7.146 ms
Execution time: 184.115 ms
(23 rows)
Oddly, that seems exactly the opposite of what OP is seeing.
Thanks,
Amit
On 2018/07/27 17:57, Amit Langote wrote:
Fwiw, on the tiny machine I work on, attempting pruning with 100-item list
takes way longer than it takes the planner to just forget about pruning
and create a plan for all partitions. But that's just about the planning
time.
[ ... ]
-- 101-item list disabling pruning
explain analyze select * from lt where b in
(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1);QUERY PLAN
─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
─────────────────────────────────────────────────────────────────────────────────────
Append (cost=0.29..4463.78 rows=51360 width=8) (actual
time=0.172..141.214 rows=10000 loops=1)
-> Index Scan using lt_1_b_idx on lt_1 (cost=0.29..438.78 rows=5136
width=8) (actual time=0.153..55.516 rows=10000 loops=1)
Index Cond: (b = ANY
('{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::integer[]))
-> Index Scan using lt_2_b_idx on lt_2 (cost=0.29..442.78 rows=5136
width=8) (actual time=0.091..0.091 rows=0 loops=1)
Index Cond: (b = ANY
('{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::integer[]))
-> Index Scan using lt_3_b_idx on lt_3 (cost=0.29..446.78 rows=5136
width=8) (actual time=0.090..0.090 rows=0 loops=1)
Index Cond: (b = ANY
('{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::integer[]))[ Index Scan nodes of remaining patitions ]
Planning time: 7.146 ms
Execution time: 184.115 ms
(23 rows)Oddly, that seems exactly the opposite of what OP is seeing.
When I tried this again, the planning time for the 101-item list case shot
up. Not sure what had gone wrong in the previous try.
explain analyze select * from lt where b in
(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1);
QUERY PLAN
─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
─────────────────────────────────────────────────────────────────────────────────────
Append (cost=0.29..2021.75 rows=10009 width=8) (actual
time=0.104..137.582 rows=10000 loops=1)
-> Index Scan using lt_1_b_idx on lt_1 (cost=0.29..403.78 rows=10000
width=8) (actual time=0.094..55.329 rows=10000 loops=1)
Index Cond: (b = ANY
('{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::integer[]))
-> Index Scan using lt_2_b_idx on lt_2 (cost=0.29..177.55 rows=1
width=8) (actual time=0.093..0.093 rows=0 loops=1)
Index Cond: (b = ANY
('{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::integer[]))
-> Index Scan using lt_3_b_idx on lt_3 (cost=0.29..173.55 rows=1
width=8) (actual time=0.045..0.045 rows=0 loops=1)
Index Cond: (b = ANY
('{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::integer[]))
[ Index Scan nodes of remaining patitions ]
Planning time: 63.100 ms
Execution time: 178.995 ms
(23 rows)
So yes, attempting pruning would've helped the 101-item case just as much
as it does the 100-item case.
Thanks,
Amit