postgres.git / summary / log / commit / refs
commit 2a55a15d6b7c1374716df427e701c46c009fbe2d
Author: Robert Haas <rhaas@postgresql.org>
Date: Thu Oct 29 15:35:49 2026 +0000
First released in: 19.0
pg_plan_advice: Mark PARTITIONWISE inapplicable on plain rels.
Previously, such advice was accepted, and just had the effect of disabling
every path for the target relation, which isn't the worst imaginable
behavior, but it's not difficult to do better. This commit causes such
advice to instead be marked inapplicable, like INDEX_SCAN(some_table
NO_SUCH_INDEX).
One advantage of this change is that it improves the test coverage
provided by test_plan_advice. In fact, prior to commit
0f7dfd7936b9e99f9aeca00f52ab1692898ffff4, these changes caused
test_plan_advice failures, because the defect fixed by that commit
caused PARTITIONWISE advice to target the wrong relations. Without
this change, the advice feedback doesn't show any sign that there's
a problem, which is how that defect escaped discovery.
Reported-by: Nikolay Samokhvalov <nik@postgres.ai>
Discussion: http://postgr.es/m/CA+Tgmob3X+_CSjAa_BtU9bXrX_ge_HyvO=7gSCLtBpncGwn-kQ@mail.gmail.com
Tested-by: Jakub Wartak <jakub.wartak@enterprisedb.com>
Backpatch-through: 19
contrib/pg_plan_advice/expected/partitionwise.out | 49 +++++++++++++++++++++++
contrib/pg_plan_advice/pgpa_planner.c | 42 ++++++++++++++++++-
contrib/pg_plan_advice/sql/partitionwise.sql | 10 +++++
3 files changed, 99 insertions(+), 2 deletions(-)
diff --git a/contrib/pg_plan_advice/expected/partitionwise.out b/contrib/pg_plan_advice/expected/partitionwise.out
index 3a3f87007e4..3df13fb1794 100644
--- a/contrib/pg_plan_advice/expected/partitionwise.out
+++ b/contrib/pg_plan_advice/expected/partitionwise.out
@@ -386,6 +386,55 @@ SELECT * FROM pt1, ptmismatch WHERE pt1.id = ptmismatch.id;
ptmismatch/public.ptmismatcha ptmismatch/public.ptmismatchb)
(22 rows)
+COMMIT;
+-- PARTITIONWISE is only applicable to inheritance parents.
+BEGIN;
+SET LOCAL pg_plan_advice.advice = 'PARTITIONWISE(pt3a)';
+EXPLAIN (PLAN_ADVICE, COSTS OFF)
+SELECT * FROM pt1, pt3a WHERE pt1.id = pt3a.id;
+ QUERY PLAN
+--------------------------------------------------------------------------------
+ Merge Join
+ Merge Cond: (pt1.id = pt3a.id)
+ -> Append
+ -> Index Scan using pt1a_pkey on pt1a pt1_1
+ -> Index Scan using pt1b_pkey on pt1b pt1_2
+ -> Index Scan using pt1c_pkey on pt1c pt1_3
+ -> Index Scan using pt3a_pkey on pt3a
+ Supplied Plan Advice:
+ PARTITIONWISE(pt3a) /* matched, inapplicable, failed */
+ Generated Plan Advice:
+ JOIN_ORDER(pt1 pt3a)
+ MERGE_JOIN_PLAIN(pt3a)
+ INDEX_SCAN(pt1/public.pt1a public.pt1a_pkey pt1/public.pt1b public.pt1b_pkey
+ pt1/public.pt1c public.pt1c_pkey pt3a public.pt3a_pkey)
+ PARTITIONWISE(pt1)
+ NO_GATHER(pt1 pt3a pt1/public.pt1a pt1/public.pt1b pt1/public.pt1c)
+(16 rows)
+
+SET LOCAL pg_plan_advice.advice = 'PARTITIONWISE((pt1 pt3a))';
+EXPLAIN (PLAN_ADVICE, COSTS OFF)
+SELECT * FROM pt1, pt3a WHERE pt1.id = pt3a.id;
+ QUERY PLAN
+--------------------------------------------------------------------------------
+ Merge Join
+ Merge Cond: (pt1.id = pt3a.id)
+ -> Append
+ -> Index Scan using pt1a_pkey on pt1a pt1_1
+ -> Index Scan using pt1b_pkey on pt1b pt1_2
+ -> Index Scan using pt1c_pkey on pt1c pt1_3
+ -> Index Scan using pt3a_pkey on pt3a
+ Supplied Plan Advice:
+ PARTITIONWISE((pt1 pt3a)) /* matched, inapplicable, failed */
+ Generated Plan Advice:
+ JOIN_ORDER(pt1 pt3a)
+ MERGE_JOIN_PLAIN(pt3a)
+ INDEX_SCAN(pt1/public.pt1a public.pt1a_pkey pt1/public.pt1b public.pt1b_pkey
+ pt1/public.pt1c public.pt1c_pkey pt3a public.pt3a_pkey)
+ PARTITIONWISE(pt1)
+ NO_GATHER(pt1 pt3a pt1/public.pt1a pt1/public.pt1b pt1/public.pt1c)
+(16 rows)
+
COMMIT;
-- Force join order for a particular branch of the partitionwise join.
BEGIN;
diff --git a/contrib/pg_plan_advice/pgpa_planner.c b/contrib/pg_plan_advice/pgpa_planner.c
index 6551037e796..02374ff703d 100644
--- a/contrib/pg_plan_advice/pgpa_planner.c
+++ b/contrib/pg_plan_advice/pgpa_planner.c
@@ -144,6 +144,7 @@ static void pgpa_planner_apply_join_path_advice(JoinType jointype,
char *plan_name,
pgpa_join_state *pjs);
static void pgpa_planner_apply_scan_advice(RelOptInfo *rel,
+ RangeTblEntry *rte,
pgpa_trove_entry *scan_entries,
Bitmapset *scan_indexes,
pgpa_trove_entry *rel_entries,
@@ -453,6 +454,7 @@ pgpa_build_simple_rel(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
uint64 original_mask = rel->pgs_mask;
pgpa_planner_apply_scan_advice(rel,
+ rte,
tresult_scan.entries,
tresult_scan.indexes,
tresult_rel.entries,
@@ -823,6 +825,23 @@ pgpa_planner_apply_joinrel_advice(uint64 *pgs_mask_p, char *plan_name,
entry->target);
Assert(itm != PGPA_ITM_DISJOINT);
+ /*
+ * If this entry has previously been marked inapplicable, don't
+ * enforce it. However, if appropriate, we still mark it fully
+ * matched.
+ */
+ if ((entry->flags & PGPA_FB_INAPPLICABLE) != 0)
+ {
+ if (itm == PGPA_ITM_EQUAL)
+ entry->flags |= PGPA_FB_MATCH_FULL;
+ continue;
+ }
+
+ /*
+ * Otherwise, if the specified targets exactly match this
+ * joinrel, arrange to enforce it here; see comments below for
+ * details on what we do when there is not an exact match.
+ */
if (itm == PGPA_ITM_EQUAL)
{
full_match = true;
@@ -1012,7 +1031,14 @@ pgpa_planner_apply_join_path_advice(JoinType jointype, uint64 *pgs_mask_p,
pgpa_itm_type inner_itm;
pgpa_itm_type outer_itm;
- if (entry->tag != PGPA_TAG_PARTITIONWISE)
+ /*
+ * This loop only considers PARTITIONWISE advice, and disregards any
+ * that has previously been marked inapplicable. Note that any
+ * inapplicable advice will already be marked fully matched by this
+ * point, so we don't need to worry about that here.
+ */
+ if (entry->tag != PGPA_TAG_PARTITIONWISE ||
+ (entry->flags & PGPA_FB_INAPPLICABLE) != 0)
continue;
outer_itm = pgpa_identifiers_match_target(pjs->outer_count,
@@ -1679,6 +1705,7 @@ pgpa_semijoin_permits_join(int outer_count, int inner_count,
*/
static void
pgpa_planner_apply_scan_advice(RelOptInfo *rel,
+ RangeTblEntry *rte,
pgpa_trove_entry *scan_entries,
Bitmapset *scan_indexes,
pgpa_trove_entry *rel_entries,
@@ -1781,7 +1808,18 @@ pgpa_planner_apply_scan_advice(RelOptInfo *rel,
*/
if (my_entry->tag == PGPA_TAG_PARTITIONWISE)
{
- if (just_one_rel)
+ if (!rte->inh)
+ {
+ /*
+ * Can't apply PARTITIONWISE advice to something that is not
+ * an inheritance parent.
+ */
+ my_entry->flags |=
+ PGPA_FB_MATCH_PARTIAL | PGPA_FB_INAPPLICABLE;
+ if (just_one_rel)
+ my_entry->flags |= PGPA_FB_MATCH_FULL;
+ }
+ else if (just_one_rel)
{
const uint64 my_scan_type = PGS_APPEND | PGS_MERGE_APPEND;
diff --git a/contrib/pg_plan_advice/sql/partitionwise.sql b/contrib/pg_plan_advice/sql/partitionwise.sql
index b295cf27c83..dc8e8808294 100644
--- a/contrib/pg_plan_advice/sql/partitionwise.sql
+++ b/contrib/pg_plan_advice/sql/partitionwise.sql
@@ -112,6 +112,16 @@ EXPLAIN (PLAN_ADVICE, COSTS OFF)
SELECT * FROM pt1, ptmismatch WHERE pt1.id = ptmismatch.id;
COMMIT;
+-- PARTITIONWISE is only applicable to inheritance parents.
+BEGIN;
+SET LOCAL pg_plan_advice.advice = 'PARTITIONWISE(pt3a)';
+EXPLAIN (PLAN_ADVICE, COSTS OFF)
+SELECT * FROM pt1, pt3a WHERE pt1.id = pt3a.id;
+SET LOCAL pg_plan_advice.advice = 'PARTITIONWISE((pt1 pt3a))';
+EXPLAIN (PLAN_ADVICE, COSTS OFF)
+SELECT * FROM pt1, pt3a WHERE pt1.id = pt3a.id;
+COMMIT;
+
-- Force join order for a particular branch of the partitionwise join.
BEGIN;
SET LOCAL pg_plan_advice.advice = 'JOIN_ORDER(pt3/public.pt3a pt2/public.pt2a pt1/public.pt1a)';
[parent: a054b77e40d0]