partnsp.diff.txt
text/plain
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)';