On Thu Oct 2, 2025 at 5:49 AM -03, Richard Guo wrote: > On Thu, Oct 2, 2025 at 10:39 AM Richard Guo <[email protected]> wrote: >> It seems eager aggregation doesn't cope well with parallel plans for >> this query. Looking into it. > > It turns out that this is not related to parallel plans but rather to > poor size estimates. > > [ ... ]
> Matheus, I wonder if you could help run TPC-DS again with this patch, > this time with nested loops disabled for all queries. > Thanks for all the details. I've disabled the nested loops and executed the benchmark again and the results look much better! I see a 55% improvement on query_31 on my machine now (MacOS M3 Max). The only query that I see a considerable regression is query 23 which I get a 23% worst execution time. I'm attaching the EXPLAIN(ANALYZE) output from master and from the patched version if it's interesting. I'm also attaching a csv with the planning time and execution time from master and the patched version for all queries. It contains the % of difference between the executions. Negative numbers means that the patched version using eager aggregation is faster. (I loaded this csv on a postgres table and played with some queries to analyze the results). I'm just wondering if there is anything that can be done on the planner to prevent this type of situation? -- Matheus Alcantara
query-23.master.explain
Description: Binary data
query-23.patch.explain
Description: Binary data
Query,Patched Planning (ms),Patched Execution (ms),Master Planning (ms),Master Execution (ms),Planning Diff (%),Execution Diff (%) query_1.sql,5.197,343109.883,5.718,342439.125,-9.111577474641482,0.19587656638241105 query_10.sql,9.652,1907.724,7.42,1711.916,30.080862533692716,11.4379443851217 query_11.sql,2.097,3679.389,1.902,12420.909,10.252365930599373,-70.37745788170577 query_12.sql,3.706,134.387,6.555,120.692,-43.46300533943555,11.34706525701787 query_13.sql,4.024,1470.213,3.821,1465.751,5.312745354619206,0.3044173259987535 query_14.sql,6.325,3035.944,5.998,3058.458,5.45181727242414,-0.7361225820331724 query_15.sql,1.706,223.125,1.782,221.967,-4.264870931537602,0.5216991714984601 query_16.sql,4.3,335.252,3.871,332.84,11.08240764660294,0.724672515322688 query_17.sql,17.019,586.035,14.329,584.251,18.77311745411402,0.30534821506509907 query_18.sql,4.558,831.184,4.739,819.676,-3.819371175353451,1.4039693732645488 query_19.sql,4.043,348.386,3.426,345.351,18.009340338587272,0.8788160451251118 query_2.sql,1.084,1009.305,1.137,1009.213,-4.66138962181178,0.009116014161528293 query_20.sql,1.411,197.526,1.331,196.463,6.010518407212627,0.5410688017591183 query_21.sql,3.656,759.008,3.374,759.377,8.358032009484292,-0.048592464612427624 query_22.sql,1.062,9664.424,1.155,9720.983,-8.051948051948049,-0.5818238752191963 query_23.sql,6.317,6733.136,2.386,5465.139,164.75272422464374,23.20155077482934 query_24.sql,4.863,71.777,6.99,69.682,-30.42918454935622,3.0065153124192743 query_25.sql,32.706,565.499,29.09,567.284,12.430388449639063,-0.314657208734949 query_26.sql,4.732,500.593,3.597,494.797,31.554072838476515,1.1713894789176151 query_27.sql,1.946,800.924,1.834,795.803,6.106870229007626,0.6435009669478478 query_28.sql,1.403,2115.748,1.177,2109.951,19.201359388275275,0.2747457168436625 query_29.sql,20.743,680.826,18.767,697.571,10.529120264293702,-2.400472496706429 query_3.sql,1.048,306.902,1.037,338.807,1.0607521697203588,-9.416865649174907 query_30.sql,2.163,23196.843,2.62,23227.62,-17.442748091603065,-0.1325017371560161 query_31.sql,3.805,2156.624,3.99,4813.289,-4.636591478696743,-55.19437956042116 query_32.sql,1.376,369.863,1.426,379.844,-3.5063113604488114,-2.627657669990837 query_33.sql,5.592,683.848,4.386,671.533,27.49658002735977,1.8338637118354484 query_34.sql,2.706,293.647,2.868,293.764,-5.648535564853554,-0.03982788905380463 query_35.sql,2.297,1714.709,2.327,1709.587,-1.2892135797163646,0.29960452436758533 query_36.sql,1.341,959.408,1.406,958.635,-4.623044096728304,0.08063548691629499 query_37.sql,3.266,701.037,3.338,692.29,-2.156980227681248,1.2634878446893747 query_38.sql,1.938,2983.255,1.867,2970.44,3.802892340653452,0.43141756776773993 query_39.sql,2.434,4296.654,2.185,4297.245,11.395881006864993,-0.013752997560051609 query_4.sql,4.104,6885.96,3.93,20300.931,4.427480916030532,-66.08057039354502 query_40.sql,4.232,227.916,3.992,226.594,6.0120240480961975,0.5834223324536407 query_41.sql,0.85,1895.989,0.825,1917.606,3.030303030303033,-1.127291007641818 query_42.sql,1.134,216.127,1.088,215.79,4.227941176470571,0.15617035080403055 query_43.sql,1.13,724.987,1.068,724.42,5.805243445692868,0.07826951216145431 query_44.sql,1.007,1009.076,0.973,1015.087,3.4943473792394575,-0.5921659916834682 query_45.sql,2.491,146.108,2.888,148.276,-13.746537396121877,-1.4621381747551905 query_46.sql,2.585,663.085,2.231,679.81,15.867324069923805,-2.460246245274402 query_47.sql,2.107,3566.484,2.349,4028.359,-10.302256279267773,-11.465586855590578 query_48.sql,2.327,1417.187,2.468,1429.552,-5.713128038897894,-0.8649562939997992 query_49.sql,5.191,1332.436,5.117,1300.731,1.446159859292551,2.437475542598733 query_5.sql,3.996,1254.475,3.78,1239.619,5.71428571428572,1.1984327442544842 query_50.sql,3.58,1306.014,2.578,1280.202,38.86733902249807,2.0162443114445923 query_51.sql,1.138,1937.95,1.043,1927.959,9.108341323106423,0.518216414353209 query_52.sql,1.057,216.683,1.026,217.304,3.0214424951266974,-0.2857747671464903 query_53.sql,1.689,299.636,1.477,299.117,14.353419092755582,0.17351069982649112 query_54.sql,3.396,690.892,2.901,687.181,17.063081695966915,0.5400323932122705 query_55.sql,1.041,215.656,0.958,216.543,8.663883089770351,-0.409618412971096 query_56.sql,6.743,696.477,5.359,682.625,25.825713752565782,2.0292254165903643 query_57.sql,2.859,1935.809,2.396,1971.9,19.323873121869788,-1.8302652264313668 query_58.sql,2.893,761.302,2.47,743.917,17.125506072874476,2.3369542569937227 query_59.sql,1.818,1294.186,1.722,1292.091,5.5749128919860675,0.1621402826890697 query_6.sql,2.387,132211.841,1.918,144414.127,24.452554744525553,-8.44950992917751 query_60.sql,4.764,709.541,8.15,770.35,-41.54601226993865,-7.8936846887778245 query_61.sql,4.542,6.09,4.613,6.447,-1.5391285497507177,-5.5374592833876255 query_62.sql,2.194,277.489,2.129,279.699,3.0530765617660847,-0.7901351095284703 query_63.sql,1.609,274.35,1.544,308.721,4.2098445595854885,-11.133353416191312 query_64.sql,231.018,993.314,110.067,993.579,109.88852244541962,-0.026671256135645617 query_65.sql,1.547,1432.056,1.402,1459.18,10.342368045649074,-1.858852232075551 query_66.sql,4.873,459.169,4.288,456.478,13.642723880597012,0.58951362387672 query_67.sql,1.332,6262.641,1.321,6268.535,0.8327024981075034,-0.09402515898850741 query_68.sql,2.459,434.767,2.04,434.896,20.539215686274513,-0.029662264081531928 query_69.sql,3.622,545.235,2.971,559.032,21.91181420397172,-2.468016142188645 query_7.sql,2.59,740.428,1.911,756.807,35.53113553113552,-2.1642241681168404 query_70.sql,1.346,1085.83,1.276,1093.831,5.4858934169279046,-0.7314658297305504 query_71.sql,1.764,690.918,1.636,695.244,7.823960880195606,-0.6222275920396324 query_72.sql,16.468,2433.574,15.637,2422.972,5.314318603312652,0.4375618042635186 query_73.sql,1.561,242.764,1.373,246.741,13.692643845593585,-1.6118115757008376 query_74.sql,2.275,2600.782,1.636,2613.011,39.05867970660147,-0.4680041530632598 query_75.sql,3.936,2060.653,3.872,2021.916,1.6528925619834725,1.9158560494105519 query_76.sql,1.839,262.956,1.808,256.183,1.7146017699114997,2.6438132116494946 query_77.sql,6.134,506.031,4.12,503.471,48.88349514563107,0.5084701998724857 query_78.sql,3.479,3376.111,2.942,3346.175,18.252889191026508,0.8946334247312138 query_79.sql,1.943,494.783,1.66,500.474,17.04819277108435,-1.1371220083360922 query_8.sql,2.108,118.778,1.603,117.003,31.503431066749854,1.5170551182448362 query_80.sql,9.398,810.869,7.552,767.436,24.44385593220339,5.659494733111294 query_81.sql,1.601,102358.136,1.673,101992.064,-4.303646144650332,0.3589220431895565 query_82.sql,2.106,910.711,1.992,888.395,5.722891566265054,2.511945699829471 query_83.sql,2.732,151.69,2.419,147.383,12.939231087226133,2.9223180421079684 query_84.sql,3.255,164.327,3.084,159.529,5.544747081712057,3.0076036331952194 query_85.sql,11.396,609.845,9.978,598.002,14.211264782521557,1.9804281591031596 query_86.sql,0.963,417.937,0.924,409.646,4.220779220779212,2.023942623631134 query_87.sql,1.908,2794.814,1.868,2739.314,2.1413276231263283,2.0260546983660874 query_88.sql,4.025,1909.274,3.887,1872.028,3.550295857988175,1.989606993057789 query_89.sql,1.589,448.853,1.409,437.45,12.775017743080195,2.6066979083323853 query_9.sql,1.044,2384.919,1.005,2353.257,3.8805970149253883,1.345454406382295 query_90.sql,1.568,239.619,1.424,234.375,10.112359550561807,2.23744 query_91.sql,3.797,207.382,2.786,202.386,36.28858578607323,2.468550196159818 query_92.sql,1.132,76.153,1.149,76.136,-1.4795474325500544,0.022328464852382737 query_93.sql,1.293,3.116,1.183,2.986,9.298393913778519,4.3536503683857966 query_94.sql,2.145,257.063,2.005,254.546,6.982543640897762,0.9888193096729062 query_95.sql,2.029,9785.071,2.102,9640.791,-3.4728829686013296,1.496557699466783 query_96.sql,1.056,233.41,1.06,229.286,-0.37735849056603804,1.7986270422092911 query_97.sql,1.142,1025.226,1.2,1015.871,-4.833333333333338,0.9208846398804702 query_98.sql,1.297,356.808,1.209,355.641,7.278742762613718,0.32813989388174397 query_99.sql,1.59,583.963,1.472,571.363,8.016304347826095,2.2052530527877914
