Thread: BUG #18935: The optimiser's choice of sort doubles the execution time.
BUG #18935: The optimiser's choice of sort doubles the execution time.
From
PG Bug reporting form
Date:
The following bug has been logged on the website:
Bug reference: 18935
Logged by: hongjun xiao
Email address: xiaohongjun@stu.xidian.edu.cn
PostgreSQL version: 17.4
Operating system: Ubuntu 20.04.6 LTS
Description:
database4=# explain analyze SELECT t0.c0 FROM t0 INNER JOIN t1* ON
((t1.c0)=(((t1.c0)-(((((t1.c0)*('(-795716537,-245904803]'::int4range)))-(range_merge(t1.c0,
t0.c0))))))) GROUP BY t0.c0;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Group (cost=365.27..365.62 rows=70 width=13) (actual time=16.143..17.958
rows=121 loops=1)
Group Key: t0.c0
-> Sort (cost=365.27..365.45 rows=70 width=13) (actual
time=16.141..16.540 rows=12688 loops=1)
Sort Key: t0.c0
Sort Method: quicksort Memory: 385kB
-> Nested Loop (cost=0.00..363.13 rows=70 width=13) (actual
time=0.055..8.431 rows=12688 loops=1)
Join Filter: (t1.c0 = (t1.c0 - ((t1.c0 *
'[-795716536,-245904802)'::int4range) - range_merge(t1.c0, t0.c0))))
Rows Removed by Join Filter: 1650
-> Seq Scan on t0 (cost=0.00..2.34 rows=134 width=13)
(actual time=0.025..0.040 rows=134 loops=1)
-> Materialize (cost=0.00..2.61 rows=107 width=13) (actual
time=0.000..0.005 rows=107 loops=134)
-> Seq Scan on t1 (cost=0.00..2.07 rows=107 width=13)
(actual time=0.015..0.028 rows=107 loops=1)
Planning Time: 0.105 ms
Execution Time: 17.998 ms
(13 rows)
database4=# set enable_sort=false;
SET
database4=# explain analyze SELECT t0.c0 FROM t0 INNER JOIN t1* ON
((t1.c0)=(((t1.c0)-(((((t1.c0)*('(-795716537,-245904803]'::int4range)))-(range_merge(t1.c0,
t0.c0))))))) GROUP BY t0.c0;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=363.30..364.00 rows=70 width=13) (actual
time=8.034..8.042 rows=121 loops=1)
Group Key: t0.c0
Batches: 1 Memory Usage: 40kB
-> Nested Loop (cost=0.00..363.13 rows=70 width=13) (actual
time=0.019..5.680 rows=12688 loops=1)
Join Filter: (t1.c0 = (t1.c0 - ((t1.c0 *
'[-795716536,-245904802)'::int4range) - range_merge(t1.c0, t0.c0))))
Rows Removed by Join Filter: 1650
-> Seq Scan on t0 (cost=0.00..2.34 rows=134 width=13) (actual
time=0.010..0.033 rows=134 loops=1)
-> Materialize (cost=0.00..2.61 rows=107 width=13) (actual
time=0.000..0.003 rows=107 loops=134)
-> Seq Scan on t1 (cost=0.00..2.07 rows=107 width=13)
(actual time=0.004..0.007 rows=107 loops=1)
Planning Time: 0.060 ms
Execution Time: 8.064 ms
(11 rows)
how to repeat:
CREATE DATABASE database4 WITH ENCODING 'UTF8' TEMPLATE template0;
\c database4;
CREATE TABLE IF NOT EXISTS t0(c0 int4range ) USING heap;
CREATE UNLOGGED TABLE IF NOT EXISTS t1(LIKE t0);
CREATE TEMPORARY TABLE IF NOT EXISTS t2(LIKE t1);
CREATE TEMPORARY TABLE t3(LIKE t0);
CREATE TEMP TABLE IF NOT EXISTS t4(LIKE t1);
CREATE TEMP TABLE t5(LIKE t1);
INSERT INTO t1 (c0) VALUES ('(-636011872,-140169948)'::int4range);
INSERT INTO t2 (c0) VALUES ((((((((('(-528351847,-74554700)'::int4range) *
('[-627461467,1030686968)'::int4range))) -
((('[549858597,1141563565]'::int4range) *
('(-1174746117,1973009836)'::int4range))))) *
((((('(-2052901688,1038393925)'::int4range) +
('(-902840864,2028933638)'::int4range))) +
((('(-1264681076,1464372478)'::int4range) -
('[-2052901688,-1009576747]'::int4range))))))) +
((((((('[-1634678320,1491796413)'::int4range) +
('[-1570225866,2027970397]'::int4range))) +
((('[-983784861,1619348298)'::int4range) *
('(-1173945552,-774761218)'::int4range))))) +
((((('[-1838627688,-380177871)'::int4range) *
('(-953581832,1609311988]'::int4range))) +
((('[-831586210,1072360616]'::int4range) -
('[319736547,1645674985)'::int4range)))))))));
INSERT INTO t0 (c0) VALUES ('(-1197946773,486777807]'::int4range);
INSERT INTO t2 (c0) VALUES ('[-819489600,738490793)'::int4range);
INSERT INTO t5 (c0) VALUES ('(-2141646075,-1462138938]'::int4range);
INSERT INTO t4 (c0) VALUES ('[-608870834,1595763947)'::int4range);
INSERT INTO t3 (c0) VALUES ('(791604522,1991036340)'::int4range);
INSERT INTO t5 (c0) VALUES ('(-547689176,512794997]'::int4range);
INSERT INTO t4(c0) OVERRIDING USER VALUE
VALUES('(803975155,1333692432)'::int4range);
INSERT INTO t0 (c0) VALUES ('[-1334162272,-816861513)'::int4range);
INSERT INTO t4 (c0) VALUES ('(-320758065,1097230849)'::int4range);
INSERT INTO t1 (c0) VALUES ('(-706134374,122024865]'::int4range),
('[-2014698905,-1917474869)'::int4range),
('(-1507582223,1418370134)'::int4range);
INSERT INTO t0 (c0) VALUES ('(-667013312,1580364581)'::int4range);
INSERT INTO t0 (c0) VALUES ('[-1379189072,1621239603)'::int4range);
INSERT INTO t2(c0) OVERRIDING USER VALUE
VALUES('(-829493299,1704565867)'::int4range);
INSERT INTO t0 (c0) VALUES ('(-588377270,-330377942]'::int4range);
INSERT INTO t4 (c0) VALUES ('(127665658,1603290535]'::int4range) ON CONFLICT
DO NOTHING;
INSERT INTO t2 (c0) VALUES ('(-87583314,965828553]'::int4range);
INSERT INTO t0 (c0) VALUES ('(-1369682860,1610783064]'::int4range) ON
CONFLICT DO NOTHING;
INSERT INTO t1 (c0) VALUES ('[-1204115279,1676545065]'::int4range);
INSERT INTO t0(c0) OVERRIDING SYSTEM VALUE
VALUES(((((range_merge((('[-1271382895,-214535461)'::int4range)+('(-1609214390,-1222070285)'::int4range)),
(('(-102446608,51276201)'::int4range)*('[-1239295485,-864975116)'::int4range))))*((('[-810417215,225423032)'::int4range)+((('(-1148239454,-439712364)'::int4range)-('[-221665010,311176369)'::int4range)))))))+((((((('(-910415449,1709438659)'::int4range)+('(1357926252,1628644343)'::int4range)))*((('(495517744,1932332282)'::int4range)*('(-693353871,1155685102)'::int4range)))))+((((('[1025656950,1181624622]'::int4range)-('[-1512742697,-670140818)'::int4range)))+((('[-268156814,863404794]'::int4range)-(NULL)))))))));
INSERT INTO t4 (c0) VALUES ('(1275631235,1854639183)'::int4range);
INSERT INTO t5 (c0) VALUES ('(-2052901688,-1413824916)'::int4range);
INSERT INTO t2 (c0) VALUES ('[115687313,1141563565)'::int4range);
INSERT INTO t3 (c0) VALUES ((('[585409737,1962222972)'::int4range) +
((((((('[-829493299,787101893)'::int4range) *
('[-1686074109,-128533461)'::int4range))) -
('(-245904803,1733167376)'::int4range))) - (((((NULL) *
('[-1689212959,1335077533)'::int4range))) -
((('[-1769967271,1155685102]'::int4range) *
('[-246516031,115687313]'::int4range))))))))) ON CONFLICT DO NOTHING;
INSERT INTO t0 (c0) VALUES ('(-578871540,1676545065]'::int4range);
INSERT INTO t1 (c0) VALUES ('[-974260841,759354810)'::int4range);
INSERT INTO t0(c0) OVERRIDING SYSTEM VALUE
VALUES('(-508720518,1381482535]'::int4range);
INSERT INTO t5 (c0) VALUES ((((((((('(-1173945552,1434252884)'::int4range) +
('(1034983716,1117481849)'::int4range))) +
(range_merge('(-459204760,396190012]'::int4range,
'(-245904803,987005761]'::int4range)))) -
('[-1894511370,1932962499)'::int4range))) +
(((range_merge((NULL)::int4range, (('(-1718573980,568301766)'::int4range) +
('[-960354110,17429570]'::int4range)))) -
('[-1931442021,-1902262184]'::int4range)))));
INSERT INTO t5 (c0) VALUES ('(-1057964527,1812167459)'::int4range);
INSERT INTO t0 (c0) VALUES ('[799405839,1730680508]'::int4range);
INSERT INTO t5 (c0) VALUES ((((((((('(956695413,1262082228)'::int4range) +
('(-282382318,1356075539)'::int4range))) *
((('(-1309574587,-282382318)'::int4range) *
('[-947126240,-722763177]'::int4range))))) -
((((('[856437029,985112755]'::int4range) *
('(137990366,665881862]'::int4range))) -
((('(-282382318,169496387)'::int4range) *
('[-181089024,487258039)'::int4range))))))) -
((((((('[-2142140639,-773113610)'::int4range) *
('(-119545832,1544377226)'::int4range))) *
((('[-822380188,396190012]'::int4range) -
('(-2125829171,1552794405)'::int4range))))) -
(((range_merge('[-967439134,1762868699)'::int4range,
'(-1557987930,-400784586)'::int4range)) +
((('(1024359960,1327138424]'::int4range) -
('[-1982656245,-440002587]'::int4range)))))))));
INSERT INTO t4 (c0) VALUES ('[115687313,486777807)'::int4range);
INSERT INTO t4 (c0) VALUES ('(-1626765058,1897312185)'::int4range);
INSERT INTO t0 (c0) VALUES ('(-1808827826,1389763430)'::int4range),
('[-819489600,1155685102]'::int4range),
('[-511019846,-140169948]'::int4range);
INSERT INTO t2 (c0) VALUES ((((((((('[-2067576753,-1780197843]'::int4range)
- ('[-996646228,928981803]'::int4range))) -
((('[-2067576753,200480115]'::int4range) *
('(-635838304,596176223]'::int4range))))) +
((((('[-1718573980,1025656950)'::int4range) -
('(72850177,1804214206)'::int4range))) +
((('[-1965334017,560635545]'::int4range) -
('[4104640,1988814399]'::int4range))))))) +
((((((('[-207616894,1740438740)'::int4range) -
('(-1871059416,2098308461]'::int4range))) +
((('[683759271,2122722689)'::int4range) *
('(-1484464165,-46083463)'::int4range))))) -
((((('[-1034380582,-24260008]'::int4range) +
('[-392217182,483846806)'::int4range))) -
((('(-1793674559,72850177]'::int4range) -
('[572596356,1747171793)'::int4range)))))))));
INSERT INTO t2 (c0) VALUES ('[-1369682860,-577597872]'::int4range);
INSERT INTO t4 (c0) VALUES ('[10938108,665881862]'::int4range),
((('(-534360232,316802870]'::int4range) -
((((((('(-274558388,-74554700]'::int4range) +
('[-1790981218,2038514859]'::int4range))) *
((('(-1469375162,1713098865)'::int4range) *
('(-1379189072,-214535461]'::int4range))))) *
((((('(-2140392190,2012209844)'::int4range) +
('[-1847331394,-1159835934)'::int4range))) +
((('(-74554700,628986282]'::int4range) -
('(-508386256,-440002587)'::int4range))))))))),
('(162742940,1848494154]'::int4range);
INSERT INTO t5 (c0) VALUES ('[-282382318,896978690)'::int4range);
INSERT INTO t4 (c0) VALUES ('(1056930772,1910962489)'::int4range);
INSERT INTO t0 (c0) VALUES ('[-759288323,-511019846)'::int4range);
INSERT INTO t1 (c0) VALUES ('[-1781481460,-967588511)'::int4range);
INSERT INTO t2 (c0) VALUES ('(-967588511,-400784586]'::int4range);
INSERT INTO t2 (c0) VALUES ('[-1749910336,-245904803)'::int4range);
INSERT INTO t5 (c0) VALUES ('[-40089,773439241)'::int4range);
INSERT INTO t2 (c0) VALUES ((((('(-1417765854,486777807]'::int4range) -
((((('[160553063,1253263808)'::int4range) -
('(-78216600,10938108)'::int4range))) *
((('[-855518674,-74554700)'::int4range) *
('(-566068387,1353408300)'::int4range))))))) -
((((((('[-258646752,-205193978)'::int4range) *
('[50732514,571697531]'::int4range))) *
((('[-2070716319,1481832050)'::int4range) *
('[-1577922293,770384358)'::int4range))))) -
((((('(-1844614733,-379857337)'::int4range) -
('[-1778322020,318307516]'::int4range))) *
((('[-1570225866,-1391146906]'::int4range) *
('(1238733762,2104182881)'::int4range)))))))));
INSERT INTO t1 (c0) VALUES ('(72850177,1623971241]'::int4range);
INSERT INTO t0 (c0) VALUES ('(-1369682860,-1236175552]'::int4range);
INSERT INTO t3 (c0) VALUES ('(-59275537,1622304460]'::int4range);
INSERT INTO t4 (c0) VALUES ('(-322293030,-107569498)'::int4range);
INSERT INTO t3 (c0) VALUES ('(-910415449,1353408300]'::int4range);
RESET ALL;
INSERT INTO t1 (c0) VALUES ('(-1125415721,2074830197)'::int4range);
INSERT INTO t2 (c0) VALUES ('(349849021,781708694)'::int4range);
INSERT INTO t1 (c0) VALUES ((((('[-1034380582,-239629582)'::int4range) *
('(-627461467,-298378394)'::int4range))) *
((((((('(1544005362,1991036340]'::int4range) *
('[-1369682860,-1148239454)'::int4range))) -
((('(486777807,1097230849]'::int4range) *
('(10938108,1238733762]'::int4range))))) *
((((('(1263066026,1333692432)'::int4range) -
('(-1817026107,1434521444]'::int4range))) +
((('[-558898235,707970363)'::int4range) *
('[-1205728538,42978066]'::int4range)))))))));
INSERT INTO t1 (c0) VALUES (NULL);
INSERT INTO t0(c0) OVERRIDING USER VALUE
VALUES('(-1904922194,202398737)'::int4range);
INSERT INTO t4 (c0) VALUES ('[-114166687,892244790)'::int4range);
INSERT INTO t5 (c0) VALUES ('[1262082228,1434252884)'::int4range);
INSERT INTO t2 (c0) VALUES ('(320475189,1922908720]'::int4range);
INSERT INTO t1 (c0) VALUES ('[-1497280331,839141497)'::int4range);
INSERT INTO t4 (c0) VALUES ('[-100523678,2117967957)'::int4range);
INSERT INTO t4 (c0) VALUES ((((((((('[-819489600,1606863390]'::int4range) *
('(-1951762513,-1027551795)'::int4range))) *
((('(-1577131634,2098308461]'::int4range) *
('[-757390184,-363729266)'::int4range))))) -
('(364186688,671001811)'::int4range))) +
((((((('(-1159835934,1991036340]'::int4range) *
('[-2142140639,-314406439)'::int4range))) -
((('[-1148239454,867993709]'::int4range) -
('[-2029765368,787354896)'::int4range))))) *
((((('[17429570,1025656950)'::int4range) -
('[-1459753089,933610481)'::int4range))) -
('(-511019846,-508558435]'::int4range)))))));
INSERT INTO t0 (c0) VALUES ((((((((('[-2140392190,-499012203]'::int4range) +
('[-1173945552,-56297535)'::int4range))) *
((('[-1526780729,-1125415721)'::int4range) -
('(-504217768,1580364581]'::int4range))))) *
((('[291220207,1947764259)'::int4range) +
((('(1497157441,1775437182]'::int4range) +
('(-870201421,1972952179]'::int4range))))))) -
((((((('(41582780,1333692432]'::int4range) +
('[1025656950,1624080137]'::int4range))) -
('[-1964409708,-268156814]'::int4range))) *
((((('(-62455448,1926509516)'::int4range) *
('[-2140392190,1707826164]'::int4range))) +
((('[-293351985,1381482535]'::int4range) -
('(-1689212959,72850177]'::int4range)))))))));
INSERT INTO t1 (c0) VALUES ('[-2111148864,471803118)'::int4range);
INSERT INTO t3(c0) OVERRIDING USER VALUE
VALUES('[-1939856091,720105222)'::int4range) ON CONFLICT DO NOTHING;
INSERT INTO t3 (c0) VALUES ('(-1628544057,1629153808)'::int4range);
INSERT INTO t4 (c0) VALUES ('[-1125415721,-423632027)'::int4range);
INSERT INTO t0(c0) OVERRIDING SYSTEM VALUE
VALUES('[-1914456683,-1005638396)'::int4range);
INSERT INTO t0 (c0) VALUES ('[-1173945552,-610887207]'::int4range);
DELETE FROM ONLY t5 RETURNING '126.246.56.77';
INSERT INTO t2 (c0) VALUES ('(1328625532,2083480892)'::int4range);
INSERT INTO t3 (c0) VALUES ('(-2140392190,2122722689)'::int4range);
INSERT INTO t3 (c0) VALUES ('[-1347017602,2065829936]'::int4range);
INSERT INTO t0 (c0) VALUES ((((((((('[-1314414628,-1038548345]'::int4range)
- ('(-392452680,1617069959]'::int4range))) +
((('(-344083952,1330841493]'::int4range) - (NULL))))) +
((((('[-1057964527,1209054683]'::int4range) +
('[-861094373,306080843]'::int4range))) *
((('[-1150986618,809574628)'::int4range) +
('[-54574828,1795514918]'::int4range))))))) -
((((((('[-1295399443,1]'::int4range) *
('(-139639321,1803965984)'::int4range))) *
((('[-967588511,581488200)'::int4range) -
('(-1827194381,971466383)'::int4range))))) *
((((('(-2067576753,1238733762)'::int4range) *
('[4104640,396190012)'::int4range))) -
((('(-1320315831,2038514859)'::int4range) +
('[-960207754,-935606317)'::int4range)))))))));
INSERT INTO t4 (c0) VALUES ('(-1995033371,1262082228]'::int4range),
('(-245904803,1772453894]'::int4range),
((((((((('[-1125415721,527540112)'::int4range) -
('[-1120284787,596176223]'::int4range))) *
((('(-2067576753,1676545065]'::int4range) *
('[-320945659,-38011940)'::int4range))))) +
((((('[527771518,923538418)'::int4range) -
('(-1127970842,882381080)'::int4range))) -
('[-293351985,9947630)'::int4range))))) +
((((((('[-2052353813,402198942]'::int4range) +
('(-829493299,-268156814]'::int4range))) -
((('(-1599417185,1238733762)'::int4range) *
('[-1689212959,1064225990)'::int4range))))) -
((((('[-2142140639,1676545065]'::int4range) +
('(-245904803,978201782)'::int4range))) -
((('[802596798,975992532)'::int4range) *
('[-1769967271,-1674225293)'::int4range)))))))));
INSERT INTO t1 (c0) VALUES ((('[-1873352103,-1273427790]'::int4range) -
(range_merge((((('[-1141966348,-86197655]'::int4range) -
('[-901728224,1)'::int4range))) + ((('(-1080071530,360587510)'::int4range) *
('(-1514127206,1914342727)'::int4range)))),
(((('(-62579531,787354896)'::int4range) -
('(-1125415721,1514125516)'::int4range))) *
((('(-511019846,903361942)'::int4range) +
('[-46898157,206924680)'::int4range)))))))),
('(323562438,1580364581]'::int4range),
('[-2067576753,1141563565)'::int4range);
INSERT INTO t5 (c0) VALUES ((((((((('(-920688022,-171848715]'::int4range) *
('[-2073163209,-92499364)'::int4range))) *
((('(396190012,486777807)'::int4range) *
('(-686034064,1361625043)'::int4range))))) *
((((('[827343765,1665938719]'::int4range) *
('(-1075138592,-268156814)'::int4range))) -
((('(-483170808,758703815]'::int4range) -
('[-1034380582,116936581)'::int4range))))))) *
((((((('[-1691355762,1676545065)'::int4range) *
('(-1148239454,-1148239454]'::int4range))) -
((('(483846806,2026675770]'::int4range) -
('(-1133525392,1766277242]'::int4range))))) -
((((('(-140169948,1134007078)'::int4range) -
('(-1687730846,-46898157)'::int4range))) -
((('(-2132937391,1056930772)'::int4range) +
('[803975155,896379560]'::int4range)))))))));
ANALYZE t3(c0);
INSERT INTO t5 (c0) VALUES ('[-942538449,-495807905)'::int4range);
INSERT INTO t3 (c0) VALUES ('(134753117,1411936636]'::int4range);
INSERT INTO t5 (c0) VALUES ('[-1718573980,1608812320]'::int4range);
INSERT INTO t2 (c0) VALUES ('(-2043981675,-2026938713]'::int4range),
((((((((('(1262082228,1911679090)'::int4range) -
('[811055115,1196155783)'::int4range))) -
((('(-1369682860,2098669276)'::int4range) *
('(526826291,1365211598)'::int4range))))) +
((((('(252565331,1736246465]'::int4range) -
('(-965332071,-46898157)'::int4range))) +
((('[628986282,1454185641)'::int4range) -
('(-1738324374,1891079319)'::int4range))))))) -
((((((('[-447039026,2146833586]'::int4range) *
('[-2140392190,72850177]'::int4range))) *
((('(1590949681,1768418470]'::int4range) *
('(-686239138,1793609150]'::int4range))))) +
((((('(-1853963076,1030686968]'::int4range) -
('(-542448897,1152554230)'::int4range))) *
((('(-74554700,1300765174]'::int4range) -
('[-2067576753,-549175432]'::int4range))))))))),
('(-1522019373,1141563565]'::int4range);
INSERT INTO t0(c0) OVERRIDING USER VALUE
VALUES('[-282382318,340745313]'::int4range);
INSERT INTO t1 (c0) VALUES ((((((((('(140624784,945993721]'::int4range) +
('(41927806,2087030254]'::int4range))) *
((('(-400784586,2072471533)'::int4range) +
('[-983784861,1533654374)'::int4range))))) *
((((('(290854718,1389131247)'::int4range) *
('[-1207255489,1733167376]'::int4range))) *
((('(-1039616195,-831895017)'::int4range) -
('[317419199,1650218825)'::int4range))))))) -
((((((('[-1420767052,452545879]'::int4range) +
('[-1447001463,840474912)'::int4range))) +
((('[-1031702653,2114686630)'::int4range) +
('(-1808827826,603812961]'::int4range))))) *
((((('[30382449,1972952179]'::int4range) *
('(-1568048122,-244950854]'::int4range))) +
((('(856437029,1238733762]'::int4range) *
('(-1147792975,1504821694)'::int4range)))))))));
INSERT INTO t2 (c0) VALUES ((((((((('[-1970754963,-984996657]'::int4range) *
('[832164921,856437029)'::int4range))) +
((('[-845378402,352785494]'::int4range) *
('(-872895295,-400784586)'::int4range))))) -
((((('(-920870912,-395764733]'::int4range) -
('(-1758770527,-1674737086)'::int4range))) *
((('[93564805,1669945548]'::int4range) -
('(-1175558578,-909130642)'::int4range))))))) -
(((((range_merge('(-645614573,2136005797)'::int4range,
'(-872625222,-759288323]'::int4range)) -
((('[-1718573980,-1372940106]'::int4range) *
('(-214535461,983218770]'::int4range))))) *
((((('(-1158891352,1054601916]'::int4range) +
('[-786592406,1733167376]'::int4range))) +
((('(-918263687,924029271]'::int4range) +
('(-1398271657,745397422]'::int4range)))))))));
INSERT INTO t5 (c0) VALUES ('[-1068402257,-32824763]'::int4range) ON
CONFLICT DO NOTHING;
INSERT INTO t1 (c0) VALUES ('[-1361112720,367668035]'::int4range);
INSERT INTO t1(c0) OVERRIDING SYSTEM VALUE
VALUES('[1025656950,1238053026]'::int4range);
DELETE FROM ONLY t2;
INSERT INTO t0 (c0) VALUES ('[156878335,867993709]'::int4range),
('[-1034380582,-996696715]'::int4range),
('(1522312311,1676545065)'::int4range);
INSERT INTO t4 (c0) VALUES (DEFAULT);
INSERT INTO t2 (c0) VALUES ('(17429570,212077410)'::int4range);
INSERT INTO t2 (c0) VALUES ((('(-1675967484,-23113362)'::int4range) -
(((range_merge(range_merge('[-1840655845,1665507244)'::int4range,
'(-1805763115,-744625391)'::int4range),
(('[290854718,1364776767]'::int4range) +
('[-707725584,1427677673)'::int4range)))) -
((((('(-2056422088,-1341271147]'::int4range) +
('[-2112441590,-829493299]'::int4range))) +
((('(-1689212959,1294282984)'::int4range) *
('(-1369682860,-221122728]'::int4range)))))))));
INSERT INTO t0 (c0) VALUES ('(270185416,1113522131)'::int4range),
('(1521710600,1580364581)'::int4range),
('[-511019846,-282382318)'::int4range);
INSERT INTO t0(c0) OVERRIDING SYSTEM VALUE
VALUES('(-658330284,1484776485]'::int4range);
INSERT INTO t4(c0) OVERRIDING USER VALUE
VALUES(((range_merge((((('(-1943040518,1893303647]'::int4range)+('[-1964409708,-1430686965)'::int4range)))+((('(-432802262,-161048487]'::int4range)*('[-252112893,200480115)'::int4range)))),
(((('(-1787540234,-1]'::int4range)*('(-140169948,1187214138)'::int4range)))-((('(-967588511,1056930772)'::int4range)+('(-1479873444,1568221637]'::int4range))))))*((((((('(566227662,695980310]'::int4range)*('[-1308902878,1737827666)'::int4range)))-((('(-1519697801,94327938)'::int4range)*('(-1057964527,1537832840]'::int4range)))))-((((('(1316384244,1636877850)'::int4range)-('(-1772018209,-1159835934)'::int4range)))-(((NULL)*('[-610672887,823955556)'::int4range)))))))));
INSERT INTO t4 (c0) VALUES ('(-211050503,494205390)'::int4range) ON CONFLICT
DO NOTHING;
INSERT INTO t5 (c0) VALUES (NULL);
INSERT INTO t4 (c0) VALUES ('(867993709,1568221637)'::int4range);
INSERT INTO t1 (c0) VALUES ('[-1991131036,276822791)'::int4range);
INSERT INTO t1 (c0) VALUES ('(-1569991371,-999289718]'::int4range);
INSERT INTO t3 (c0) VALUES ('(-1411439733,1651215900)'::int4range);
INSERT INTO t5 (c0) VALUES ('[-2067576753,-1381779580]'::int4range),
('[-267698953,1540340334]'::int4range),
('[-46898157,1084022460)'::int4range);
INSERT INTO t5 (c0) VALUES ('(-1795578494,-330377942)'::int4range),
('(-1147590758,527540112)'::int4range),
((((((((('(-221665010,2070585183]'::int4range) +
('[-1885448259,-214535461]'::int4range))) *
((('(-2007661551,-159450124)'::int4range) -
('[-2054711177,-1808827826]'::int4range))))) -
((((('(-2141646075,1293832327)'::int4range) +
('(-967588511,899087538]'::int4range))) *
((('(-1868648299,-1192355427)'::int4range) *
('(94327938,1561426762)'::int4range))))))) +
((((((('[-503536833,1097230849]'::int4range) -
('(-1296942950,1277384716]'::int4range))) +
((('[-1039112117,1056930772]'::int4range) -
('[-395661501,2122279646)'::int4range))))) -
((((('[-2039031034,957753098]'::int4range) *
('[-258379500,86467484)'::int4range))) *
((('(-2091566505,-1999397975]'::int4range) -
('(1580364581,1888594590)'::int4range)))))))));
INSERT INTO t2 (c0) VALUES ('[-1834284359,-1369682860]'::int4range);
INSERT INTO t0 (c0) VALUES ('[-77944043,1713817543]'::int4range);
INSERT INTO t0 (c0) VALUES ((((((((('(-37570269,988181523]'::int4range) -
('[-892160353,1056930772)'::int4range))) -
((('[8174329,442759388]'::int4range) -
('[-1951836380,1640121033)'::int4range))))) *
((((('(-74554700,453709923]'::int4range) +
('(-1450317324,1565909800]'::int4range))) +
((('[-2140392190,1333692432]'::int4range) *
('[-1620365865,803975155)'::int4range))))))) * (((((((NULL) -
('(474859050,1775731862)'::int4range))) *
((('[-983784861,-282382318)'::int4range) *
('(-140169948,1238733762]'::int4range))))) *
((((('(-1867135479,-74554700)'::int4range) *
('(-1695460212,17429570]'::int4range))) *
((('(-214535461,1926110877)'::int4range) *
('(-947791902,1713917361)'::int4range)))))))));
INSERT INTO t2 (c0) VALUES ('[-397660019,1591220265)'::int4range);
INSERT INTO t1 (c0) VALUES ('[-1088781094,141410199)'::int4range);
INSERT INTO t0 (c0) VALUES ('(-1215891136,1763404524)'::int4range);
INSERT INTO t5 (c0) VALUES ('(-1728381552,-139639321]'::int4range);
VACUUM (FULL);
INSERT INTO t0 (c0) VALUES ('[-862737424,715353603]'::int4range),
('(924924175,1097230849]'::int4range),
('[-2061631199,-1125415721]'::int4range),
('[-819489600,-293351985)'::int4range), ('[4104640,114386456)'::int4range)
ON CONFLICT DO NOTHING;
INSERT INTO t0 (c0) VALUES ('[1374132844,1591220265]'::int4range);
INSERT INTO t4 (c0) VALUES ('(881901441,2122722689]'::int4range);
INSERT INTO t0 (c0) VALUES ('[495702522,731753738]'::int4range);
INSERT INTO t3 (c0) VALUES ('[-1972519429,1566304847]'::int4range),
((((((((('(-1022807245,-954966588)'::int4range) *
('(264675590,1762018031]'::int4range))) -
((('(1381482535,1737827666)'::int4range) -
('(-77944043,2122722689)'::int4range))))) -
((((('(10938108,410451329]'::int4range) *
('[-46898157,1939983679)'::int4range))) -
((('(-1103856588,1408520412]'::int4range) *
('(-293351985,1141563565]'::int4range))))))) *
((((((('(423735483,867993709)'::int4range) -
('[-2035014057,-1528484514)'::int4range))) *
((('[-1951859064,474859050)'::int4range) +
('[-2052901688,307317519)'::int4range))))) -
((((('(-1397045231,-420551207)'::int4range) -
('[486777807,1676545065)'::int4range))) *
((('[-1642355866,-1403156127]'::int4range) +
('[-1420209284,-1148239454)'::int4range))))))))),
('[-221665010,546608612)'::int4range) ON CONFLICT DO NOTHING;
INSERT INTO t0 (c0) VALUES (NULL);
INSERT INTO t1 (c0) VALUES ('(381776705,2063728809]'::int4range);
INSERT INTO t3 (c0) VALUES ('(317419199,1099722558]'::int4range);
INSERT INTO t5 (c0) VALUES ('(-1308205589,1572586896)'::int4range),
('[-467686702,-198607511]'::int4range),
('(-1512670643,1097230849]'::int4range);
INSERT INTO t1 (c0) VALUES ('[-1673647540,1240634517]'::int4range);
INSERT INTO t4 (c0) VALUES ('(803975155,1207294396]'::int4range);
INSERT INTO t2 (c0) VALUES ('[-2052901688,-2045828188]'::int4range),
('(-1769967271,939601939)'::int4range),
('[-176871872,1651353860]'::int4range);
INSERT INTO t4 (c0) VALUES ('(-2142140639,388238487)'::int4range);
INSERT INTO t1(c0) OVERRIDING SYSTEM VALUE
VALUES('(474859050,1353408300)'::int4range);
INSERT INTO t1 (c0) VALUES ('[-1793674559,1212559654]'::int4range);
INSERT INTO t0 (c0) VALUES ('[-673947761,464764637)'::int4range);
INSERT INTO t4 (c0) VALUES ('[-363729266,1903543937)'::int4range),
('(-1689212959,397166241)'::int4range),
('(-2140392190,587940864]'::int4range);
INSERT INTO t5 (c0) VALUES ('[-1187717266,107709161]'::int4range);
INSERT INTO t0 (c0) VALUES ('(-149436975,1996837665)'::int4range),
((((((((('[1338490828,1501182484)'::int4range) -
('(-262265515,1097230849]'::int4range))) *
((('(-649632748,935010260)'::int4range) *
('[-1455191261,1035375597]'::int4range))))) *
((((('(-1213572190,2098308461)'::int4range) -
('(-2140392190,1661129719)'::int4range))) *
((('[-1899665569,115687313)'::int4range) +
('(-2036148383,-698972348)'::int4range))))))) -
((((((('(-9898192,1636958539)'::int4range) +
('(486777807,1962222972)'::int4range))) - (((NULL) -
('[-1031702653,229353293)'::int4range))))) -
((((('(-1076431407,396190012]'::int4range) +
('[-1688585392,963120975]'::int4range))) +
((('[-932827393,1141563565]'::int4range) *
('[-211466194,1691255809]'::int4range))))))))),
('(1060553791,1097230849)'::int4range);
INSERT INTO t0(c0) OVERRIDING SYSTEM VALUE
VALUES('(293033051,661580987]'::int4range);
DISCARD PLANS;
INSERT INTO t4 (c0) VALUES ('(-2084768363,-2056736950)'::int4range);
INSERT INTO t0 (c0) VALUES ('(-1512670643,-1391242505)'::int4range) ON
CONFLICT DO NOTHING;
INSERT INTO t1 (c0) VALUES ('(-897755091,872731298]'::int4range);
INSERT INTO t1 (c0) VALUES ('(-161596197,1155685102)'::int4range),
('[1454634536,2098308461)'::int4range),
('(-1639845222,-1170430157]'::int4range);
INSERT INTO t1 (c0) VALUES ('[-62455448,976582984]'::int4range),
('(564658014,999120403)'::int4range), ((('[9024409,1387602771)'::int4range)
* ('[-709044438,-14073149]'::int4range)));
INSERT INTO t4 (c0) VALUES ('(-1797481532,1991036340]'::int4range);
INSERT INTO t2 (c0) VALUES ('[-1284434237,-1009930529)'::int4range);
INSERT INTO t2 (c0) VALUES ('[-488154128,1680232583]'::int4range);
INSERT INTO t4 (c0) VALUES (DEFAULT);
INSERT INTO t5 (c0) VALUES (NULL);
INSERT INTO t1 (c0) VALUES ('[-1864658730,1417483263]'::int4range);
INSERT INTO t0 (c0) VALUES ('[-1757862433,474859050]'::int4range);
INSERT INTO t5 (c0) VALUES ('[-1669429843,628986282]'::int4range);
INSERT INTO t5 (c0) VALUES ('(-1689212959,743220875]'::int4range);
INSERT INTO t5 (c0) VALUES ('[-229627171,1543605146]'::int4range);
INSERT INTO t1 (c0) VALUES ((((((((('(-1512670643,995544458]'::int4range) -
('[-1679344079,72850177)'::int4range))) *
((('[-408108896,1564422162]'::int4range) -
('[1134446496,1592327326)'::int4range))))) *
((((('(943834303,2037997815)'::int4range) *
('(-221665010,411288035]'::int4range))) +
((('(-334537563,619698137]'::int4range) +
('[474859050,1556074061]'::int4range))))))) *
((((((('[-445005313,553715236)'::int4range) -
('(-140169948,1569315420]'::int4range))) *
((('[-931556910,1733167376)'::int4range) *
('(-159181246,1584149485)'::int4range))))) -
((((('(-1716933731,-827663483)'::int4range) *
('[4104640,815027544]'::int4range))) - ((('(-293351985,965590]'::int4range)
* ('[-203324002,595490073]'::int4range)))))))));
DISCARD TEMPORARY;
INSERT INTO t0 (c0) VALUES ('(-759288323,1962222972)'::int4range);
INSERT INTO t1 (c0) VALUES ('(10938108,1962222972)'::int4range);
INSERT INTO t0 (c0) VALUES ('(-1227613830,4104640]'::int4range);
INSERT INTO t0 (c0) VALUES ('[-2049331253,55691758]'::int4range),
('[490830804,628986282]'::int4range), ('[-1746431371,1]'::int4range);
RESET ROLE;
INSERT INTO t1 (c0) VALUES ('[-77944043,2122722689)'::int4range);
INSERT INTO t0(c0) OVERRIDING USER VALUE
VALUES('(-576781707,1333692432]'::int4range);
DISCARD TEMP;
INSERT INTO t0 (c0) VALUES ('[-250498435,94327938)'::int4range);
INSERT INTO t0 (c0) VALUES ('[-1410171659,1733167376)'::int4range);
INSERT INTO t1 (c0) VALUES ('(-1009845352,-440002587]'::int4range);
INSERT INTO t1 (c0) VALUES (DEFAULT);
INSERT INTO t1 (c0) VALUES ('[-268156814,132501279]'::int4range);
INSERT INTO t1 (c0) VALUES ('(-330377942,1035950253]'::int4range);
INSERT INTO t1 (c0) VALUES ('(-1798702671,880384467]'::int4range);
INSERT INTO t1 (c0) VALUES ((((((((('(-549717521,396190012]'::int4range) +
('(-2090150311,-365710356]'::int4range))) +
((('[-1570225866,1238733762]'::int4range) +
('(-483058735,-181569592)'::int4range))))) *
((((('(-1718573980,1262082228)'::int4range) +
('[33636334,1030686968)'::int4range))) +
((('(-328559770,2123696103]'::int4range) *
('[-864120142,1586670493]'::int4range))))))) *
((((((('(867993709,1398124403)'::int4range) *
('(-2009209460,-1689212959]'::int4range))) *
((('[1049468944,1164345182)'::int4range) *
('[295789041,908535612)'::int4range))))) -
((((('(-936629889,1463843509]'::int4range) +
('(799747496,1201324927]'::int4range))) - ((('(1,740455090]'::int4range) +
('[-1234084773,867993709)'::int4range)))))))));
INSERT INTO t0 (c0) VALUES ('[-125148333,196802894)'::int4range);
INSERT INTO t1 (c0) VALUES ('(192992210,2025938066]'::int4range);
INSERT INTO t0 (c0) VALUES ('[1,290854718]'::int4range);
INSERT INTO t0 (c0) VALUES ((((((((('[-392432227,289181186]'::int4range) -
('[-1397442598,1737827666]'::int4range))) +
((('[-1570225866,1844950750)'::int4range) *
('[1026826599,1991036340]'::int4range))))) + (((((NULL) +
('[-560348088,-466638634]'::int4range))) *
('[-1570225866,1568221637)'::int4range))))) *
((((((('[-1338912377,-819489600)'::int4range) *
('(214023681,1866578073)'::int4range))) +
((('(-967588511,535293240)'::int4range) + (NULL))))) -
((((('(1622771817,1876920311)'::int4range) *
('[-330377942,1383194086]'::int4range))) -
((('[-688136578,1568221637]'::int4range) -
('(-1667165854,1867541956]'::int4range)))))))));
INSERT INTO t1 (c0) VALUES ('[-1964409708,-46898157)'::int4range);
INSERT INTO t0 (c0) VALUES ('[-1304271141,1011053948)'::int4range);
INSERT INTO t0 (c0) VALUES ('[-140169948,1269988145]'::int4range);
INSERT INTO t1 (c0) VALUES ('(-1421669383,-1205023282)'::int4range);
INSERT INTO t1 (c0) VALUES ('[-627461467,-166721140)'::int4range);
INSERT INTO t1 (c0) VALUES ('(735270161,871294266)'::int4range);
INSERT INTO t1 (c0) VALUES ('[81952348,1030686968)'::int4range);
INSERT INTO t0 (c0) VALUES ('(-1741922286,1256838135)'::int4range);
INSERT INTO t1 (c0) VALUES
(((((((range_merge('[8995531,1333692432]'::int4range,
'[-1163804205,803975155)'::int4range)) *
((('(-1700119028,-1111676316)'::int4range) -
('[-245904803,1143171567]'::int4range))))) *
('[-1095129257,580212198)'::int4range))) +
((((((('(-432802262,1168122653]'::int4range) +
('(-705888927,343011264)'::int4range))) +
((('[207720611,1003133194]'::int4range) -
('(-346027642,1423057167)'::int4range))))) -
('[-821754124,-33955528]'::int4range)))));
INSERT INTO t0 (c0) VALUES ('(-1284426761,-400784586]'::int4range),
('(-1225863567,808174420)'::int4range),
('[200480115,397345667)'::int4range);
INSERT INTO t1 (c0) VALUES ('(523770768,1352995458]'::int4range);
INSERT INTO t0 (c0) VALUES ('(-2084130290,1359748186]'::int4range);
INSERT INTO t0 (c0) VALUES ('(-2106782404,-1)'::int4range),
('(-495877303,486777807]'::int4range),
('[-2142140639,-1847149894)'::int4range);
INSERT INTO t1 (c0) VALUES ((((((((('[-910415449,-856874282]'::int4range) -
('[-419262331,115687313)'::int4range))) +
((('[-1630889975,596176223)'::int4range) +
('(-1761953640,-1496925589)'::int4range))))) +
('[-1964409708,1123515505]'::int4range))) *
((('(290854718,527540112]'::int4range) +
((((('(396190012,571523334]'::int4range) *
('(-1512670643,-675891923]'::int4range))) +
((('[364186688,1238733762)'::int4range) *
('[-281935683,1292083125)'::int4range)))))))));
INSERT INTO t1 (c0) VALUES ('(396190012,1491967189)'::int4range);
INSERT INTO t1 (c0) VALUES ('(202398737,1625828854]'::int4range);
INSERT INTO t1 (c0) VALUES ('[1568221637,1899171843)'::int4range);
INSERT INTO t1 (c0) VALUES ('[-2052901688,1120875420)'::int4range);
INSERT INTO t1 (c0) VALUES ((((((((('[-1021468126,1381482535]'::int4range) *
('[-1148239454,-550051939]'::int4range))) *
((('(-1512670643,-41224411]'::int4range) -
('[1353408300,1413540491)'::int4range))))) *
('[-282382318,1382947283)'::int4range))) *
((((((('[-8466749,787354896)'::int4range) +
('(-793778913,185300023]'::int4range))) *
((('(-361597083,483846806]'::int4range) +
('[-282382318,306029004]'::int4range))))) *
((((('[-1437213739,1602560517]'::int4range) *
('(90029517,1321609599]'::int4range))) *
((('(-1800051603,-610388604)'::int4range) -
('[-1241017409,1036470167)'::int4range)))))))));
INSERT INTO t0(c0) OVERRIDING SYSTEM VALUE
VALUES('(657233620,1168122653)'::int4range);
INSERT INTO t1 (c0) VALUES ('[-282382318,1878824232)'::int4range);
INSERT INTO t1 (c0) VALUES ('(856437029,1591220265]'::int4range);
INSERT INTO t0 (c0) VALUES ('(1207871596,2107442137]'::int4range);
INSERT INTO t0 (c0) VALUES ('(-1841210902,-1617388693]'::int4range);
INSERT INTO t0 (c0) VALUES ('[-2067576753,684183030)'::int4range);
INSERT INTO t0 (c0) VALUES ('[-432802262,1157654470)'::int4range);
INSERT INTO t1 (c0) VALUES ('[1737827666,2056469902]'::int4range);
INSERT INTO t0 (c0) VALUES ('[-1871547302,-1164403384]'::int4range);
INSERT INTO t0 (c0) VALUES ('(-1169832949,292057808)'::int4range);
INSERT INTO t0 (c0) VALUES ((((((((('[-126487589,845361448]'::int4range) -
('[-1782462708,1025656950]'::int4range))) *
((('[-1856146896,981292884)'::int4range) +
('[-214535461,1626192764]'::int4range))))) *
((((('[-1842634525,725084959)'::int4range) +
('[-1813566611,856437029]'::int4range))) *
((('[-232536994,1403632000]'::int4range) +
('[-1283293550,115687313]'::int4range))))))) *
((((((('[-268156814,1509769239)'::int4range) *
('[1025656950,1176962686]'::int4range))) *
((('(1322277090,1746611640]'::int4range) -
('[-64216753,1789219307)'::int4range))))) +
(range_merge((('(-910415449,50536199]'::int4range) -
('(-1025659720,1168122653]'::int4range)),
(('[-1585450892,1476352524]'::int4range) *
('[-1908949161,2038514859]'::int4range))))))));
INSERT INTO t0 (c0) VALUES ('(94396800,1465481374)'::int4range);
INSERT INTO t1 (c0) VALUES ('[272164226,1940681247]'::int4range);
INSERT INTO t0 (c0) VALUES ('[-1507553974,202398737]'::int4range),
('[-1791304502,628986282]'::int4range),
('[776822801,2122722689]'::int4range);
INSERT INTO t0 (c0) VALUES ('[-2141646075,-6219785]'::int4range);
INSERT INTO t1 (c0) VALUES ('[1412348330,2122722689]'::int4range);
INSERT INTO t0 (c0) VALUES ('(-1858090938,274780116]'::int4range);
INSERT INTO t0 (c0) VALUES (DEFAULT);
INSERT INTO t1(c0) OVERRIDING USER VALUE
VALUES('(-2067576753,825399974]'::int4range);
INSERT INTO t0 (c0) VALUES ((((((((('(-1097523623,2098308461]'::int4range) +
(NULL))) * ((('(-2061066937,1915980190]'::int4range) +
('[-222480844,-42624264)'::int4range))))) -
((((('(-1843855081,290854718)'::int4range) -
('[-890553616,1918750153)'::int4range))) -
((('(474859050,1421948971]'::int4range) -
('(-479280323,987192797)'::int4range))))))) +
('(-141526740,665881862)'::int4range)));
INSERT INTO t0 (c0) VALUES ('[-1781282755,290854718)'::int4range);
ALTER TABLE ONLY t0 ALTER c0 SET STATISTICS 2308, REPLICA IDENTITY DEFAULT;
INSERT INTO t0 (c0) VALUES ('(-1369682860,89439003]'::int4range);
INSERT INTO t0 (c0) VALUES ('(210393241,596176223]'::int4range),
('(-1823787780,-309574270)'::int4range), (NULL);
INSERT INTO t0(c0) OVERRIDING SYSTEM VALUE
VALUES('(762551249,1987533740)'::int4range);
INSERT INTO t0 (c0) VALUES ('[-1638860476,-1457453583)'::int4range);
INSERT INTO t0 (c0) VALUES ((((((('[1333692432,1358335449]'::int4range) *
((('(-296118309,-221665010)'::int4range) +
('[-221665010,1404952771]'::int4range))))) *
('[-1343782336,1727950535]'::int4range))) -
((('(1580364581,1595133684)'::int4range) *
('[-282785877,2090306111]'::int4range)))));
INSERT INTO t0 (c0) VALUES ('(-139639321,2098308461)'::int4range);
INSERT INTO t1 (c0) VALUES ('[-512086966,497920350)'::int4range);
INSERT INTO t1 (c0) VALUES ((((((((('[868729749,1030686968)'::int4range) *
('[-461562389,299503712)'::int4range))) +
((('(-1769967271,-322824407)'::int4range) *
('[71921841,1259890561]'::int4range))))) -
((((('[-1448054137,1030686968]'::int4range) *
('[-934306885,1357361531)'::int4range))) +
((('(-530416508,170344842]'::int4range) -
('[-309521243,1622181467]'::int4range))))))) *
((((((('[956410809,2108610281]'::int4range) -
('(299844987,1169688255)'::int4range))) -
((('(1353408300,1635585538)'::int4range) *
('[-719123375,198238462)'::int4range))))) +
((((('[-749885038,1602835271]'::int4range) -
('(-1525126211,1686831657)'::int4range))) -
((('(-627461467,-569598930)'::int4range) -
('[-1001053010,-100032308]'::int4range)))))))));
INSERT INTO t1 (c0) VALUES ('(96187622,1407059728]'::int4range);
INSERT INTO t1 (c0) VALUES ('(504923162,1263223184]'::int4range);
INSERT INTO t1 (c0) VALUES (DEFAULT);
INSERT INTO t0 (c0) VALUES ('(-1125415721,1717964047]'::int4range);
INSERT INTO t1 (c0) VALUES ('[59742147,1870274466)'::int4range) ON CONFLICT
DO NOTHING;
INSERT INTO t1(c0) OVERRIDING SYSTEM VALUE
VALUES('[-1548908916,1607089922]'::int4range);
INSERT INTO t1(c0) OVERRIDING SYSTEM VALUE
VALUES('(-1808827826,-765914935]'::int4range);
INSERT INTO t1 (c0) VALUES ('(-1769967271,440697151)'::int4range);
INSERT INTO t1 (c0) VALUES ('[-2067576753,889850841]'::int4range);
INSERT INTO t1 (c0) VALUES ((((('(-330377942,-139639321]'::int4range) -
((((('(-717604363,486777807)'::int4range) -
('[-743637252,1049634511)'::int4range))) -
((('[-31856806,1906000229]'::int4range) +
('[-1595828264,497231768]'::int4range))))))) -
((((((('[-2071652582,612738068)'::int4range) +
('[-1408287382,2027970397)'::int4range))) -
((('(-1013917160,555343005]'::int4range) *
('[991221074,1632165844)'::int4range))))) *
((((('(1642879585,2038514859]'::int4range) *
('[-2142140639,703178142)'::int4range))) +
((('[622447256,1733167376)'::int4range) -
('(-491081047,435642115]'::int4range)))))))));
INSERT INTO t0(c0) OVERRIDING USER VALUE
VALUES('(-1951985316,-1148239454]'::int4range);
INSERT INTO t0 (c0) VALUES ('[17429570,1591220265)'::int4range);
INSERT INTO t1 (c0) VALUES ('(94327938,1198924224]'::int4range);
INSERT INTO t0 (c0) VALUES ('[-1509632365,1056930772]'::int4range);
INSERT INTO t1 (c0) VALUES ('(-1924998434,-1793674559)'::int4range);
INSERT INTO t1 (c0) VALUES ('[-294372499,1262082228]'::int4range);
INSERT INTO t1 (c0) VALUES ('[-462729590,-300625755]'::int4range);
INSERT INTO t0 (c0) VALUES ('(-46898157,1591220265]'::int4range);
NOTIFY asdf;
INSERT INTO t0 (c0) VALUES ('[-1057964527,-1)'::int4range);
ANALYZE;
INSERT INTO t1 (c0) VALUES ('(-1915797955,234157772)'::int4range);
INSERT INTO t0 (c0) VALUES ((((((((('[-1843378596,-267443882)'::int4range) -
('[-1250472679,778413678]'::int4range))) *
((('[-1663443656,-232546368)'::int4range) -
('[-1094482487,1414685471]'::int4range))))) *
((((('[-371991347,1106206094]'::int4range) *
('(-2087175970,1972952179)'::int4range))) -
((('[-806580275,327356056]'::int4range) *
('(-1057964527,298374594)'::int4range))))))) -
((((((('[-386627643,94327938)'::int4range) * ('[1,1972952179)'::int4range)))
+ ((('[-1624401738,290854718)'::int4range) + (NULL))))) *
((((('(-910415449,1972952179)'::int4range) *
('(1733167376,1897312185)'::int4range))) -
((('[115687313,1737827666]'::int4range) -
('[1121882514,2027970397]'::int4range)))))))));
INSERT INTO t0(c0) OVERRIDING SYSTEM VALUE
VALUES('(-1159835934,-448762417]'::int4range);
INSERT INTO t1 (c0) VALUES ('(-77944043,1773590078)'::int4range);
INSERT INTO t0 (c0) VALUES ('(-46898157,1262082228)'::int4range);
INSERT INTO t0 (c0) VALUES ('[-598405911,817834948)'::int4range);
INSERT INTO t0 (c0) VALUES ('(277683076,1168188871)'::int4range);
INSERT INTO t0 (c0) VALUES ('[-716798710,-314596634]'::int4range);
INSERT INTO t1 (c0) VALUES ('(-2063987367,-920488185]'::int4range);
INSERT INTO t1 (c0) VALUES ('[-503460664,1262082228]'::int4range);
INSERT INTO t1(c0) OVERRIDING SYSTEM VALUE
VALUES('(-1519594733,1262082228]'::int4range),
('(-697298977,792859724)'::int4range),
('(1548622768,1867748163]'::int4range);
INSERT INTO t0 (c0) VALUES ('[-156891072,1150129299]'::int4range);
INSERT INTO t1 (c0) VALUES ('[233321836,1225425302)'::int4range);
INSERT INTO t1(c0) OVERRIDING USER VALUE
VALUES('[-1219157801,1262082228]'::int4range);
INSERT INTO t0 (c0) VALUES ('[-2141646075,-1635839611]'::int4range);
INSERT INTO t1 (c0) VALUES ('[-920679191,385402817]'::int4range);
INSERT INTO t0 (c0) VALUES ('[-1517975475,-894705703]'::int4range);
INSERT INTO t0 (c0) VALUES ('[718995232,1434252884)'::int4range),
('(-184459121,424484294)'::int4range),
('[-1186010297,1359476046)'::int4range);
INSERT INTO t1 (c0) VALUES ('[207834882,1962222972]'::int4range);
INSERT INTO t0 (c0) VALUES ('(-1552826979,699901933)'::int4range);
INSERT INTO t1 (c0) VALUES ('(-2134268302,809783863)'::int4range);
INSERT INTO t0 (c0) VALUES ((((((((('(-201736241,119472483]'::int4range) *
('(-1988898600,1216782682)'::int4range))) -
((('(-1369682860,-259703716]'::int4range) *
('(-1628433326,1443913465]'::int4range))))) -
((((('[-1957902223,581215709)'::int4range) *
('(-1411520870,1298388741)'::int4range))) +
((('(-1446561859,-62455448]'::int4range) *
('[-330377942,2138727111)'::int4range))))))) +
((((((('(202398737,541875098]'::int4range) -
('[-1529831673,1224196893]'::int4range))) - (((NULL) *
('[1025656950,1267707168)'::int4range))))) *
((((('[1733167376,2055508419]'::int4range) +
('(1141563565,1795734357]'::int4range))) *
((('(-2140392190,30382449]'::int4range) *
('(655549802,1404996488)'::int4range)))))))));
INSERT INTO t0(c0) OVERRIDING USER VALUE
VALUES('(-62455448,1056930772]'::int4range);
INSERT INTO t0(c0) OVERRIDING USER VALUE
VALUES('(246514571,1674461144]'::int4range);
INSERT INTO t0 (c0) VALUES ('(-1368652577,1690594739]'::int4range);
INSERT INTO t0 (c0) VALUES ('[529344206,1168122653]'::int4range);
INSERT INTO t1 (c0) VALUES ('[-579756074,1794042534)'::int4range);
INSERT INTO t0 (c0) VALUES (NULL);
INSERT INTO t1(c0) OVERRIDING SYSTEM VALUE
VALUES('(464583473,969486917]'::int4range);
INSERT INTO t1 (c0) VALUES ('(-658471669,-400784586)'::int4range),
('(-1076742743,0)'::int4range), ('(-1917796028,-191859408)'::int4range);
INSERT INTO t1 (c0) VALUES ('[-1120812376,1991036340)'::int4range);
INSERT INTO t1(c0) OVERRIDING SYSTEM VALUE
VALUES('[392184940,1442302405]'::int4range);
INSERT INTO t1 (c0) VALUES ('[-2135555890,1454678926)'::int4range);
INSERT INTO t1(c0) OVERRIDING USER VALUE
VALUES('(-114244833,2027970397)'::int4range);
INSERT INTO t1 (c0) VALUES ('[-864333456,2098308461]'::int4range);
INSERT INTO t1 (c0) VALUES ('[922901267,950148067]'::int4range);
INSERT INTO t0 (c0) VALUES ('(-1125415721,1737827666]'::int4range);
INSERT INTO t0 (c0) VALUES ('[1192509578,2013074355)'::int4range);
INSERT INTO t0 (c0) VALUES ('[-1925980901,17429570]'::int4range);
INSERT INTO t1 (c0) VALUES ('(28131223,1931065190]'::int4range);
INSERT INTO t1 (c0) VALUES ('[-1783817125,1353408300]'::int4range);
INSERT INTO t0 (c0) VALUES ('[803975155,1434252884)'::int4range);
INSERT INTO t0 (c0) VALUES ('[-759288323,-68454793)'::int4range);
INSERT INTO t0 (c0) VALUES ('[690220215,1591220265]'::int4range);
INSERT INTO t0 (c0) VALUES ('[-650187642,83578134]'::int4range);
INSERT INTO t1 (c0) VALUES ('(-694416940,1123075894)'::int4range);
INSERT INTO t0 (c0) VALUES ('(-2137399699,-1669057991]'::int4range);
INSERT INTO t0 (c0) VALUES ('(-1718573980,115687313]'::int4range);
INSERT INTO t0 (c0) VALUES ('(-1406129180,1468631913)'::int4range);
INSERT INTO t0(c0) OVERRIDING SYSTEM VALUE
VALUES('(-1890810224,-440002587)'::int4range);
INSERT INTO t1 (c0) VALUES ((((((((('(-627461467,390778780]'::int4range) -
('(-1021765875,1496926965]'::int4range))) *
('(-1148239454,1042359727]'::int4range))) -
((((('[-615845435,1591220265]'::int4range) *
('(1141563565,1737827666]'::int4range))) -
((('[-2141646075,-77944043)'::int4range) +
('(-1699401909,1448570992]'::int4range))))))) -
((((((('[-1085150487,-62455448)'::int4range) -
('[-1906896724,-1857076652)'::int4range))) +
((('(-140169948,1471713301)'::int4range) -
('(-1974074454,1733167376]'::int4range))))) *
((((('(-1025462836,379436536)'::int4range) -
('[-1570225866,1062559937]'::int4range))) +
('(-996752730,2103132840)'::int4range)))))));
INSERT INTO t0 (c0) VALUES ('[1,353785345)'::int4range);
INSERT INTO t0 (c0) VALUES ('[-363729266,1155685102)'::int4range);
INSERT INTO t0 (c0) VALUES ('(-1549081676,-1481032609)'::int4range);
INSERT INTO t0 (c0) VALUES ('[-1718573980,1030686968]'::int4range);
INSERT INTO t1 (c0) VALUES ('(-819489600,1666033412]'::int4range);
INSERT INTO t1 (c0) VALUES ('(-1718573980,-1019460940)'::int4range);
INSERT INTO t0 (c0) VALUES ('[-93294703,272000933)'::int4range);
INSERT INTO t0 (c0) VALUES ('(1591220265,2098308461)'::int4range);
ANALYZE t0,t1;
PG Bug reporting form <noreply@postgresql.org> writes:
> database4=# explain analyze SELECT t0.c0 FROM t0 INNER JOIN t1* ON
> ((t1.c0)=(((t1.c0)-(((((t1.c0)*('(-795716537,-245904803]'::int4range)))-(range_merge(t1.c0,
> t0.c0))))))) GROUP BY t0.c0;
[ planner incorrectly prefers sort/group over hashed grouping ]
I don't think there's much to be done about this. The core of the
problem is that the estimate of the number of rows coming into the
grouping step is off by more than two orders of magnitude:
> -> Nested Loop (cost=0.00..363.13 rows=70 width=13) (actual
> time=0.055..8.431 rows=12688 loops=1)
There's little point in complaining that the cost of the grouping
is off by a factor of two when there's such a large error in its
input information.
So the main thing that would have to be done is improving the
selectivity estimate for
> Join Filter: (t1.c0 = (t1.c0 - ((t1.c0 *
> '[-795716536,-245904802)'::int4range) - range_merge(t1.c0, t0.c0))))
If this condition weren't so obviously random junk generated by
a fuzzer, maybe people would be motivated to try to improve that.
But as it stands, there's neither a clear path to improving it
nor a lot of motivation to try.
regards, tom lane
On Mon, 2025-05-19 at 12:42 +0000, PG Bug reporting form wrote:
> database4=# explain analyze SELECT t0.c0 FROM t0 INNER JOIN t1* ON
> ((t1.c0)=(((t1.c0)-(((((t1.c0)*('(-795716537,-245904803]'::int4range)))-(range_merge(t1.c0,
> t0.c0))))))) GROUP BY t0.c0;
>
> [explicitly disabling sort leads to a faster execution plan]
I see no bug here.
The join condition is so complicated that the planner has a hard
time estimating the number of result rows for the join.
It guesses way to low, so the sort is expensive, and avoiding it is cheaper.
The optimizer cannot be perfect, particularly in the face of complicated
expressions like that.
Yours,
Laurenz Albe
Re: BUG #18935: The optimiser's choice of sort doubles the execution time.
From
Alena Rybakina
Date:
Hi!
On 19.05.2025 08:42, PG Bug reporting form wrote:
The following bug has been logged on the website: Bug reference: 18935 Logged by: hongjun xiao Email address: xiaohongjun@stu.xidian.edu.cn PostgreSQL version: 17.4 Operating system: Ubuntu 20.04.6 LTS Description: database4=# explain analyze SELECT t0.c0 FROM t0 INNER JOIN t1* ON ((t1.c0)=(((t1.c0)-(((((t1.c0)*('(-795716537,-245904803]'::int4range)))-(range_merge(t1.c0, t0.c0))))))) GROUP BY t0.c0; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Group (cost=365.27..365.62 rows=70 width=13) (actual time=16.143..17.958 rows=121 loops=1) Group Key: t0.c0 -> Sort (cost=365.27..365.45 rows=70 width=13) (actual time=16.141..16.540 rows=12688 loops=1) Sort Key: t0.c0 Sort Method: quicksort Memory: 385kB -> Nested Loop (cost=0.00..363.13 rows=70 width=13) (actual time=0.055..8.431 rows=12688 loops=1) Join Filter: (t1.c0 = (t1.c0 - ((t1.c0 * '[-795716536,-245904802)'::int4range) - range_merge(t1.c0, t0.c0)))) Rows Removed by Join Filter: 1650 -> Seq Scan on t0 (cost=0.00..2.34 rows=134 width=13) (actual time=0.025..0.040 rows=134 loops=1) -> Materialize (cost=0.00..2.61 rows=107 width=13) (actual time=0.000..0.005 rows=107 loops=134) -> Seq Scan on t1 (cost=0.00..2.07 rows=107 width=13) (actual time=0.015..0.028 rows=107 loops=1) Planning Time: 0.105 ms Execution Time: 17.998 ms (13 rows) database4=# set enable_sort=false; SET database4=# explain analyze SELECT t0.c0 FROM t0 INNER JOIN t1* ON ((t1.c0)=(((t1.c0)-(((((t1.c0)*('(-795716537,-245904803]'::int4range)))-(range_merge(t1.c0, t0.c0))))))) GROUP BY t0.c0; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=363.30..364.00 rows=70 width=13) (actual time=8.034..8.042 rows=121 loops=1) Group Key: t0.c0 Batches: 1 Memory Usage: 40kB -> Nested Loop (cost=0.00..363.13 rows=70 width=13) (actual time=0.019..5.680 rows=12688 loops=1) Join Filter: (t1.c0 = (t1.c0 - ((t1.c0 * '[-795716536,-245904802)'::int4range) - range_merge(t1.c0, t0.c0)))) Rows Removed by Join Filter: 1650 -> Seq Scan on t0 (cost=0.00..2.34 rows=134 width=13) (actual time=0.010..0.033 rows=134 loops=1) -> Materialize (cost=0.00..2.61 rows=107 width=13) (actual time=0.000..0.003 rows=107 loops=134) -> Seq Scan on t1 (cost=0.00..2.07 rows=107 width=13) (actual time=0.004..0.007 rows=107 loops=1) Planning Time: 0.060 ms Execution Time: 8.064 ms (11 rows)
I noticed underestimation problem here because of inability to predict the actual number of rows for this expression by the planner: range_merge(t1.c0, t0.c0))
As I know it is not possible to create extended statistics on different relations.
CREATE STATISTICS t0_t1_stats (dependencies) ON c0 FROM t0, t1;But I added index on table t0: create index idx on t0(c0) and sort operation was skipped because of index scan but I could got this only with disable seqscan:
set enable_seqscan = off;
Group (cost=0.14..375.12 rows=70 width=13) (actual time=0.129..44.850 rows=121.00 loops=1)
Group Key: t0.c0
Buffers: shared hit=3
-> Nested Loop (cost=0.14..374.94 rows=70 width=13) (actual time=0.125..37.008 rows=12688.00 loops=1)
Join Filter: (t1.c0 = (t1.c0 - ((t1.c0 * '[-795716536,-245904802)'::int4range) - range_merge(t1.c0, t0.c0))))
Rows Removed by Join Filter: 1650
Buffers: shared hit=3
-> Index Only Scan using idx on t0 (cost=0.14..14.15 rows=134 width=13) (actual time=0.067..0.267 rows=134.00 loops=1)
Heap Fetches: 134
Index Searches: 1
Buffers: shared hit=2
-> Materialize (cost=0.00..2.61 rows=107 width=13) (actual time=0.000..0.016 rows=107.00 loops=134)
Storage: Memory Maximum Storage: 21kB
Buffers: shared hit=1
-> Seq Scan on t1 (cost=0.00..2.07 rows=107 width=13) (actual time=0.026..0.063 rows=107.00 loops=1)
Disabled: true
Buffers: shared hit=1
Planning Time: 0.289 ms
Execution Time: 44.956 ms
(19 rows)
The execution ti,e on my laptop is almost the same with disabled sorting:
set enable_sort = off;
-----------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=363.30..364.00 rows=70 width=13) (actual time=55.001..55.077 rows=121.00 loops=1)
Group Key: t0.c0
Batches: 1 Memory Usage: 40kB
Buffers: shared hit=2
-> Nested Loop (cost=0.00..363.13 rows=70 width=13) (actual time=0.050..38.315 rows=12688.00 loops=1)
Join Filter: (t1.c0 = (t1.c0 - ((t1.c0 * '[-795716536,-245904802)'::int4range) - range_merge(t1.c0, t0.c0))))
Rows Removed by Join Filter: 1650
Buffers: shared hit=2
-> Seq Scan on t0 (cost=0.00..2.34 rows=134 width=13) (actual time=0.024..0.065 rows=134.00 loops=1)
Buffers: shared hit=1
-> Materialize (cost=0.00..2.61 rows=107 width=13) (actual time=0.000..0.014 rows=107.00 loops=134)
Storage: Memory Maximum Storage: 21kB
Buffers: shared hit=1
-> Seq Scan on t1 (cost=0.00..2.07 rows=107 width=13) (actual time=0.007..0.023 rows=107.00 loops=1)
Buffers: shared hit=1
Planning Time: 0.144 ms
Execution Time: 55.166 ms
(17 rows)
Original query plan:
-----------------------------------------------------------------------------------------------------------------------------
Group (cost=365.27..365.62 rows=70 width=13) (actual time=47.471..51.439 rows=121.00 loops=1)
Group Key: t0.c0
Buffers: shared hit=5
-> Sort (cost=365.27..365.45 rows=70 width=13) (actual time=47.467..48.234 rows=12688.00 loops=1)
Sort Key: t0.c0
Sort Method: quicksort Memory: 385kB
Buffers: shared hit=5
-> Nested Loop (cost=0.00..363.13 rows=70 width=13) (actual time=0.077..34.222 rows=12688.00 loops=1)
Join Filter: (t1.c0 = (t1.c0 - ((t1.c0 * '[-795716536,-245904802)'::int4range) - range_merge(t1.c0, t0.c0))))
Rows Removed by Join Filter: 1650
Buffers: shared hit=2
-> Seq Scan on t0 (cost=0.00..2.34 rows=134 width=13) (actual time=0.028..0.061 rows=134.00 loops=1)
Buffers: shared hit=1
-> Materialize (cost=0.00..2.61 rows=107 width=13) (actual time=0.000..0.015 rows=107.00 loops=134)
Storage: Memory Maximum Storage: 21kB
Buffers: shared hit=1
-> Seq Scan on t1 (cost=0.00..2.07 rows=107 width=13) (actual time=0.016..0.053 rows=107.00 loops=1)
Buffers: shared hit=1
Planning:
Buffers: shared hit=25 read=3
Planning Time: 0.863 ms
Execution Time: 51.609 ms
-- Regards, Alena Rybakina Postgres Professional