On Mon, Dec 15, 2025 at 12:06 PM Robert Haas <[email protected]> wrote:
> - Add gather((d d/d.d)) test case, per Jacob, and fix the related bug
> in pgpa_identifier_matches_target, per Jacob's email.
I think this fix affected the ability to omit the partition schema in
advice strings. Attached is a quick test that shows it on my machine,
plus an attempted fix that mashes together the v6 and v7 approaches.
(I have diffs in my generated plan advice compared to what's in
v7-0005, so I'm not sure if my .out file is correct.)
--Jacob
diff --git a/contrib/pg_plan_advice/expected/partitionwise.out
b/contrib/pg_plan_advice/expected/partitionwise.out
index df0f05531d5..4c3d73c21f5 100644
--- a/contrib/pg_plan_advice/expected/partitionwise.out
+++ b/contrib/pg_plan_advice/expected/partitionwise.out
@@ -103,6 +103,124 @@ SELECT * FROM pt1, pt2, pt3 WHERE pt1.id = pt2.id AND
pt2.id = pt3.id
pt2/public.pt2b pt2/public.pt2c pt3/public.pt3a pt3/public.pt3b
pt3/public.pt3c)
(47 rows)
+-- Test partition matching.
+-- FIXME doesn't belong in partitionwise tests
+BEGIN;
+SET LOCAL pg_plan_advice.advice = 'JOIN_ORDER(pt2/public.pt2a pt3/public.pt3a
pt1/public.pt1a)';
+EXPLAIN (PLAN_ADVICE, COSTS OFF)
+SELECT * FROM pt1, pt2, pt3 WHERE pt1.id = pt2.id AND pt2.id = pt3.id
+ AND val1 = 1 AND val2 = 1 AND val3 = 1;
+ QUERY PLAN
+-------------------------------------------------------------------------------------
+ Append
+ -> Nested Loop
+ -> Hash Join
+ Hash Cond: (pt2_1.id = pt3_1.id)
+ -> Seq Scan on pt2a pt2_1
+ Filter: (val2 = 1)
+ -> Hash
+ -> Seq Scan on pt3a pt3_1
+ Filter: (val3 = 1)
+ -> Index Scan using pt1a_pkey on pt1a pt1_1
+ Index Cond: (id = pt2_1.id)
+ Filter: (val1 = 1)
+ -> Nested Loop
+ -> Hash Join
+ Hash Cond: (pt3_2.id = pt2_2.id)
+ -> Seq Scan on pt3b pt3_2
+ Filter: (val3 = 1)
+ -> Hash
+ -> Seq Scan on pt2b pt2_2
+ Filter: (val2 = 1)
+ -> Index Scan using pt1b_pkey on pt1b pt1_2
+ Index Cond: (id = pt2_2.id)
+ Filter: (val1 = 1)
+ -> Nested Loop
+ -> Hash Join
+ Hash Cond: (pt3_3.id = pt2_3.id)
+ -> Seq Scan on pt3c pt3_3
+ Filter: (val3 = 1)
+ -> Hash
+ -> Seq Scan on pt2c pt2_3
+ Filter: (val2 = 1)
+ -> Index Scan using pt1c_pkey on pt1c pt1_3
+ Index Cond: (id = pt2_3.id)
+ Filter: (val1 = 1)
+ Supplied Plan Advice:
+ JOIN_ORDER(pt2/public.pt2a pt3/public.pt3a pt1/public.pt1a) /* matched */
+ Generated Plan Advice:
+ JOIN_ORDER(pt2/public.pt2a pt3/public.pt3a pt1/public.pt1a)
+ JOIN_ORDER(pt3/public.pt3b pt2/public.pt2b pt1/public.pt1b)
+ JOIN_ORDER(pt3/public.pt3c pt2/public.pt2c pt1/public.pt1c)
+ NESTED_LOOP_PLAIN(pt1/public.pt1a pt1/public.pt1b pt1/public.pt1c)
+ HASH_JOIN(pt3/public.pt3a pt2/public.pt2b pt2/public.pt2c)
+ SEQ_SCAN(pt2/public.pt2a pt3/public.pt3a pt3/public.pt3b pt2/public.pt2b
+ pt3/public.pt3c pt2/public.pt2c)
+ INDEX_SCAN(pt1/public.pt1a public.pt1a_pkey pt1/public.pt1b public.pt1b_pkey
+ pt1/public.pt1c public.pt1c_pkey)
+ PARTITIONWISE((pt1 pt2 pt3))
+ NO_GATHER(pt1/public.pt1a pt1/public.pt1b pt1/public.pt1c pt2/public.pt2a
+ pt2/public.pt2b pt2/public.pt2c pt3/public.pt3a pt3/public.pt3b
pt3/public.pt3c)
+(49 rows)
+
+SET LOCAL pg_plan_advice.advice = 'JOIN_ORDER(pt2/pt2a pt3/pt3a pt1/pt1a)';
+EXPLAIN (PLAN_ADVICE, COSTS OFF)
+SELECT * FROM pt1, pt2, pt3 WHERE pt1.id = pt2.id AND pt2.id = pt3.id
+ AND val1 = 1 AND val2 = 1 AND val3 = 1;
+ QUERY PLAN
+-------------------------------------------------------------------------------------
+ Append
+ -> Nested Loop
+ -> Hash Join
+ Hash Cond: (pt2_1.id = pt3_1.id)
+ -> Seq Scan on pt2a pt2_1
+ Filter: (val2 = 1)
+ -> Hash
+ -> Seq Scan on pt3a pt3_1
+ Filter: (val3 = 1)
+ -> Index Scan using pt1a_pkey on pt1a pt1_1
+ Index Cond: (id = pt2_1.id)
+ Filter: (val1 = 1)
+ -> Nested Loop
+ -> Hash Join
+ Hash Cond: (pt3_2.id = pt2_2.id)
+ -> Seq Scan on pt3b pt3_2
+ Filter: (val3 = 1)
+ -> Hash
+ -> Seq Scan on pt2b pt2_2
+ Filter: (val2 = 1)
+ -> Index Scan using pt1b_pkey on pt1b pt1_2
+ Index Cond: (id = pt2_2.id)
+ Filter: (val1 = 1)
+ -> Nested Loop
+ -> Hash Join
+ Hash Cond: (pt3_3.id = pt2_3.id)
+ -> Seq Scan on pt3c pt3_3
+ Filter: (val3 = 1)
+ -> Hash
+ -> Seq Scan on pt2c pt2_3
+ Filter: (val2 = 1)
+ -> Index Scan using pt1c_pkey on pt1c pt1_3
+ Index Cond: (id = pt2_3.id)
+ Filter: (val1 = 1)
+ Supplied Plan Advice:
+ JOIN_ORDER(pt2/pt2a pt3/pt3a pt1/pt1a) /* matched */
+ Generated Plan Advice:
+ JOIN_ORDER(pt2/public.pt2a pt3/public.pt3a pt1/public.pt1a)
+ JOIN_ORDER(pt3/public.pt3b pt2/public.pt2b pt1/public.pt1b)
+ JOIN_ORDER(pt3/public.pt3c pt2/public.pt2c pt1/public.pt1c)
+ NESTED_LOOP_PLAIN(pt1/public.pt1a pt1/public.pt1b pt1/public.pt1c)
+ HASH_JOIN(pt3/public.pt3a pt2/public.pt2b pt2/public.pt2c)
+ SEQ_SCAN(pt2/public.pt2a pt3/public.pt3a pt3/public.pt3b pt2/public.pt2b
+ pt3/public.pt3c pt2/public.pt2c)
+ INDEX_SCAN(pt1/public.pt1a public.pt1a_pkey pt1/public.pt1b public.pt1b_pkey
+ pt1/public.pt1c public.pt1c_pkey)
+ PARTITIONWISE((pt1 pt2 pt3))
+ NO_GATHER(pt1/public.pt1a pt1/public.pt1b pt1/public.pt1c pt2/public.pt2a
+ pt2/public.pt2b pt2/public.pt2c pt3/public.pt3a pt3/public.pt3b
pt3/public.pt3c)
+(49 rows)
+
+COMMIT;
-- Suppress partitionwise join, or do it just partially.
BEGIN;
SET LOCAL pg_plan_advice.advice = 'PARTITIONWISE(pt1 pt2 pt3)';
diff --git a/contrib/pg_plan_advice/pgpa_ast.c
b/contrib/pg_plan_advice/pgpa_ast.c
index 5f822bce036..b9478ba2abb 100644
--- a/contrib/pg_plan_advice/pgpa_ast.c
+++ b/contrib/pg_plan_advice/pgpa_ast.c
@@ -282,24 +282,26 @@ pgpa_identifier_matches_target(pgpa_identifier *rid,
pgpa_advice_target *target)
return false;
}
- /*
- * If a relation identifer mentions a partition name, it should also
specify
- * a partition schema.
- */
- Assert(rid->partnsp != NULL || rid->partrel == NULL);
-
/* Straightforward comparisons of alias name and occcurrence number. */
if (strcmp(rid->alias_name, target->rid.alias_name) != 0)
return false;
if (rid->occurrence != target->rid.occurrence)
return false;
+ /*
+ * If a relation identifer mentions a partition name, it should also
specify
+ * a partition schema. But the target may leave the schema NULL to match
+ * anything.
+ */
+ Assert(rid->partnsp != NULL || rid->partrel == NULL);
+ if (rid->partnsp != NULL && target->rid.partnsp != NULL &&
+ strcmp(rid->partnsp, target->rid.partnsp) != 0)
+ return false;
+
/*
* These fields can be NULL on either side, but NULL only matches
another
* NULL.
*/
- if (!strings_equal_or_both_null(rid->partnsp, target->rid.partnsp))
- return false;
if (!strings_equal_or_both_null(rid->partrel, target->rid.partrel))
return false;
if (!strings_equal_or_both_null(rid->plan_name, target->rid.plan_name))
diff --git a/contrib/pg_plan_advice/sql/partitionwise.sql
b/contrib/pg_plan_advice/sql/partitionwise.sql
index e42c0611760..52d971102f9 100644
--- a/contrib/pg_plan_advice/sql/partitionwise.sql
+++ b/contrib/pg_plan_advice/sql/partitionwise.sql
@@ -58,6 +58,20 @@ EXPLAIN (PLAN_ADVICE, COSTS OFF)
SELECT * FROM pt1, pt2, pt3 WHERE pt1.id = pt2.id AND pt2.id = pt3.id
AND val1 = 1 AND val2 = 1 AND val3 = 1;
+-- Test partition matching.
+-- FIXME doesn't belong in partitionwise tests
+BEGIN;
+SET LOCAL pg_plan_advice.advice = 'JOIN_ORDER(pt2/public.pt2a pt3/public.pt3a
pt1/public.pt1a)';
+EXPLAIN (PLAN_ADVICE, COSTS OFF)
+SELECT * FROM pt1, pt2, pt3 WHERE pt1.id = pt2.id AND pt2.id = pt3.id
+ AND val1 = 1 AND val2 = 1 AND val3 = 1;
+
+SET LOCAL pg_plan_advice.advice = 'JOIN_ORDER(pt2/pt2a pt3/pt3a pt1/pt1a)';
+EXPLAIN (PLAN_ADVICE, COSTS OFF)
+SELECT * FROM pt1, pt2, pt3 WHERE pt1.id = pt2.id AND pt2.id = pt3.id
+ AND val1 = 1 AND val2 = 1 AND val3 = 1;
+COMMIT;
+
-- Suppress partitionwise join, or do it just partially.
BEGIN;
SET LOCAL pg_plan_advice.advice = 'PARTITIONWISE(pt1 pt2 pt3)';
