From 905120efb4bbf7580a3d3b95fb113f945f69e78f Mon Sep 17 00:00:00 2001 From: Richard Guo Date: Mon, 5 Oct 2026 07:44:37 +0900 Subject: [PATCH v1 3/3] Fix sorting on extra grouping keys of child relations A Var that is needed by a join but is not a GROUP BY expression becomes an extra grouping key for partial aggregation. The EquivalenceClass for sorting on it is created when the grouped paths are generated, which is after the children of appendrels and partitionwise joins have received their EC members, so it has no child members. Sort-based partial aggregation on a child relation then failed with "could not find pathkey item to sort", as with a partitionwise join whose join clause includes a non-equality condition. To fix, add the missing child members when the grouping pathkeys of a child relation are built. This is done by the new function add_child_rel_pathkey_equivalences(), which handles both child base relations and child joins, including keys that are nullable by an outer join within the child join. Reported-by: Robert Haas Author: Richard Guo Discussion: https://postgr.es/m/CA+Tgmob7iSM9YkRM44VjUDuaCchW-fY54MV5njpTZTL9uNyV4w@mail.gmail.com Backpatch-through: 19 --- src/backend/optimizer/path/allpaths.c | 4 + src/backend/optimizer/path/equivclass.c | 78 ++++++ src/include/optimizer/paths.h | 3 + src/test/regress/expected/eager_aggregate.out | 229 ++++++++++++++++++ src/test/regress/sql/eager_aggregate.sql | 27 +++ 5 files changed, 341 insertions(+) diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index de8f29c2b57..d52a6d40505 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -3545,6 +3545,10 @@ generate_grouped_paths(PlannerInfo *root, RelOptInfo *grouped_rel, group_pathkeys = make_pathkeys_for_sortclauses(root, agg_info->group_clauses, top_group_tlist); + + /* The ECs just made for extra grouping keys lack child members */ + if (IS_OTHER_REL(rel)) + add_child_rel_pathkey_equivalences(root, rel, group_pathkeys); } /* diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c index 7995c48e0e2..62907da9480 100644 --- a/src/backend/optimizer/path/equivclass.c +++ b/src/backend/optimizer/path/equivclass.c @@ -3035,6 +3035,84 @@ add_child_join_rel_equivalences(PlannerInfo *root, MemoryContextSwitchTo(oldcontext); } +/* + * add_child_rel_pathkey_equivalences + * Make sure the ECs of the given pathkeys have members for child_rel. + * + * An EC created after its relations' children were processed has no child + * members, so child_rel could not be sorted by it. Add them here. + */ +void +add_child_rel_pathkey_equivalences(PlannerInfo *root, RelOptInfo *child_rel, + List *pathkeys) +{ + Relids top_parent_relids = child_rel->top_parent_relids; + MemoryContext oldcontext; + ListCell *lc; + + Assert(IS_OTHER_REL(child_rel)); + + /* As in add_child_join_rel_equivalences, new members must survive GEQO */ + oldcontext = MemoryContextSwitchTo(root->planner_cxt); + + foreach(lc, pathkeys) + { + EquivalenceClass *ec = lfirst_node(PathKey, lc)->pk_eclass; + + if (ec->ec_has_volatile) + continue; + + foreach_node(EquivalenceMember, cur_em, ec->ec_members) + { + EquivalenceMemberIterator it; + EquivalenceMember *em; + Expr *child_expr; + Relids new_relids; + int child_relid; + + /* Consider only members computable at the topmost parent */ + if (cur_em->em_is_const || + !bms_is_subset(cur_em->em_relids, top_parent_relids)) + continue; + + /* Skip members that already have a child version for this rel */ + setup_eclass_member_iterator(&it, ec, child_rel->relids); + while ((em = eclass_member_iterator_next(&it)) != NULL) + { + if (em->em_parent == cur_em && + bms_is_subset(em->em_relids, child_rel->relids)) + break; + } + if (em != NULL) + continue; + + new_relids = adjust_child_relids_multilevel(root, + cur_em->em_relids, + child_rel, + child_rel->top_parent); + + /* Store the member under one of its child relations */ + child_relid = bms_next_member(bms_difference(new_relids, + top_parent_relids), + -1); + if (child_relid < 0) + continue; + + child_expr = (Expr *) + adjust_appendrel_attrs_multilevel(root, + (Node *) cur_em->em_expr, + child_rel, + child_rel->top_parent); + + add_child_eq_member(root, ec, -1, child_expr, new_relids, + cur_em->em_jdomain, cur_em, + cur_em->em_datatype, child_relid); + } + } + + MemoryContextSwitchTo(oldcontext); +} + /* * add_setop_child_rel_equivalences * Add equivalence members for each non-resjunk target in 'child_tlist' diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h index d3853d1c076..64f87da1674 100644 --- a/src/include/optimizer/paths.h +++ b/src/include/optimizer/paths.h @@ -184,6 +184,9 @@ extern void add_child_join_rel_equivalences(PlannerInfo *root, AppendRelInfo **appinfos, RelOptInfo *parent_joinrel, RelOptInfo *child_joinrel); +extern void add_child_rel_pathkey_equivalences(PlannerInfo *root, + RelOptInfo *child_rel, + List *pathkeys); extern void add_setop_child_rel_equivalences(PlannerInfo *root, RelOptInfo *child_rel, List *child_tlist, diff --git a/src/test/regress/expected/eager_aggregate.out b/src/test/regress/expected/eager_aggregate.out index 9bcd4a26fad..b420a125e1c 100644 --- a/src/test/regress/expected/eager_aggregate.out +++ b/src/test/regress/expected/eager_aggregate.out @@ -1086,6 +1086,235 @@ GROUP BY t3.y ORDER BY t3.y; 9 | 6890088 (10 rows) +-- partial aggregation with an extra grouping key needed by a non-equality +-- join clause +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.x, sum(t1.y) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab1 t2 ON t1.x = t2.x AND t1.y < t2.y +GROUP BY t1.x ORDER BY t1.x; + QUERY PLAN +------------------------------------------------------------------------- + Merge Append + Sort Key: t1.x + -> Finalize GroupAggregate + Output: t1.x, sum(t1.y) + Group Key: t1.x + -> Merge Join + Output: t1.x, (PARTIAL sum(t1.y)) + Merge Cond: (t1.x = t2.x) + Join Filter: (t1.y < t2.y) + -> Partial GroupAggregate + Output: t1.x, t1.y, PARTIAL sum(t1.y) + Group Key: t1.x, t1.y + -> Sort + Output: t1.x, t1.y + Sort Key: t1.x, t1.y + -> Seq Scan on public.eager_agg_tab1_p1 t1 + Output: t1.x, t1.y + -> Sort + Output: t2.x, t2.y + Sort Key: t2.x + -> Seq Scan on public.eager_agg_tab1_p1 t2 + Output: t2.x, t2.y + -> Finalize GroupAggregate + Output: t1_1.x, sum(t1_1.y) + Group Key: t1_1.x + -> Merge Join + Output: t1_1.x, (PARTIAL sum(t1_1.y)) + Merge Cond: (t1_1.x = t2_1.x) + Join Filter: (t1_1.y < t2_1.y) + -> Partial GroupAggregate + Output: t1_1.x, t1_1.y, PARTIAL sum(t1_1.y) + Group Key: t1_1.x, t1_1.y + -> Sort + Output: t1_1.x, t1_1.y + Sort Key: t1_1.x, t1_1.y + -> Seq Scan on public.eager_agg_tab1_p2 t1_1 + Output: t1_1.x, t1_1.y + -> Sort + Output: t2_1.x, t2_1.y + Sort Key: t2_1.x + -> Seq Scan on public.eager_agg_tab1_p2 t2_1 + Output: t2_1.x, t2_1.y + -> Finalize GroupAggregate + Output: t1_2.x, sum(t1_2.y) + Group Key: t1_2.x + -> Merge Join + Output: t1_2.x, (PARTIAL sum(t1_2.y)) + Merge Cond: (t1_2.x = t2_2.x) + Join Filter: (t1_2.y < t2_2.y) + -> Partial GroupAggregate + Output: t1_2.x, t1_2.y, PARTIAL sum(t1_2.y) + Group Key: t1_2.x, t1_2.y + -> Sort + Output: t1_2.x, t1_2.y + Sort Key: t1_2.x, t1_2.y + -> Seq Scan on public.eager_agg_tab1_p3 t1_2 + Output: t1_2.x, t1_2.y + -> Sort + Output: t2_2.x, t2_2.y + Sort Key: t2_2.x + -> Seq Scan on public.eager_agg_tab1_p3 t2_2 + Output: t2_2.x, t2_2.y +(62 rows) + +SELECT t1.x, sum(t1.y) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab1 t2 ON t1.x = t2.x AND t1.y < t2.y +GROUP BY t1.x ORDER BY t1.x; + x | sum +----+------ + 0 | 0 + 1 | 1122 + 2 | 2244 + 3 | 3366 + 4 | 4488 + 5 | 0 + 6 | 1122 + 7 | 2244 + 8 | 3366 + 9 | 4488 + 10 | 0 + 11 | 1089 + 12 | 2178 + 13 | 3267 + 14 | 4356 +(15 rows) + +-- same, with the extra grouping key nullable by an outer join below +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.x, sum(t2.y) + FROM eager_agg_tab1 t1 + LEFT JOIN eager_agg_tab1 t2 ON t1.x = t2.x AND t1.y = t2.y + JOIN eager_agg_tab1 t3 ON t1.x = t3.x AND COALESCE(t2.y, 0) < t3.y +GROUP BY t1.x ORDER BY t1.x; + QUERY PLAN +--------------------------------------------------------------------------------------- + Merge Append + Sort Key: t1.x + -> Finalize GroupAggregate + Output: t1.x, sum(t2.y) + Group Key: t1.x + -> Merge Join + Output: t1.x, (PARTIAL sum(t2.y)) + Merge Cond: (t1.x = t3.x) + Join Filter: (COALESCE(t2.y, 0) < t3.y) + -> Partial GroupAggregate + Output: t1.x, t2.y, PARTIAL sum(t2.y) + Group Key: t1.x, t2.y + -> Incremental Sort + Output: t1.x, t2.y + Sort Key: t1.x, t2.y + Presorted Key: t1.x + -> Merge Left Join + Output: t1.x, t2.y + Merge Cond: ((t1.x = t2.x) AND (t1.y = t2.y)) + -> Sort + Output: t1.x, t1.y + Sort Key: t1.x, t1.y + -> Seq Scan on public.eager_agg_tab1_p1 t1 + Output: t1.x, t1.y + -> Sort + Output: t2.y, t2.x + Sort Key: t2.x, t2.y + -> Seq Scan on public.eager_agg_tab1_p1 t2 + Output: t2.y, t2.x + -> Sort + Output: t3.x, t3.y + Sort Key: t3.x + -> Seq Scan on public.eager_agg_tab1_p1 t3 + Output: t3.x, t3.y + -> Finalize GroupAggregate + Output: t1_1.x, sum(t2_1.y) + Group Key: t1_1.x + -> Merge Join + Output: t1_1.x, (PARTIAL sum(t2_1.y)) + Merge Cond: (t1_1.x = t3_1.x) + Join Filter: (COALESCE(t2_1.y, 0) < t3_1.y) + -> Partial GroupAggregate + Output: t1_1.x, t2_1.y, PARTIAL sum(t2_1.y) + Group Key: t1_1.x, t2_1.y + -> Incremental Sort + Output: t1_1.x, t2_1.y + Sort Key: t1_1.x, t2_1.y + Presorted Key: t1_1.x + -> Merge Left Join + Output: t1_1.x, t2_1.y + Merge Cond: ((t1_1.x = t2_1.x) AND (t1_1.y = t2_1.y)) + -> Sort + Output: t1_1.x, t1_1.y + Sort Key: t1_1.x, t1_1.y + -> Seq Scan on public.eager_agg_tab1_p2 t1_1 + Output: t1_1.x, t1_1.y + -> Sort + Output: t2_1.y, t2_1.x + Sort Key: t2_1.x, t2_1.y + -> Seq Scan on public.eager_agg_tab1_p2 t2_1 + Output: t2_1.y, t2_1.x + -> Sort + Output: t3_1.x, t3_1.y + Sort Key: t3_1.x + -> Seq Scan on public.eager_agg_tab1_p2 t3_1 + Output: t3_1.x, t3_1.y + -> Finalize GroupAggregate + Output: t1_2.x, sum(t2_2.y) + Group Key: t1_2.x + -> Merge Join + Output: t1_2.x, (PARTIAL sum(t2_2.y)) + Merge Cond: (t1_2.x = t3_2.x) + Join Filter: (COALESCE(t2_2.y, 0) < t3_2.y) + -> Partial GroupAggregate + Output: t1_2.x, t2_2.y, PARTIAL sum(t2_2.y) + Group Key: t1_2.x, t2_2.y + -> Incremental Sort + Output: t1_2.x, t2_2.y + Sort Key: t1_2.x, t2_2.y + Presorted Key: t1_2.x + -> Merge Left Join + Output: t1_2.x, t2_2.y + Merge Cond: ((t1_2.x = t2_2.x) AND (t1_2.y = t2_2.y)) + -> Sort + Output: t1_2.x, t1_2.y + Sort Key: t1_2.x, t1_2.y + -> Seq Scan on public.eager_agg_tab1_p3 t1_2 + Output: t1_2.x, t1_2.y + -> Sort + Output: t2_2.y, t2_2.x + Sort Key: t2_2.x, t2_2.y + -> Seq Scan on public.eager_agg_tab1_p3 t2_2 + Output: t2_2.y, t2_2.x + -> Sort + Output: t3_2.x, t3_2.y + Sort Key: t3_2.x + -> Seq Scan on public.eager_agg_tab1_p3 t3_2 + Output: t3_2.x, t3_2.y +(98 rows) + +SELECT t1.x, sum(t2.y) + FROM eager_agg_tab1 t1 + LEFT JOIN eager_agg_tab1 t2 ON t1.x = t2.x AND t1.y = t2.y + JOIN eager_agg_tab1 t3 ON t1.x = t3.x AND COALESCE(t2.y, 0) < t3.y +GROUP BY t1.x ORDER BY t1.x; + x | sum +----+-------- + 0 | 0 + 1 | 38148 + 2 | 76296 + 3 | 114444 + 4 | 152592 + 5 | 0 + 6 | 37026 + 7 | 74052 + 8 | 111078 + 9 | 148104 + 10 | 0 + 11 | 35937 + 12 | 71874 + 13 | 107811 + 14 | 143748 +(15 rows) + RESET enable_hashagg; RESET max_parallel_workers_per_gather; -- try that with GEQO too diff --git a/src/test/regress/sql/eager_aggregate.sql b/src/test/regress/sql/eager_aggregate.sql index 105a6391734..7e3623aa6fd 100644 --- a/src/test/regress/sql/eager_aggregate.sql +++ b/src/test/regress/sql/eager_aggregate.sql @@ -324,6 +324,33 @@ SELECT t3.y, sum(t2.y + t3.y) JOIN eager_agg_tab1 t3 ON t2.x = t3.x GROUP BY t3.y ORDER BY t3.y; +-- partial aggregation with an extra grouping key needed by a non-equality +-- join clause +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.x, sum(t1.y) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab1 t2 ON t1.x = t2.x AND t1.y < t2.y +GROUP BY t1.x ORDER BY t1.x; + +SELECT t1.x, sum(t1.y) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab1 t2 ON t1.x = t2.x AND t1.y < t2.y +GROUP BY t1.x ORDER BY t1.x; + +-- same, with the extra grouping key nullable by an outer join below +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.x, sum(t2.y) + FROM eager_agg_tab1 t1 + LEFT JOIN eager_agg_tab1 t2 ON t1.x = t2.x AND t1.y = t2.y + JOIN eager_agg_tab1 t3 ON t1.x = t3.x AND COALESCE(t2.y, 0) < t3.y +GROUP BY t1.x ORDER BY t1.x; + +SELECT t1.x, sum(t2.y) + FROM eager_agg_tab1 t1 + LEFT JOIN eager_agg_tab1 t2 ON t1.x = t2.x AND t1.y = t2.y + JOIN eager_agg_tab1 t3 ON t1.x = t3.x AND COALESCE(t2.y, 0) < t3.y +GROUP BY t1.x ORDER BY t1.x; + RESET enable_hashagg; RESET max_parallel_workers_per_gather; -- 2.37.1 (Apple Git-137.1)