v22-0003-Add-an-alternative_root-field-to-PlannerInfo.patch

From 9bc42f4dbb4afbe6f69439b929f9ec0c7501277e Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
Date: Mon, 23 Mar 2026 08:49:44 -0400
Subject: [PATCH v22 3/6] Add an alternative_root field to PlannerInfo.

Typically, we have only one PlannerInfo for any given subquery, but
when we are considering a MinMaxAggPath or a hashed subplan, we end
up creating a second PlannerInfo for the same portion of the query,
with a clone of the original range table. In fact, in the MinMaxAggPath
case, we might end up creating several clones, one per aggregate.

At present, there's no easy way for a plugin, such as pg_plan_advice,
to understand the relationships between the original range table and
the copies of it that are created in these cases.  To fix, add an
alternative_root field to PlannerInfo. For a hashed subplan, this
points back to the PlannerInfo for the non-hashed alternative; for
minmax aggregates, this points back to the parent PlannerInfo; in
other cases, it's just NULL.
---
 src/backend/optimizer/path/allpaths.c     |  2 +-
 src/backend/optimizer/plan/planagg.c      |  1 +
 src/backend/optimizer/plan/planner.c      | 12 ++++++++----
 src/backend/optimizer/plan/subselect.c    |  6 +++---
 src/backend/optimizer/prep/prepjointree.c |  1 +
 src/backend/optimizer/prep/prepunion.c    |  2 +-
 src/include/nodes/pathnodes.h             | 11 +++++++++++
 src/include/optimizer/planner.h           |  1 +
 8 files changed, 27 insertions(+), 9 deletions(-)

diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index c26f48edfa0..61093f222a1 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -2833,7 +2833,7 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
 	/* Generate a subroot and Paths for the subquery */
 	plan_name = choose_plan_name(root->glob, rte->eref->aliasname, false);
 	rel->subroot = subquery_planner(root->glob, subquery, plan_name,
-									root, false, tuple_fraction, NULL);
+									root, NULL, false, tuple_fraction, NULL);
 
 	/* Isolate the params needed by this specific subplan */
 	rel->subplan_params = root->plan_params;
diff --git a/src/backend/optimizer/plan/planagg.c b/src/backend/optimizer/plan/planagg.c
index 09b38b2c378..559a8c14e88 100644
--- a/src/backend/optimizer/plan/planagg.c
+++ b/src/backend/optimizer/plan/planagg.c
@@ -340,6 +340,7 @@ build_minmax_path(PlannerInfo *root, MinMaxAggInfo *mminfo,
 	memcpy(subroot, root, sizeof(PlannerInfo));
 	subroot->query_level++;
 	subroot->parent_root = root;
+	subroot->alternative_root = root;
 	subroot->plan_name = choose_plan_name(root->glob, "minmax", true);
 
 	/* reset subplan-related stuff */
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 42604a0f75c..9dcf49c0055 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -515,8 +515,8 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions,
 							   &tuple_fraction, es);
 
 	/* primary planning entry point (may recurse for subqueries) */
-	root = subquery_planner(glob, parse, NULL, NULL, false, tuple_fraction,
-							NULL);
+	root = subquery_planner(glob, parse, NULL, NULL, NULL, false,
+							tuple_fraction, NULL);
 
 	/* Select best Path and turn it into a Plan */
 	final_rel = fetch_upper_rel(root, UPPERREL_FINAL, NULL);
@@ -715,6 +715,8 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions,
  * parse is the querytree produced by the parser & rewriter.
  * plan_name is the name to assign to this subplan (NULL at the top level).
  * parent_root is the immediate parent Query's info (NULL at the top level).
+ * alternative_root is a previously created PlannerInfo for which this query
+ * level is an alternative implementation, or else NULL.
  * hasRecursion is true if this is a recursive WITH query.
  * tuple_fraction is the fraction of tuples we expect will be retrieved.
  * tuple_fraction is interpreted as explained for grouping_planner, below.
@@ -741,8 +743,9 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions,
  */
 PlannerInfo *
 subquery_planner(PlannerGlobal *glob, Query *parse, char *plan_name,
-				 PlannerInfo *parent_root, bool hasRecursion,
-				 double tuple_fraction, SetOperationStmt *setops)
+				 PlannerInfo *parent_root, PlannerInfo *alternative_root,
+				 bool hasRecursion, double tuple_fraction,
+				 SetOperationStmt *setops)
 {
 	PlannerInfo *root;
 	List	   *newWithCheckOptions;
@@ -759,6 +762,7 @@ subquery_planner(PlannerGlobal *glob, Query *parse, char *plan_name,
 	root->query_level = parent_root ? parent_root->query_level + 1 : 1;
 	root->plan_name = plan_name;
 	root->parent_root = parent_root;
+	root->alternative_root = alternative_root;
 	root->plan_params = NIL;
 	root->outer_params = NULL;
 	root->planner_cxt = CurrentMemoryContext;
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index 0d31861da7f..ccec1eaa7fe 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -224,7 +224,7 @@ make_subplan(PlannerInfo *root, Query *orig_subquery,
 	/* Generate Paths for the subquery */
 	subroot = subquery_planner(root->glob, subquery,
 							   choose_plan_name(root->glob, sublinkstr, true),
-							   root, false, tuple_fraction, NULL);
+							   root, NULL, false, tuple_fraction, NULL);
 
 	/* Isolate the params needed by this specific subplan */
 	plan_params = root->plan_params;
@@ -274,7 +274,7 @@ make_subplan(PlannerInfo *root, Query *orig_subquery,
 			/* Generate Paths for the ANY subquery; we'll need all rows */
 			plan_name = choose_plan_name(root->glob, sublinkstr, true);
 			subroot = subquery_planner(root->glob, subquery, plan_name,
-									   root, false, 0.0, NULL);
+									   root, subroot, false, 0.0, NULL);
 
 			/* Isolate the params needed by this specific subplan */
 			plan_params = root->plan_params;
@@ -971,7 +971,7 @@ SS_process_ctes(PlannerInfo *root)
 		 */
 		subroot = subquery_planner(root->glob, subquery,
 								   choose_plan_name(root->glob, cte->ctename, false),
-								   root, cte->cterecursive, 0.0, NULL);
+								   root, NULL, cte->cterecursive, 0.0, NULL);
 
 		/*
 		 * Since the current query level doesn't yet contain any RTEs, it
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index d5e1041ffa3..8c0e6a61c96 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -1419,6 +1419,7 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
 	subroot->query_level = root->query_level;
 	subroot->plan_name = root->plan_name;
 	subroot->parent_root = root->parent_root;
+	subroot->alternative_root = root->alternative_root;
 	subroot->plan_params = NIL;
 	subroot->outer_params = NULL;
 	subroot->planner_cxt = CurrentMemoryContext;
diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c
index 583cb0b7a25..d1f022c5bfd 100644
--- a/src/backend/optimizer/prep/prepunion.c
+++ b/src/backend/optimizer/prep/prepunion.c
@@ -250,7 +250,7 @@ recurse_set_operations(Node *setOp, PlannerInfo *root,
 		 */
 		plan_name = choose_plan_name(root->glob, "setop", true);
 		subroot = rel->subroot = subquery_planner(root->glob, subquery,
-												  plan_name, root,
+												  plan_name, root, NULL,
 												  false, root->tuple_fraction,
 												  parentOp);
 
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 27758ec16fe..58ce19d1d21 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -317,6 +317,17 @@ struct PlannerInfo
 	/* NULL at outermost Query */
 	PlannerInfo *parent_root pg_node_attr(read_write_ignore);
 
+	/*
+	 * If this PlannerInfo exists to consider an alternative implementation
+	 * strategy for a portion of the query that could also be implemented by
+	 * some other PlannerInfo, this points to that other PlannerInfo. When
+	 * we are considering the first or only alternative, it is NULL.
+	 *
+	 * Currently, we use this when considering a MinMaxAggPath or a hashed
+	 * SubPlan.
+	 */
+	PlannerInfo *alternative_root pg_node_attr(read_write_ignore);
+
 	/* Subplan name for EXPLAIN and debugging purposes (NULL at top level) */
 	char	   *plan_name;
 
diff --git a/src/include/optimizer/planner.h b/src/include/optimizer/planner.h
index 80509773c01..9c4950b340f 100644
--- a/src/include/optimizer/planner.h
+++ b/src/include/optimizer/planner.h
@@ -63,6 +63,7 @@ extern PlannedStmt *standard_planner(Query *parse, const char *query_string,
 extern PlannerInfo *subquery_planner(PlannerGlobal *glob, Query *parse,
 									 char *plan_name,
 									 PlannerInfo *parent_root,
+									 PlannerInfo *alternative_root,
 									 bool hasRecursion, double tuple_fraction,
 									 SetOperationStmt *setops);
 
-- 
2.51.0