BUG #18921: The larger the value of max_parallel_workers_per_gather, the longer the SQL execution time.
The following bug has been logged on the website:
Bug reference: 18921
Logged by: hongjun xiao
Email address: xiaohongjun@stu.xidian.edu.cn
PostgreSQL version: 17.4
Operating system: Linux-Ubuntu 20.04.6 LTS
Description:
-- performance bug for query: SELECT ALL t0.c0, t1.c0 FROM ONLY t0 RIGHT
OUTER JOIN ONLY t1 ON
(((((t0.c0)+(((t0.c0)+(t1.c0)))))*(((((t0.c0)+(t1.c0)))+(t1.c0))))) BETWEEN
(t1.c0) AND
(((((t0.c0)+(((t0.c0)+('[-1506176850,461487478)'::int4range)))))+(((t0.c0)+((('[74602774,1501850467)'::int4range)+(t0.c0)))))))
JOIN (SELECT (+ (+ (+ (-1957636332))))::INT FROM ONLY t2 as sub0_t2, ONLY
t1 as sub0_t1, ONLY t0 as sub0_t0) AS sub0 ON
((t0.c0)>(((((t0.c0)*((('(-2011769719,907690705)'::int4range)*(t0.c0)))))+(t0.c0))))
WHERE ((t1.c0)&&(t1.c0)) GROUP BY t0.c0, t1.c0 HAVING (t0.c0) BETWEEN
(t0.c0) AND (t1.c0); --max_parallel_workers_per_gather=2
-- set max_parallel_workers_per_gather=8; The larger the value, the slower
the query.
-- set max_parallel_workers_per_gather=0; Disabling parallelism actually
speeds up the query.
-- how to repeat：
-- version: 17.4
\c test;
DROP DATABASE IF EXISTS database0;
CREATE DATABASE database0 ;
\c database0;
CREATE UNLOGGED TABLE t0(c0 int4range ) WITH
(autovacuum_freeze_max_age=1025036036, parallel_workers=603,
autovacuum_vacuum_cost_limit=1667, autovacuum_vacuum_cost_delay=17,
autovacuum_freeze_min_age=264236879, autovacuum_enabled=0,
autovacuum_vacuum_scale_factor=1, autovacuum_analyze_scale_factor=1);
CREATE TEMPORARY TABLE IF NOT EXISTS t1(c0 int4range UNIQUE CHECK
(pg_jit_available()) NOT NULL PRIMARY KEY) INHERITS(t0) WITH
(autovacuum_freeze_table_age=218187874, autovacuum_freeze_min_age=293076738,
autovacuum_vacuum_scale_factor=0, autovacuum_enabled=0,
autovacuum_freeze_max_age=415583756) ON COMMIT DROP ;
CREATE UNLOGGED TABLE IF NOT EXISTS t1(LIKE t0);
CREATE UNLOGGED TABLE t2(LIKE t0);
CREATE TEMP TABLE IF NOT EXISTS t3(LIKE t0);
CREATE TEMP TABLE t4(c0 FLOAT ) ON COMMIT PRESERVE ROWS ;
INSERT INTO t1(c0) VALUES('[-375401873,2116247747)'::int4range);
INSERT INTO t2(c0) VALUES('(-1953761777,1783088849)'::int4range);
INSERT INTO t1(c0) VALUES('[-219501997,1759101807)'::int4range);
INSERT INTO t4(c0) VALUES(0.4323197),
(lower((((((('(676258392,1058049539)'::int4range)-('[780787480,1647544086]'::int4range)))-((('(407360024,1138643640]'::int4range)-('[-1306492953,604968998)'::int4range)))))-((((('(1050009536,1923757606)'::int4range)*('[-220579913,104374701)'::int4range)))-((('[779508428,931911755)'::int4range)+('(475209074,1949084465]'::int4range)))))))),
(lower((((('(862996776,979220706)'::int4range)*('(-2112929550,46284867]'::int4range)))*((('[1563095881,1768760596)'::int4range)-('(-1914315998,328659252)'::int4range)))))),
(0.0024587153), (0.10740306);
INSERT INTO t1(c0) VALUES('[-1825448734,1762494049)'::int4range),
('(-1710737445,-790115247]'::int4range),
((((((('2030078139')::VARCHAR(96))::int4range)-('(897477404,1675282233]'::int4range)))+(((((((NULL)*('[655074312,736625435]'::int4range)))-((('(-1686016498,136198942]'::int4range)-('(556558825,1947251463)'::int4range)))))+((((('[-1525407757,112948262)'::int4range)*('[-872915674,1022534947)'::int4range)))+((('(-185021299,-125996189)'::int4range)-('(-1838582838,-1167225735]'::int4range)))))))));
INSERT INTO t1(c0) VALUES('(-825991654,106157799)'::int4range),
('[-1209370907,1527598059]'::int4range),
('[104374701,131171411]'::int4range);
INSERT INTO t0(c0) VALUES('(-1689729305,835517253]'::int4range);
INSERT INTO t3(c0) OVERRIDING SYSTEM VALUE
VALUES('(-1825147975,-267637793]'::int4range);
INSERT INTO t3(c0) VALUES('(-1427373615,902727234)'::int4range) ON CONFLICT
DO NOTHING;
INSERT INTO t3(c0)
VALUES((((((((('[-1468317609,2079792173)'::int4range)+('(-1692728559,-1091192164)'::int4range)))*((('[856444784,1475443820]'::int4range)*('(-884047914,1783088849)'::int4range)))))*((('(-1692728559,-756846307]'::int4range)-((('(-1493164455,26478725)'::int4range)+('(-1434415315,-79229296)'::int4range)))))))-((((((('(-1429278115,-884047914]'::int4range)*('(-193044894,779508428)'::int4range)))+((('(-51759413,2136481993)'::int4range)-('(-965113301,1693721815]'::int4range)))))*((((('(-522944972,791285225)'::int4range)*('(902727234,1869171201]'::int4range)))+((('(-1575347535,1143168703)'::int4range)+('(890263670,1854694701)'::int4range)))))))));
INSERT INTO t4(c0) VALUES(-
(num_nulls(((B'1101111011010010100101000000001')||(B'1111111111111111111111111111111111101110101101110110111001011110')))));
INSERT INTO t4(c0) VALUES(1.11673408E9);
INSERT INTO t1(c0) VALUES('(-745830180,-551837592)'::int4range);
INSERT INTO t0(c0) VALUES('(394782686,1503522807)'::int4range);
INSERT INTO t1(c0) VALUES('(-1468504248,902727234]'::int4range);
INSERT INTO t4(c0) OVERRIDING SYSTEM VALUE VALUES(0.09627435) ON CONFLICT
DO NOTHING;
INSERT INTO t4(c0) VALUES(0.50430954);
INSERT INTO t3(c0) VALUES('(-2065028308,-1851833532]'::int4range);
INSERT INTO t3(c0) OVERRIDING USER VALUE
VALUES((((((((('[-79124606,65075550]'::int4range)+(NULL)))*((('(-491149429,1907262251]'::int4range)+('[-1373500427,1404564559)'::int4range)))))*((((('(470778662,1329318809)'::int4range)*('(-146413565,1567632484]'::int4range)))+((('[-1799499932,1644053075)'::int4range)*('(-1851833532,1561600525)'::int4range)))))))+((((((('[-1980606865,1302977050]'::int4range)*('(-811252607,597984597]'::int4range)))+((('(-1674789562,1544420946)'::int4range)*('[-1563436192,2087940293]'::int4range)))))+((((('[-922380343,1494216837]'::int4range)-('[-91456065,1987726027)'::int4range)))-((('(-719466207,791285225)'::int4range)+('(-1350640790,-656911113)'::int4range))))))))),
('(585596765,1173397953]'::int4range),
(((range_merge('(339110547,1467320415]'::int4range,
(((('[1529663374,1957697129]'::int4range)*('(-1576228754,187370554]'::int4range)))+((('(-1148064191,-861189187)'::int4range)-('[-472933796,-289968546)'::int4range))))))+((((((('(501574668,1923757606)'::int4range)+('[791285225,1783088849)'::int4range)))*((('[-237241581,614450194)'::int4range)*('[214964467,583669213)'::int4range)))))*((((('[-1621855145,-713562053]'::int4range)+('[-1593887397,339946881)'::int4range)))*((('[-1568204533,655074312]'::int4range)+('(-655142316,625611997]'::int4range)))))))));
INSERT INTO t2(c0) OVERRIDING USER VALUE
VALUES('(-602326816,-289968546)'::int4range);
INSERT INTO t1(c0) VALUES('(1561600525,1985633599]'::int4range);
REINDEX TABLE t2;
INSERT INTO t3(c0) VALUES('[1159081989,1434967532]'::int4range);
INSERT INTO t0(c0) VALUES('[-1108294490,1]'::int4range);
INSERT INTO t1(c0) VALUES('(-1178593854,-307921022)'::int4range);
INSERT INTO t1(c0) VALUES('[1418055331,1442224862]'::int4range);
INSERT INTO t2(c0) VALUES('(1060792178,1402163932)'::int4range);
INSERT INTO t3(c0) VALUES('[763803682,1781240382]'::int4range);
UPDATE t1 SET c0=(t1.c0) WHERE (t1.c0) BETWEEN
(((t1.c0)*(((((t1.c0)*(NULL)))-(((t1.c0)+(t1.c0))))))) AND (t1.c0);
INSERT INTO t2(c0) VALUES('[655074312,2110518674]'::int4range);
INSERT INTO t2(c0) VALUES('(-2010577729,-1414126774]'::int4range),
('(-516256457,1438654584)'::int4range),
('(-1323692936,-317272010)'::int4range);
INSERT INTO t1(c0) VALUES('[13373266,1047580776]'::int4range);
DISCARD TEMPORARY;
SET SESSION geqo_seed=0.5;
INSERT INTO t1(c0) VALUES('(-1590108845,2145047963]'::int4range);
INSERT INTO t1(c0) VALUES('[1160608725,2082905377)'::int4range) ON CONFLICT
DO NOTHING;
RESET ALL;
INSERT INTO t1(c0) OVERRIDING USER VALUE
VALUES('[-179033208,1388760613)'::int4range);
INSERT INTO t0(c0) VALUES('(-1035372070,882707750)'::int4range),
('[-1885150039,-1689729305)'::int4range),
('[-2010577729,-884047914)'::int4range);
INSERT INTO t0(c0) VALUES('[-1914362340,-829870294)'::int4range);
ANALYZE( SKIP_LOCKED) t1(c0);
INSERT INTO t1(c0) VALUES('[-1825448734,2072543093)'::int4range);
INSERT INTO t1(c0) OVERRIDING SYSTEM VALUE
VALUES('[1116734040,1192386784)'::int4range),
('(-1953142917,-1936359069)'::int4range),
('[-1551566505,1531963892]'::int4range);
INSERT INTO t0(c0) VALUES('[-1980249566,1300153964)'::int4range);
INSERT INTO t1(c0) VALUES('[-1043684853,1121868562)'::int4range);
INSERT INTO t1(c0) VALUES('(-1404586625,-124703743)'::int4range);
INSERT INTO t0(c0) VALUES('[-1546956305,-634457006]'::int4range);
INSERT INTO t2(c0) VALUES('[65075550,1923757606]'::int4range);
CLUSTER ;
INSERT INTO t1(c0) VALUES('[-1892164595,979220706)'::int4range);
INSERT INTO t0(c0) OVERRIDING USER VALUE VALUES(NULL);
INSERT INTO t1(c0)
VALUES((((((((('(382960487,1315906101]'::int4range)-(NULL)))+((('[26608815,2065345115)'::int4range)+('(791285225,1335279059)'::int4range)))))-((((('(-177963710,0]'::int4range)+('(-1675509796,1820423065)'::int4range)))+((('[-1763487712,-1545841198)'::int4range)-(NULL)))))))*((((((('[-551837592,902727234)'::int4range)*('[97945055,570910979]'::int4range)))*((('(-679767528,863827555)'::int4range)*('(-1847673300,307471603)'::int4range)))))-((((('[-344786590,650625746]'::int4range)+('[26508875,127650334]'::int4range)))-((('(-1908320054,-26421273)'::int4range)-('[569083696,1881392523)'::int4range)))))))));
INSERT INTO t2(c0) VALUES('(-119485513,1923757606)'::int4range);
INSERT INTO t0(c0) VALUES('[-1407809739,686619603]'::int4range);
INSERT INTO t0(c0) VALUES('[-377367275,885836932)'::int4range);
INSERT INTO t1(c0) VALUES('[1103290545,2008949842]'::int4range);
CREATE INDEX i0 ON ONLY
t2((((((((((t2.c0)+(t2.c0)))*(((t2.c0)-(t2.c0)))))-(t2.c0)))-(((t2.c0)*(t2.c0)))))
ASC NULLS FIRST);
INSERT INTO t1(c0) VALUES('(104374701,395972738)'::int4range);
RESET ALL;
INSERT INTO t0(c0) VALUES('(-1130721019,-74034965]'::int4range);
INSERT INTO t0(c0) OVERRIDING SYSTEM VALUE
VALUES('(65075550,967855890]'::int4range) ON CONFLICT DO NOTHING;
INSERT INTO t0(c0) VALUES('[702744487,702744487)'::int4range),
((((((((('(-1019654343,-502599296]'::int4range)*('(-1408534251,1370986230]'::int4range)))*('(-1292889477,1622300133]'::int4range)))*((((('[414141999,2103599759)'::int4range)-('[-1917412292,-1433137914]'::int4range)))-((('[-1816158970,489794267)'::int4range)*('(-792690963,-267637793)'::int4range)))))))*((((((('[284028045,1854694701]'::int4range)*('[-944118751,246311111]'::int4range)))*('(-1564124828,1883376867]'::int4range)))*((((('[-1924508063,730874315]'::int4range)*('[1431299026,1845273676]'::int4range)))*((('(269867152,1989621524)'::int4range)-('(-289968546,655074312)'::int4range))))))))),
('[-185021299,1548299184]'::int4range);
INSERT INTO t0(c0) VALUES('(-1987812072,-1738710491)'::int4range),
('(-789400269,890263670]'::int4range),
('(-1692728559,-378750532]'::int4range);
INSERT INTO t1(c0) VALUES('[-1540313667,221617625]'::int4range);
INSERT INTO t2(c0) VALUES('[-1464369314,-1321960161]'::int4range);
INSERT INTO t2(c0) VALUES('(-852278216,-809665578)'::int4range);
DISCARD TEMPORARY;
INSERT INTO t0(c0) VALUES('(-910352205,1503522807]'::int4range);
INSERT INTO t2(c0) VALUES('[343634934,1121868562]'::int4range);
INSERT INTO t1(c0) VALUES(DEFAULT);
INSERT INTO t1(c0) VALUES('(-458802106,1845273676)'::int4range);
INSERT INTO t1(c0) VALUES('(-2006658271,148328270]'::int4range) ON CONFLICT
DO NOTHING;
INSERT INTO t2(c0) VALUES('[-309863833,1253608779)'::int4range);
INSERT INTO t1(c0) VALUES('[-1454759874,-741535213)'::int4range);
INSERT INTO t0(c0) VALUES('[-233600639,1503522807]'::int4range);
INSERT INTO t0(c0) VALUES('[-2131042553,1160608725]'::int4range);
INSERT INTO t1(c0) VALUES('(-705412962,1221592635)'::int4range);
INSERT INTO t2(c0) VALUES('(-970808473,791285225)'::int4range);
INSERT INTO t1(c0) VALUES('[-2065028308,-3108442)'::int4range);
INSERT INTO t1(c0) VALUES('(-1291823785,205553179)'::int4range);
INSERT INTO t2(c0)
VALUES((('(860344052,915616311)'::int4range)+((('(-1038847494,2084083707]'::int4range)*((((('[-286193595,2066023229]'::int4range)+('(-169157914,1155743469]'::int4range)))+((('(-2055504552,1525549402)'::int4range)+('(-1433604987,-695288998)'::int4range)))))))));
INSERT INTO t1(c0) VALUES('(-2109151464,-1062111449]'::int4range);
INSERT INTO t1(c0) VALUES('[-29184910,492167848]'::int4range);
INSERT INTO t1(c0) VALUES('[-185021299,2079975772]'::int4range);
INSERT INTO t0(c0) VALUES('(-1399318080,-511079214]'::int4range),
('(399227874,428320386)'::int4range),
('[-1873733443,1103290545)'::int4range);
INSERT INTO t1(c0) OVERRIDING USER VALUE
VALUES('(64360837,640681803)'::int4range);
INSERT INTO t0(c0) VALUES('(-213326504,2072543093)'::int4range);
INSERT INTO t0(c0)
VALUES((((((((('(-2006658271,1074933999)'::int4range)*('(-1980249566,1]'::int4range)))+('[-1289684523,1976762962]'::int4range)))*((((('(-1414126774,-685855702)'::int4range)-('(279223185,1202774987]'::int4range)))*((('[1237929711,1894959161]'::int4range)*('[1116734040,2129065089]'::int4range)))))))*((((((('[-169157914,-121801870]'::int4range)*('(-730535791,1155743469]'::int4range)))*('[-285717005,655861568]'::int4range)))*(((((NULL)+('[-1540313667,-47791725]'::int4range)))+((('(-443009828,1074933999)'::int4range)-('[-519825805,-315752793]'::int4range)))))))));
INSERT INTO t1(c0) VALUES('(-631633306,1896758135)'::int4range);
DELETE FROM t0;
INSERT INTO t0(c0) VALUES('(-147132764,-127492160)'::int4range);
INSERT INTO t0(c0) VALUES('[-1292889477,999201816]'::int4range);
INSERT INTO t1(c0) VALUES('(211236537,890263670]'::int4range);
INSERT INTO t0(c0)
VALUES((((((((('[268813585,1514687493]'::int4range)*('(-1454759874,-1399260484]'::int4range)))-((('[-1652028540,-1454759874)'::int4range)-('[-852278216,303609973)'::int4range)))))*((((('(1584110,698995561)'::int4range)*('(-1709072396,423043666]'::int4range)))+((('[-1883114321,234332985)'::int4range)*('[389969571,1397516920)'::int4range)))))))+((((((('(-811252607,1192386784]'::int4range)*('(-698161361,902727234)'::int4range)))+((('[-1905321282,1912623841)'::int4range)*('[-1633146168,751019131]'::int4range)))))+((((('(-2144411908,779508428)'::int4range)*('[-1105951771,1116734040]'::int4range)))*('(-1969026090,1593699439]'::int4range)))))));
INSERT INTO t0(c0) VALUES('[-2000272950,2072543093]'::int4range);
INSERT INTO t0(c0) VALUES('[1778369222,1894317772)'::int4range);
INSERT INTO t1(c0) VALUES('(-220579913,-185287139)'::int4range);
RESET ROLE;
INSERT INTO t0(c0) VALUES('(652631414,1397331489)'::int4range);
INSERT INTO t2(c0) VALUES('[-934153351,1358325788)'::int4range);
INSERT INTO t0(c0) VALUES('[-570521696,-249916003]'::int4range) ON CONFLICT
DO NOTHING;
INSERT INTO t0(c0) VALUES('[-1280774871,-486818586)'::int4range) ON CONFLICT
DO NOTHING;
INSERT INTO t2(c0) VALUES('[-1353692124,1121868562]'::int4range) ON CONFLICT
DO NOTHING;
INSERT INTO t2(c0) OVERRIDING USER VALUE
VALUES((((((((('(1494492205,1907262251)'::int4range)+('[-1269704267,1757624882)'::int4range)))+((('[-1843798602,803953278)'::int4range)+('(-849167365,2007816778]'::int4range)))))-((((('(-220579913,1505731903)'::int4range)*('[-1139551514,-1038215931)'::int4range)))+((('[-96431376,1103290545]'::int4range)-('[-2040186171,1963350296]'::int4range)))))))*((((((('(-1079670983,902727234]'::int4range)+('[-789992025,1854694701)'::int4range)))*((('(-627347913,1074933999]'::int4range)*('[-1038215931,807646713)'::int4range)))))*((((('(-626074091,1221592635)'::int4range)*('[882707750,917760259)'::int4range)))-('[800332348,1783088849)'::int4range))))))),
('(-443009828,-138925283]'::int4range),
('(101389110,1354148610)'::int4range);
INSERT INTO t2(c0)
VALUES((((((((('[1162090325,1531963892]'::int4range)-('(-861189187,1644053075)'::int4range)))*((('(-137205934,967887459)'::int4range)+('[-2076459044,303883756)'::int4range)))))+(CAST((('[-50923049,1447604446)'::int4range)*('[-1674789562,-1407997686)'::int4range))
AS
int4range))))-((((((('(1221592635,1449844011]'::int4range)-('(-231887490,925418118]'::int4range)))+((('[-1454759874,356370687]'::int4range)+('(-988242091,1397331489]'::int4range)))))+((((('(390314635,1531963892]'::int4range)+('(-2010577729,1006171394)'::int4range)))+((('(1160608725,1230145700)'::int4range)-('(1184964320,1757624882)'::int4range)))))))));
INSERT INTO t1(c0) VALUES('[842822139,1263868836]'::int4range);
REINDEX DATABASE CONCURRENTLY database0;
INSERT INTO t2(c0) VALUES('[-1921626003,2123675987]'::int4range);
INSERT INTO t0(c0) OVERRIDING SYSTEM VALUE
VALUES('(-2006658271,2072543093]'::int4range);
INSERT INTO t2(c0) VALUES('(-2112929550,475209074)'::int4range);
INSERT INTO t2(c0) VALUES('[-1376320658,1116734040)'::int4range);
INSERT INTO t0(c0) VALUES('(1418055331,1875465337)'::int4range);
INSERT INTO t0(c0) VALUES('(-347060602,979220706]'::int4range);
INSERT INTO t2(c0) VALUES('(-1237060132,1451493023)'::int4range);
INSERT INTO t0(c0) OVERRIDING SYSTEM VALUE
VALUES('[-1827056462,844519721]'::int4range) ON CONFLICT DO NOTHING;
INSERT INTO t2(c0) VALUES('[-155675869,192373182)'::int4range);
INSERT INTO t1(c0) OVERRIDING SYSTEM VALUE
VALUES('(-88350183,1117680170]'::int4range);
INSERT INTO t1(c0) VALUES('(-163338947,1604226130)'::int4range);
INSERT INTO t2(c0) VALUES('[-2056579324,1822778620)'::int4range);
INSERT INTO t2(c0) VALUES('[64360837,913923497)'::int4range);
INSERT INTO t0(c0) VALUES('(-1492902036,396546757]'::int4range);
INSERT INTO t0(c0) VALUES('(1018398213,1947251463]'::int4range);
INSERT INTO t0(c0)
VALUES((((((((('(-1316652468,557567434)'::int4range)*('(-893372384,1020832517)'::int4range)))+((('(442608011,1776839289]'::int4range)+('(1221592635,1854694701]'::int4range)))))*('[-1689729305,1527598059]'::int4range)))+((((((('[-1916357374,398062271)'::int4range)+('[-2010577729,1455817086)'::int4range)))*((('[292171751,779508428)'::int4range)-(NULL)))))*((((('(354967304,475209074]'::int4range)-('(-1467978154,475209074]'::int4range)))+((('[93057572,1138643640]'::int4range)*('(-1454759874,740787950)'::int4range)))))))));
RESET ROLE;
INSERT INTO t0(c0)
VALUES((((((((('(437286915,1604815536]'::int4range)-('[5711819,649520896]'::int4range)))+((('[-2065028308,-1880493904)'::int4range)*('(28922331,1531963892]'::int4range)))))+((((('(-618769767,996309487]'::int4range)+('[-1019186570,-50972913]'::int4range)))*((('(1923757606,2072543093]'::int4range)*('[-659062333,-415867250)'::int4range)))))))+((((((('[-2036367693,-811252607)'::int4range)*('(655074312,1854694701)'::int4range)))+((('[-942524775,1103290545)'::int4range)*('(775067973,1647471039]'::int4range)))))*((((('(-1466283411,1732983047]'::int4range)-('[-1712126189,678776460)'::int4range)))*((('(1,280402784)'::int4range)-('(-1399260484,326541442)'::int4range)))))))));
INSERT INTO t2(c0) VALUES('(-1339194234,1854694701)'::int4range);
DISCARD ALL;
INSERT INTO t0(c0) VALUES('[-497869211,-40023151)'::int4range);
INSERT INTO t2(c0) VALUES('[-1599138488,-1585525081]'::int4range);
UPDATE t2 SET c0=(t2.c0) WHERE ((((((((t2.c0)-(t2.c0)))-(t2.c0)))IS NOT
DISTINCT FROM(((((t2.c0)-(t2.c0)))*(t2.c0)))))OR((CAST(0.44308254 AS MONEY))
IN ((-1.68972928E9)::MONEY)));
INSERT INTO t2(c0) VALUES('(-2101654602,-755613011]'::int4range);
INSERT INTO t1(c0) VALUES('[-162409408,1067618464]'::int4range),
('(-1399260484,1418055331]'::int4range),
('[-485795104,-260502385)'::int4range), ('[-7027260,658725266]'::int4range),
('(-1994604501,254390911]'::int4range);
INSERT INTO t0(c0) VALUES('[-410195901,1598186744]'::int4range);
INSERT INTO t1(c0) VALUES('(-443009828,1981826766]'::int4range);
INSERT INTO t1(c0) VALUES('(224890179,285630735]'::int4range);
INSERT INTO t0(c0)
VALUES(((((((range_merge('(-1469736415,351322118)'::int4range,
'[1616909582,1644053075)'::int4range))+(((NULL)+('[1218893526,1907262251)'::int4range)))))*((((('(-1007975282,947736703]'::int4range)+('[65075550,1293274226)'::int4range)))+(((NULL)+('(-210643289,59294928]'::int4range)))))))*((((((('(-2006658271,699415472]'::int4range)*('(1265533497,1653791962]'::int4range)))*((('(-1476169638,-789400269]'::int4range)*('[-1372177100,-1336875796)'::int4range)))))*((((('(-1252696395,825985689)'::int4range)-('[1537703572,2090520020]'::int4range)))*((('(-1350640790,4075218)'::int4range)-('[-1094456451,500984745]'::int4range))))))))),
((('[882707750,1757624882)'::int4range)*('[490610389,702744487)'::int4range))),
('(2118279698,2142442120)'::int4range) ON CONFLICT DO NOTHING;
INSERT INTO t2(c0) VALUES('[885556919,1142355993]'::int4range);
CLUSTER ;
INSERT INTO t1(c0) VALUES('[1,1585507327)'::int4range);
INSERT INTO t0(c0) VALUES('(1305227940,1771701650)'::int4range);
INSERT INTO t1(c0) VALUES('[-1350640790,777904737]'::int4range),
('[-134113691,785906048]'::int4range),
('(1367425891,1500928828]'::int4range);
LISTEN test;
INSERT INTO t1(c0) OVERRIDING USER VALUE
VALUES('(-1566859673,792327210]'::int4range);
INSERT INTO t0(c0) VALUES('[-1980249566,-1509308814)'::int4range);
INSERT INTO t2(c0) VALUES('[-2143556114,5711819]'::int4range);
INSERT INTO t0(c0) VALUES('[1534610859,2116666117]'::int4range);
INSERT INTO t0(c0) VALUES('[64360837,1163192592]'::int4range);
INSERT INTO t0(c0) VALUES('[1036139898,1728512091)'::int4range);
INSERT INTO t2(c0) VALUES('(-2007047039,358080080)'::int4range),
('[-2028502130,1907722047)'::int4range),
('(-1673166412,2079792173]'::int4range);
INSERT INTO t1(c0) VALUES('[758503988,1176174059]'::int4range) ON CONFLICT
DO NOTHING;
INSERT INTO t1(c0) VALUES('[-968597187,1561600525]'::int4range);
INSERT INTO t0(c0) VALUES('(65075550,1852078972)'::int4range);
INSERT INTO t1(c0) VALUES('[475209074,547311387]'::int4range);
INSERT INTO t2(c0) VALUES('(-2105142140,-896216695]'::int4range);
INSERT INTO t1(c0) VALUES('[-991948094,658026289)'::int4range);
INSERT INTO t0(c0) VALUES('[-267637793,1907262251)'::int4range),
('(-1692728559,1353430269]'::int4range),
('[96237011,309097898]'::int4range);
INSERT INTO t2(c0) VALUES('[-1230719789,1103290545)'::int4range);
DROP INDEX IF EXISTS i0 CASCADE;
INSERT INTO t2(c0) VALUES('[-1659185037,760148147)'::int4range);
INSERT INTO t2(c0) VALUES('(-1113059815,-277024940)'::int4range);
INSERT INTO t2(c0) VALUES('(-1926682659,224890179]'::int4range) ON CONFLICT
DO NOTHING;
INSERT INTO t0(c0) VALUES('[-1941992756,1192386784)'::int4range) ON CONFLICT
DO NOTHING;
INSERT INTO t2(c0) VALUES('(-1799919924,1718148743]'::int4range),
('[-1479583885,263340678]'::int4range),
('(-2112929550,-619337717)'::int4range);
INSERT INTO t2(c0) VALUES('(-1427373615,-639341197]'::int4range);
INSERT INTO t2(c0) VALUES('[-272271714,1604815536]'::int4range);
INSERT INTO t0(c0) VALUES('(-2006658271,-1043684853]'::int4range);
INSERT INTO t2(c0) VALUES('(802243105,1730392801)'::int4range);
INSERT INTO t2(c0) OVERRIDING SYSTEM VALUE
VALUES('(-1689729305,-789400269]'::int4range);
INSERT INTO t2(c0) VALUES('(-1689729305,-1564124828]'::int4range);
INSERT INTO t1(c0) VALUES('[-1469048007,1604815536)'::int4range);
INSERT INTO t2(c0)
VALUES((((((((('(33246437,120866595]'::int4range)-('(1221592635,1811116536]'::int4range)))-((('(-780618820,2079792173]'::int4range)+('[-1692728559,1981826766)'::int4range)))))-((((('[-895937243,1987663138)'::int4range)+('(-2034676477,1405873294)'::int4range)))*((('[-220579913,1869171201]'::int4range)*('(1143499963,1693981609)'::int4range)))))))*('[-1302677385,1498690145]'::int4range))),
('[1033157537,1836686526]'::int4range),
('(146513020,347119085]'::int4range);
UPDATE t0 SET c0='[-675106119,-289968546]'::int4range WHERE
(('149.142.176.227')::VARCHAR SIMILAR TO
((lower(''))::VARCHAR)::VARCHAR(411));
DROP INDEX IF EXISTS i0, i0;
INSERT INTO t1(c0) VALUES('(-744939139,1561600525]'::int4range),
('(-1249873439,-884047914]'::int4range),
('[-1998026580,106157799]'::int4range);
INSERT INTO t0(c0) VALUES('(-20033259,1907262251)'::int4range);
INSERT INTO t2(c0) VALUES('[-234317496,5711819)'::int4range);
INSERT INTO t1(c0) VALUES('(-838035602,-811252607]'::int4range);
INSERT INTO t1(c0) VALUES('[543178753,1103290545)'::int4range);
INSERT INTO t2(c0) VALUES('[-1285614378,1600727315]'::int4range);
INSERT INTO t2(c0) VALUES('[189529154,1835937846)'::int4range);
INSERT INTO t1(c0) VALUES('[371267838,743662036]'::int4range);
INSERT INTO t0(c0) VALUES('(25079313,1160608725]'::int4range);
INSERT INTO t0(c0) VALUES('[655074312,796645171]'::int4range);
INSERT INTO t0(c0) VALUES('(-1170312761,-779521363]'::int4range);
INSERT INTO t1(c0) VALUES('(-706381495,68535611]'::int4range);
INSERT INTO t2(c0) VALUES('[-1678003839,1923757606)'::int4range);
INSERT INTO t1(c0)
VALUES((((((('(-183937683,151604048]'::int4range)*((('[-828326123,1531363034)'::int4range)*(NULL)))))*((((('(-786099124,1116734040]'::int4range)-('(-604345750,1940673645]'::int4range)))*((('(-1540313667,1937752930)'::int4range)+('(-884047914,2101725151)'::int4range)))))))*('[-1692728559,-518108735)'::int4range)));
INSERT INTO t1(c0) OVERRIDING USER VALUE
VALUES((((((((('(-1356538019,-103976819]'::int4range)-('[-1386373199,1105021412]'::int4range)))+((('(-1528869577,-167294854)'::int4range)+('(-965113301,228111315]'::int4range)))))*((((('[1441910056,1689309589]'::int4range)+('[570910979,1990389460]'::int4range)))-((('[-1905136361,-466487609)'::int4range)-('[-118947542,1036979878]'::int4range)))))))-((((((('(-1872730157,1103290545)'::int4range)+('[-169157914,265282380)'::int4range)))*((('[-1368711676,1121868562)'::int4range)+('(-428191673,-307513649]'::int4range)))))-((((('[1816408164,1868244957]'::int4range)*('[-422786137,594801559)'::int4range)))-((('[-838478842,209940777]'::int4range)-('(-1190909868,655074312]'::int4range)))))))));
INSERT INTO t1(c0) OVERRIDING USER VALUE VALUES('(0,917760259)'::int4range),
('(-786099124,876464515)'::int4range),
('[-556975085,-289968546]'::int4range);
INSERT INTO t1(c0) VALUES('[-1893417554,1757624882]'::int4range);
INSERT INTO t0(c0) VALUES('(-2106829419,-2034975607)'::int4range);
INSERT INTO t1(c0) VALUES('[-1427373615,1947251463)'::int4range);
INSERT INTO t2(c0) VALUES('(489776274,1381754446]'::int4range);
INSERT INTO t1(c0) VALUES('[-685304637,708825927)'::int4range);
INSERT INTO t1(c0) OVERRIDING SYSTEM VALUE
VALUES('[655074312,1202724742)'::int4range),
('[-261598093,-137205934]'::int4range),
('(-1420011453,-1179113905)'::int4range);
INSERT INTO t0(c0) OVERRIDING USER VALUE
VALUES('(-1353619600,1472061319]'::int4range);
INSERT INTO t2(c0) VALUES('[1627600979,1756276179)'::int4range);
INSERT INTO t2(c0) VALUES('[1215777375,1783088849)'::int4range);
INSERT INTO t0(c0) VALUES('(-965113301,1103766876]'::int4range);
INSERT INTO t2(c0)
VALUES((((((((('[-1267434215,935355260]'::int4range)*('(-1802742143,135374970]'::int4range)))*((('[-1079670983,-849819849]'::int4range)*('(-1540313667,1418055331)'::int4range)))))*((((('[-2047650556,1604815536]'::int4range)+('[-1564124828,1086820516]'::int4range)))+((('(515164352,570910979]'::int4range)-('[923397219,1439115972)'::int4range)))))))*((((((('(1455817086,1845273676)'::int4range)*('(-1414126774,-884047914]'::int4range)))-('(-703616898,1854694701]'::int4range)))*((((('[694025844,1594998472]'::int4range)-('[201356711,702744487]'::int4range)))+((('(-1427373615,630706012]'::int4range)+('(287480123,1910542365]'::int4range)))))))));
INSERT INTO t2(c0) VALUES('(1694120817,1970908038]'::int4range);
INSERT INTO t2(c0) VALUES('(-877414745,193069718)'::int4range) ON CONFLICT
DO NOTHING;
INSERT INTO t2(c0) VALUES('(471401461,779508428]'::int4range);
INSERT INTO t2(c0) VALUES('[-443009828,144007532)'::int4range);
DROP INDEX IF EXISTS i0, i0 RESTRICT;
INSERT INTO t0(c0) VALUES('[1179460493,1347308196]'::int4range);
INSERT INTO t2(c0)
VALUES((('(442608011,1397331489]'::int4range)+((((((('(-852278216,980014699)'::int4range)*('[-1427373615,746956100)'::int4range)))-((('[-1202800838,1367312295)'::int4range)*('(-1842295581,-795522553]'::int4range)))))-((((('[-267637793,1103290545)'::int4range)-('(-21142487,1308105763]'::int4range)))*('[882707750,1531963892]'::int4range)))))));
INSERT INTO t2(c0) VALUES('(-1689729305,1270465807]'::int4range);
INSERT INTO t2(c0) VALUES('(-1671965013,-2450530]'::int4range);
INSERT INTO t2(c0) OVERRIDING SYSTEM VALUE
VALUES('(-1616259508,489776274]'::int4range);
INSERT INTO t2(c0) VALUES('(-500773466,1016639965)'::int4range);
COMMIT;
ANALYZE t0;
ANALYZE t1;
ANALYZE t2;
On Sat, May 10, 2025 at 6:18 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 18921
Logged by: hongjun xiao
Email address: xiaohongjun@stu.xidian.edu.cn
PostgreSQL version: 17.4
Operating system: Linux-Ubuntu 20.04.6 LTS
Description:-- performance bug for query: SELECT ALL t0.c0, t1.c0 FROM ONLY t0 RIGHT
Thanks for reporting this. I tried to analyze the issue, and based on
my analysis, it doesn’t appear to be a parallelism problem. Instead,
it looks like the root cause is a poor plan choice at the bottom level
of the plan, due to inaccurate statistics for one of the tables, which
then caused everything else to go off track. I have given a more
detailed analysis below.
So I could see that the parallel plan is slower than the normal plan,
and then I tried to investigate it further. I realized the root cause
of the issue is not a parallelism but an estimation issue at the very
bottom end of the plan, as shown below[1]-> Nested Loop (cost=0.00..4.12 rows=1 width=26) (actual time=13.486..13.486 rows=0 loops=1) Output: t0.c0, t1.c0 Join Filter: ((t0.c0 <= t1.c0) AND (((t0.c0 + (t0.c0 + t1.c0)) * ((t0.c0 + t1.c0) + t1.c0)) >= t1.c0) AND (((t0.c0 + (t0.c0 + t1.c0)) * ((t0.c0 + t1.c0) + t1.c0)) <= ((t0.c0 + (t0.c0 + '[-1506176850,461487478)'::int4range)) + (t0.c0 + ('[74602774,1501850467)'::int4range + t0.c0))))) -> Seq Scan on public.t1 (cost=0.00..2.00 rows=1 width=13) (actual time=0.028..0.114 rows=75 loops=1) Output: t1.c0 Filter: (t1.c0 && t1.c0) Rows Removed by Filter: 5 -> Seq Scan on public.t0 (cost=0.00..1.90 rows=4 width=13) (actual time=0.176..0.176 rows=0 loops=75) Output: t0.c0 Filter: ((t0.c0 >= t0.c0) AND (t0.c0 > ((t0.c0 * ('[-2011769718,907690705)'::int4range * t0.c0)) + t0.c0))) Rows Removed by Filter: 40, the "Seq Scan on public.t1"
is estimating 1 row. In contrast, there are 75 rows, and this is an
outer table of the nested loop join, and because of this
underestimation, no materialization is chosen on the inner side, which
makes the whole plan execution worse, see complete plan [2]HashAggregate (cost=3908.21..3908.25 rows=4 width=26) (actual time=13.595..13.598 rows=0 loops=1) Output: t0.c0, t1.c0 Group Key: t0.c0, t1.c0 Batches: 1 Memory Usage: 24kB -> Nested Loop (cost=0.00..2804.21 rows=220800 width=26) (actual time=13.591..13.593 rows=0 loops=1) Output: t0.c0, t1.c0 -> Nested Loop (cost=0.00..42.21 rows=2760 width=26) (actual time=13.590..13.592 rows=0 loops=1) Output: t0.c0, t1.c0 -> Seq Scan on public.t2 sub0_t2 (cost=0.00..1.69 rows=69 width=0) (actual time=0.067..0.077 rows=69 loops=1) Output: sub0_t2.c0 -> Materialize (cost=0.00..6.12 rows=40 width=26) (actual time=0.196..0.196 rows=0 loops=69) Output: t0.c0, t1.c0 -> Nested Loop (cost=0.00..5.92 rows=40 width=26) (actual time=13.486..13.487 rows=0 loops=1) Output: t0.c0, t1.c0 -> Nested Loop (cost=0.00..4.12 rows=1 width=26) (actual time=13.486..13.486 rows=0 loops=1) Output: t0.c0, t1.c0 Join Filter: ((t0.c0 <= t1.c0) AND (((t0.c0 + (t0.c0 + t1.c0)) * ((t0.c0 + t1.c0) + t1.c0)) >= t1.c0) AND (((t0.c0 + (t0.c0 + t1.c0)) * ((t0.c0 + t1.c0) + t1.c0)) <= ((t0.c0 + (t0.c0 + '[-1506176850,461487478)'::int4range)) + (t0.c0 + ('[74602774,1501850467)'::int4range + t0.c0))))) -> Seq Scan on public.t1 (cost=0.00..2.00 rows=1 width=13) (actual time=0.028..0.114 rows=75 loops=1) Output: t1.c0 Filter: (t1.c0 && t1.c0) Rows Removed by Filter: 5 -> Seq Scan on public.t0 (cost=0.00..1.90 rows=4 width=13) (actual time=0.176..0.176 rows=0 loops=75) Output: t0.c0 Filter: ((t0.c0 >= t0.c0) AND (t0.c0 > ((t0.c0 * ('[-2011769718,907690705)'::int4range * t0.c0)) + t0.c0))) Rows Removed by Filter: 40 -> Seq Scan on public.t0 sub0_t0 (cost=0.00..1.40 rows=40 width=0) (never executed) Output: sub0_t0.c0 -> Materialize (cost=0.00..2.20 rows=80 width=0) (never executed) -> Seq Scan on public.t1 sub0_t1 (cost=0.00..1.80 rows=80 width=0) (never executed) Planning Time: 0.885 ms Execution Time: 13.764 ms (31 rows).
I just tried to disable the nested loop join to see what changes, so
the execution time reduced from 13.764 ms to 0.621 ms [3]Group (cost=40000027688.14..40000029344.14 rows=4 width=26) (actual time=0.364..0.368 rows=0 loops=1) Output: t0.c0, t1.c0 Group Key: t0.c0, t1.c0 -> Sort (cost=40000027688.14..40000028240.14 rows=220800 width=26) (actual time=0.363..0.366 rows=0 loops=1) Output: t0.c0, t1.c0 Sort Key: t0.c0, t1.c0 Sort Method: quicksort Memory: 25kB -> Nested Loop (cost=40000000000.00..40000002804.51 rows=220800 width=26) (actual time=0.324..0.327 rows=0 loops=1) Output: t0.c0, t1.c0 -> Nested Loop (cost=30000000000.00..30000000042.51 rows=2760 width=26) (actual time=0.324..0.326 rows=0 loops=1) Output: t0.c0, t1.c0 -> Nested Loop (cost=20000000000.00..20000000006.51 rows=69 width=26) (actual time=0.323..0.325 rows=0 loops=1) Output: t0.c0, t1.c0 -> Nested Loop (cost=10000000000.00..10000000004.13 rows=1 width=26) (actual time=0.323..0.324 rows=0 loops=1) Output: t0.c0, t1.c0 Join Filter: ((t0.c0 <= t1.c0) AND (((t0.c0 + (t0.c0 + t1.c0)) * ((t0.c0 + t1.c0) + t1.c0)) >= t1.c0) AND (((t0.c0 + (t0.c0 + t1.c0)) * ((t0.c0 + t1.c0) + t1.c0)) <= ((t0.c0 + (t0.c0 + '[-1506176850,461487478)'::int4range)) + (t0.c0 + ('[74602774,1501850467)'::int4range + t0.c0))))) -> Seq Scan on public.t0 (cost=0.00..1.90 rows=4 width=13) (actual time=0.322..0.322 rows=0 loops=1) Output: t0.c0 Filter: ((t0.c0 >= t0.c0) AND (t0.c0 > ((t0.c0 * ('[-2011769718,907690705)'::int4range * t0.c0)) + t0.c0))) Rows Removed by Filter: 40 -> Materialize (cost=0.00..2.00 rows=1 width=13) (never executed) Output: t1.c0 -> Seq Scan on public.t1 (cost=0.00..2.00 rows=1 width=13) (never executed) Output: t1.c0 Filter: (t1.c0 && t1.c0) -> Seq Scan on public.t2 sub0_t2 (cost=0.00..1.69 rows=69 width=0) (never executed) Output: sub0_t2.c0 -> Materialize (cost=0.00..1.60 rows=40 width=0) (never executed) -> Seq Scan on public.t0 sub0_t0 (cost=0.00..1.40 rows=40 width=0) (never executed) -> Materialize (cost=0.00..2.20 rows=80 width=0) (never executed) -> Seq Scan on public.t1 sub0_t1 (cost=0.00..1.80 rows=80 width=0) (never executed) Planning Time: 1.048 ms Execution Time: 0.621 ms (33 rows), although we
still have the nested loop plans but by turning it off make planner
choose some other combination of the nested loop plan and the outer
node "Seq Scan on public.t1" become inner node and then the problem
due to underestimation is gone because now we are not looping over
inner table for 75 times. This plan turns out to be superior in many
other ways, although I did not analyze all aspects of this plan
change.
For reference, I'm also including a parallel plan [4]Group (cost=3716.70..3717.68 rows=4 width=26) (actual time=16.097..16.132 rows=0 loops=1) Group Key: t0.c0, t1.c0 -> Gather Merge (cost=3716.70..3717.64 rows=8 width=26) (actual time=16.096..16.130 rows=0 loops=1) Workers Planned: 2 Workers Launched: 2 -> Sort (cost=2716.68..2716.69 rows=4 width=26) (actual time=4.968..4.970 rows=0 loops=3) Sort Key: t0.c0, t1.c0 Sort Method: quicksort Memory: 25kB Worker 0: Sort Method: quicksort Memory: 25kB Worker 1: Sort Method: quicksort Memory: 25kB -> Partial HashAggregate (cost=2716.60..2716.64 rows=4 width=26) (actual time=4.918..4.920 rows=0 loops=3) Group Key: t0.c0, t1.c0 Batches: 1 Memory Usage: 24kB Worker 0: Batches: 1 Memory Usage: 24kB Worker 1: Batches: 1 Memory Usage: 24kB -> Nested Loop (cost=0.00..2256.60 rows=92000 width=26) (actual time=4.916..4.917 rows=0 loops=3) -> Parallel Seq Scan on t0 sub0_t0 (cost=0.00..1.17 rows=17 width=0) (actual time=0.016..0.021 rows=13 loops=3) -> Nested Loop (cost=0.00..77.47 rows=5520 width=26) (actual time=0.366..0.366 rows=0 loops=40) -> Seq Scan on t1 sub0_t1 (cost=0.00..1.80 rows=80 width=0) (actual time=0.005..0.016 rows=80 loops=40) -> Materialize (cost=0.00..6.85 rows=69 width=26) (actual time=0.004..0.004 rows=0 loops=3200) -> Nested Loop (cost=0.00..6.50 rows=69 width=26) (actual time=13.206..13.206 rows=0 loops=1) -> Nested Loop (cost=0.00..4.12 rows=1 width=26) (actual time=13.205..13.206 rows=0 loops=1) Join Filter: ((t0.c0 <= t1.c0) AND (((t0.c0 + (t0.c0 + t1.c0)) * ((t0.c0 + t1.c0) + t1.c0)) >= t1.c0) AND (((t0.c0 + (t0.c0 + t1.c0)) * ((t0.c0 + t1.c0) + t1.c0)) <= ((t0.c0 + (t0.c0 + '[-1506176850,461487478)'::int4range)) + (t0.c0 + ('[74602774,1501850467)'::int4range + t0.c0))))) -> Seq Scan on t1 (cost=0.00..2.00 rows=1 width=13) (actual time=0.026..0.109 rows=75 loops=1) Filter: (c0 && c0) Rows Removed by Filter: 5 -> Seq Scan on t0 (cost=0.00..1.90 rows=4 width=13) (actual time=0.173..0.173 rows=0 loops=75) Filter: ((c0, where the
nested loop is enabled. While it's slightly slower than the
non-parallel plan [2]HashAggregate (cost=3908.21..3908.25 rows=4 width=26) (actual time=13.595..13.598 rows=0 loops=1) Output: t0.c0, t1.c0 Group Key: t0.c0, t1.c0 Batches: 1 Memory Usage: 24kB -> Nested Loop (cost=0.00..2804.21 rows=220800 width=26) (actual time=13.591..13.593 rows=0 loops=1) Output: t0.c0, t1.c0 -> Nested Loop (cost=0.00..42.21 rows=2760 width=26) (actual time=13.590..13.592 rows=0 loops=1) Output: t0.c0, t1.c0 -> Seq Scan on public.t2 sub0_t2 (cost=0.00..1.69 rows=69 width=0) (actual time=0.067..0.077 rows=69 loops=1) Output: sub0_t2.c0 -> Materialize (cost=0.00..6.12 rows=40 width=26) (actual time=0.196..0.196 rows=0 loops=69) Output: t0.c0, t1.c0 -> Nested Loop (cost=0.00..5.92 rows=40 width=26) (actual time=13.486..13.487 rows=0 loops=1) Output: t0.c0, t1.c0 -> Nested Loop (cost=0.00..4.12 rows=1 width=26) (actual time=13.486..13.486 rows=0 loops=1) Output: t0.c0, t1.c0 Join Filter: ((t0.c0 <= t1.c0) AND (((t0.c0 + (t0.c0 + t1.c0)) * ((t0.c0 + t1.c0) + t1.c0)) >= t1.c0) AND (((t0.c0 + (t0.c0 + t1.c0)) * ((t0.c0 + t1.c0) + t1.c0)) <= ((t0.c0 + (t0.c0 + '[-1506176850,461487478)'::int4range)) + (t0.c0 + ('[74602774,1501850467)'::int4range + t0.c0))))) -> Seq Scan on public.t1 (cost=0.00..2.00 rows=1 width=13) (actual time=0.028..0.114 rows=75 loops=1) Output: t1.c0 Filter: (t1.c0 && t1.c0) Rows Removed by Filter: 5 -> Seq Scan on public.t0 (cost=0.00..1.90 rows=4 width=13) (actual time=0.176..0.176 rows=0 loops=75) Output: t0.c0 Filter: ((t0.c0 >= t0.c0) AND (t0.c0 > ((t0.c0 * ('[-2011769718,907690705)'::int4range * t0.c0)) + t0.c0))) Rows Removed by Filter: 40 -> Seq Scan on public.t0 sub0_t0 (cost=0.00..1.40 rows=40 width=0) (never executed) Output: sub0_t0.c0 -> Materialize (cost=0.00..2.20 rows=80 width=0) (never executed) -> Seq Scan on public.t1 sub0_t1 (cost=0.00..1.80 rows=80 width=0) (never executed) Planning Time: 0.885 ms Execution Time: 13.764 ms (31 rows), as mentioned earlier, the real issue is that it
falls victim to a bad plan caused by inaccurate statistics.
[1]: -> Nested Loop (cost=0.00..4.12 rows=1 width=26) (actual time=13.486..13.486 rows=0 loops=1) Output: t0.c0, t1.c0 Join Filter: ((t0.c0 <= t1.c0) AND (((t0.c0 + (t0.c0 + t1.c0)) * ((t0.c0 + t1.c0) + t1.c0)) >= t1.c0) AND (((t0.c0 + (t0.c0 + t1.c0)) * ((t0.c0 + t1.c0) + t1.c0)) <= ((t0.c0 + (t0.c0 + '[-1506176850,461487478)'::int4range)) + (t0.c0 + ('[74602774,1501850467)'::int4range + t0.c0))))) -> Seq Scan on public.t1 (cost=0.00..2.00 rows=1 width=13) (actual time=0.028..0.114 rows=75 loops=1) Output: t1.c0 Filter: (t1.c0 && t1.c0) Rows Removed by Filter: 5 -> Seq Scan on public.t0 (cost=0.00..1.90 rows=4 width=13) (actual time=0.176..0.176 rows=0 loops=75) Output: t0.c0 Filter: ((t0.c0 >= t0.c0) AND (t0.c0 > ((t0.c0 * ('[-2011769718,907690705)'::int4range * t0.c0)) + t0.c0))) Rows Removed by Filter: 40
-> Nested Loop (cost=0.00..4.12 rows=1
width=26) (actual time=13.486..13.486 rows=0 loops=1)
Output: t0.c0, t1.c0
Join Filter: ((t0.c0 <= t1.c0) AND
(((t0.c0 + (t0.c0 + t1.c0)) * ((t0.c0 + t1.c0) + t1.c0)) >= t1.c0) AND
(((t0.c0 + (t0.c0 + t1.c0)) * ((t0.c0 + t1.c0) + t1.c0)) <= ((t0.c0 +
(t0.c0 + '[-1506176850,461487478)'::int4range)) + (t0.c0 +
('[74602774,1501850467)'::int4range + t0.c0)))))
-> Seq Scan on public.t1
(cost=0.00..2.00 rows=1 width=13) (actual time=0.028..0.114 rows=75
loops=1)
Output: t1.c0
Filter: (t1.c0 && t1.c0)
Rows Removed by Filter: 5
-> Seq Scan on public.t0
(cost=0.00..1.90 rows=4 width=13) (actual time=0.176..0.176 rows=0
loops=75)
Output: t0.c0
Filter: ((t0.c0 >= t0.c0) AND
(t0.c0 > ((t0.c0 * ('[-2011769718,907690705)'::int4range * t0.c0)) +
t0.c0)))
Rows Removed by Filter: 40
[2]: HashAggregate (cost=3908.21..3908.25 rows=4 width=26) (actual time=13.595..13.598 rows=0 loops=1) Output: t0.c0, t1.c0 Group Key: t0.c0, t1.c0 Batches: 1 Memory Usage: 24kB -> Nested Loop (cost=0.00..2804.21 rows=220800 width=26) (actual time=13.591..13.593 rows=0 loops=1) Output: t0.c0, t1.c0 -> Nested Loop (cost=0.00..42.21 rows=2760 width=26) (actual time=13.590..13.592 rows=0 loops=1) Output: t0.c0, t1.c0 -> Seq Scan on public.t2 sub0_t2 (cost=0.00..1.69 rows=69 width=0) (actual time=0.067..0.077 rows=69 loops=1) Output: sub0_t2.c0 -> Materialize (cost=0.00..6.12 rows=40 width=26) (actual time=0.196..0.196 rows=0 loops=69) Output: t0.c0, t1.c0 -> Nested Loop (cost=0.00..5.92 rows=40 width=26) (actual time=13.486..13.487 rows=0 loops=1) Output: t0.c0, t1.c0 -> Nested Loop (cost=0.00..4.12 rows=1 width=26) (actual time=13.486..13.486 rows=0 loops=1) Output: t0.c0, t1.c0 Join Filter: ((t0.c0 <= t1.c0) AND (((t0.c0 + (t0.c0 + t1.c0)) * ((t0.c0 + t1.c0) + t1.c0)) >= t1.c0) AND (((t0.c0 + (t0.c0 + t1.c0)) * ((t0.c0 + t1.c0) + t1.c0)) <= ((t0.c0 + (t0.c0 + '[-1506176850,461487478)'::int4range)) + (t0.c0 + ('[74602774,1501850467)'::int4range + t0.c0))))) -> Seq Scan on public.t1 (cost=0.00..2.00 rows=1 width=13) (actual time=0.028..0.114 rows=75 loops=1) Output: t1.c0 Filter: (t1.c0 && t1.c0) Rows Removed by Filter: 5 -> Seq Scan on public.t0 (cost=0.00..1.90 rows=4 width=13) (actual time=0.176..0.176 rows=0 loops=75) Output: t0.c0 Filter: ((t0.c0 >= t0.c0) AND (t0.c0 > ((t0.c0 * ('[-2011769718,907690705)'::int4range * t0.c0)) + t0.c0))) Rows Removed by Filter: 40 -> Seq Scan on public.t0 sub0_t0 (cost=0.00..1.40 rows=40 width=0) (never executed) Output: sub0_t0.c0 -> Materialize (cost=0.00..2.20 rows=80 width=0) (never executed) -> Seq Scan on public.t1 sub0_t1 (cost=0.00..1.80 rows=80 width=0) (never executed) Planning Time: 0.885 ms Execution Time: 13.764 ms (31 rows)
HashAggregate (cost=3908.21..3908.25 rows=4 width=26) (actual
time=13.595..13.598 rows=0 loops=1)
Output: t0.c0, t1.c0
Group Key: t0.c0, t1.c0
Batches: 1 Memory Usage: 24kB
-> Nested Loop (cost=0.00..2804.21 rows=220800 width=26) (actual
time=13.591..13.593 rows=0 loops=1)
Output: t0.c0, t1.c0
-> Nested Loop (cost=0.00..42.21 rows=2760 width=26)
(actual time=13.590..13.592 rows=0 loops=1)
Output: t0.c0, t1.c0
-> Seq Scan on public.t2 sub0_t2 (cost=0.00..1.69
rows=69 width=0) (actual time=0.067..0.077 rows=69 loops=1)
Output: sub0_t2.c0
-> Materialize (cost=0.00..6.12 rows=40 width=26)
(actual time=0.196..0.196 rows=0 loops=69)
Output: t0.c0, t1.c0
-> Nested Loop (cost=0.00..5.92 rows=40
width=26) (actual time=13.486..13.487 rows=0 loops=1)
Output: t0.c0, t1.c0
-> Nested Loop (cost=0.00..4.12 rows=1
width=26) (actual time=13.486..13.486 rows=0 loops=1)
Output: t0.c0, t1.c0
Join Filter: ((t0.c0 <= t1.c0) AND
(((t0.c0 + (t0.c0 + t1.c0)) * ((t0.c0 + t1.c0) + t1.c0)) >= t1.c0) AND
(((t0.c0 + (t0.c0 + t1.c0)) * ((t0.c0 + t1.c0) + t1.c0)) <= ((t0.c0 +
(t0.c0 + '[-1506176850,461487478)'::int4range)) + (t0.c0 +
('[74602774,1501850467)'::int4range + t0.c0)))))
-> Seq Scan on public.t1
(cost=0.00..2.00 rows=1 width=13) (actual time=0.028..0.114 rows=75
loops=1)
Output: t1.c0
Filter: (t1.c0 && t1.c0)
Rows Removed by Filter: 5
-> Seq Scan on public.t0
(cost=0.00..1.90 rows=4 width=13) (actual time=0.176..0.176 rows=0
loops=75)
Output: t0.c0
Filter: ((t0.c0 >= t0.c0) AND
(t0.c0 > ((t0.c0 * ('[-2011769718,907690705)'::int4range * t0.c0)) +
t0.c0)))
Rows Removed by Filter: 40
-> Seq Scan on public.t0 sub0_t0
(cost=0.00..1.40 rows=40 width=0) (never executed)
Output: sub0_t0.c0
-> Materialize (cost=0.00..2.20 rows=80 width=0) (never executed)
-> Seq Scan on public.t1 sub0_t1 (cost=0.00..1.80
rows=80 width=0) (never executed)
Planning Time: 0.885 ms
Execution Time: 13.764 ms
(31 rows)
[3]: Group (cost=40000027688.14..40000029344.14 rows=4 width=26) (actual time=0.364..0.368 rows=0 loops=1) Output: t0.c0, t1.c0 Group Key: t0.c0, t1.c0 -> Sort (cost=40000027688.14..40000028240.14 rows=220800 width=26) (actual time=0.363..0.366 rows=0 loops=1) Output: t0.c0, t1.c0 Sort Key: t0.c0, t1.c0 Sort Method: quicksort Memory: 25kB -> Nested Loop (cost=40000000000.00..40000002804.51 rows=220800 width=26) (actual time=0.324..0.327 rows=0 loops=1) Output: t0.c0, t1.c0 -> Nested Loop (cost=30000000000.00..30000000042.51 rows=2760 width=26) (actual time=0.324..0.326 rows=0 loops=1) Output: t0.c0, t1.c0 -> Nested Loop (cost=20000000000.00..20000000006.51 rows=69 width=26) (actual time=0.323..0.325 rows=0 loops=1) Output: t0.c0, t1.c0 -> Nested Loop (cost=10000000000.00..10000000004.13 rows=1 width=26) (actual time=0.323..0.324 rows=0 loops=1) Output: t0.c0, t1.c0 Join Filter: ((t0.c0 <= t1.c0) AND (((t0.c0 + (t0.c0 + t1.c0)) * ((t0.c0 + t1.c0) + t1.c0)) >= t1.c0) AND (((t0.c0 + (t0.c0 + t1.c0)) * ((t0.c0 + t1.c0) + t1.c0)) <= ((t0.c0 + (t0.c0 + '[-1506176850,461487478)'::int4range)) + (t0.c0 + ('[74602774,1501850467)'::int4range + t0.c0))))) -> Seq Scan on public.t0 (cost=0.00..1.90 rows=4 width=13) (actual time=0.322..0.322 rows=0 loops=1) Output: t0.c0 Filter: ((t0.c0 >= t0.c0) AND (t0.c0 > ((t0.c0 * ('[-2011769718,907690705)'::int4range * t0.c0)) + t0.c0))) Rows Removed by Filter: 40 -> Materialize (cost=0.00..2.00 rows=1 width=13) (never executed) Output: t1.c0 -> Seq Scan on public.t1 (cost=0.00..2.00 rows=1 width=13) (never executed) Output: t1.c0 Filter: (t1.c0 && t1.c0) -> Seq Scan on public.t2 sub0_t2 (cost=0.00..1.69 rows=69 width=0) (never executed) Output: sub0_t2.c0 -> Materialize (cost=0.00..1.60 rows=40 width=0) (never executed) -> Seq Scan on public.t0 sub0_t0 (cost=0.00..1.40 rows=40 width=0) (never executed) -> Materialize (cost=0.00..2.20 rows=80 width=0) (never executed) -> Seq Scan on public.t1 sub0_t1 (cost=0.00..1.80 rows=80 width=0) (never executed) Planning Time: 1.048 ms Execution Time: 0.621 ms (33 rows)
Group (cost=40000027688.14..40000029344.14 rows=4 width=26) (actual
time=0.364..0.368 rows=0 loops=1)
Output: t0.c0, t1.c0
Group Key: t0.c0, t1.c0
-> Sort (cost=40000027688.14..40000028240.14 rows=220800
width=26) (actual time=0.363..0.366 rows=0 loops=1)
Output: t0.c0, t1.c0
Sort Key: t0.c0, t1.c0
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=40000000000.00..40000002804.51
rows=220800 width=26) (actual time=0.324..0.327 rows=0 loops=1)
Output: t0.c0, t1.c0
-> Nested Loop (cost=30000000000.00..30000000042.51
rows=2760 width=26) (actual time=0.324..0.326 rows=0 loops=1)
Output: t0.c0, t1.c0
-> Nested Loop
(cost=20000000000.00..20000000006.51 rows=69 width=26) (actual
time=0.323..0.325 rows=0 loops=1)
Output: t0.c0, t1.c0
-> Nested Loop
(cost=10000000000.00..10000000004.13 rows=1 width=26) (actual
time=0.323..0.324 rows=0 loops=1)
Output: t0.c0, t1.c0
Join Filter: ((t0.c0 <= t1.c0) AND
(((t0.c0 + (t0.c0 + t1.c0)) * ((t0.c0 + t1.c0) + t1.c0)) >= t1.c0) AND
(((t0.c0 + (t0.c0 + t1.c0)) * ((t0.c0 + t1.c0) + t1.c0)) <= ((t0.c0 +
(t0.c0 + '[-1506176850,461487478)'::int4range)) + (t0.c0 +
('[74602774,1501850467)'::int4range + t0.c0)))))
-> Seq Scan on public.t0
(cost=0.00..1.90 rows=4 width=13) (actual time=0.322..0.322 rows=0
loops=1)
Output: t0.c0
Filter: ((t0.c0 >= t0.c0) AND
(t0.c0 > ((t0.c0 * ('[-2011769718,907690705)'::int4range * t0.c0)) +
t0.c0)))
Rows Removed by Filter: 40
-> Materialize (cost=0.00..2.00
rows=1 width=13) (never executed)
Output: t1.c0
-> Seq Scan on public.t1
(cost=0.00..2.00 rows=1 width=13) (never executed)
Output: t1.c0
Filter: (t1.c0 && t1.c0)
-> Seq Scan on public.t2 sub0_t2
(cost=0.00..1.69 rows=69 width=0) (never executed)
Output: sub0_t2.c0
-> Materialize (cost=0.00..1.60 rows=40
width=0) (never executed)
-> Seq Scan on public.t0 sub0_t0
(cost=0.00..1.40 rows=40 width=0) (never executed)
-> Materialize (cost=0.00..2.20 rows=80 width=0)
(never executed)
-> Seq Scan on public.t1 sub0_t1
(cost=0.00..1.80 rows=80 width=0) (never executed)
Planning Time: 1.048 ms
Execution Time: 0.621 ms
(33 rows)
[4]: Group (cost=3716.70..3717.68 rows=4 width=26) (actual time=16.097..16.132 rows=0 loops=1) Group Key: t0.c0, t1.c0 -> Gather Merge (cost=3716.70..3717.64 rows=8 width=26) (actual time=16.096..16.130 rows=0 loops=1) Workers Planned: 2 Workers Launched: 2 -> Sort (cost=2716.68..2716.69 rows=4 width=26) (actual time=4.968..4.970 rows=0 loops=3) Sort Key: t0.c0, t1.c0 Sort Method: quicksort Memory: 25kB Worker 0: Sort Method: quicksort Memory: 25kB Worker 1: Sort Method: quicksort Memory: 25kB -> Partial HashAggregate (cost=2716.60..2716.64 rows=4 width=26) (actual time=4.918..4.920 rows=0 loops=3) Group Key: t0.c0, t1.c0 Batches: 1 Memory Usage: 24kB Worker 0: Batches: 1 Memory Usage: 24kB Worker 1: Batches: 1 Memory Usage: 24kB -> Nested Loop (cost=0.00..2256.60 rows=92000 width=26) (actual time=4.916..4.917 rows=0 loops=3) -> Parallel Seq Scan on t0 sub0_t0 (cost=0.00..1.17 rows=17 width=0) (actual time=0.016..0.021 rows=13 loops=3) -> Nested Loop (cost=0.00..77.47 rows=5520 width=26) (actual time=0.366..0.366 rows=0 loops=40) -> Seq Scan on t1 sub0_t1 (cost=0.00..1.80 rows=80 width=0) (actual time=0.005..0.016 rows=80 loops=40) -> Materialize (cost=0.00..6.85 rows=69 width=26) (actual time=0.004..0.004 rows=0 loops=3200) -> Nested Loop (cost=0.00..6.50 rows=69 width=26) (actual time=13.206..13.206 rows=0 loops=1) -> Nested Loop (cost=0.00..4.12 rows=1 width=26) (actual time=13.205..13.206 rows=0 loops=1) Join Filter: ((t0.c0 <= t1.c0) AND (((t0.c0 + (t0.c0 + t1.c0)) * ((t0.c0 + t1.c0) + t1.c0)) >= t1.c0) AND (((t0.c0 + (t0.c0 + t1.c0)) * ((t0.c0 + t1.c0) + t1.c0)) <= ((t0.c0 + (t0.c0 + '[-1506176850,461487478)'::int4range)) + (t0.c0 + ('[74602774,1501850467)'::int4range + t0.c0))))) -> Seq Scan on t1 (cost=0.00..2.00 rows=1 width=13) (actual time=0.026..0.109 rows=75 loops=1) Filter: (c0 && c0) Rows Removed by Filter: 5 -> Seq Scan on t0 (cost=0.00..1.90 rows=4 width=13) (actual time=0.173..0.173 rows=0 loops=75) Filter: ((c0
Group (cost=3716.70..3717.68 rows=4 width=26) (actual
time=16.097..16.132 rows=0 loops=1)
Group Key: t0.c0, t1.c0
-> Gather Merge (cost=3716.70..3717.64 rows=8 width=26) (actual
time=16.096..16.130 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=2716.68..2716.69 rows=4 width=26) (actual
time=4.968..4.970 rows=0 loops=3)
Sort Key: t0.c0, t1.c0
Sort Method: quicksort Memory: 25kB
Worker 0: Sort Method: quicksort Memory: 25kB
Worker 1: Sort Method: quicksort Memory: 25kB
-> Partial HashAggregate (cost=2716.60..2716.64
rows=4 width=26) (actual time=4.918..4.920 rows=0 loops=3)
Group Key: t0.c0, t1.c0
Batches: 1 Memory Usage: 24kB
Worker 0: Batches: 1 Memory Usage: 24kB
Worker 1: Batches: 1 Memory Usage: 24kB
-> Nested Loop (cost=0.00..2256.60 rows=92000
width=26) (actual time=4.916..4.917 rows=0 loops=3)
-> Parallel Seq Scan on t0 sub0_t0
(cost=0.00..1.17 rows=17 width=0) (actual time=0.016..0.021 rows=13
loops=3)
-> Nested Loop (cost=0.00..77.47
rows=5520 width=26) (actual time=0.366..0.366 rows=0 loops=40)
-> Seq Scan on t1 sub0_t1
(cost=0.00..1.80 rows=80 width=0) (actual time=0.005..0.016 rows=80
loops=40)
-> Materialize (cost=0.00..6.85
rows=69 width=26) (actual time=0.004..0.004 rows=0 loops=3200)
-> Nested Loop
(cost=0.00..6.50 rows=69 width=26) (actual time=13.206..13.206 rows=0
loops=1)
-> Nested Loop
(cost=0.00..4.12 rows=1 width=26) (actual time=13.205..13.206 rows=0
loops=1)
Join Filter:
((t0.c0 <= t1.c0) AND (((t0.c0 + (t0.c0 + t1.c0)) * ((t0.c0 + t1.c0) +
t1.c0)) >= t1.c0) AND (((t0.c0 + (t0.c0 + t1.c0)) * ((t0.c0 + t1.c0) +
t1.c0)) <= ((t0.c0 + (t0.c0 + '[-1506176850,461487478)'::int4range)) +
(t0.c0 + ('[74602774,1501850467)'::int4range + t0.c0)))))
-> Seq Scan on t1
(cost=0.00..2.00 rows=1 width=13) (actual time=0.026..0.109 rows=75
loops=1)
Filter: (c0 && c0)
Rows Removed
by Filter: 5
-> Seq Scan on t0
(cost=0.00..1.90 rows=4 width=13) (actual time=0.173..0.173 rows=0
loops=75)
Filter: ((c0
= c0) AND (c0 > ((c0 * ('[-2011769718,907690705)'::int4range * c0)) +
c0)))
Rows Removed
by Filter: 40
-> Seq Scan on t2
sub0_t2 (cost=0.00..1.69 rows=69 width=0) (never executed)
Planning Time: 1.112 ms
Execution Time: 16.338 ms
(32 rows)
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com