From f21eedd7dfa071300b7e64056f41a0bd0890193c Mon Sep 17 00:00:00 2001 From: Richard Guo Date: Fri, 14 Aug 2026 11:31:43 +0900 Subject: [PATCH v3 3/3] Teach UniqueKeys about eager aggregation's grouped relations Eager aggregation partially aggregates a relation before joining it. That keeps one row per group, NULL-aware just as GROUP BY is, so the grouped relation is distinct over its grouping expressions. Unlike the query's own grouping step, this one has joins above it, so the key can prove such a join inner-unique. Deduce these keys for grouped simple relations only. A grouped join relation may instead get its paths by joining an already-grouped input to a plain one, which emits one row per group per matching row of the other side rather than one row per group. --- .../postgres_fdw/expected/postgres_fdw.out | 3 +- src/backend/optimizer/README | 8 ++ src/backend/optimizer/path/costsize.c | 25 +++-- src/backend/optimizer/path/uniquekeys.c | 91 +++++++++++++++++-- src/backend/optimizer/util/relnode.c | 3 + src/include/optimizer/paths.h | 2 + src/test/regress/expected/eager_aggregate.out | 59 +++++++++--- src/test/regress/expected/join.out | 8 +- src/test/regress/expected/select_parallel.out | 34 +++---- src/test/regress/expected/uniquekeys.out | 57 ++++++++++++ src/test/regress/sql/uniquekeys.sql | 22 +++++ 11 files changed, 261 insertions(+), 51 deletions(-) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 1bfe3f5ae1d..4cc57d6eeda 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -3817,6 +3817,7 @@ select count(*), x.b from ft1, (select c2 a, sum(c1) b from ft1 group by c2) x w Sort Key: (sum(ft1_1.c1)) -> Hash Join Output: (sum(ft1_1.c1)), (PARTIAL count(*)) + Inner Unique: true Hash Cond: (ft1_1.c2 = ft1.c2) -> Foreign Scan Output: ft1_1.c2, (sum(ft1_1.c1)) @@ -3830,7 +3831,7 @@ select count(*), x.b from ft1, (select c2 a, sum(c1) b from ft1 group by c2) x w -> Foreign Scan on public.ft1 Output: ft1.c2 Remote SQL: SELECT c2 FROM "S 1"."T 1" -(24 rows) +(25 rows) select count(*), x.b from ft1, (select c2 a, sum(c1) b from ft1 group by c2) x where ft1.c2 = x.a group by x.b order by 1, 2; count | b diff --git a/src/backend/optimizer/README b/src/backend/optimizer/README index 3c99a8d7cb4..2aba9d4ca84 100644 --- a/src/backend/optimizer/README +++ b/src/backend/optimizer/README @@ -1144,6 +1144,14 @@ join. No translation is needed: a child Var belongs to the same EquivalenceClass as its parent, so the keys' EC indexes carry over unchanged. This is what lets a partitionwise join prove its child joins inner-unique. +Eager aggregation partially aggregates a relation before joining it, and that +too keeps one row per group, so the grouped relation is distinct over its +grouping expressions. Unlike the query's own grouping step this one has joins +above it, so the key can prove such a join inner-unique. We deduce this only +for grouped simple relations: a grouped join relation may instead get its +paths by joining an already-grouped input to a plain one, and those emit one +row per group per matching row of the other side, not one row per group. + Finally, the query's upper relations get keys too. The grouping step is NULL-aware distinct over the grouping columns, and emits a single row (the empty key) for a plain aggregation or grouping only by constants; the DISTINCT diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c index fd794c946ab..09e87af79d6 100644 --- a/src/backend/optimizer/path/costsize.c +++ b/src/backend/optimizer/path/costsize.c @@ -4460,10 +4460,10 @@ final_cost_hashjoin(PlannerInfo *root, HashPath *path, Cost run_cost = workspace->run_cost; int numbuckets = workspace->numbuckets; int numbatches = workspace->numbatches; - Cost cpu_per_tuple; QualCost hash_qual_cost; QualCost qp_qual_cost; double hashjointuples; + double hashjoinrows; double virtualbuckets; Selectivity innerbucketsize; Selectivity innermcvfreq; @@ -4681,14 +4681,25 @@ final_cost_hashjoin(PlannerInfo *root, HashPath *path, } /* - * For each tuple that gets through the hashjoin proper, we charge - * cpu_tuple_cost plus the cost of evaluating additional restriction - * clauses that are to be applied at the join. (This is pessimistic since - * not all of the quals may get evaluated at each tuple.) + * For each tuple that gets through the hashjoin proper, we charge the + * cost of evaluating additional restriction clauses that are to be + * applied at the join. (This is pessimistic since not all of the quals + * may get evaluated at each tuple.) cpu_tuple_cost, on the other hand, + * is charged once per row the join emits. + * + * Those two counts coincide except for JOIN_RIGHT_SEMI and + * JOIN_RIGHT_ANTI, which emit inner rows whereas hashjointuples counts + * outer-side tuples. For those, use the path's own row estimate as the + * number of rows emitted. */ startup_cost += qp_qual_cost.startup; - cpu_per_tuple = cpu_tuple_cost + qp_qual_cost.per_tuple; - run_cost += cpu_per_tuple * hashjointuples; + if (path->jpath.jointype == JOIN_RIGHT_SEMI || + path->jpath.jointype == JOIN_RIGHT_ANTI) + hashjoinrows = path->jpath.path.rows; + else + hashjoinrows = hashjointuples; + run_cost += qp_qual_cost.per_tuple * hashjointuples + + cpu_tuple_cost * hashjoinrows; /* tlist eval costs are paid per output row, not per tuple scanned */ startup_cost += path->jpath.path.pathtarget->cost.startup; diff --git a/src/backend/optimizer/path/uniquekeys.c b/src/backend/optimizer/path/uniquekeys.c index f4e997ee11f..f9d9c37f447 100644 --- a/src/backend/optimizer/path/uniquekeys.c +++ b/src/backend/optimizer/path/uniquekeys.c @@ -71,7 +71,7 @@ static bool side_is_unique(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype, List *restrictlist); static int find_ec_position(PlannerInfo *root, Expr *expr, List *opfamilies, Oid collation, - Bitmapset *candidates); + Bitmapset *candidates, Relids child_relids); static int base_ec_position(PlannerInfo *root, EquivalenceClass *ec); @@ -172,7 +172,7 @@ populate_plain_rel_uniquekeys(PlannerInfo *root, RelOptInfo *rel) pos = find_ec_position(root, colexpr, get_mergejoin_opfamilies(equality_op), ind->indexcollations[c], - rel->eclass_indexes); + rel->eclass_indexes, NULL); if (pos < 0) { useless = true; @@ -322,7 +322,7 @@ add_subquery_key_col(PlannerInfo *root, RelOptInfo *rel, exprTypmod((Node *) tle->expr), collation, 0); pos = find_ec_position(root, (Expr *) colvar, opfamilies, collation, - rel->eclass_indexes); + rel->eclass_indexes, NULL); if (pos < 0) return false; ec = (EquivalenceClass *) list_nth(root->eq_classes, pos); @@ -821,7 +821,7 @@ populate_unique_rel_uniquekeys(PlannerInfo *root, RelOptInfo *unique_rel, /* Match in base-EC space: strip any outer-join nulling first */ expr = (Expr *) remove_nulling_relids((Node *) expr, root->outer_join_rels, NULL); - pos = find_ec_position(root, expr, opfamilies, collation, NULL); + pos = find_ec_position(root, expr, opfamilies, collation, NULL, NULL); if (pos < 0) return; @@ -837,6 +837,75 @@ populate_unique_rel_uniquekeys(PlannerInfo *root, RelOptInfo *unique_rel, add_uniquekey(unique_rel, key_ecs, true); } +/* + * populate_agg_rel_uniquekeys + * Deduce the unique keys of a relation that eager aggregation has + * partially aggregated. + * + * Partial aggregation keeps one row per group, NULL-aware just as GROUP BY is, + * so the grouped relation is distinct over its grouping expressions. + * + * The caller applies this only to a grouped simple relation, whose only way to + * produce rows is to aggregate that relation. A grouped join relation could + * instead be built by joining an already-grouped input to a plain one, which + * emits one row per group per matching row of the other side rather than one + * row per group. + */ +void +populate_agg_rel_uniquekeys(PlannerInfo *root, RelOptInfo *grouped_rel) +{ + RelAggInfo *agg_info = grouped_rel->agg_info; + Bitmapset *key_ecs = NULL; + ListCell *lc1; + ListCell *lc2; + + Assert(agg_info != NULL); + Assert(IS_SIMPLE_REL(grouped_rel)); + Assert(bms_equal(agg_info->apply_agg_at, grouped_rel->relids)); + + forboth(lc1, agg_info->group_clauses, lc2, agg_info->group_exprs) + { + SortGroupClause *sgc = lfirst_node(SortGroupClause, lc1); + Expr *expr = (Expr *) lfirst(lc2); + List *opfamilies = get_mergejoin_opfamilies(sgc->eqop); + Oid collation = exprCollation((Node *) expr); + EquivalenceClass *ec; + int pos; + + /* The key needs all these expressions, so we can't skip one */ + if (opfamilies == NIL) + return; + + /* Match in base-EC space: strip any outer-join nulling first */ + expr = (Expr *) remove_nulling_relids((Node *) expr, + root->outer_join_rels, NULL); + + /* + * For a child relation the grouping expressions are child Vars, which + * are child members of the same ECs as their parents, so we must let + * those match too. + */ + pos = find_ec_position(root, expr, opfamilies, collation, NULL, + IS_OTHER_REL(grouped_rel) ? + grouped_rel->relids : NULL); + if (pos < 0) + return; + + ec = list_nth_node(EquivalenceClass, root->eq_classes, pos); + + /* A column equated to a constant is not needed in the key */ + if (ec->ec_has_const) + continue; + + key_ecs = bms_add_member(key_ecs, pos); + } + + if (!bms_is_subset(key_ecs, get_interesting_unique_ecs(root))) + return; + + add_uniquekey(grouped_rel, key_ecs, true); +} + /* * uniquekeys_distinct_is_noop * Is the input relation of the query's DISTINCT step already provably @@ -989,7 +1058,7 @@ uniquekeys_uniquification_is_noop(PlannerInfo *root, RelOptInfo *rel, /* Match in base-EC space: strip any outer-join nulling first */ expr = (Expr *) remove_nulling_relids((Node *) expr, root->outer_join_rels, NULL); - pos = find_ec_position(root, expr, opfamilies, collation, NULL); + pos = find_ec_position(root, expr, opfamilies, collation, NULL, NULL); if (pos >= 0) covered = bms_add_member(covered, pos); } @@ -1182,7 +1251,7 @@ collect_clause_ecs(PlannerInfo *root, List *clause, Bitmapset **ecs_p) expr = (Expr *) remove_nulling_relids((Node *) tle->expr, root->outer_join_rels, NULL); - pos = find_ec_position(root, expr, opfamilies, collation, NULL); + pos = find_ec_position(root, expr, opfamilies, collation, NULL, NULL); } if (pos < 0) return false; @@ -1277,12 +1346,16 @@ side_is_unique(PlannerInfo *root, RelOptInfo *joinrel, * matching "expr". If "candidates" is non-NULL, consider only the ECs at * those positions. Returns -1 if there is none. * + * "child_relids" identifies a child relation whose members should be + * considered as well; pass NULL to match only against parent members. + * * Keys live in base-EC space, so a caller matching a reference that an outer * join may have null-extended must first strip its varnullingrels. */ static int find_ec_position(PlannerInfo *root, Expr *expr, - List *opfamilies, Oid collation, Bitmapset *candidates) + List *opfamilies, Oid collation, Bitmapset *candidates, + Relids child_relids) { foreach_node(EquivalenceClass, ec, root->eq_classes) { @@ -1297,7 +1370,7 @@ find_ec_position(PlannerInfo *root, Expr *expr, ec->ec_collation != collation || !equal(ec->ec_opfamilies, opfamilies)) continue; - if (find_ec_member_matching_expr(ec, expr, NULL)) + if (find_ec_member_matching_expr(ec, expr, child_relids)) return pos; } return -1; @@ -1342,7 +1415,7 @@ base_ec_position(PlannerInfo *root, EquivalenceClass *ec) expr = remove_nulling_relids(expr, root->outer_join_rels, NULL); pos = find_ec_position(root, (Expr *) expr, ec->ec_opfamilies, - ec->ec_collation, NULL); + ec->ec_collation, NULL, NULL); if (pos >= 0) return pos; } diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c index 4701786b308..8bc275c9936 100644 --- a/src/backend/optimizer/util/relnode.c +++ b/src/backend/optimizer/util/relnode.c @@ -486,6 +486,9 @@ build_simple_grouped_rel(PlannerInfo *root, RelOptInfo *rel) grouped_rel->rows = agg_info->grouped_rows; grouped_rel->agg_info = agg_info; + /* Deduce the grouped rel's unique keys */ + populate_agg_rel_uniquekeys(root, grouped_rel); + rel->grouped_rel = grouped_rel; return grouped_rel; diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h index 11a577772cc..df45dc94a95 100644 --- a/src/include/optimizer/paths.h +++ b/src/include/optimizer/paths.h @@ -310,6 +310,8 @@ extern void populate_unique_rel_uniquekeys(PlannerInfo *root, RelOptInfo *input_rel, SpecialJoinInfo *sjinfo, bool is_noop); +extern void populate_agg_rel_uniquekeys(PlannerInfo *root, + RelOptInfo *grouped_rel); extern bool uniquekeys_distinct_is_noop(PlannerInfo *root, RelOptInfo *input_rel); extern bool uniquekeys_grouping_is_noop(PlannerInfo *root, diff --git a/src/test/regress/expected/eager_aggregate.out b/src/test/regress/expected/eager_aggregate.out index 091ae48a92b..5096971087d 100644 --- a/src/test/regress/expected/eager_aggregate.out +++ b/src/test/regress/expected/eager_aggregate.out @@ -31,6 +31,7 @@ GROUP BY t1.a ORDER BY t1.a; Sort Key: t1.a -> Hash Join Output: t1.a, (PARTIAL avg(t2.c)) + Inner Unique: true Hash Cond: (t1.b = t2.b) -> Seq Scan on public.eager_agg_t1 t1 Output: t1.a, t1.b, t1.c @@ -41,7 +42,7 @@ GROUP BY t1.a ORDER BY t1.a; Group Key: t2.b -> Seq Scan on public.eager_agg_t2 t2 Output: t2.a, t2.b, t2.c -(18 rows) +(19 rows) SELECT t1.a, avg(t2.c) FROM eager_agg_t1 t1 @@ -77,6 +78,7 @@ GROUP BY t1.a ORDER BY t1.a; Sort Key: t1.a -> Hash Join Output: t1.a, (PARTIAL avg(t2.c)) + Inner Unique: true Hash Cond: (t1.b = t2.b) -> Seq Scan on public.eager_agg_t1 t1 Output: t1.a, t1.b, t1.c @@ -90,7 +92,7 @@ GROUP BY t1.a ORDER BY t1.a; Sort Key: t2.b -> Seq Scan on public.eager_agg_t2 t2 Output: t2.c, t2.b -(21 rows) +(22 rows) SELECT t1.a, avg(t2.c) FROM eager_agg_t1 t1 @@ -246,6 +248,7 @@ GROUP BY t1.a ORDER BY t1.a; Sort Key: t1.a -> Hash Right Join Output: t1.a, (PARTIAL avg(t2.c)) + Inner Unique: true Hash Cond: (t1.b = t2.b) -> Seq Scan on public.eager_agg_t1 t1 Output: t1.a, t1.b, t1.c @@ -256,7 +259,7 @@ GROUP BY t1.a ORDER BY t1.a; Group Key: t2.b -> Seq Scan on public.eager_agg_t2 t2 Output: t2.a, t2.b, t2.c -(18 rows) +(19 rows) SELECT t1.a, avg(t2.c) FROM eager_agg_t1 t1 @@ -344,6 +347,7 @@ GROUP BY t1.a ORDER BY t1.a; Sort Key: t1.a -> Parallel Hash Join Output: t1.a, (PARTIAL avg(t2.c)) + Inner Unique: true Hash Cond: (t1.b = t2.b) -> Parallel Seq Scan on public.eager_agg_t1 t1 Output: t1.a, t1.b, t1.c @@ -354,7 +358,7 @@ GROUP BY t1.a ORDER BY t1.a; Group Key: t2.b -> Parallel Seq Scan on public.eager_agg_t2 t2 Output: t2.a, t2.b, t2.c -(21 rows) +(22 rows) SELECT t1.a, avg(t2.c) FROM eager_agg_t1 t1 @@ -397,6 +401,7 @@ GROUP BY t1.a ORDER BY t1.a; Sort Key: t1.a -> Hash Join Output: t1.a, (PARTIAL avg(t2.c)) + Inner Unique: true Hash Cond: (t1.b = t2.b) -> Seq Scan on public.eager_agg_t1 t1 Output: t1.a, t1.b, t1.c @@ -407,7 +412,7 @@ GROUP BY t1.a ORDER BY t1.a; Group Key: t2.b -> Seq Scan on public.eager_agg_t2 t2 Output: t2.a, t2.b, t2.c -(18 rows) +(19 rows) SELECT t1.a, avg(t2.c) FROM eager_agg_t1 t1 @@ -527,6 +532,7 @@ GROUP BY t2.b ORDER BY t2.b; Sort Key: t2.b -> Hash Right Semi Join Output: t2.b, (PARTIAL count(*)) + Inner Unique: true Hash Cond: (t1.b = t2.b) -> Seq Scan on public.eager_agg_t1 t1 Output: t1.a, t1.b, t1.c @@ -537,7 +543,7 @@ GROUP BY t2.b ORDER BY t2.b; Group Key: t2.b -> Seq Scan on public.eager_agg_t2 t2 Output: t2.a, t2.b, t2.c -(18 rows) +(19 rows) SELECT t2.b, count(*) FROM eager_agg_t2 t2 @@ -596,6 +602,7 @@ GROUP BY t1.x ORDER BY t1.x; Group Key: t1.x -> Hash Join Output: t1.x, (PARTIAL sum(t1.y)), (PARTIAL count(*)) + Inner Unique: true Hash Cond: (t2.y = t1.x) -> Seq Scan on public.eager_agg_tab2_p1 t2 Output: t2.y @@ -611,6 +618,7 @@ GROUP BY t1.x ORDER BY t1.x; Group Key: t1_1.x -> Hash Join Output: t1_1.x, (PARTIAL sum(t1_1.y)), (PARTIAL count(*)) + Inner Unique: true Hash Cond: (t2_1.y = t1_1.x) -> Seq Scan on public.eager_agg_tab2_p2 t2_1 Output: t2_1.y @@ -626,6 +634,7 @@ GROUP BY t1.x ORDER BY t1.x; Group Key: t1_2.x -> Hash Join Output: t1_2.x, (PARTIAL sum(t1_2.y)), (PARTIAL count(*)) + Inner Unique: true Hash Cond: (t2_2.y = t1_2.x) -> Seq Scan on public.eager_agg_tab2_p3 t2_2 Output: t2_2.y @@ -636,7 +645,7 @@ GROUP BY t1.x ORDER BY t1.x; Group Key: t1_2.x -> Seq Scan on public.eager_agg_tab1_p3 t1_2 Output: t1_2.x, t1_2.y -(49 rows) +(52 rows) SELECT t1.x, sum(t1.y), count(*) FROM eager_agg_tab1 t1 @@ -678,6 +687,7 @@ GROUP BY t2.y ORDER BY t2.y; Group Key: t2.y -> Hash Join Output: t2.y, (PARTIAL sum(t1.y)), (PARTIAL count(*)) + Inner Unique: true Hash Cond: (t2.y = t1.x) -> Seq Scan on public.eager_agg_tab2_p1 t2 Output: t2.y @@ -693,6 +703,7 @@ GROUP BY t2.y ORDER BY t2.y; Group Key: t2_1.y -> Hash Join Output: t2_1.y, (PARTIAL sum(t1_1.y)), (PARTIAL count(*)) + Inner Unique: true Hash Cond: (t2_1.y = t1_1.x) -> Seq Scan on public.eager_agg_tab2_p2 t2_1 Output: t2_1.y @@ -708,6 +719,7 @@ GROUP BY t2.y ORDER BY t2.y; Group Key: t2_2.y -> Hash Join Output: t2_2.y, (PARTIAL sum(t1_2.y)), (PARTIAL count(*)) + Inner Unique: true Hash Cond: (t2_2.y = t1_2.x) -> Seq Scan on public.eager_agg_tab2_p3 t2_2 Output: t2_2.y @@ -718,7 +730,7 @@ GROUP BY t2.y ORDER BY t2.y; Group Key: t1_2.x -> Seq Scan on public.eager_agg_tab1_p3 t1_2 Output: t1_2.y, t1_2.x -(49 rows) +(52 rows) SELECT t2.y, sum(t1.y), count(*) FROM eager_agg_tab1 t1 @@ -762,6 +774,7 @@ GROUP BY t2.x HAVING avg(t1.x) > 5 ORDER BY t2.x; -> Append -> Hash Join Output: t2.x, (PARTIAL sum(t1.x)), (PARTIAL count(*)), (PARTIAL avg(t1.x)) + Inner Unique: true Hash Cond: (t2.y = t1.x) -> Seq Scan on public.eager_agg_tab2_p1 t2 Output: t2.x, t2.y @@ -774,6 +787,7 @@ GROUP BY t2.x HAVING avg(t1.x) > 5 ORDER BY t2.x; Output: t1.x -> Hash Join Output: t2_1.x, (PARTIAL sum(t1_1.x)), (PARTIAL count(*)), (PARTIAL avg(t1_1.x)) + Inner Unique: true Hash Cond: (t2_1.y = t1_1.x) -> Seq Scan on public.eager_agg_tab2_p2 t2_1 Output: t2_1.x, t2_1.y @@ -786,6 +800,7 @@ GROUP BY t2.x HAVING avg(t1.x) > 5 ORDER BY t2.x; Output: t1_1.x -> Hash Join Output: t2_2.x, (PARTIAL sum(t1_2.x)), (PARTIAL count(*)), (PARTIAL avg(t1_2.x)) + Inner Unique: true Hash Cond: (t2_2.y = t1_2.x) -> Seq Scan on public.eager_agg_tab2_p3 t2_2 Output: t2_2.x, t2_2.y @@ -796,7 +811,7 @@ GROUP BY t2.x HAVING avg(t1.x) > 5 ORDER BY t2.x; Group Key: t1_2.x -> Seq Scan on public.eager_agg_tab1_p3 t1_2 Output: t1_2.x -(44 rows) +(47 rows) SELECT t2.x, sum(t1.x), count(*) FROM eager_agg_tab1 t1 @@ -1063,6 +1078,7 @@ GROUP BY t1.x ORDER BY t1.x; Group Key: t1.x -> Hash Join Output: t1.x, (PARTIAL sum(t1.y)), (PARTIAL count(*)) + Inner Unique: true Hash Cond: (t2.y = t1.x) -> Seq Scan on public.eager_agg_tab2_p1 t2 Output: t2.y @@ -1078,6 +1094,7 @@ GROUP BY t1.x ORDER BY t1.x; Group Key: t1_1.x -> Hash Join Output: t1_1.x, (PARTIAL sum(t1_1.y)), (PARTIAL count(*)) + Inner Unique: true Hash Cond: (t2_1.y = t1_1.x) -> Seq Scan on public.eager_agg_tab2_p2 t2_1 Output: t2_1.y @@ -1093,6 +1110,7 @@ GROUP BY t1.x ORDER BY t1.x; Group Key: t1_2.x -> Hash Join Output: t1_2.x, (PARTIAL sum(t1_2.y)), (PARTIAL count(*)) + Inner Unique: true Hash Cond: (t2_2.y = t1_2.x) -> Seq Scan on public.eager_agg_tab2_p3 t2_2 Output: t2_2.y @@ -1103,7 +1121,7 @@ GROUP BY t1.x ORDER BY t1.x; Group Key: t1_2.x -> Seq Scan on public.eager_agg_tab1_p3 t1_2 Output: t1_2.x, t1_2.y -(49 rows) +(52 rows) SELECT t1.x, sum(t1.y), count(*) FROM eager_agg_tab1 t1 @@ -1163,6 +1181,7 @@ GROUP BY t1.x ORDER BY t1.x; Group Key: t1.x -> Hash Join Output: t1.x, (PARTIAL sum(t2.y)), (PARTIAL count(*)) + Inner Unique: true Hash Cond: (t1.x = t2.x) -> Seq Scan on public.eager_agg_tab_ml_p1 t1 Output: t1.x @@ -1178,6 +1197,7 @@ GROUP BY t1.x ORDER BY t1.x; Group Key: t1_1.x -> Hash Join Output: t1_1.x, (PARTIAL sum(t2_1.y)), (PARTIAL count(*)) + Inner Unique: true Hash Cond: (t1_1.x = t2_1.x) -> Seq Scan on public.eager_agg_tab_ml_p2_s1 t1_1 Output: t1_1.x @@ -1193,6 +1213,7 @@ GROUP BY t1.x ORDER BY t1.x; Group Key: t1_2.x -> Hash Join Output: t1_2.x, (PARTIAL sum(t2_2.y)), (PARTIAL count(*)) + Inner Unique: true Hash Cond: (t1_2.x = t2_2.x) -> Seq Scan on public.eager_agg_tab_ml_p2_s2 t1_2 Output: t1_2.x @@ -1208,6 +1229,7 @@ GROUP BY t1.x ORDER BY t1.x; Group Key: t1_3.x -> Hash Join Output: t1_3.x, (PARTIAL sum(t2_3.y)), (PARTIAL count(*)) + Inner Unique: true Hash Cond: (t1_3.x = t2_3.x) -> Seq Scan on public.eager_agg_tab_ml_p3_s1 t1_3 Output: t1_3.x @@ -1223,6 +1245,7 @@ GROUP BY t1.x ORDER BY t1.x; Group Key: t1_4.x -> Hash Join Output: t1_4.x, (PARTIAL sum(t2_4.y)), (PARTIAL count(*)) + Inner Unique: true Hash Cond: (t1_4.x = t2_4.x) -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t1_4 Output: t1_4.x @@ -1233,7 +1256,7 @@ GROUP BY t1.x ORDER BY t1.x; Group Key: t2_4.x -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t2_4 Output: t2_4.y, t2_4.x -(79 rows) +(84 rows) SELECT t1.x, sum(t2.y), count(*) FROM eager_agg_tab_ml t1 @@ -1291,6 +1314,7 @@ GROUP BY t1.y ORDER BY t1.y; -> Append -> Hash Join Output: t1.y, (PARTIAL sum(t2.y)), (PARTIAL count(*)) + Inner Unique: true Hash Cond: (t1.x = t2.x) -> Seq Scan on public.eager_agg_tab_ml_p1 t1 Output: t1.y, t1.x @@ -1303,6 +1327,7 @@ GROUP BY t1.y ORDER BY t1.y; Output: t2.y, t2.x -> Hash Join Output: t1_1.y, (PARTIAL sum(t2_1.y)), (PARTIAL count(*)) + Inner Unique: true Hash Cond: (t1_1.x = t2_1.x) -> Seq Scan on public.eager_agg_tab_ml_p2_s1 t1_1 Output: t1_1.y, t1_1.x @@ -1315,6 +1340,7 @@ GROUP BY t1.y ORDER BY t1.y; Output: t2_1.y, t2_1.x -> Hash Join Output: t1_2.y, (PARTIAL sum(t2_2.y)), (PARTIAL count(*)) + Inner Unique: true Hash Cond: (t1_2.x = t2_2.x) -> Seq Scan on public.eager_agg_tab_ml_p2_s2 t1_2 Output: t1_2.y, t1_2.x @@ -1327,6 +1353,7 @@ GROUP BY t1.y ORDER BY t1.y; Output: t2_2.y, t2_2.x -> Hash Join Output: t1_3.y, (PARTIAL sum(t2_3.y)), (PARTIAL count(*)) + Inner Unique: true Hash Cond: (t1_3.x = t2_3.x) -> Seq Scan on public.eager_agg_tab_ml_p3_s1 t1_3 Output: t1_3.y, t1_3.x @@ -1339,6 +1366,7 @@ GROUP BY t1.y ORDER BY t1.y; Output: t2_3.y, t2_3.x -> Hash Join Output: t1_4.y, (PARTIAL sum(t2_4.y)), (PARTIAL count(*)) + Inner Unique: true Hash Cond: (t1_4.x = t2_4.x) -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t1_4 Output: t1_4.y, t1_4.x @@ -1349,7 +1377,7 @@ GROUP BY t1.y ORDER BY t1.y; Group Key: t2_4.x -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t2_4 Output: t2_4.y, t2_4.x -(67 rows) +(72 rows) SELECT t1.y, sum(t2.y), count(*) FROM eager_agg_tab_ml t1 @@ -1725,6 +1753,7 @@ GROUP BY t1.x ORDER BY t1.x; Group Key: t1.x -> Hash Join Output: t1.x, (PARTIAL sum(t2.y)), (PARTIAL count(*)) + Inner Unique: true Hash Cond: (t1.x = t2.x) -> Seq Scan on public.eager_agg_tab_ml_p1 t1 Output: t1.x @@ -1740,6 +1769,7 @@ GROUP BY t1.x ORDER BY t1.x; Group Key: t1_1.x -> Hash Join Output: t1_1.x, (PARTIAL sum(t2_1.y)), (PARTIAL count(*)) + Inner Unique: true Hash Cond: (t1_1.x = t2_1.x) -> Seq Scan on public.eager_agg_tab_ml_p2_s1 t1_1 Output: t1_1.x @@ -1755,6 +1785,7 @@ GROUP BY t1.x ORDER BY t1.x; Group Key: t1_2.x -> Hash Join Output: t1_2.x, (PARTIAL sum(t2_2.y)), (PARTIAL count(*)) + Inner Unique: true Hash Cond: (t1_2.x = t2_2.x) -> Seq Scan on public.eager_agg_tab_ml_p2_s2 t1_2 Output: t1_2.x @@ -1770,6 +1801,7 @@ GROUP BY t1.x ORDER BY t1.x; Group Key: t1_3.x -> Hash Join Output: t1_3.x, (PARTIAL sum(t2_3.y)), (PARTIAL count(*)) + Inner Unique: true Hash Cond: (t1_3.x = t2_3.x) -> Seq Scan on public.eager_agg_tab_ml_p3_s1 t1_3 Output: t1_3.x @@ -1785,6 +1817,7 @@ GROUP BY t1.x ORDER BY t1.x; Group Key: t1_4.x -> Hash Join Output: t1_4.x, (PARTIAL sum(t2_4.y)), (PARTIAL count(*)) + Inner Unique: true Hash Cond: (t1_4.x = t2_4.x) -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t1_4 Output: t1_4.x @@ -1795,7 +1828,7 @@ GROUP BY t1.x ORDER BY t1.x; Group Key: t2_4.x -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t2_4 Output: t2_4.y, t2_4.x -(79 rows) +(84 rows) SELECT t1.x, sum(t2.y), count(*) FROM eager_agg_tab_ml t1 diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index f576b97ff41..b6ee2e59aa8 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2848,13 +2848,13 @@ order by x.thousand desc, x.twothousand; Sort Key: x.thousand DESC, x.twothousand Presorted Key: x.thousand -> Merge Join - Merge Cond: (y.thousand = x.thousand) - -> Partial GroupAggregate - Group Key: y.thousand - -> Index Only Scan Backward using tenk1_thous_tenthous on tenk1 y + Merge Cond: (x.thousand = y.thousand) -> Sort Sort Key: x.thousand DESC -> Seq Scan on tenk1 x + -> Partial GroupAggregate + Group Key: y.thousand + -> Index Only Scan Backward using tenk1_thous_tenthous on tenk1 y (13 rows) reset enable_hashagg; diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out index 933921d1860..e1344215644 100644 --- a/src/test/regress/expected/select_parallel.out +++ b/src/test/regress/expected/select_parallel.out @@ -1125,27 +1125,27 @@ reset role; explain (costs off, verbose) select count(*) from tenk1 a where (unique1, two) in (select unique1, row_number() over() from tenk1 b); - QUERY PLAN ----------------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------- Aggregate Output: count(*) - -> Hash Right Semi Join - Hash Cond: ((b.unique1 = a.unique1) AND ((row_number() OVER w1) = a.two)) - -> WindowAgg - Output: b.unique1, row_number() OVER w1 - Window: w1 AS (ROWS UNBOUNDED PRECEDING) - -> Gather - Output: b.unique1 - Workers Planned: 4 - -> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 b - Output: b.unique1 - -> Hash + -> Hash Semi Join + Hash Cond: ((a.unique1 = b.unique1) AND (a.two = (row_number() OVER w1))) + -> Gather Output: a.unique1, a.two - -> Gather + Workers Planned: 4 + -> Parallel Seq Scan on public.tenk1 a Output: a.unique1, a.two - Workers Planned: 4 - -> Parallel Seq Scan on public.tenk1 a - Output: a.unique1, a.two + -> Hash + Output: b.unique1, (row_number() OVER w1) + -> WindowAgg + Output: b.unique1, row_number() OVER w1 + Window: w1 AS (ROWS UNBOUNDED PRECEDING) + -> Gather + Output: b.unique1 + Workers Planned: 4 + -> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 b + Output: b.unique1 (19 rows) -- LIMIT/OFFSET within sub-selects can't be pushed to workers. diff --git a/src/test/regress/expected/uniquekeys.out b/src/test/regress/expected/uniquekeys.out index f15bb6cf6f2..d6417def7a7 100644 --- a/src/test/regress/expected/uniquekeys.out +++ b/src/test/regress/expected/uniquekeys.out @@ -633,6 +633,63 @@ select uk_pt1.b from uk_pt1 left join (uk_pt2 join uk_pt3 on uk_pt2.a = uk_pt3.a reset enable_hashjoin; reset enable_partitionwise_join; -- +-- Eager aggregation +-- +-- Partial aggregation keeps one row per group, so the grouped relation is +-- distinct over its grouping expressions, which can prove a join above it +-- inner-unique. +-- +create table uk_ea1 (id int primary key, x int, val int); +create table uk_ea2 (id int, y int); +insert into uk_ea1 select i, i % 10, i from generate_series(1, 1000) i; +insert into uk_ea2 select i % 100, i from generate_series(1, 1000) i; +analyze uk_ea1, uk_ea2; +-- uk_ea2 is grouped by the join column, so the join sees a unique inner side +explain (verbose, costs off) +select uk_ea1.x, sum(uk_ea2.y) from uk_ea1 join uk_ea2 on uk_ea1.id = uk_ea2.id + group by uk_ea1.x order by 1; + QUERY PLAN +-------------------------------------------------------------------- + Sort + Output: uk_ea1.x, (sum(uk_ea2.y)) + Sort Key: uk_ea1.x + -> Finalize HashAggregate + Output: uk_ea1.x, sum(uk_ea2.y) + Group Key: uk_ea1.x + -> Merge Join + Output: uk_ea1.x, (PARTIAL sum(uk_ea2.y)) + Inner Unique: true + Merge Cond: (uk_ea1.id = uk_ea2.id) + -> Index Scan using uk_ea1_pkey on public.uk_ea1 + Output: uk_ea1.id, uk_ea1.x, uk_ea1.val + -> Sort + Output: uk_ea2.id, (PARTIAL sum(uk_ea2.y)) + Sort Key: uk_ea2.id + -> Partial HashAggregate + Output: uk_ea2.id, PARTIAL sum(uk_ea2.y) + Group Key: uk_ea2.id + -> Seq Scan on public.uk_ea2 + Output: uk_ea2.id, uk_ea2.y +(20 rows) + +select uk_ea1.x, sum(uk_ea2.y) from uk_ea1 join uk_ea2 on uk_ea1.id = uk_ea2.id + group by uk_ea1.x order by 1; + x | sum +---+------- + 0 | 45000 + 1 | 49600 + 2 | 49700 + 3 | 49800 + 4 | 49900 + 5 | 50000 + 6 | 50100 + 7 | 50200 + 8 | 50300 + 9 | 50400 +(10 rows) + +drop table uk_ea1, uk_ea2; +-- -- Result correctness: steps that must not be removed -- -- Cases where an over-strong key would drop a step that is really needed, and diff --git a/src/test/regress/sql/uniquekeys.sql b/src/test/regress/sql/uniquekeys.sql index e45d1c6cb93..ebb1e178716 100644 --- a/src/test/regress/sql/uniquekeys.sql +++ b/src/test/regress/sql/uniquekeys.sql @@ -201,6 +201,28 @@ select uk_pt1.b from uk_pt1 left join (uk_pt2 join uk_pt3 on uk_pt2.a = uk_pt3.a reset enable_hashjoin; reset enable_partitionwise_join; +-- +-- Eager aggregation +-- +-- Partial aggregation keeps one row per group, so the grouped relation is +-- distinct over its grouping expressions, which can prove a join above it +-- inner-unique. +-- +create table uk_ea1 (id int primary key, x int, val int); +create table uk_ea2 (id int, y int); +insert into uk_ea1 select i, i % 10, i from generate_series(1, 1000) i; +insert into uk_ea2 select i % 100, i from generate_series(1, 1000) i; +analyze uk_ea1, uk_ea2; + +-- uk_ea2 is grouped by the join column, so the join sees a unique inner side +explain (verbose, costs off) +select uk_ea1.x, sum(uk_ea2.y) from uk_ea1 join uk_ea2 on uk_ea1.id = uk_ea2.id + group by uk_ea1.x order by 1; +select uk_ea1.x, sum(uk_ea2.y) from uk_ea1 join uk_ea2 on uk_ea1.id = uk_ea2.id + group by uk_ea1.x order by 1; + +drop table uk_ea1, uk_ea2; + -- -- Result correctness: steps that must not be removed -- -- 2.37.1 (Apple Git-137.1)