From 142ae3807fb9d3fc71a62bab7ef1bcc250fbddad Mon Sep 17 00:00:00 2001 From: Richard Guo Date: Fri, 14 Aug 2026 10:39:16 +0900 Subject: [PATCH v3 2/3] Teach UniqueKeys about appendrel children and child joins A child relation emits a subset of its parent's rows, and a subset of a distinct set is distinct, so every UniqueKey of the parent is a UniqueKey of the child. Have appendrel children, and the child join relations built for a partitionwise join, simply inherit their parent's keys. --- src/backend/optimizer/README | 7 ++ src/backend/optimizer/path/allpaths.c | 5 +- src/backend/optimizer/path/uniquekeys.c | 16 +++- src/backend/optimizer/util/relnode.c | 7 +- src/test/regress/expected/uniquekeys.out | 107 +++++++++++++++++++++++ src/test/regress/sql/uniquekeys.sql | 39 +++++++++ 6 files changed, 174 insertions(+), 7 deletions(-) diff --git a/src/backend/optimizer/README b/src/backend/optimizer/README index 3cb0020026e..3c99a8d7cb4 100644 --- a/src/backend/optimizer/README +++ b/src/backend/optimizer/README @@ -1137,6 +1137,13 @@ A side with lateral references into the other is re-executed once per row of that other side, so its rows are effectively multiplied and it keeps its keys only through Combination, not Preservation. +An appendrel child emits a subset of its parent's rows, and a subset of a +distinct set is distinct, so a child relation simply inherits its parent's +keys. The same holds for the child join relations built for a partitionwise +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. + 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/allpaths.c b/src/backend/optimizer/path/allpaths.c index 7899db96686..6b618213ed9 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -404,10 +404,7 @@ set_base_rel_uniquekeys(PlannerInfo *root) continue; Assert(rel->relid == rti); /* sanity check on array */ - - /* ignore RTEs that are "other rels" */ - if (rel->reloptkind != RELOPT_BASEREL) - continue; + Assert(IS_SIMPLE_REL(rel)); /* sanity check on rel */ populate_baserel_uniquekeys(root, rel); } diff --git a/src/backend/optimizer/path/uniquekeys.c b/src/backend/optimizer/path/uniquekeys.c index 5e1035cd988..f4e997ee11f 100644 --- a/src/backend/optimizer/path/uniquekeys.c +++ b/src/backend/optimizer/path/uniquekeys.c @@ -80,12 +80,24 @@ static int base_ec_position(PlannerInfo *root, EquivalenceClass *ec); * Deduce the unique keys of a base relation's output. * * This is called once per base rel, after its restriction clauses and (for - * plain relations) index information have been set up. + * plain relations) index information have been set up, and once per appendrel + * child, which just inherits its parent's keys. */ void populate_baserel_uniquekeys(PlannerInfo *root, RelOptInfo *rel) { - Assert(rel->reloptkind == RELOPT_BASEREL); + Assert(IS_SIMPLE_REL(rel)); + + /* + * An appendrel child emits a subset of its parent's rows, so every key of + * the parent is a key of the child. A child Var belongs to the same ECs + * as its parent, so the EC indexes carry over unchanged. + */ + if (rel->reloptkind == RELOPT_OTHER_MEMBER_REL) + { + rel->uniquekeys = rel->top_parent->uniquekeys; + return; + } /* * Compute this now even if we have no rules for this rtekind: it is diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c index 49e9a4dde77..4701786b308 100644 --- a/src/backend/optimizer/util/relnode.c +++ b/src/backend/optimizer/util/relnode.c @@ -1087,7 +1087,6 @@ build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel, joinrel->useridiscurrent = false; joinrel->fdwroutine = NULL; joinrel->fdw_private = NULL; - joinrel->uniquekeys = NIL; joinrel->unique_rel = NULL; joinrel->unique_pathkeys = NIL; joinrel->unique_groupclause = NIL; @@ -1113,6 +1112,12 @@ build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel, joinrel->partexprs = NULL; joinrel->nullable_partexprs = NULL; + /* + * A child joinrel emits a subset of the parent joinrel's rows, so every + * key of the parent is a key of the child. + */ + joinrel->uniquekeys = parent_joinrel->uniquekeys; + /* Compute information relevant to foreign relations. */ set_foreign_rel_properties(joinrel, outer_rel, inner_rel); diff --git a/src/test/regress/expected/uniquekeys.out b/src/test/regress/expected/uniquekeys.out index a37ba6a3ceb..f15bb6cf6f2 100644 --- a/src/test/regress/expected/uniquekeys.out +++ b/src/test/regress/expected/uniquekeys.out @@ -528,6 +528,110 @@ select * from uk_p, uk_q where (uk_p.id, uk_q.id) in (select c, c from uk_pk2); -> Seq Scan on uk_q (11 rows) +-- +-- Appendrel children +-- +-- A child emits a subset of its parent's rows, so it inherits the parent's +-- keys. A partitioned table's unique index must include the partition key, +-- so the parent has keys to inherit; an inheritance parent has none. +-- +create table uk_pt1 (a int, b int, primary key (a)) partition by range (a); +create table uk_pt1a partition of uk_pt1 for values from (0) to (10); +create table uk_pt1b partition of uk_pt1 for values from (10) to (20); +create table uk_pt2 (a int, b int, primary key (a)) partition by range (a); +create table uk_pt2a partition of uk_pt2 for values from (0) to (10); +create table uk_pt2b partition of uk_pt2 for values from (10) to (20); +create table uk_pt3 (a int, b int, primary key (a)) partition by range (a); +create table uk_pt3a partition of uk_pt3 for values from (0) to (10); +create table uk_pt3b partition of uk_pt3 for values from (10) to (20); +create table uk_inh (id int primary key, v int); +create table uk_inh_c () inherits (uk_inh); +-- the partitioned parent's own key removes the DISTINCT +explain (costs off) select distinct a from uk_pt1; + QUERY PLAN +------------------------------------ + Append + -> Seq Scan on uk_pt1a uk_pt1_1 + -> Seq Scan on uk_pt1b uk_pt1_2 +(3 rows) + +-- an inheritance parent has no key: a child row may duplicate a parent one +explain (costs off) select distinct id from uk_inh; + QUERY PLAN +------------------------------------------- + HashAggregate + Group Key: uk_inh.id + -> Append + -> Seq Scan on uk_inh uk_inh_1 + -> Seq Scan on uk_inh_c uk_inh_2 +(5 rows) + +set enable_partitionwise_join to on; +set enable_hashjoin to off; +-- each child join is inner-unique, proven from the child base rel's key +explain (verbose, costs off) +select uk_pt1.b from uk_pt1 join uk_pt2 on uk_pt1.a = uk_pt2.a; + QUERY PLAN +--------------------------------------------------------------------------- + Append + -> Merge Join + Output: uk_pt1_1.b + Inner Unique: true + Merge Cond: (uk_pt1_1.a = uk_pt2_1.a) + -> Index Scan using uk_pt1a_pkey on public.uk_pt1a uk_pt1_1 + Output: uk_pt1_1.b, uk_pt1_1.a + -> Index Only Scan using uk_pt2a_pkey on public.uk_pt2a uk_pt2_1 + Output: uk_pt2_1.a + -> Merge Join + Output: uk_pt1_2.b + Inner Unique: true + Merge Cond: (uk_pt1_2.a = uk_pt2_2.a) + -> Index Scan using uk_pt1b_pkey on public.uk_pt1b uk_pt1_2 + Output: uk_pt1_2.b, uk_pt1_2.a + -> Index Only Scan using uk_pt2b_pkey on public.uk_pt2b uk_pt2_2 + Output: uk_pt2_2.a +(17 rows) + +-- here the outer join's inner side is itself a child joinrel, whose key is +-- inherited from the parent joinrel +explain (verbose, costs off) +select uk_pt1.b from uk_pt1 left join (uk_pt2 join uk_pt3 on uk_pt2.a = uk_pt3.a) + on uk_pt1.a = uk_pt2.a; + QUERY PLAN +--------------------------------------------------------------------------------- + Append + -> Merge Left Join + Output: uk_pt1_1.b + Inner Unique: true + Merge Cond: (uk_pt1_1.a = uk_pt2_1.a) + -> Index Scan using uk_pt1a_pkey on public.uk_pt1a uk_pt1_1 + Output: uk_pt1_1.b, uk_pt1_1.a + -> Merge Join + Output: uk_pt2_1.a + Inner Unique: true + Merge Cond: (uk_pt2_1.a = uk_pt3_1.a) + -> Index Only Scan using uk_pt2a_pkey on public.uk_pt2a uk_pt2_1 + Output: uk_pt2_1.a + -> Index Only Scan using uk_pt3a_pkey on public.uk_pt3a uk_pt3_1 + Output: uk_pt3_1.a + -> Merge Left Join + Output: uk_pt1_2.b + Inner Unique: true + Merge Cond: (uk_pt1_2.a = uk_pt2_2.a) + -> Index Scan using uk_pt1b_pkey on public.uk_pt1b uk_pt1_2 + Output: uk_pt1_2.b, uk_pt1_2.a + -> Merge Join + Output: uk_pt2_2.a + Inner Unique: true + Merge Cond: (uk_pt2_2.a = uk_pt3_2.a) + -> Index Only Scan using uk_pt2b_pkey on public.uk_pt2b uk_pt2_2 + Output: uk_pt2_2.a + -> Index Only Scan using uk_pt3b_pkey on public.uk_pt3b uk_pt3_2 + Output: uk_pt3_2.a +(29 rows) + +reset enable_hashjoin; +reset enable_partitionwise_join; -- -- Result correctness: steps that must not be removed -- @@ -737,3 +841,6 @@ select uk_e2.id, uk_e2.v from uk_e2 join uk_e1 on uk_e2.id = uk_e1.a and uk_e2.v drop table uk_p, uk_q, uk_r, uk_pk2, uk_nul, uk_nnd, uk_txt; drop table uk_defer, uk_part, uk_d1, uk_d2, uk_e1, uk_e2, uk_e3; +drop table uk_pt1, uk_pt2, uk_pt3; +drop table uk_inh cascade; +NOTICE: drop cascades to table uk_inh_c diff --git a/src/test/regress/sql/uniquekeys.sql b/src/test/regress/sql/uniquekeys.sql index 8de13c9d233..e45d1c6cb93 100644 --- a/src/test/regress/sql/uniquekeys.sql +++ b/src/test/regress/sql/uniquekeys.sql @@ -164,6 +164,43 @@ select * from uk_p, uk_q where (uk_p.id, uk_q.id) in (select a, b from uk_pk2); explain (costs off) select * from uk_p, uk_q where (uk_p.id, uk_q.id) in (select c, c from uk_pk2); +-- +-- Appendrel children +-- +-- A child emits a subset of its parent's rows, so it inherits the parent's +-- keys. A partitioned table's unique index must include the partition key, +-- so the parent has keys to inherit; an inheritance parent has none. +-- +create table uk_pt1 (a int, b int, primary key (a)) partition by range (a); +create table uk_pt1a partition of uk_pt1 for values from (0) to (10); +create table uk_pt1b partition of uk_pt1 for values from (10) to (20); +create table uk_pt2 (a int, b int, primary key (a)) partition by range (a); +create table uk_pt2a partition of uk_pt2 for values from (0) to (10); +create table uk_pt2b partition of uk_pt2 for values from (10) to (20); +create table uk_pt3 (a int, b int, primary key (a)) partition by range (a); +create table uk_pt3a partition of uk_pt3 for values from (0) to (10); +create table uk_pt3b partition of uk_pt3 for values from (10) to (20); +create table uk_inh (id int primary key, v int); +create table uk_inh_c () inherits (uk_inh); + +-- the partitioned parent's own key removes the DISTINCT +explain (costs off) select distinct a from uk_pt1; +-- an inheritance parent has no key: a child row may duplicate a parent one +explain (costs off) select distinct id from uk_inh; + +set enable_partitionwise_join to on; +set enable_hashjoin to off; +-- each child join is inner-unique, proven from the child base rel's key +explain (verbose, costs off) +select uk_pt1.b from uk_pt1 join uk_pt2 on uk_pt1.a = uk_pt2.a; +-- here the outer join's inner side is itself a child joinrel, whose key is +-- inherited from the parent joinrel +explain (verbose, costs off) +select uk_pt1.b from uk_pt1 left join (uk_pt2 join uk_pt3 on uk_pt2.a = uk_pt3.a) + on uk_pt1.a = uk_pt2.a; +reset enable_hashjoin; +reset enable_partitionwise_join; + -- -- Result correctness: steps that must not be removed -- @@ -236,3 +273,5 @@ select uk_e2.id, uk_e2.v from uk_e2 join uk_e1 on uk_e2.id = uk_e1.a and uk_e2.v drop table uk_p, uk_q, uk_r, uk_pk2, uk_nul, uk_nnd, uk_txt; drop table uk_defer, uk_part, uk_d1, uk_d2, uk_e1, uk_e2, uk_e3; +drop table uk_pt1, uk_pt2, uk_pt3; +drop table uk_inh cascade; -- 2.37.1 (Apple Git-137.1)