On Fri Oct 3, 2025 at 12:14 AM -03, Richard Guo wrote: >> 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 tested query 23 in my local environment but didn't observe the > regression. > > -- on master > Planning Time: 1.950 ms > Execution Time: 3260.924 ms > > -- on patched > Planning Time: 2.197 ms > Execution Time: 3237.287 ms > > I ran the benchmark at scale factor 1 and executed ANALYZE beforehand. > For the build configuration, I disabled cassert. > I've disabled the cassert and executed the ANALYZE again before benchmarking and now I have similar results with a improvement on eager aggregate version:
-- master Planning Time: 2.734 ms Execution Time: 5238.128 ms -- patched Planning Time: 2.578 ms Execution Time: 4732.584 ms > Comparing the plans, I noticed one key difference: in the plan you > provided (query-23.patch.explain), the frequent_ss_items CTE uses > parallel aggregation, whereas in my local environment it does not. > This leads to a different final join order between the two plans. > > However, given the highly inaccurate size and cost estimates for the > CTE Scan nodes, I'm not sure it's worth investigating further. I'm > starting to feel that trying to tune performance here, with such > inaccurate underlying estimates for CTEs, is like building on sand. > > [ ...] > >> I'm just wondering if there is anything that can be done on the planner >> to prevent this type of situation? > > I think the ideal solution is to improve our estimates for CTE > relations to make the plans for TPC-DS queries more reasonable. Of > course, for queries from other benchmarks, the issues may stem from > other plan nodes. IMHO, we really need some improvements in our cost > estimation. > Fair points, agree. The performance results look good to me. I don't have to much comments about the code although I'm still learning about the planner internals this patch seems in good shape to me. I'm just attaching a new csv with the last results after running with cassert disabled and after executing ANALYZE. It looks good to me. Thanks for working on this! -- Matheus Alcantara
Query,Patched Planning (ms),Patched Execution (ms),Master Planning (ms),Master Execution (ms),Planning Diff (%),Execution Diff (%) query_1.sql,1.772,348111.128,1.448,347986.657,22.37569060773481,0.035768900185164154 query_10.sql,3.916,1708.264,3.628,1735.879,7.93825799338478,-1.5908366885019065 query_11.sql,1.874,3938.543,1.732,12631.077,8.198614318706705,-68.81862884693048 query_12.sql,2.423,118.035,1.938,120.104,25.025799793601657,-1.7226736828082352 query_13.sql,3.725,1449.302,3.875,1472.918,-3.8709677419354813,-1.603347912103728 query_14.sql,5.585,3142.689,4.926,3153.894,13.377994315874945,-0.3552750980216814 query_15.sql,3.787,229.036,16.127,226.61,-76.51764122279407,1.070561758086575 query_16.sql,3.933,340.588,3.744,330.124,5.048076923076913,3.169718045340538 query_17.sql,20.183,582.729,16.598,581.64,21.598987829859027,0.18722921394678074 query_18.sql,6.141,832.748,5.543,895.849,10.788381742738586,-7.043709375129067 query_19.sql,4.363,345.011,3.951,341.711,10.427739812705653,0.9657283493946672 query_2.sql,1.777,1029.284,2.096,1013.598,-15.219465648854968,1.5475563290377594 query_20.sql,1.605,199.484,1.696,198.66,-5.365566037735848,0.41477901943018836 query_21.sql,2.993,763.408,2.926,760.504,2.289815447710175,0.38185203496628506 query_22.sql,1.081,9782.704,1.017,9812.876,6.293018682399219,-0.3074735684013584 query_23.sql,8.857,4937.117,7.745,5440.361,14.357650096836657,-9.25019497787003 query_24.sql,6.435,73.181,5.783,68.873,11.274425038907127,6.254991070521093 query_25.sql,33.859,563.156,29.377,562.256,15.256833577288365,0.1600694345636111 query_26.sql,4.605,496.635,3.725,461.931,23.624161073825512,7.512810354793251 query_27.sql,2.108,802.41,2.013,791.632,4.719324391455549,1.3614911979303541 query_28.sql,1.203,2129.017,1.157,2114.691,3.975799481417462,0.6774512210058123 query_29.sql,20.799,699.452,17.871,695.774,16.384085949303344,0.5286199254355577 query_3.sql,1.076,316.886,1.465,314.489,-26.552901023890783,0.7621888205946944 query_30.sql,2.068,23992.385,2.01,23732.395,2.8855721393034965,1.0955067956689495 query_31.sql,3.443,2170.827,3.71,4956.94,-7.196765498652288,-56.20630873078956 query_32.sql,1.46,381.865,1.558,384.881,-6.290115532734281,-0.7836188328340352 query_33.sql,6.144,665.558,5.328,683.21,15.315315315315312,-2.583685835980159 query_34.sql,3.072,294.182,2.48,294.62,23.870967741935488,-0.14866607833819434 query_35.sql,3.058,1696.758,3.066,1741.545,-0.26092628832354886,-2.571682040946403 query_36.sql,1.472,951.782,1.543,957.131,-4.601425793907969,-0.5588576694308232 query_37.sql,2.862,692.185,3.06,695.129,-6.470588235294115,-0.42351851239123584 query_38.sql,1.757,2934.366,2.039,2945.52,-13.830308974987751,-0.3786767701458485 query_39.sql,2.106,4287.367,2.459,4355.186,-14.355429036193582,-1.5572010012890267 query_4.sql,4.78,7367.944,4.783,20359.41,-0.06272214091574563,-63.81062123116534 query_40.sql,4.527,229.529,17.79,235.618,-74.55311973018549,-2.584267755434644 query_41.sql,1.076,1911.485,1.364,1898.805,-21.114369501466275,0.6677884248250787 query_42.sql,2.185,214.184,1.876,216.248,16.471215351812376,-0.9544596944249164 query_43.sql,1.618,717.386,1.678,719.886,-3.5756853396900974,-0.3472772077801208 query_44.sql,1.095,999.478,1.022,1020.661,7.142857142857138,-2.0754197524937266 query_45.sql,4.503,147.938,3.965,150.025,13.568726355611608,-1.3911014830861639 query_46.sql,3.389,670.253,2.905,669.081,16.66092943201377,0.17516563764327867 query_47.sql,2.107,3993.831,2.096,3928.992,0.5248091603053493,1.650270603757909 query_48.sql,3.193,1433.704,3.028,1413.382,5.449141347424043,1.4378278483806846 query_49.sql,6.013,1328.264,5.649,1305.393,6.443618339529118,1.7520394241427575 query_5.sql,12.624,1248.898,4.026,1263.176,213.56184798807752,-1.1303254653349986 query_50.sql,3.642,1363.389,2.689,1318.568,35.44068426924507,3.3992179394615913 query_51.sql,1.567,1934.372,1.078,1905.328,45.36178107606678,1.524356961111163 query_52.sql,1.323,215.693,1.063,215.418,24.459078080903108,0.12765878431700492 query_53.sql,1.74,296.488,1.558,297.292,11.681643132220792,-0.27044118240651405 query_54.sql,3.749,689.41,3.331,684.939,12.548784148904238,0.6527588588180852 query_55.sql,1.077,213.579,0.969,214.217,11.145510835913312,-0.29782883711377023 query_56.sql,7.254,693.427,5.672,691.625,27.891396332863188,0.26054581601301585 query_57.sql,2.649,1828.543,2.581,1956.034,2.634637737311122,-6.517831489636694 query_58.sql,2.929,769.344,2.601,752.896,12.610534409842364,2.184631077864684 query_59.sql,1.899,1275.897,1.797,1287.483,5.676126878130222,-0.8998953772593512 query_6.sql,2.646,136423.314,1.94,148868.237,36.391752577319586,-8.359689918273151 query_60.sql,4.999,723.689,11.688,772.551,-57.22963723477071,-6.324760436527825 query_61.sql,5.127,6.56,6.288,6.482,-18.463740458015273,1.2033323048441746 query_62.sql,2.522,279.014,2.42,276.941,4.2148760330578465,0.7485348864920818 query_63.sql,1.722,271.157,1.439,299.55,19.66643502432244,-9.478551160073453 query_64.sql,246.953,961.432,108.404,1220.635,127.80801446441092,-21.235094848173286 query_65.sql,2.185,1451.433,1.613,1459.66,35.46187228766274,-0.5636244056835213 query_66.sql,6.342,469.304,4.331,461.772,46.432694527822655,1.6311079926890288 query_67.sql,1.381,6239.233,1.333,6275.725,3.600900225056267,-0.581478633942695 query_68.sql,2.712,444.651,2.462,454.96,10.154346060113728,-2.2659134868999407 query_69.sql,3.659,549.403,3.068,559.8,19.26336375488917,-1.8572704537334648 query_7.sql,2.512,748.722,1.877,763.937,33.830580713905164,-1.991656380041814 query_70.sql,1.378,1082.349,1.282,1099.89,7.488299531981268,-1.5947958432207008 query_71.sql,1.616,664.626,1.879,690.085,-13.996806812134107,-3.6892556714028064 query_72.sql,17.423,2425.441,16.905,2431.505,3.06418219461696,-0.24939286573543157 query_73.sql,1.532,240.721,1.491,248.724,2.749832327297111,-3.2176227464981206 query_74.sql,2.461,2606.065,1.679,2695.639,46.57534246575341,-3.32292269105767 query_75.sql,5.763,2152.559,5.39,2252.586,6.920222634508353,-4.440540782904608 query_76.sql,1.77,260.015,1.832,273.168,-3.384279475982536,-4.814985649856506 query_77.sql,7.189,502.823,4.539,504.798,58.382903723287086,-0.3912456071537571 query_78.sql,4.667,3404.293,3.075,3526.288,51.77235772357722,-3.4595869651032443 query_79.sql,2.518,441.678,1.757,497.753,43.31246442800227,-11.265627731023216 query_8.sql,2.616,113.6,1.731,118.141,51.12651646447141,-3.8437121744356415 query_80.sql,9.607,777.569,7.965,794.619,20.615191462649083,-2.145682396217567 query_81.sql,1.744,104737.831,1.539,103999.861,13.320337881741395,0.7095874868525076 query_82.sql,1.98,904.683,1.956,903.053,1.226993865030676,0.18049881900619294 query_83.sql,2.78,159.719,2.572,155.356,8.087091757387237,2.808388475501429 query_84.sql,3.311,164.835,3.243,162.348,2.096823928461303,1.5318944489614867 query_85.sql,11.635,607.475,9.547,603.498,21.87074473656645,0.6589914133932465 query_86.sql,1.038,444.518,0.948,435.156,9.493670886075959,2.151412367059162 query_87.sql,2.509,3033.169,1.84,3022.709,36.3586956521739,0.34604720467633626 query_88.sql,4.439,1887.336,4.055,1882.765,9.469790382244152,0.2427812286716564 query_89.sql,1.578,447.047,1.429,447.93,10.426871938418476,-0.19712901569441235 query_9.sql,0.999,2377.692,1.06,2359.84,-5.754716981132081,0.7564919655569811 query_90.sql,1.484,243.019,1.521,240.875,-2.432610124917812,0.8900882200311387 query_91.sql,4.539,211.898,3.484,203.032,30.281285878300796,4.366799322274314 query_92.sql,1.185,76.483,1.149,77.097,3.1331592689295062,-0.7963993410897832 query_93.sql,1.427,3.337,1.236,3.07,15.453074433656964,8.697068403908807 query_94.sql,2.112,265.029,2.338,259.955,-9.666381522668946,1.9518762862803227 query_95.sql,2.02,9955.019,1.959,9880.937,3.1138335885655914,0.7497467092442786 query_96.sql,1.295,231.706,1.092,226.021,18.589743589743573,2.5152530074639095 query_97.sql,1.204,1032.419,1.11,1033.895,8.468468468468455,-0.14276111210518336 query_98.sql,1.472,374.712,1.341,366.122,9.768829231916481,2.346212464697553 query_99.sql,1.509,585.547,1.578,587.693,-4.372623574144498,-0.365156637904477
