Hello Matheus,
Thanks for the updated (v7) set of these patches. The set applies
cleanly to master and check-world runs without any failures.
On 31.08.26 20:07, Matheus Alcantara wrote:
>
> Your 0003 patch looks correct to me, I've just made two additions to its
> test:
>
> 1. The fix strips both rel->partexprs and rel->nullable_partexprs, but a
> two-way LEFT JOIN only ever consults the non-nullable list, so half the
> change had no coverage. I added a three-way case:
>
> 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;
Patches 1-3
===========
Your analysis regarding distribute_qual_to_rels() / maybe_equivalence
sounds reasonable to me. Thanks as well for adding more test coverage to
patch 3. I verified the nullable_partexprs point by reverting only that
branch of the strip. The two-way case still passes, while the three-way
case goes fully non-partitionwise. So, the new test covers the half that
mine missed.
I also looked for other places in the same family that might still be
missing the stripping (e.g., RIGHT, SEMI, and ANTI joins), and didn't
find any. So patches 1 to 3 look good to me from my side.
Patch 4
=======
> While checking whether match_expr_to_partition_keys(), I found one more
> case, and I've fixed on 0004. group_by_has_partkey() strips RelabelType
> from the grouping expressions but not from the partition key expression.
> The effect is that full partitionwise aggregation is never chosen for a
> partition key involving a binary-compatible cast, and the plan falls
> back to partial aggregation with a finalize step. Both spellings fail,
> for slightly different reasons: GROUP BY c compares a bare Var against a
> RelabelType, and GROUP BY c::text strips the grouping side to a bare Var
> while the partition key side stays wrapped.
I think I found a problem in patch 4. group_by_has_partkey() has no
opfamily check, so stripping the RelabelType from the partition key side
means the grouping uses the argument type's equality. In contrast, the
partitioning uses the result type's. If the argument type has coarser
equality than the target, a group can span partitions, and full
partitionwise aggregation produces the wrong result.
For example:
CREATE EXTENSION citext;
CREATE TABLE ct (a int, c citext) PARTITION BY LIST ((c::text));
CREATE TABLE ct_1 PARTITION OF ct FOR VALUES IN ('A');
CREATE TABLE ct_2 PARTITION OF ct FOR VALUES IN ('a');
INSERT INTO ct SELECT i, (CASE WHEN i%2=0 THEN 'A' ELSE 'a' END)::citext
FROM generate_series(1,2000) i;
ANALYZE ct;
SET enable_partitionwise_aggregate = on;
SELECT c, count(*) FROM ct GROUP BY c;
c | count
---+-------
A | 1000
a | 1000
(2 rows)
SET enable_partitionwise_aggregate = off;
SELECT c, count(*) FROM ct GROUP BY c;
c | count
---+-------
A | 2000
(1 row)
The partitions split 'A' and 'a' by text equality, while the grouping
merges them by citext equality. I confirmed that patch 4 introduces
this. Reverting only the planner.c change gives the correct result.
So, I think we need to add an opfamily check here.
Version 8
=========
I attached a new version of the patch series. It contains patch 3 with
an improved commit message. When I wrote the old one, I thought we would
merge it. But if it stays separate, it deserves a better message. Apart
from the commit message, patches 1 to 3 are unchanged.
In patch 4, I added an op_in_opfamily() check in group_by_has_partkey().
This check requires the grouping clause's equality operator to be a
member of the partitioning operator family, which mirrors what
have_partkey_equi_join() already does for the clause operator. The
problem I described above is fixed with that change.
What do you think?
Best regards
Jan
--
Jan Nidzwetzki
PlanetScale Postgres Core Team
From 78008bcb49e90fe7398e0dd38c52306acaeabe66 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email protected]>
Date: Wed, 26 Aug 2026 15:45:56 -0300
Subject: [PATCH v8 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 ad8ad27559d107cfdc7a8a39fc22252c7d7a1bbc Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email protected]>
Date: Wed, 26 Aug 2026 15:49:42 -0300
Subject: [PATCH v8 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 04072ead8317e10bc90d469145040d9e4c9a16a8 Mon Sep 17 00:00:00 2001
From: Jan Nidzwetzki <[email protected]>
Date: Fri, 28 Aug 2026 15:03:59 +0200
Subject: [PATCH v8 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 cc8f88adf08a93f8d2cc07695569345bee16dae8 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email protected]>
Date: Mon, 31 Aug 2026 10:49:50 -0300
Subject: [PATCH v8 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..4ce1154466b 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));
+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..8befdca71fc 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));
+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
