On Wed, Sep 9, 2026 at 4:16 PM Robert Haas <[email protected]> wrote:
> Generally, all of these problems stem from advice
> enforcement (which tries to make the plan obey the advice) being out
> of step with advice feedback (which says whether the plan actually did
> obey the advice).
AI found another case and prepared the attached patch. A single-target
PARTITIONWISE entry on a plain table disables every scan path,
although the advice only says that the table must not participate in a
partitionwise join.
load 'pg_plan_advice';
create table plain (i int);
set pg_plan_advice.advice = 'PARTITIONWISE(plain)';
explain (costs off, plan_advice) select * from plain;
This produces:
Seq Scan on plain
Disabled: true
Supplied Plan Advice:
PARTITIONWISE(plain) /* matched, failed */
pgpa_planner_apply_scan_advice() treats this single-target PARTITIONWISE
entry as an Append/MergeAppend scan restriction. For the plain table,
that clears all available scan methods. The feedback walker then
looks for a partitionwise scan and reports failure.
The patch uses the preprocessed RTE inheritance flag to treat this as a
matched no-op. It also handles the equivalent PARTITIONWISE((plain)) form.
This is on REL_19_STABLE at b73d13c3, and the patch applies cleanly to
master at 374522aa.
I have not manually reviewed the C changes. The pg_plan_advice regression
suite, its foreign-scan TAP test, and the test_plan_advice TAP test pass.
Nik
From 0d596e4ec1c8f17d6f6eeab5724c0356c8661ecf Mon Sep 17 00:00:00 2001
From: Nik Samokhvalov <[email protected]>
Date: Tue, 22 Sep 2026 08:44:18 -0700
Subject: [PATCH] pg_plan_advice: Make PARTITIONWISE() on plain tables a no-op
Single-target PARTITIONWISE advice says that a relation should not take
part in a partitionwise join. This is vacuous when the relation has no
children, but enforcement restricted its scan methods to Append and
MergeAppend, disabling every available path. Feedback then reported the
advice as failed.
Use the preprocessed RTE inheritance flag to treat this case as matched
without changing the scan mask. Test both the identifier and
singleton-list forms.
---
.../pg_plan_advice/expected/partitionwise.out | 28 +++++++++++++++++++
contrib/pg_plan_advice/pgpa_planner.c | 15 ++++++----
contrib/pg_plan_advice/pgpa_walker.c | 11 ++++++++
contrib/pg_plan_advice/sql/partitionwise.sql | 9 ++++++
4 files changed, 58 insertions(+), 5 deletions(-)
diff --git a/contrib/pg_plan_advice/expected/partitionwise.out b/contrib/pg_plan_advice/expected/partitionwise.out
index 0ae22600a4..4ad9d6be40 100644
--- a/contrib/pg_plan_advice/expected/partitionwise.out
+++ b/contrib/pg_plan_advice/expected/partitionwise.out
@@ -211,6 +211,34 @@ 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)
+COMMIT;
+-- PARTITIONWISE advice on a table without children is vacuous.
+CREATE TABLE ptplain (id integer);
+BEGIN;
+SET LOCAL pg_plan_advice.advice = 'PARTITIONWISE(ptplain)';
+EXPLAIN (PLAN_ADVICE, COSTS OFF) SELECT * FROM ptplain;
+ QUERY PLAN
+----------------------------------------
+ Seq Scan on ptplain
+ Supplied Plan Advice:
+ PARTITIONWISE(ptplain) /* matched */
+ Generated Plan Advice:
+ SEQ_SCAN(ptplain)
+ NO_GATHER(ptplain)
+(6 rows)
+
+SET LOCAL pg_plan_advice.advice = 'PARTITIONWISE((ptplain))';
+EXPLAIN (PLAN_ADVICE, COSTS OFF) SELECT * FROM ptplain;
+ QUERY PLAN
+------------------------------------------
+ Seq Scan on ptplain
+ Supplied Plan Advice:
+ PARTITIONWISE((ptplain)) /* matched */
+ Generated Plan Advice:
+ SEQ_SCAN(ptplain)
+ NO_GATHER(ptplain)
+(6 rows)
+
COMMIT;
-- Test conflicting advice.
BEGIN;
diff --git a/contrib/pg_plan_advice/pgpa_planner.c b/contrib/pg_plan_advice/pgpa_planner.c
index c57df4aa7f..2352a96c48 100644
--- a/contrib/pg_plan_advice/pgpa_planner.c
+++ b/contrib/pg_plan_advice/pgpa_planner.c
@@ -132,7 +132,7 @@ static void pgpa_planner_apply_join_path_advice(JoinType jointype,
uint64 *pgs_mask_p,
char *plan_name,
pgpa_join_state *pjs);
-static void pgpa_planner_apply_scan_advice(RelOptInfo *rel,
+static void pgpa_planner_apply_scan_advice(RelOptInfo *rel, bool inh,
pgpa_trove_entry *scan_entries,
Bitmapset *scan_indexes,
pgpa_trove_entry *rel_entries,
@@ -439,7 +439,7 @@ pgpa_build_simple_rel(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
{
uint64 original_mask = rel->pgs_mask;
- pgpa_planner_apply_scan_advice(rel,
+ pgpa_planner_apply_scan_advice(rel, rte->inh,
tresult_scan.entries,
tresult_scan.indexes,
tresult_rel.entries,
@@ -1624,7 +1624,7 @@ pgpa_semijoin_permits_join(int outer_count, int inner_count,
* Apply scan advice to a RelOptInfo.
*/
static void
-pgpa_planner_apply_scan_advice(RelOptInfo *rel,
+pgpa_planner_apply_scan_advice(RelOptInfo *rel, bool inh,
pgpa_trove_entry *scan_entries,
Bitmapset *scan_indexes,
pgpa_trove_entry *rel_entries,
@@ -1721,11 +1721,16 @@ pgpa_planner_apply_scan_advice(RelOptInfo *rel,
/*
* PARTITIONWISE behaves like a scan type, except that if there's more
- * than one relation targeted, it has no effect at this level.
+ * than one relation targeted, it has no effect at this level. If the
+ * relation has no children, this advice is vacuous and should not
+ * restrict the scan type.
*/
if (my_entry->tag == PGPA_TAG_PARTITIONWISE)
{
- if (just_one_rel)
+ if (just_one_rel && !inh)
+ scan_type_rel_indexes =
+ bms_add_member(scan_type_rel_indexes, i);
+ else if (just_one_rel)
{
const uint64 my_scan_type = PGS_APPEND | PGS_MERGE_APPEND;
diff --git a/contrib/pg_plan_advice/pgpa_walker.c b/contrib/pg_plan_advice/pgpa_walker.c
index 7cb227fb1d..35e7f8573e 100644
--- a/contrib/pg_plan_advice/pgpa_walker.c
+++ b/contrib/pg_plan_advice/pgpa_walker.c
@@ -847,6 +847,17 @@ pgpa_walker_would_advise(pgpa_plan_walker_context *walker,
return pgpa_walker_index_target_matches_plan(target->itarget, scan->plan);
}
case PGPA_TAG_PARTITIONWISE:
+ /* Match the vacuous case handled by the planner. */
+ if (target->ttype == PGPA_TARGET_IDENTIFIER ||
+ list_length(target->children) == 1)
+ {
+ RangeTblEntry *rte;
+
+ rte = rt_fetch(bms_singleton_member(relids),
+ walker->pstmt->rtable);
+ if (!rte->inh)
+ return true;
+ }
return pgpa_walker_find_scan(walker,
PGPA_SCAN_PARTITIONWISE,
relids) != NULL;
diff --git a/contrib/pg_plan_advice/sql/partitionwise.sql b/contrib/pg_plan_advice/sql/partitionwise.sql
index ce10d2abd7..bad373e940 100644
--- a/contrib/pg_plan_advice/sql/partitionwise.sql
+++ b/contrib/pg_plan_advice/sql/partitionwise.sql
@@ -70,6 +70,15 @@ 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;
+-- PARTITIONWISE advice on a table without children is vacuous.
+CREATE TABLE ptplain (id integer);
+BEGIN;
+SET LOCAL pg_plan_advice.advice = 'PARTITIONWISE(ptplain)';
+EXPLAIN (PLAN_ADVICE, COSTS OFF) SELECT * FROM ptplain;
+SET LOCAL pg_plan_advice.advice = 'PARTITIONWISE((ptplain))';
+EXPLAIN (PLAN_ADVICE, COSTS OFF) SELECT * FROM ptplain;
+COMMIT;
+
-- Test conflicting advice.
BEGIN;
SET LOCAL pg_plan_advice.advice = 'PARTITIONWISE((pt1 pt2) (pt1 pt3))';
--
2.50.1 (Apple Git-155)
