v23-0001-Add-an-alternative_plan_name-field-to-PlannerInf.patch

From 722000d42a267f4d20c2164bc9095ad00c3a8bc4 Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
Date: Mon, 23 Mar 2026 08:49:44 -0400
Subject: [PATCH v23 1/4] Add an alternative_plan_name 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_plan_name field to PlannerInfo. For a hashed subplan, this
is the plan name for the non-hashed alternative; for minmax aggregates,
this is the plan_name from the parent PlannerInfo; otherwise, it's the
same as plan_name.
---
 src/backend/optimizer/path/allpaths.c     |  2 +-
 src/backend/optimizer/plan/planagg.c      |  1 +
 src/backend/optimizer/plan/planner.c      | 15 +++++++++++----
 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             | 12 ++++++++++++
 src/include/optimizer/planner.h           |  1 +
 8 files changed, 31 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..75f6475cb56 100644
--- a/src/backend/optimizer/plan/planagg.c
+++ b/src/backend/optimizer/plan/planagg.c
@@ -341,6 +341,7 @@ build_minmax_path(PlannerInfo *root, MinMaxAggInfo *mminfo,
 	subroot->query_level++;
 	subroot->parent_root = root;
 	subroot->plan_name = choose_plan_name(root->glob, "minmax", true);
+	subroot->alternative_plan_name = root->plan_name;
 
 	/* reset subplan-related stuff */
 	subroot->plan_params = NIL;
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 42604a0f75c..d19800ad6a5 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;
@@ -758,6 +761,10 @@ subquery_planner(PlannerGlobal *glob, Query *parse, char *plan_name,
 	root->glob = glob;
 	root->query_level = parent_root ? parent_root->query_level + 1 : 1;
 	root->plan_name = plan_name;
+	if (alternative_root != NULL)
+		root->alternative_plan_name = alternative_root->plan_name;
+	else
+		root->alternative_plan_name = plan_name;
 	root->parent_root = parent_root;
 	root->plan_params = NIL;
 	root->outer_params = NULL;
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..95bf51606cc 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -1418,6 +1418,7 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
 	subroot->glob = root->glob;
 	subroot->query_level = root->query_level;
 	subroot->plan_name = root->plan_name;
+	subroot->alternative_plan_name = root->alternative_plan_name;
 	subroot->parent_root = root->parent_root;
 	subroot->plan_params = NIL;
 	subroot->outer_params = NULL;
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..96ec3227262 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -320,6 +320,18 @@ struct PlannerInfo
 	/* Subplan name for EXPLAIN and debugging purposes (NULL at top level) */
 	char	   *plan_name;
 
+	/*
+	 * 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 is the plan_name for that other
+	 * PlannerInfo. When we are considering the first or only alternative,
+	 * it is the same as plan_name.
+	 *
+	 * Currently, we set this to a value other than plan_name only when
+	 * considering a MinMaxAggPath or a hashed SubPlan.
+	 */
+	char	   *alternative_plan_name;
+
 	/*
 	 * plan_params contains the expressions that this query level needs to
 	 * make available to a lower query level that is currently being planned.
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