From d02a1d1b0aa722bef39373229592129d4a07382c Mon Sep 17 00:00:00 2001 From: David Rowley Date: Fri, 26 Sep 2025 09:23:17 +1200 Subject: [PATCH v1] Improve width estimates for set operations For UNION, EXCEPT and INTERSECT, we were not very good at estimating the PathTarget.width for the set operation. Since the targetlist of the set operation is made up of Vars with varno==0, a default estimate was used based on the Var's type. Here we attempt to improve the situation by looking at the width estimates for the set operation child paths and calculating the average width of the relevant child paths weighted over the estimated number of rows. For UNION and INTERSECT, the relevant child paths are all child paths. For EXCEPT, since we don't return rows from the right-hand child (only possibly remove left-hand rows matching those), we use only the left-hand child for width estimates. Author: David Rowley Discussion: https://postgr.es/m/tencent_34CF8017AB81944A4C08DD089D410AB6C306@qq.com --- src/backend/optimizer/prep/prepunion.c | 62 ++++++++++++++++++++++++-- 1 file changed, 58 insertions(+), 4 deletions(-) diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c index 28a4ae64440..b1139febe03 100644 --- a/src/backend/optimizer/prep/prepunion.c +++ b/src/backend/optimizer/prep/prepunion.c @@ -23,6 +23,8 @@ */ #include "postgres.h" +#include + #include "access/htup_details.h" #include "catalog/pg_type.h" #include "miscadmin.h" @@ -74,6 +76,8 @@ static List *generate_append_tlist(List *colTypes, List *colCollations, List *input_tlists, List *refnames_tlist); static List *generate_setop_grouplist(SetOperationStmt *op, List *targetlist); +static PathTarget *create_setop_pathtarget(PlannerInfo *root, List *tlist, + List *child_pathlist); /* @@ -803,7 +807,8 @@ generate_union_paths(SetOperationStmt *op, PlannerInfo *root, /* Build result relation. */ result_rel = fetch_upper_rel(root, UPPERREL_SETOP, relids); - result_rel->reltarget = create_pathtarget(root, tlist); + result_rel->reltarget = create_setop_pathtarget(root, tlist, + cheapest_pathlist); result_rel->consider_parallel = consider_parallel; result_rel->consider_startup = (root->tuple_fraction > 0); @@ -892,7 +897,7 @@ generate_union_paths(SetOperationStmt *op, PlannerInfo *root, path = (Path *) create_agg_path(root, result_rel, apath, - create_pathtarget(root, tlist), + result_rel->reltarget, AGG_HASHED, AGGSPLIT_SIMPLE, groupList, @@ -908,7 +913,7 @@ generate_union_paths(SetOperationStmt *op, PlannerInfo *root, path = (Path *) create_agg_path(root, result_rel, gpath, - create_pathtarget(root, tlist), + result_rel->reltarget, AGG_HASHED, AGGSPLIT_SIMPLE, groupList, @@ -1130,7 +1135,18 @@ generate_nonunion_paths(SetOperationStmt *op, PlannerInfo *root, /* Build result relation. */ result_rel = fetch_upper_rel(root, UPPERREL_SETOP, bms_union(lrel->relids, rrel->relids)); - result_rel->reltarget = create_pathtarget(root, tlist); + + /* + * Create the PathTarget and set the width accordingly. For EXCEPT, since + * the set op result won't contain rows from the rpath, we only account + * for the width of the lpath. For INTERSECT, use both input paths. + */ + if (op->op == SETOP_EXCEPT) + result_rel->reltarget = create_setop_pathtarget(root, tlist, + list_make1(lpath)); + else + result_rel->reltarget = create_setop_pathtarget(root, tlist, + list_make2(lpath, rpath)); /* * Estimate number of distinct groups that we'll need hashtable entries @@ -1619,3 +1635,41 @@ generate_setop_grouplist(SetOperationStmt *op, List *targetlist) Assert(lg == NULL); return grouplist; } + +/* + * create_setop_pathtarget + * Do the normal create_pathtarget() work, plus set the resulting + * PathTarget's width to the average width of the Paths in child_pathlist + * weighted using the estimated row count. + * + * Note: This is required because set op target lists use varno==0, which + * results in a type default width estimate rather than one that's based on + * statistics of the columns from the set op children. + */ +static PathTarget * +create_setop_pathtarget(PlannerInfo *root, List *tlist, List *child_pathlist) +{ + PathTarget *reltarget; + ListCell *lc; + double parent_rows = 0; + double parent_size = 0; + + reltarget = create_pathtarget(root, tlist); + + /* + * Estimate the width of the new PathTarget by calculating the average + * width of the child paths weighted over the expected number of rows. + */ + foreach(lc, child_pathlist) + { + Path *path = (Path *) lfirst(lc); + + parent_rows += path->rows; + parent_size += path->parent->reltarget->width * path->rows; + } + + if (parent_rows > 0) + reltarget->width = rint(parent_size / parent_rows); + + return reltarget; +} -- 2.43.0