Hello Matheus, On 01.09.26 23:20, Jan Nidzwetzki wrote: > On 01.09.26 21:37, Matheus Alcantara wrote: >> I think it's right, the citext case now plans as partial aggregation and >> returns the correct single group. So v8 looks good to me. > Thank you for reviewing and confirming. I ran additional tests on v8 and > found no further issues. The patch series looks good to me as well. Sorry for the extra round. I noticed that v8 does not pass the tests on all platforms. The partition bounds 'A' and 'a' in the test I added sort differently depending on the collation, which changes the order of the Append children on macOS and Windows. Since the test type compares case-insensitively, it also changes which of the two values is reported for the group.
Attached is v9. It adds COLLATE "C" to the partition key of that test, so the partition order is stable everywhere. This is a test-only change; no code changed, and patches 1 to 3 are unmodified. Best regards Jan -- Jan Nidzwetzki PlanetScale Postgres Core Team
From 02b8283b21da7e1f8710188439dfe03daa3e0728 Mon Sep 17 00:00:00 2001 From: Matheus Alcantara <[email protected]> Date: Wed, 26 Aug 2026 15:45:56 -0300 Subject: [PATCH v9 1/4] Fix partition pruning for partition keys wrapped by RelabelType match_clause_to_partition_key() strips RelabelType decorations from the operands of the clause being matched, but not from the partition key expression itself. When the partition key is an expression involving a binary-compatible cast, for example PARTITION BY LIST ((col::text)) on a varchar column, the stored partition key expression is itself a RelabelType. The equal() comparisons against the stripped clause operands therefore never match, so no pruning steps are generated at all and every partition is scanned regardless of the clause. Fix by stripping the partition key expression the same way, once, up front. This covers all of the clause shapes handled by the function (OpExpr, ScalarArrayOpExpr, NullTest, and the Boolean clause forms recognized by match_boolean_partition_clause()), since they all receive the same partkey. Collation correctness does not depend on the partition key expression's exposed collation: for the operator clause forms it is established by checking the clause's input collation against the partition collation via PartCollMatchesExprColl(), and nullness and Boolean tests do not depend on collation at all. Reviewed-by: Jan Nidzwetzki <[email protected]> Discussion: https://postgr.es/m/[email protected] --- src/backend/partitioning/partprune.c | 10 ++++ src/test/regress/expected/partition_prune.out | 51 +++++++++++++++++++ src/test/regress/sql/partition_prune.sql | 17 +++++++ 3 files changed, 78 insertions(+) diff --git a/src/backend/partitioning/partprune.c b/src/backend/partitioning/partprune.c index 5e98f503244..dc87b9b9329 100644 --- a/src/backend/partitioning/partprune.c +++ b/src/backend/partitioning/partprune.c @@ -1836,6 +1836,16 @@ match_clause_to_partition_key(GeneratePruningStepsContext *context, Expr *expr; bool notclause; + /* + * Strip any RelabelType from the partition key expression itself, to + * match the stripping already done below on the clause operands. + * Partition key expressions can be wrapped in a RelabelType. Collation + * correctness doesn't depend on keeping the RelabelType here, since it's + * separately verified below via PartCollMatchesExprColl(). + */ + while (IsA(partkey, RelabelType)) + partkey = ((const RelabelType *) partkey)->arg; + /* * Recognize specially shaped clauses that match a Boolean partition key. */ diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index 8f1119a026d..899df49fbc1 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -186,6 +186,57 @@ explain (costs off) select * from coll_pruning where a collate "POSIX" = 'a' col Filter: ((a)::text = 'a'::text COLLATE "POSIX") (7 rows) +-- the partition key expression involves a binary-compatible cast, so it is +-- stored wrapped in a RelabelType; that has to be ignored when matching +-- clauses to the partition key, else no pruning occurs +create table cast_pruning (a int, c varchar(40)) partition by list ((c::text)); +create table cast_pruning_a partition of cast_pruning for values in ('a'); +create table cast_pruning_b partition of cast_pruning for values in ('b'); +create table cast_pruning_null partition of cast_pruning for values in (null); +insert into cast_pruning values (1, 'a'), (2, 'b'), (3, null); +explain (costs off) select * from cast_pruning where c = 'a'; + QUERY PLAN +----------------------------------------- + Seq Scan on cast_pruning_a cast_pruning + Filter: ((c)::text = 'a'::text) +(2 rows) + +explain (costs off) select * from cast_pruning where c in ('a', 'b'); + QUERY PLAN +----------------------------------------------------- + Append + -> Seq Scan on cast_pruning_a cast_pruning_1 + Filter: ((c)::text = ANY ('{a,b}'::text[])) + -> Seq Scan on cast_pruning_b cast_pruning_2 + Filter: ((c)::text = ANY ('{a,b}'::text[])) +(5 rows) + +explain (costs off) select * from cast_pruning where c is null; + QUERY PLAN +-------------------------------------------- + Seq Scan on cast_pruning_null cast_pruning + Filter: (c IS NULL) +(2 rows) + +select * from cast_pruning where c = 'a'; + a | c +---+--- + 1 | a +(1 row) + +select * from cast_pruning where c in ('a', 'b') order by a; + a | c +---+--- + 1 | a + 2 | b +(2 rows) + +select * from cast_pruning where c is null; + a | c +---+--- + 3 | +(1 row) + create table rlp (a int, b varchar) partition by range (a); create table rlp_default partition of rlp default partition by list (a); create table rlp_default_default partition of rlp_default default; diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql index f967658d4b5..918d24a5b11 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -53,6 +53,23 @@ explain (costs off) select * from coll_pruning where a collate "C" = 'a' collate -- collation doesn't match the partitioning collation, no pruning occurs explain (costs off) select * from coll_pruning where a collate "POSIX" = 'a' collate "POSIX"; +-- the partition key expression involves a binary-compatible cast, so it is +-- stored wrapped in a RelabelType; that has to be ignored when matching +-- clauses to the partition key, else no pruning occurs +create table cast_pruning (a int, c varchar(40)) partition by list ((c::text)); +create table cast_pruning_a partition of cast_pruning for values in ('a'); +create table cast_pruning_b partition of cast_pruning for values in ('b'); +create table cast_pruning_null partition of cast_pruning for values in (null); +insert into cast_pruning values (1, 'a'), (2, 'b'), (3, null); + +explain (costs off) select * from cast_pruning where c = 'a'; +explain (costs off) select * from cast_pruning where c in ('a', 'b'); +explain (costs off) select * from cast_pruning where c is null; + +select * from cast_pruning where c = 'a'; +select * from cast_pruning where c in ('a', 'b') order by a; +select * from cast_pruning where c is null; + create table rlp (a int, b varchar) partition by range (a); create table rlp_default partition of rlp default partition by list (a); create table rlp_default_default partition of rlp_default default; -- 2.47.3
From c7cb933cebc2742b606e46cf7f5314446e35f141 Mon Sep 17 00:00:00 2001 From: Matheus Alcantara <[email protected]> Date: Wed, 26 Aug 2026 15:49:42 -0300 Subject: [PATCH v9 2/4] Enable partitionwise join for partition keys wrapped by RelabelType The function exprs_known_equal() is used by the planner to determine if two expressions are semantically equivalent, often by checking if they belong to the same Equivalence Class (EC). Either side of that comparison may be decorated with a binary-compatible RelabelType, and which side carries it varies. When a partitioned table uses a varchar(N) type as a partition key, the expression stored in the equivalence class member (em->em_expr) is wrapped in a RelabelType while the input expression (item1 or item2) is a plain Var; when the partition key is itself an expression involving a binary-compatible cast, such as PARTITION BY HASH ((col::text)), it is the input expression that is wrapped. Comparing a wrapped expression against an unwrapped one leads to an incorrect equal() comparison and fails to detect a known equivalence. This prevents the planner from recognizing that a join condition matches the partition keys, thereby disabling optimizations like partitionwise joins. This commit modifies exprs_known_equal() to strip RelabelType decorations from both sides of the comparison: the EC member's expression as well as the input expressions (item1 and item2). Stripping only proceeds through layers that don't change the expression's exposed collation. canonicalize_ec_expression() deliberately wraps equivalence class members in a collation-changing RelabelType so that every member of a class exposes that class's collation, and discarding one is not safe: under a non-deterministic collation, equality in the EC's collation differs from equality in the partition collation, so a table's partitions are not join-closed and reporting such keys as equal would let have_partkey_equi_join() choose a partitionwise join that silently omits matching rows. Co-authored-by: Jian He Reviewed-by: Jan Nidzwetzki <[email protected]> Discussion: https://postgr.es/m/[email protected] --- .../postgres_fdw/expected/postgres_fdw.out | 22 +++++++ contrib/postgres_fdw/sql/postgres_fdw.sql | 17 ++++++ src/backend/optimizer/path/equivclass.c | 52 ++++++++++++++++- .../regress/expected/collate.icu.utf8.out | 46 +++++++++++++++ src/test/regress/expected/partition_join.out | 58 +++++++++++++++++++ src/test/regress/sql/collate.icu.utf8.sql | 26 +++++++++ src/test/regress/sql/partition_join.sql | 37 ++++++++++++ 7 files changed, 256 insertions(+), 2 deletions(-) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 517d15cf1fa..24c3cf31588 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -11192,6 +11192,16 @@ CREATE FOREIGN TABLE ftprt2_p2 PARTITION OF fprt2 FOR VALUES FROM (250) TO (500) ANALYZE fprt2; ANALYZE fprt2_p1; ANALYZE fprt2_p2; +CREATE TABLE fprt3 (a int, b int, c varchar(40)) PARTITION BY HASH(c); +CREATE TABLE fprt3_ft (a int, b int, c varchar(40)); +CREATE TABLE fprt3_p1 PARTITION OF fprt3 FOR VALUES WITH (MODULUS 2, REMAINDER 0); +CREATE FOREIGN TABLE fprt3_p2 PARTITION OF fprt3 FOR VALUES WITH (MODULUS 2, REMAINDER 1) + SERVER loopback OPTIONS (table_name 'fprt3_ft'); +CREATE TABLE fprt4 (a int, b int, c varchar(40)) PARTITION BY HASH(c); +CREATE TABLE fprt4_ft (a int, b int, c varchar(40)); +CREATE TABLE fprt4_p1 PARTITION OF fprt4 FOR VALUES WITH (MODULUS 2, REMAINDER 0); +CREATE FOREIGN TABLE fprt4_p2 PARTITION OF fprt4 FOR VALUES WITH (MODULUS 2, REMAINDER 1) + SERVER loopback OPTIONS (table_name 'fprt4_ft'); -- inner join three tables EXPLAIN (COSTS OFF) SELECT t1.a,t2.b,t3.c FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) INNER JOIN fprt1 t3 ON (t2.b = t3.a) WHERE t1.a % 25 =0 ORDER BY 1,2,3; @@ -11361,6 +11371,18 @@ SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE t1.a 400 | 400 (4 rows) +-- with a varchar partition key, the partitionwise join lets the whole join be +-- pushed down to the remote server +EXPLAIN (VERBOSE, COSTS OFF) +SELECT fprt3.a, fprt4.a FROM fprt3 JOIN fprt4 ON fprt3.c = fprt4.c WHERE fprt3.c = '0002'; + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------------- + Foreign Scan + Output: fprt3.a, fprt4.a + Relations: (public.fprt3_p2 fprt3) INNER JOIN (public.fprt4_p2 fprt4) + Remote SQL: SELECT r4.a, r5.a FROM (public.fprt3_ft r4 INNER JOIN public.fprt4_ft r5 ON (((r5.c = '0002')) AND ((r4.c = '0002')))) +(4 rows) + RESET enable_partitionwise_join; -- =================================================================== -- test partitionwise aggregates diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index ec766e2b28a..041b81d6885 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -3693,6 +3693,18 @@ ANALYZE fprt2; ANALYZE fprt2_p1; ANALYZE fprt2_p2; +CREATE TABLE fprt3 (a int, b int, c varchar(40)) PARTITION BY HASH(c); +CREATE TABLE fprt3_ft (a int, b int, c varchar(40)); +CREATE TABLE fprt3_p1 PARTITION OF fprt3 FOR VALUES WITH (MODULUS 2, REMAINDER 0); +CREATE FOREIGN TABLE fprt3_p2 PARTITION OF fprt3 FOR VALUES WITH (MODULUS 2, REMAINDER 1) + SERVER loopback OPTIONS (table_name 'fprt3_ft'); + +CREATE TABLE fprt4 (a int, b int, c varchar(40)) PARTITION BY HASH(c); +CREATE TABLE fprt4_ft (a int, b int, c varchar(40)); +CREATE TABLE fprt4_p1 PARTITION OF fprt4 FOR VALUES WITH (MODULUS 2, REMAINDER 0); +CREATE FOREIGN TABLE fprt4_p2 PARTITION OF fprt4 FOR VALUES WITH (MODULUS 2, REMAINDER 1) + SERVER loopback OPTIONS (table_name 'fprt4_ft'); + -- inner join three tables EXPLAIN (COSTS OFF) SELECT t1.a,t2.b,t3.c FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) INNER JOIN fprt1 t3 ON (t2.b = t3.a) WHERE t1.a % 25 =0 ORDER BY 1,2,3; @@ -3723,6 +3735,11 @@ EXPLAIN (COSTS OFF) SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE t1.a % 25 = 0 ORDER BY 1,2 FOR UPDATE OF t1; SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE t1.a % 25 = 0 ORDER BY 1,2 FOR UPDATE OF t1; +-- with a varchar partition key, the partitionwise join lets the whole join be +-- pushed down to the remote server +EXPLAIN (VERBOSE, COSTS OFF) +SELECT fprt3.a, fprt4.a FROM fprt3 JOIN fprt4 ON fprt3.c = fprt4.c WHERE fprt3.c = '0002'; + RESET enable_partitionwise_join; diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c index 393a7a69742..df2717d9584 100644 --- a/src/backend/optimizer/path/equivclass.c +++ b/src/backend/optimizer/path/equivclass.c @@ -2583,6 +2583,40 @@ find_join_domain(PlannerInfo *root, Relids relids) } +/* + * strip_collation_preserving_relabel + * Remove any leading RelabelType decorations from "node", but only the + * layers that don't change the expression's exposed collation. + * + * This lets us match expressions that differ only by a binary-compatible + * cast, such as varchar to text. + * + * Layers that do change the exposed collation must be kept. Note that + * canonicalize_ec_expression() deliberately wraps equivalence class members + * in such a RelabelType, so that every member of a class exposes that + * class's collation; that wrapper is not mere decoration. Under a + * non-deterministic collation, equality in the equivalence class's collation + * differs from equality in the partition collation, so a table's partitions + * are not join-closed and reporting such keys as equal would let + * have_partkey_equi_join() choose a partitionwise join that silently omits + * matching rows. We can't leave that check to the caller, since + * exprs_known_equal() returns only a boolean and its callers never learn the + * collation of the equivalence class that proved the equality. + */ +static Node * +strip_collation_preserving_relabel(Node *node) +{ + while (node && IsA(node, RelabelType)) + { + RelabelType *re = (RelabelType *) node; + + if (re->resultcollid != exprCollation((Node *) re->arg)) + break; + node = (Node *) re->arg; + } + return node; +} + /* * exprs_known_equal * Detect whether two expressions are known equal due to equivalence @@ -2602,6 +2636,17 @@ exprs_known_equal(PlannerInfo *root, Node *item1, Node *item2, Oid opfamily) { ListCell *lc1; + /* + * Strip any collation-preserving RelabelType decorations from the input + * expressions, so that they can still be matched below against EC members + * that get the same treatment. This is needed because, e.g., partition + * key expressions stored in an EC member may be wrapped in a RelabelType + * (binary-compatible cast) that the caller-supplied item may or may not + * also carry. + */ + item1 = strip_collation_preserving_relabel(item1); + item2 = strip_collation_preserving_relabel(item2); + foreach(lc1, root->eq_classes) { EquivalenceClass *ec = (EquivalenceClass *) lfirst(lc1); @@ -2628,12 +2673,15 @@ exprs_known_equal(PlannerInfo *root, Node *item1, Node *item2, Oid opfamily) foreach(lc2, ec->ec_members) { EquivalenceMember *em = (EquivalenceMember *) lfirst(lc2); + Node *expr; /* Child members should not exist in ec_members */ Assert(!em->em_is_child); - if (equal(item1, em->em_expr)) + + expr = strip_collation_preserving_relabel((Node *) em->em_expr); + if (equal(item1, expr)) item1member = true; - else if (equal(item2, em->em_expr)) + else if (equal(item2, expr)) item2member = true; /* Exit as soon as equality is proven */ if (item1member && item2member) diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out index fcfcc658bea..130997f9928 100644 --- a/src/test/regress/expected/collate.icu.utf8.out +++ b/src/test/regress/expected/collate.icu.utf8.out @@ -3574,11 +3574,57 @@ SELECT t1.c, count(t2.c) FROM pagg_tab5 t1 JOIN pagg_tab6 t2 ON t1.c = t2.c AND d | 9 (4 rows) +-- Here the partition key collation is the same as the collation of the +-- partitioned column, so a partitionwise join can't be rejected by comparing +-- those two. The equality of the partition keys is proven only by an +-- equivalence class, whose collation (case_insensitive) is not the partition +-- key collation. Under a non-deterministic collation the partitions are not +-- join-closed, so a partitionwise join would give a wrong answer here (it +-- would return 5 rather than 9). +SET enable_partitionwise_join TO true; +CREATE TABLE pwj_tab1 (a int, b text) PARTITION BY LIST (b); +CREATE TABLE pwj_tab1_p1 PARTITION OF pwj_tab1 FOR VALUES IN ('AbC'); +CREATE TABLE pwj_tab1_p2 PARTITION OF pwj_tab1 FOR VALUES IN ('abc', 'ABC'); +CREATE TABLE pwj_tab2 (a int, b text) PARTITION BY LIST (b); +CREATE TABLE pwj_tab2_p1 PARTITION OF pwj_tab2 FOR VALUES IN ('AbC'); +CREATE TABLE pwj_tab2_p2 PARTITION OF pwj_tab2 FOR VALUES IN ('abc', 'ABC'); +INSERT INTO pwj_tab1 VALUES (1, 'abc'), (2, 'ABC'), (3, 'AbC'); +INSERT INTO pwj_tab2 VALUES (1, 'abc'), (2, 'ABC'), (3, 'AbC'); +ANALYZE pwj_tab1; +ANALYZE pwj_tab2; +EXPLAIN (COSTS OFF) +SELECT count(*) FROM pwj_tab1 t1, pwj_tab2 t2 WHERE t1.b = 'abc' COLLATE case_insensitive AND t2.b = 'abc' COLLATE case_insensitive; + QUERY PLAN +-------------------------------------------------------------------------------------- + Aggregate + -> Nested Loop + -> Append + -> Seq Scan on pwj_tab1_p2 t1_1 + Filter: ((b)::text = 'abc'::text COLLATE case_insensitive) + -> Seq Scan on pwj_tab1_p1 t1_2 + Filter: ((b)::text = 'abc'::text COLLATE case_insensitive) + -> Materialize + -> Append + -> Seq Scan on pwj_tab2_p2 t2_1 + Filter: ((b)::text = 'abc'::text COLLATE case_insensitive) + -> Seq Scan on pwj_tab2_p1 t2_2 + Filter: ((b)::text = 'abc'::text COLLATE case_insensitive) +(13 rows) + +SELECT count(*) FROM pwj_tab1 t1, pwj_tab2 t2 WHERE t1.b = 'abc' COLLATE case_insensitive AND t2.b = 'abc' COLLATE case_insensitive; + count +------- + 9 +(1 row) + DROP TABLE pagg_tab3; DROP TABLE pagg_tab4; DROP TABLE pagg_tab5; DROP TABLE pagg_tab6; +DROP TABLE pwj_tab1; +DROP TABLE pwj_tab2; RESET enable_partitionwise_aggregate; +RESET enable_partitionwise_join; RESET max_parallel_workers_per_gather; RESET enable_incremental_sort; -- diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out index 38643d41fd7..8a122a6cc24 100644 --- a/src/test/regress/expected/partition_join.out +++ b/src/test/regress/expected/partition_join.out @@ -1750,6 +1750,64 @@ SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, ph 273.0000000000000000 | 273.0000000000000000 | 548.0000000000000000 | 0005 | 0005 | A0005 (6 rows) +-- +-- hash partitioned by a varchar column and an int column +-- +-- The partition key for c is a plain Var, but an equivalence class member +-- derived from comparing it against a text value is wrapped in a RelabelType. +-- Proving the partition keys equal therefore requires ignoring +-- binary-compatible relabeling on both sides of the comparison. +-- +-- Below, c's equality is proven by the equivalence class rather than by a join +-- clause, which is why both sides are compared to a constant instead of to +-- each other; d's equality comes from the join clause. Hash pruning needs +-- every partition key column to be constrained, so constraining only c prunes +-- nothing and all partitions remain on both sides, which is what makes the +-- partitionwise join visible here. +-- +CREATE TABLE pht3 (a int, c varchar(40), d int) PARTITION BY HASH (c, d); +CREATE TABLE pht3_p1 PARTITION OF pht3 FOR VALUES WITH (MODULUS 2, REMAINDER 0); +CREATE TABLE pht3_p2 PARTITION OF pht3 FOR VALUES WITH (MODULUS 2, REMAINDER 1); +INSERT INTO pht3 SELECT i, to_char(i/50, 'FM0000'), i % 10 FROM generate_series(0, 599, 2) i; +ANALYZE pht3; +CREATE TABLE pht4 (a int, c varchar(40), d int) PARTITION BY HASH (c, d); +CREATE TABLE pht4_p1 PARTITION OF pht4 FOR VALUES WITH (MODULUS 2, REMAINDER 0); +CREATE TABLE pht4_p2 PARTITION OF pht4 FOR VALUES WITH (MODULUS 2, REMAINDER 1); +INSERT INTO pht4 SELECT i, to_char(i/50, 'FM0000'), i % 10 FROM generate_series(0, 599, 3) i; +ANALYZE pht4; +-- avoid hash and merge joins, whose costs here are close enough to the +-- partitionwise nested loop's to make the test output unstable +SET enable_hashjoin = off; +SET enable_mergejoin = off; +EXPLAIN (COSTS OFF) +SELECT t1.a, t2.a FROM pht3 t1 JOIN pht4 t2 ON t1.d = t2.d WHERE t1.c = '0002' AND t2.c = '0002'; + QUERY PLAN +-------------------------------------------------------- + Append + -> Nested Loop + Join Filter: (t1_1.d = t2_1.d) + -> Seq Scan on pht3_p1 t1_1 + Filter: ((c)::text = '0002'::text) + -> Materialize + -> Seq Scan on pht4_p1 t2_1 + Filter: ((c)::text = '0002'::text) + -> Nested Loop + Join Filter: (t1_2.d = t2_2.d) + -> Seq Scan on pht3_p2 t1_2 + Filter: ((c)::text = '0002'::text) + -> Materialize + -> Seq Scan on pht4_p2 t2_2 + Filter: ((c)::text = '0002'::text) +(15 rows) + +SELECT count(*) FROM pht3 t1 JOIN pht4 t2 ON t1.d = t2.d WHERE t1.c = '0002' AND t2.c = '0002'; + count +------- + 40 +(1 row) + +RESET enable_hashjoin; +RESET enable_mergejoin; -- test default partition behavior for range ALTER TABLE prt1 DETACH PARTITION prt1_p3; ALTER TABLE prt1 ATTACH PARTITION prt1_p3 DEFAULT; diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql index ce4e2bb3ffd..ea7321f22d7 100644 --- a/src/test/regress/sql/collate.icu.utf8.sql +++ b/src/test/regress/sql/collate.icu.utf8.sql @@ -1301,12 +1301,38 @@ EXPLAIN (COSTS OFF) SELECT t1.c, count(t2.c) FROM pagg_tab5 t1 JOIN pagg_tab6 t2 ON t1.c = t2.c AND t1.c = t2.b GROUP BY 1 ORDER BY t1.c COLLATE "C"; SELECT t1.c, count(t2.c) FROM pagg_tab5 t1 JOIN pagg_tab6 t2 ON t1.c = t2.c AND t1.c = t2.b GROUP BY 1 ORDER BY t1.c COLLATE "C"; +-- Here the partition key collation is the same as the collation of the +-- partitioned column, so a partitionwise join can't be rejected by comparing +-- those two. The equality of the partition keys is proven only by an +-- equivalence class, whose collation (case_insensitive) is not the partition +-- key collation. Under a non-deterministic collation the partitions are not +-- join-closed, so a partitionwise join would give a wrong answer here (it +-- would return 5 rather than 9). +SET enable_partitionwise_join TO true; +CREATE TABLE pwj_tab1 (a int, b text) PARTITION BY LIST (b); +CREATE TABLE pwj_tab1_p1 PARTITION OF pwj_tab1 FOR VALUES IN ('AbC'); +CREATE TABLE pwj_tab1_p2 PARTITION OF pwj_tab1 FOR VALUES IN ('abc', 'ABC'); +CREATE TABLE pwj_tab2 (a int, b text) PARTITION BY LIST (b); +CREATE TABLE pwj_tab2_p1 PARTITION OF pwj_tab2 FOR VALUES IN ('AbC'); +CREATE TABLE pwj_tab2_p2 PARTITION OF pwj_tab2 FOR VALUES IN ('abc', 'ABC'); +INSERT INTO pwj_tab1 VALUES (1, 'abc'), (2, 'ABC'), (3, 'AbC'); +INSERT INTO pwj_tab2 VALUES (1, 'abc'), (2, 'ABC'), (3, 'AbC'); +ANALYZE pwj_tab1; +ANALYZE pwj_tab2; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM pwj_tab1 t1, pwj_tab2 t2 WHERE t1.b = 'abc' COLLATE case_insensitive AND t2.b = 'abc' COLLATE case_insensitive; +SELECT count(*) FROM pwj_tab1 t1, pwj_tab2 t2 WHERE t1.b = 'abc' COLLATE case_insensitive AND t2.b = 'abc' COLLATE case_insensitive; + DROP TABLE pagg_tab3; DROP TABLE pagg_tab4; DROP TABLE pagg_tab5; DROP TABLE pagg_tab6; +DROP TABLE pwj_tab1; +DROP TABLE pwj_tab2; RESET enable_partitionwise_aggregate; +RESET enable_partitionwise_join; RESET max_parallel_workers_per_gather; RESET enable_incremental_sort; diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql index c4549fc1ad8..2a423f14190 100644 --- a/src/test/regress/sql/partition_join.sql +++ b/src/test/regress/sql/partition_join.sql @@ -350,6 +350,43 @@ EXPLAIN (COSTS OFF) SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c; SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c; +-- +-- hash partitioned by a varchar column and an int column +-- +-- The partition key for c is a plain Var, but an equivalence class member +-- derived from comparing it against a text value is wrapped in a RelabelType. +-- Proving the partition keys equal therefore requires ignoring +-- binary-compatible relabeling on both sides of the comparison. +-- +-- Below, c's equality is proven by the equivalence class rather than by a join +-- clause, which is why both sides are compared to a constant instead of to +-- each other; d's equality comes from the join clause. Hash pruning needs +-- every partition key column to be constrained, so constraining only c prunes +-- nothing and all partitions remain on both sides, which is what makes the +-- partitionwise join visible here. +-- +CREATE TABLE pht3 (a int, c varchar(40), d int) PARTITION BY HASH (c, d); +CREATE TABLE pht3_p1 PARTITION OF pht3 FOR VALUES WITH (MODULUS 2, REMAINDER 0); +CREATE TABLE pht3_p2 PARTITION OF pht3 FOR VALUES WITH (MODULUS 2, REMAINDER 1); +INSERT INTO pht3 SELECT i, to_char(i/50, 'FM0000'), i % 10 FROM generate_series(0, 599, 2) i; +ANALYZE pht3; + +CREATE TABLE pht4 (a int, c varchar(40), d int) PARTITION BY HASH (c, d); +CREATE TABLE pht4_p1 PARTITION OF pht4 FOR VALUES WITH (MODULUS 2, REMAINDER 0); +CREATE TABLE pht4_p2 PARTITION OF pht4 FOR VALUES WITH (MODULUS 2, REMAINDER 1); +INSERT INTO pht4 SELECT i, to_char(i/50, 'FM0000'), i % 10 FROM generate_series(0, 599, 3) i; +ANALYZE pht4; + +-- avoid hash and merge joins, whose costs here are close enough to the +-- partitionwise nested loop's to make the test output unstable +SET enable_hashjoin = off; +SET enable_mergejoin = off; +EXPLAIN (COSTS OFF) +SELECT t1.a, t2.a FROM pht3 t1 JOIN pht4 t2 ON t1.d = t2.d WHERE t1.c = '0002' AND t2.c = '0002'; +SELECT count(*) FROM pht3 t1 JOIN pht4 t2 ON t1.d = t2.d WHERE t1.c = '0002' AND t2.c = '0002'; +RESET enable_hashjoin; +RESET enable_mergejoin; + -- test default partition behavior for range ALTER TABLE prt1 DETACH PARTITION prt1_p3; ALTER TABLE prt1 ATTACH PARTITION prt1_p3 DEFAULT; -- 2.47.3
From 97528082ce98d0bc13aac129896a9c53eb408b4e Mon Sep 17 00:00:00 2001 From: Jan Nidzwetzki <[email protected]> Date: Fri, 28 Aug 2026 15:03:59 +0200 Subject: [PATCH v9 3/4] Enable partitionwise join for outer joins on RelabelType-wrapped keys An outer join qual forms no equivalence class, so have_partkey_equi_join() can only prove the keys equal by matching the clause with match_expr_to_partition_keys(). That function strips RelabelType decorations from the clause operand but not from the partition key expressions, which are themselves wrapped in a RelabelType when the key involves a binary-coercible cast, so the match fails. Fix by stripping both sides. Collation is not lost, as the caller compares the clause's inputcollid against the partition collation. Discussion: https://postgr.es/m/[email protected] --- src/backend/optimizer/util/relnode.c | 27 ++++-- src/test/regress/expected/partition_join.out | 88 ++++++++++++++++++++ src/test/regress/sql/partition_join.sql | 43 ++++++++++ 3 files changed, 153 insertions(+), 5 deletions(-) diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c index ee69f81945f..1d45a013acf 100644 --- a/src/backend/optimizer/util/relnode.c +++ b/src/backend/optimizer/util/relnode.c @@ -2480,6 +2480,18 @@ have_partkey_equi_join(PlannerInfo *root, RelOptInfo *joinrel, return false; } +/* + * strip_relabel_decorations + * Remove any RelabelType decorations from "expr". + */ +static Expr * +strip_relabel_decorations(Expr *expr) +{ + while (expr && IsA(expr, RelabelType)) + expr = ((RelabelType *) expr)->arg; + return expr; +} + /* * match_expr_to_partition_keys * @@ -2501,9 +2513,14 @@ match_expr_to_partition_keys(Expr *expr, RelOptInfo *rel, bool strict_op) Assert(rel->partexprs); Assert(rel->nullable_partexprs); - /* Remove any relabel decorations. */ - while (IsA(expr, RelabelType)) - expr = (Expr *) (castNode(RelabelType, expr))->arg; + /* + * Remove any relabel decorations, from the clause expression here and + * from each partition key expression below. A key involving a + * binary-compatible cast is itself stored wrapped in a RelabelType. The + * collation is not lost, as the caller compares the clause's inputcollid + * against the partition collation. + */ + expr = strip_relabel_decorations(expr); for (cnt = 0; cnt < rel->part_scheme->partnatts; cnt++) { @@ -2512,7 +2529,7 @@ match_expr_to_partition_keys(Expr *expr, RelOptInfo *rel, bool strict_op) /* We can always match to the non-nullable partition keys. */ foreach(lc, rel->partexprs[cnt]) { - if (equal(lfirst(lc), expr)) + if (equal(strip_relabel_decorations(lfirst(lc)), expr)) return cnt; } @@ -2528,7 +2545,7 @@ match_expr_to_partition_keys(Expr *expr, RelOptInfo *rel, bool strict_op) */ foreach(lc, rel->nullable_partexprs[cnt]) { - if (equal(lfirst(lc), expr)) + if (equal(strip_relabel_decorations(lfirst(lc)), expr)) return cnt; } } diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out index 8a122a6cc24..dd25536f85f 100644 --- a/src/test/regress/expected/partition_join.out +++ b/src/test/regress/expected/partition_join.out @@ -1806,6 +1806,94 @@ SELECT count(*) FROM pht3 t1 JOIN pht4 t2 ON t1.d = t2.d WHERE t1.c = '0002' AND 40 (1 row) +RESET enable_hashjoin; +RESET enable_mergejoin; +-- outer join on a partition key that is an expression wrapped in a RelabelType +CREATE TABLE pht5 (a int, c varchar(40)) PARTITION BY HASH ((c::text)); +CREATE TABLE pht5_p1 PARTITION OF pht5 FOR VALUES WITH (MODULUS 2, REMAINDER 0); +CREATE TABLE pht5_p2 PARTITION OF pht5 FOR VALUES WITH (MODULUS 2, REMAINDER 1); +INSERT INTO pht5 SELECT i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 2) i; +-- a key with no match in pht6, so that the join produces a null-extended row +INSERT INTO pht5 VALUES (600, '9999'); +ANALYZE pht5; +CREATE TABLE pht6 (a int, c varchar(40)) PARTITION BY HASH ((c::text)); +CREATE TABLE pht6_p1 PARTITION OF pht6 FOR VALUES WITH (MODULUS 2, REMAINDER 0); +CREATE TABLE pht6_p2 PARTITION OF pht6 FOR VALUES WITH (MODULUS 2, REMAINDER 1); +INSERT INTO pht6 SELECT i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 3) i; +ANALYZE pht6; +CREATE TABLE pht7 (a int, c varchar(40)) PARTITION BY HASH ((c::text)); +CREATE TABLE pht7_p1 PARTITION OF pht7 FOR VALUES WITH (MODULUS 2, REMAINDER 0); +CREATE TABLE pht7_p2 PARTITION OF pht7 FOR VALUES WITH (MODULUS 2, REMAINDER 1); +INSERT INTO pht7 SELECT i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 5) i; +ANALYZE pht7; +-- avoid hash and merge joins, whose costs here are close enough to the +-- partitionwise nested loop's to make the test output unstable +SET enable_hashjoin = off; +SET enable_mergejoin = off; +SET max_parallel_workers_per_gather = 0; +-- the second count() shows that a null-extended row is produced +EXPLAIN (COSTS OFF) +SELECT count(*), count(t2.a) FROM pht5 t1 LEFT JOIN pht6 t2 ON t1.c::text = t2.c::text; + QUERY PLAN +-------------------------------------------------------------- + Aggregate + -> Append + -> Nested Loop Left Join + Join Filter: ((t1_1.c)::text = (t2_1.c)::text) + -> Seq Scan on pht5_p1 t1_1 + -> Materialize + -> Seq Scan on pht6_p1 t2_1 + -> Nested Loop Left Join + Join Filter: ((t1_2.c)::text = (t2_2.c)::text) + -> Seq Scan on pht5_p2 t1_2 + -> Materialize + -> Seq Scan on pht6_p2 t2_2 +(12 rows) + +SELECT count(*), count(t2.a) FROM pht5 t1 LEFT JOIN pht6 t2 ON t1.c::text = t2.c::text; + count | count +-------+------- + 5001 | 5000 +(1 row) + +-- Here the upper join clause references t2.c, which is a nullable partition +-- key expression of the (pht5, pht6) join relation, so matching it requires +-- ignoring relabeling on nullable_partexprs as well. +EXPLAIN (COSTS OFF) +SELECT count(*) FROM (pht5 t1 LEFT JOIN pht6 t2 ON t1.c::text = t2.c::text) + LEFT JOIN pht7 t3 ON t2.c::text = t3.c::text; + QUERY PLAN +-------------------------------------------------------------------------- + Aggregate + -> Append + -> Nested Loop Left Join + Join Filter: ((t1_1.c)::text = (t2_1.c)::text) + -> Seq Scan on pht5_p1 t1_1 + -> Materialize + -> Nested Loop Left Join + Join Filter: ((t2_1.c)::text = (t3_1.c)::text) + -> Seq Scan on pht6_p1 t2_1 + -> Materialize + -> Seq Scan on pht7_p1 t3_1 + -> Nested Loop Left Join + Join Filter: ((t1_2.c)::text = (t2_2.c)::text) + -> Seq Scan on pht5_p2 t1_2 + -> Materialize + -> Nested Loop Left Join + Join Filter: ((t2_2.c)::text = (t3_2.c)::text) + -> Seq Scan on pht6_p2 t2_2 + -> Materialize + -> Seq Scan on pht7_p2 t3_2 +(20 rows) + +SELECT count(*) FROM (pht5 t1 LEFT JOIN pht6 t2 ON t1.c::text = t2.c::text) + LEFT JOIN pht7 t3 ON t2.c::text = t3.c::text; + count +------- + 50001 +(1 row) + +RESET max_parallel_workers_per_gather; RESET enable_hashjoin; RESET enable_mergejoin; -- test default partition behavior for range diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql index 2a423f14190..34e7e1fdaca 100644 --- a/src/test/regress/sql/partition_join.sql +++ b/src/test/regress/sql/partition_join.sql @@ -387,6 +387,49 @@ SELECT count(*) FROM pht3 t1 JOIN pht4 t2 ON t1.d = t2.d WHERE t1.c = '0002' AND RESET enable_hashjoin; RESET enable_mergejoin; +-- outer join on a partition key that is an expression wrapped in a RelabelType +CREATE TABLE pht5 (a int, c varchar(40)) PARTITION BY HASH ((c::text)); +CREATE TABLE pht5_p1 PARTITION OF pht5 FOR VALUES WITH (MODULUS 2, REMAINDER 0); +CREATE TABLE pht5_p2 PARTITION OF pht5 FOR VALUES WITH (MODULUS 2, REMAINDER 1); +INSERT INTO pht5 SELECT i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 2) i; +-- a key with no match in pht6, so that the join produces a null-extended row +INSERT INTO pht5 VALUES (600, '9999'); +ANALYZE pht5; + +CREATE TABLE pht6 (a int, c varchar(40)) PARTITION BY HASH ((c::text)); +CREATE TABLE pht6_p1 PARTITION OF pht6 FOR VALUES WITH (MODULUS 2, REMAINDER 0); +CREATE TABLE pht6_p2 PARTITION OF pht6 FOR VALUES WITH (MODULUS 2, REMAINDER 1); +INSERT INTO pht6 SELECT i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 3) i; +ANALYZE pht6; + +CREATE TABLE pht7 (a int, c varchar(40)) PARTITION BY HASH ((c::text)); +CREATE TABLE pht7_p1 PARTITION OF pht7 FOR VALUES WITH (MODULUS 2, REMAINDER 0); +CREATE TABLE pht7_p2 PARTITION OF pht7 FOR VALUES WITH (MODULUS 2, REMAINDER 1); +INSERT INTO pht7 SELECT i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 5) i; +ANALYZE pht7; + +-- avoid hash and merge joins, whose costs here are close enough to the +-- partitionwise nested loop's to make the test output unstable +SET enable_hashjoin = off; +SET enable_mergejoin = off; +SET max_parallel_workers_per_gather = 0; +-- the second count() shows that a null-extended row is produced +EXPLAIN (COSTS OFF) +SELECT count(*), count(t2.a) FROM pht5 t1 LEFT JOIN pht6 t2 ON t1.c::text = t2.c::text; +SELECT count(*), count(t2.a) FROM pht5 t1 LEFT JOIN pht6 t2 ON t1.c::text = t2.c::text; + +-- Here the upper join clause references t2.c, which is a nullable partition +-- key expression of the (pht5, pht6) join relation, so matching it requires +-- ignoring relabeling on nullable_partexprs as well. +EXPLAIN (COSTS OFF) +SELECT count(*) FROM (pht5 t1 LEFT JOIN pht6 t2 ON t1.c::text = t2.c::text) + LEFT JOIN pht7 t3 ON t2.c::text = t3.c::text; +SELECT count(*) FROM (pht5 t1 LEFT JOIN pht6 t2 ON t1.c::text = t2.c::text) + LEFT JOIN pht7 t3 ON t2.c::text = t3.c::text; +RESET max_parallel_workers_per_gather; +RESET enable_hashjoin; +RESET enable_mergejoin; + -- test default partition behavior for range ALTER TABLE prt1 DETACH PARTITION prt1_p3; ALTER TABLE prt1 ATTACH PARTITION prt1_p3 DEFAULT; -- 2.47.3
From 4fc48a0d3377eec78394f3395ed910dd98249bd2 Mon Sep 17 00:00:00 2001 From: Matheus Alcantara <[email protected]> Date: Mon, 31 Aug 2026 10:49:50 -0300 Subject: [PATCH v9 4/4] Enable full partitionwise aggregate for partition keys wrapped by RelabelType group_by_has_partkey() decides whether a partitioned aggregate can be computed independently per partition (PARTITIONWISE_AGGREGATE_FULL) or whether partial aggregates have to be combined afterwards (PARTITIONWISE_AGGREGATE_PARTIAL). It does so by matching each partition key expression against the GROUP BY expressions with equal(). The grouping expressions have their RelabelType decorations stripped before that comparison, but the partition key expressions do not. When the partition key is an expression involving a binary-compatible cast, for example PARTITION BY LIST ((col::text)) on a varchar column, the stored partition key expression is itself a RelabelType, so no GROUP BY expression can ever match it: grouping by the column compares a bare Var against a RelabelType, and spelling out the cast strips the grouping side down to a bare Var while the partition key side stays wrapped. Full partitionwise aggregation is therefore never chosen for such a key, and the plan falls back to partial aggregation with a finalize step on top. Fix by stripping RelabelType from the partition key expression as well. Collation correctness is unaffected: the partition collation comes from the PartitionScheme rather than from the expression, and the grouping expression's collation is captured before it is stripped, so the existing comparison of the two is unchanged. When the partition key is stripped in the function group_by_has_partkey(), grouping employs the equality operator of the argument type, whereas partitioning uses the result type, and a binary-coercible cast is not required to preserve the equality: citext is binary-coercible to text but is compared in a case-insensitive manner. As a result, a group might span across partitions and yield an incorrect answer. Therefore, require the grouping clause's equality operator to be a member of the partitioning operator family, as have_partkey_equi_join() already does for the clause operator. Discussion: https://postgr.es/m/[email protected] --- src/backend/optimizer/plan/planner.c | 23 ++++- .../regress/expected/partition_aggregate.out | 99 +++++++++++++++++++ src/test/regress/sql/partition_aggregate.sql | 47 +++++++++ 3 files changed, 168 insertions(+), 1 deletion(-) diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index c3c158a253d..c7b0154a261 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -8531,12 +8531,26 @@ group_by_has_partkey(RelOptInfo *input_rel, foreach(lc, partexprs) { ListCell *lg; + ListCell *lgc; Expr *partexpr = lfirst(lc); Oid partcoll = input_rel->part_scheme->partcollation[cnt]; + Oid partopfamily = input_rel->part_scheme->partopfamily[cnt]; - foreach(lg, groupexprs) + /* + * Strip any RelabelType decorations, to match the stripping done + * on the grouping expressions below. A partition key involving a + * binary-compatible cast, such as ((col::text)) on a varchar + * column, is itself stored wrapped in a RelabelType. The + * collation is not lost, since partcoll and groupcoll are + * compared separately below. + */ + while (partexpr && IsA(partexpr, RelabelType)) + partexpr = ((RelabelType *) partexpr)->arg; + + forboth(lg, groupexprs, lgc, groupClause) { Expr *groupexpr = lfirst(lg); + SortGroupClause *sgc = lfirst_node(SortGroupClause, lgc); Oid groupcoll = exprCollation((Node *) groupexpr); /* @@ -8557,6 +8571,13 @@ group_by_has_partkey(RelOptInfo *input_rel, partcoll != groupcoll) return false; + /* + * Reject a match if the operator that grouping will use + * is not part of the partitioning operator family. + */ + if (!op_in_opfamily(sgc->eqop, partopfamily)) + return false; + found = true; break; } diff --git a/src/test/regress/expected/partition_aggregate.out b/src/test/regress/expected/partition_aggregate.out index c30304b99c7..562b25aadca 100644 --- a/src/test/regress/expected/partition_aggregate.out +++ b/src/test/regress/expected/partition_aggregate.out @@ -961,6 +961,105 @@ SELECT a, c, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY (a+b)/2, 2, 1 HAV 20 | 40 | 50 | 40.0000000000000000 | 5 (6 rows) +-- Partition by an expression that is a binary-compatible cast, so that the +-- stored partition key expression is itself wrapped in a RelabelType +CREATE TABLE pagg_tab_v (a int, c varchar(40)) PARTITION BY LIST ((c::text)); +CREATE TABLE pagg_tab_v_p1 PARTITION OF pagg_tab_v FOR VALUES IN ('0000', '0001'); +CREATE TABLE pagg_tab_v_p2 PARTITION OF pagg_tab_v FOR VALUES IN ('0002'); +INSERT INTO pagg_tab_v SELECT i, to_char(i % 3, 'FM0000') FROM generate_series(0, 2999) i; +ANALYZE pagg_tab_v; +-- Full aggregation as GROUP BY clause matches with PARTITION KEY +EXPLAIN (COSTS OFF) +SELECT c, sum(a), count(*) FROM pagg_tab_v GROUP BY c ORDER BY 1; + QUERY PLAN +---------------------------------------------------------- + Sort + Sort Key: pagg_tab_v.c + -> Append + -> HashAggregate + Group Key: pagg_tab_v.c + -> Seq Scan on pagg_tab_v_p1 pagg_tab_v + -> HashAggregate + Group Key: pagg_tab_v_1.c + -> Seq Scan on pagg_tab_v_p2 pagg_tab_v_1 +(9 rows) + +SELECT c, sum(a), count(*) FROM pagg_tab_v GROUP BY c ORDER BY 1; + c | sum | count +------+---------+------- + 0000 | 1498500 | 1000 + 0001 | 1499500 | 1000 + 0002 | 1500500 | 1000 +(3 rows) + +-- Full aggregation also when the GROUP BY clause spells out the cast +EXPLAIN (COSTS OFF) +SELECT c::text, sum(a), count(*) FROM pagg_tab_v GROUP BY c::text ORDER BY 1; + QUERY PLAN +---------------------------------------------------------- + Sort + Sort Key: ((pagg_tab_v.c)::text) + -> Append + -> HashAggregate + Group Key: (pagg_tab_v.c)::text + -> Seq Scan on pagg_tab_v_p1 pagg_tab_v + -> HashAggregate + Group Key: (pagg_tab_v_1.c)::text + -> Seq Scan on pagg_tab_v_p2 pagg_tab_v_1 +(9 rows) + +SELECT c::text, sum(a), count(*) FROM pagg_tab_v GROUP BY c::text ORDER BY 1; + c | sum | count +------+---------+------- + 0000 | 1498500 | 1000 + 0001 | 1499500 | 1000 + 0002 | 1500500 | 1000 +(3 rows) + +-- A binary-compatible cast need not preserve equality semantics, so matching +-- the stripped partition key is not enough on its own. Build a type that is +-- binary-coercible to text but compares case insensitively. +CREATE TYPE pagg_ci; +CREATE FUNCTION pagg_ci_in(cstring) RETURNS pagg_ci STRICT IMMUTABLE LANGUAGE internal AS 'textin'; +NOTICE: return type pagg_ci is only a shell +CREATE FUNCTION pagg_ci_out(pagg_ci) RETURNS cstring STRICT IMMUTABLE LANGUAGE internal AS 'textout'; +NOTICE: argument type pagg_ci is only a shell +LINE 1: CREATE FUNCTION pagg_ci_out(pagg_ci) RETURNS cstring STRICT ... + ^ +CREATE TYPE pagg_ci (input = pagg_ci_in, output = pagg_ci_out, like = text); +CREATE CAST (pagg_ci AS text) WITHOUT FUNCTION; +CREATE FUNCTION pagg_ci_eq(pagg_ci, pagg_ci) RETURNS bool + STRICT IMMUTABLE LANGUAGE sql AS $$SELECT lower($1::text) = lower($2::text)$$; +CREATE OPERATOR = (leftarg = pagg_ci, rightarg = pagg_ci, procedure = pagg_ci_eq); +CREATE FUNCTION pagg_ci_hash(pagg_ci) RETURNS int4 STRICT IMMUTABLE LANGUAGE sql AS $$SELECT hashtext(lower($1::text))$$; +CREATE OPERATOR CLASS pagg_ci_ops DEFAULT FOR TYPE pagg_ci USING hash AS OPERATOR 1 =, FUNCTION 1 pagg_ci_hash(pagg_ci); +CREATE TABLE pagg_tab_ci (a int, c pagg_ci) PARTITION BY LIST ((c::text) COLLATE "C"); +CREATE TABLE pagg_tab_ci_p1 PARTITION OF pagg_tab_ci FOR VALUES IN ('A'); +CREATE TABLE pagg_tab_ci_p2 PARTITION OF pagg_tab_ci FOR VALUES IN ('a'); +INSERT INTO pagg_tab_ci SELECT i, (CASE WHEN i % 3 = 0 THEN 'A' ELSE 'a' END)::pagg_ci FROM generate_series(1, 3000) i; +ANALYZE pagg_tab_ci; +-- Partial aggregation only +EXPLAIN (COSTS OFF) +SELECT c, count(*) FROM pagg_tab_ci GROUP BY c; + QUERY PLAN +------------------------------------------------------------ + Finalize HashAggregate + Group Key: pagg_tab_ci.c + -> Append + -> Partial HashAggregate + Group Key: pagg_tab_ci.c + -> Seq Scan on pagg_tab_ci_p1 pagg_tab_ci + -> Partial HashAggregate + Group Key: pagg_tab_ci_1.c + -> Seq Scan on pagg_tab_ci_p2 pagg_tab_ci_1 +(9 rows) + +SELECT c, count(*) FROM pagg_tab_ci GROUP BY c; + c | count +---+------- + A | 3000 +(1 row) + -- Test with multi-level partitioning scheme CREATE TABLE pagg_tab_ml (a int, b int, c text) PARTITION BY RANGE(a); CREATE TABLE pagg_tab_ml_p1 PARTITION OF pagg_tab_ml FOR VALUES FROM (0) TO (12); diff --git a/src/test/regress/sql/partition_aggregate.sql b/src/test/regress/sql/partition_aggregate.sql index 7c725e2663a..4dfea086fd5 100644 --- a/src/test/regress/sql/partition_aggregate.sql +++ b/src/test/regress/sql/partition_aggregate.sql @@ -208,6 +208,53 @@ SELECT a, c, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY (a+b)/2, 2, 1 HAV SELECT a, c, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY (a+b)/2, 2, 1 HAVING sum(b) = 50 AND avg(c) > 25 ORDER BY 1, 2, 3; +-- Partition by an expression that is a binary-compatible cast, so that the +-- stored partition key expression is itself wrapped in a RelabelType + +CREATE TABLE pagg_tab_v (a int, c varchar(40)) PARTITION BY LIST ((c::text)); +CREATE TABLE pagg_tab_v_p1 PARTITION OF pagg_tab_v FOR VALUES IN ('0000', '0001'); +CREATE TABLE pagg_tab_v_p2 PARTITION OF pagg_tab_v FOR VALUES IN ('0002'); +INSERT INTO pagg_tab_v SELECT i, to_char(i % 3, 'FM0000') FROM generate_series(0, 2999) i; +ANALYZE pagg_tab_v; + +-- Full aggregation as GROUP BY clause matches with PARTITION KEY +EXPLAIN (COSTS OFF) +SELECT c, sum(a), count(*) FROM pagg_tab_v GROUP BY c ORDER BY 1; +SELECT c, sum(a), count(*) FROM pagg_tab_v GROUP BY c ORDER BY 1; + +-- Full aggregation also when the GROUP BY clause spells out the cast +EXPLAIN (COSTS OFF) +SELECT c::text, sum(a), count(*) FROM pagg_tab_v GROUP BY c::text ORDER BY 1; +SELECT c::text, sum(a), count(*) FROM pagg_tab_v GROUP BY c::text ORDER BY 1; + +-- A binary-compatible cast need not preserve equality semantics, so matching +-- the stripped partition key is not enough on its own. Build a type that is +-- binary-coercible to text but compares case insensitively. + +CREATE TYPE pagg_ci; +CREATE FUNCTION pagg_ci_in(cstring) RETURNS pagg_ci STRICT IMMUTABLE LANGUAGE internal AS 'textin'; +CREATE FUNCTION pagg_ci_out(pagg_ci) RETURNS cstring STRICT IMMUTABLE LANGUAGE internal AS 'textout'; +CREATE TYPE pagg_ci (input = pagg_ci_in, output = pagg_ci_out, like = text); +CREATE CAST (pagg_ci AS text) WITHOUT FUNCTION; + +CREATE FUNCTION pagg_ci_eq(pagg_ci, pagg_ci) RETURNS bool + STRICT IMMUTABLE LANGUAGE sql AS $$SELECT lower($1::text) = lower($2::text)$$; +CREATE OPERATOR = (leftarg = pagg_ci, rightarg = pagg_ci, procedure = pagg_ci_eq); +CREATE FUNCTION pagg_ci_hash(pagg_ci) RETURNS int4 STRICT IMMUTABLE LANGUAGE sql AS $$SELECT hashtext(lower($1::text))$$; +CREATE OPERATOR CLASS pagg_ci_ops DEFAULT FOR TYPE pagg_ci USING hash AS OPERATOR 1 =, FUNCTION 1 pagg_ci_hash(pagg_ci); + +CREATE TABLE pagg_tab_ci (a int, c pagg_ci) PARTITION BY LIST ((c::text) COLLATE "C"); +CREATE TABLE pagg_tab_ci_p1 PARTITION OF pagg_tab_ci FOR VALUES IN ('A'); +CREATE TABLE pagg_tab_ci_p2 PARTITION OF pagg_tab_ci FOR VALUES IN ('a'); +INSERT INTO pagg_tab_ci SELECT i, (CASE WHEN i % 3 = 0 THEN 'A' ELSE 'a' END)::pagg_ci FROM generate_series(1, 3000) i; +ANALYZE pagg_tab_ci; + +-- Partial aggregation only +EXPLAIN (COSTS OFF) +SELECT c, count(*) FROM pagg_tab_ci GROUP BY c; +SELECT c, count(*) FROM pagg_tab_ci GROUP BY c; + + -- Test with multi-level partitioning scheme CREATE TABLE pagg_tab_ml (a int, b int, c text) PARTITION BY RANGE(a); -- 2.47.3
