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). As far as I have found so far, those things are in
> lock step for all of the cases that test_plan_advice exercises, or to
> say it differently, they're in lock step for all the kinds of advice
> that pg_plan_advice generates itself.
AI found a related case and prepared the attached patch:
pg_plan_advice generates Gather advice for a set-operation upper relation
that it cannot enforce. This is on REL_19_STABLE at b368bdd2.
Reproducer:
load 'pg_plan_advice';
create table t (a int);
set max_parallel_workers_per_gather = 1;
set min_parallel_table_scan_size = 0;
set parallel_setup_cost = 0;
set parallel_tuple_cost = 0;
set enable_gathermerge = off;
explain (costs off, plan_advice)
select a from t union select a from t;
This produces a Gather above the Parallel Append and generates:
GATHER((unnamed_subquery unnamed_subquery#2))
Feeding that advice back already reports it as only partially matched:
set pg_plan_advice.advice =
'GATHER((unnamed_subquery unnamed_subquery#2))';
set parallel_setup_cost = 1000000;
explain (costs off)
select a from t union select a from t;
The second plan has no Gather, despite the supplied advice, and reports:
GATHER((unnamed_subquery unnamed_subquery#2)) /* partially matched */
The Gather belongs to the set-operation upper relation. That relation is
built by plan_set_operations(), where pg_plan_advice has no hook to enforce
the generated advice. This is also consistent with the documented
limitation that set-operation planning cannot currently be controlled.
The attached patch records which PlannerInfo objects contain set
operations, maps their RTIs into the final flattened range table, and
omits GATHER and GATHER_MERGE advice for those upper relations. It retains
Gather advice within set-operation input queries and for flattened union
all append relations.
I have not manually reviewed the C changes. The following passed:
make check
make -C src/test/isolation check
make -C contrib/pg_plan_advice check
make -C src/test/modules/test_plan_advice check
The pg_plan_advice regression additions cover union at the top level and
in a subquery, Gather within intersect inputs, and flattened union all.
--
Nik
From f782a7e198171cf62220e1d221f7b74e09475d2a Mon Sep 17 00:00:00 2001
From: Nik Samokhvalov <[email protected]>
Date: Mon, 21 Sep 2026 07:18:41 -0700
Subject: [PATCH] Don't emit Gather advice for set-operation upper rels
---
contrib/pg_plan_advice/expected/gather.out | 65 ++++++++++++++++++++++
contrib/pg_plan_advice/pgpa_planner.c | 1 +
contrib/pg_plan_advice/pgpa_planner.h | 3 +
contrib/pg_plan_advice/pgpa_walker.c | 40 ++++++++++++-
contrib/pg_plan_advice/sql/gather.sql | 14 +++++
5 files changed, 122 insertions(+), 1 deletion(-)
diff --git a/contrib/pg_plan_advice/expected/gather.out b/contrib/pg_plan_advice/expected/gather.out
index 0cc0dedf8..fbb915d22 100644
--- a/contrib/pg_plan_advice/expected/gather.out
+++ b/contrib/pg_plan_advice/expected/gather.out
@@ -166,6 +166,71 @@ EXPLAIN (COSTS OFF, PLAN_ADVICE)
(17 rows)
COMMIT;
+-- Gather over a set-operation upper relation cannot be controlled by advice.
+EXPLAIN (COSTS OFF, PLAN_ADVICE)
+ SELECT id FROM gt_dim UNION SELECT id FROM gt_dim;
+ QUERY PLAN
+--------------------------------------------------------
+ HashAggregate
+ Group Key: gt_dim.id
+ -> Gather
+ Workers Planned: 1
+ -> Parallel Append
+ -> Parallel Seq Scan on gt_dim
+ -> Parallel Seq Scan on gt_dim gt_dim_1
+ Generated Plan Advice:
+ SEQ_SCAN(gt_dim@setop_1 gt_dim@setop_2)
+(9 rows)
+
+EXPLAIN (COSTS OFF, PLAN_ADVICE)
+ SELECT * FROM (SELECT id FROM gt_dim UNION SELECT id FROM gt_dim) s;
+ QUERY PLAN
+--------------------------------------------------------
+ HashAggregate
+ Group Key: gt_dim.id
+ -> Gather
+ Workers Planned: 1
+ -> Parallel Append
+ -> Parallel Seq Scan on gt_dim
+ -> Parallel Seq Scan on gt_dim gt_dim_1
+ Generated Plan Advice:
+ SEQ_SCAN(gt_dim@setop_1 gt_dim@setop_2)
+ NO_GATHER(s)
+(10 rows)
+
+-- Gather within the input queries remains controllable.
+EXPLAIN (COSTS OFF, PLAN_ADVICE)
+ SELECT id FROM gt_dim INTERSECT SELECT id FROM gt_dim;
+ QUERY PLAN
+--------------------------------------------------
+ HashSetOp Intersect
+ -> Gather
+ Workers Planned: 1
+ -> Parallel Seq Scan on gt_dim
+ -> Gather
+ Workers Planned: 1
+ -> Parallel Seq Scan on gt_dim gt_dim_1
+ Generated Plan Advice:
+ SEQ_SCAN(gt_dim@setop_1 gt_dim@setop_2)
+ GATHER(gt_dim@setop_1 gt_dim@setop_2)
+ NO_GATHER(unnamed_subquery unnamed_subquery#2)
+(11 rows)
+
+-- A flattened UNION ALL is planned as an append relation, not an upper relation.
+EXPLAIN (COSTS OFF, PLAN_ADVICE)
+ SELECT id FROM gt_dim UNION ALL SELECT id FROM gt_dim;
+ QUERY PLAN
+--------------------------------------------------
+ Gather
+ Workers Planned: 1
+ -> Parallel Append
+ -> Parallel Seq Scan on gt_dim
+ -> Parallel Seq Scan on gt_dim gt_dim_1
+ Generated Plan Advice:
+ SEQ_SCAN(gt_dim gt_dim#2)
+ GATHER(unnamed_subquery)
+(8 rows)
+
-- Force a Gather or Gather Merge on one relation but no parallelism on other.
BEGIN;
SET LOCAL pg_plan_advice.advice = 'gather_merge(f) no_gather(d)';
diff --git a/contrib/pg_plan_advice/pgpa_planner.c b/contrib/pg_plan_advice/pgpa_planner.c
index c57df4aa7..30ebbe74d 100644
--- a/contrib/pg_plan_advice/pgpa_planner.c
+++ b/contrib/pg_plan_advice/pgpa_planner.c
@@ -2016,6 +2016,7 @@ pgpa_planner_get_proot(pgpa_planner_state *pps, PlannerInfo *root)
/* Set plan name and alternative plan name. */
new_proot->plan_name = root->plan_name;
new_proot->alternative_plan_name = root->alternative_plan_name;
+ new_proot->has_set_operations = root->parse->setOperations != NULL;
/*
* If the newly-created proot shares an alternative_plan_name with one or
diff --git a/contrib/pg_plan_advice/pgpa_planner.h b/contrib/pg_plan_advice/pgpa_planner.h
index 366142a0c..fe94e38ef 100644
--- a/contrib/pg_plan_advice/pgpa_planner.h
+++ b/contrib/pg_plan_advice/pgpa_planner.h
@@ -52,6 +52,9 @@ typedef struct pgpa_planner_info
bool has_rtoffset;
Index rtoffset;
+ /* Does this query level contain a set operation? */
+ bool has_set_operations;
+
/*
* List of Bitmapset objects. Each represents the relid set of a relation
* that the planner considers making unique during semijoin planning.
diff --git a/contrib/pg_plan_advice/pgpa_walker.c b/contrib/pg_plan_advice/pgpa_walker.c
index 7cb227fb1..d46be2470 100644
--- a/contrib/pg_plan_advice/pgpa_walker.c
+++ b/contrib/pg_plan_advice/pgpa_walker.c
@@ -82,6 +82,7 @@ pgpa_plan_walker(pgpa_plan_walker_context *walker, PlannedStmt *pstmt,
ListCell *lc;
List *sj_unique_rtis = NULL;
List *sj_nonunique_qfs = NULL;
+ List *setop_relid_sets = NIL;
List *chosen_proots;
List *discarded_proots;
@@ -101,9 +102,24 @@ pgpa_plan_walker(pgpa_plan_walker_context *walker, PlannedStmt *pstmt,
pgpa_walk_recursively(walker, plan, false, NULL, NIL, false);
}
- /* Adjust RTIs from sj_unique_rels for the flattened range table. */
+ /* Collect query-level RTIs using the final, flattened range table. */
foreach_ptr(pgpa_planner_info, proot, proots)
{
+ if (proot->has_set_operations && proot->has_rtoffset)
+ {
+ Bitmapset *relids = NULL;
+
+ for (int rti = 1; rti <= proot->rid_array_size; ++rti)
+ {
+ if (proot->rid_array[rti - 1].alias_name != NULL)
+ relids = bms_add_member(relids,
+ rti + proot->rtoffset);
+ }
+
+ if (relids != NULL)
+ setop_relid_sets = lappend(setop_relid_sets, relids);
+ }
+
/* If there are no sj_unique_rels for this proot, we can skip it. */
if (proot->sj_unique_rels == NIL)
continue;
@@ -168,6 +184,10 @@ pgpa_plan_walker(pgpa_plan_walker_context *walker, PlannedStmt *pstmt,
* (Should the Partial Aggregates in such a case be created in an
* UPPERREL_GROUP_AGG with a non-empty relid set? Right now that doesn't
* happen, but it seems like it would make life easier for us if it did.)
+ *
+ * Likewise, omit Gather advice for a set-operation upper relation, which
+ * cannot enforce such advice. Gather nodes within a set-operation input
+ * query use different RTIs and remain controllable.
*/
for (int t = 0; t < NUM_PGPA_QF_TYPES; ++t)
{
@@ -175,6 +195,24 @@ pgpa_plan_walker(pgpa_plan_walker_context *walker, PlannedStmt *pstmt,
foreach_ptr(pgpa_query_feature, qf, walker->query_features[t])
{
+ bool over_setop = false;
+
+ if ((t == PGPAQF_GATHER || t == PGPAQF_GATHER_MERGE) &&
+ qf->relids != NULL)
+ {
+ foreach_node(Bitmapset, setop_relids, setop_relid_sets)
+ {
+ if (bms_is_subset(qf->relids, setop_relids))
+ {
+ over_setop = true;
+ break;
+ }
+ }
+ }
+
+ if (over_setop)
+ continue;
+
if (qf->relids != NULL)
query_features = lappend(query_features, qf);
else
diff --git a/contrib/pg_plan_advice/sql/gather.sql b/contrib/pg_plan_advice/sql/gather.sql
index 776666bf1..2008b465f 100644
--- a/contrib/pg_plan_advice/sql/gather.sql
+++ b/contrib/pg_plan_advice/sql/gather.sql
@@ -45,6 +45,20 @@ EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM gt_fact f JOIN gt_dim d ON f.dim_id = d.id ORDER BY d.id;
COMMIT;
+-- Gather over a set-operation upper relation cannot be controlled by advice.
+EXPLAIN (COSTS OFF, PLAN_ADVICE)
+ SELECT id FROM gt_dim UNION SELECT id FROM gt_dim;
+EXPLAIN (COSTS OFF, PLAN_ADVICE)
+ SELECT * FROM (SELECT id FROM gt_dim UNION SELECT id FROM gt_dim) s;
+
+-- Gather within the input queries remains controllable.
+EXPLAIN (COSTS OFF, PLAN_ADVICE)
+ SELECT id FROM gt_dim INTERSECT SELECT id FROM gt_dim;
+
+-- A flattened UNION ALL is planned as an append relation, not an upper relation.
+EXPLAIN (COSTS OFF, PLAN_ADVICE)
+ SELECT id FROM gt_dim UNION ALL SELECT id FROM gt_dim;
+
-- Force a Gather or Gather Merge on one relation but no parallelism on other.
BEGIN;
SET LOCAL pg_plan_advice.advice = 'gather_merge(f) no_gather(d)';
