v3-0005-not-for-commit-count-distinct-joinrels-and-joinre.patch

From 641cf91bf28d2f449f2b6b87d75bfe5cd27298f3 Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
Date: Mon, 25 Aug 2025 15:22:57 -0400
Subject: [PATCH v3 5/5] not for commit: count distinct joinrels and joinrel
 planning attempts

---
 .../expected/pg_overexplain.out               |  22 +++-
 contrib/pg_overexplain/pg_overexplain.c       | 107 ++++++++++++++++++
 2 files changed, 124 insertions(+), 5 deletions(-)

diff --git a/contrib/pg_overexplain/expected/pg_overexplain.out b/contrib/pg_overexplain/expected/pg_overexplain.out
index 6de02323d7c..d8d3e36d7f1 100644
--- a/contrib/pg_overexplain/expected/pg_overexplain.out
+++ b/contrib/pg_overexplain/expected/pg_overexplain.out
@@ -38,7 +38,9 @@ EXPLAIN (DEBUG) SELECT 1;
    Relation OIDs: none
    Executor Parameter Types: none
    Parse Location: 0 to end
-(11 rows)
+   Total Joinrel Attempts: 0
+   Distinct Joinrels: 0
+(13 rows)
 
 EXPLAIN (RANGE_TABLE) SELECT 1;
                 QUERY PLAN                
@@ -120,6 +122,8 @@ $$);
    Relation OIDs: NNN...
    Executor Parameter Types: none
    Parse Location: 0 to end
+   Total Joinrel Attempts: 0
+   Distinct Joinrels: 0
  RTI 1 (relation, inherited, in-from-clause):
    Eref: vegetables (id, name, genus)
    Relation: vegetables
@@ -141,7 +145,7 @@ $$);
    Relation Kind: relation
    Relation Lock Mode: AccessShareLock
  Unprunable RTIs: 1 3 4
-(53 rows)
+(55 rows)
 
 -- Test a different output format.
 SELECT explain_filter($$
@@ -241,6 +245,8 @@ $$);
        <Relation-OIDs>NNN...</Relation-OIDs>                        +
        <Executor-Parameter-Types>none</Executor-Parameter-Types>    +
        <Parse-Location>0 to end</Parse-Location>                    +
+       <Total-Joinrel-Attempts>0</Total-Joinrel-Attempts>           +
+       <Distinct-Joinrels>0</Distinct-Joinrels>                     +
      </PlannedStmt>                                                 +
      <Range-Table>                                                  +
        <Range-Table-Entry>                                          +
@@ -345,7 +351,9 @@ $$);
    Relation OIDs: NNN...
    Executor Parameter Types: none
    Parse Location: 0 to end
-(37 rows)
+   Total Joinrel Attempts: 0
+   Distinct Joinrels: 0
+(39 rows)
 
 SET debug_parallel_query = false;
 RESET enable_seqscan;
@@ -373,7 +381,9 @@ $$);
    Relation OIDs: NNN...
    Executor Parameter Types: 0
    Parse Location: 0 to end
-(15 rows)
+   Total Joinrel Attempts: 0
+   Distinct Joinrels: 0
+(17 rows)
 
 -- Create an index, and then attempt to force a nested loop with inner index
 -- scan so that we can see parameter-related information. Also, let's try
@@ -437,7 +447,9 @@ $$);
    Relation OIDs: NNN...
    Executor Parameter Types: 23
    Parse Location: 0 to end
-(47 rows)
+   Total Joinrel Attempts: 2
+   Distinct Joinrels: 1
+(49 rows)
 
 RESET enable_hashjoin;
 RESET enable_material;
diff --git a/contrib/pg_overexplain/pg_overexplain.c b/contrib/pg_overexplain/pg_overexplain.c
index de824566f8c..bf4852fcadc 100644
--- a/contrib/pg_overexplain/pg_overexplain.c
+++ b/contrib/pg_overexplain/pg_overexplain.c
@@ -16,6 +16,10 @@
 #include "commands/explain_format.h"
 #include "commands/explain_state.h"
 #include "fmgr.h"
+#include "nodes/makefuncs.h"
+#include "optimizer/extendplan.h"
+#include "optimizer/paths.h"
+#include "optimizer/planner.h"
 #include "parser/parsetree.h"
 #include "storage/lock.h"
 #include "utils/builtins.h"
@@ -32,6 +36,12 @@ typedef struct
 	bool		range_table;
 } overexplain_options;
 
+typedef struct
+{
+	int			total_joinrel_attempts;
+	int			distinct_joinrel_count;
+} overexplain_plannerglobal;
+
 static overexplain_options *overexplain_ensure_options(ExplainState *es);
 static void overexplain_debug_handler(ExplainState *es, DefElem *opt,
 									  ParseState *pstate);
@@ -57,9 +67,27 @@ static void overexplain_bitmapset(const char *qlabel, Bitmapset *bms,
 static void overexplain_intlist(const char *qlabel, List *list,
 								ExplainState *es);
 
+static void overexplain_planner_setup_hook(PlannerGlobal *glob, Query *parse,
+										   const char *query_string,
+										   double *tuple_fraction);
+static void overexplain_planner_shutdown_hook(PlannerGlobal *glob,
+											  Query *parse,
+											  const char *query_string,
+											  PlannedStmt *pstmt);
+static void overexplain_set_join_pathlist_hook(PlannerInfo *root,
+											   RelOptInfo *joinrel,
+											   RelOptInfo *outerrel,
+											   RelOptInfo *innerrel,
+											   JoinType jointype,
+											   JoinPathExtraData *extra);
+
 static int	es_extension_id;
+static int	planner_extension_id = -1;
 static explain_per_node_hook_type prev_explain_per_node_hook;
 static explain_per_plan_hook_type prev_explain_per_plan_hook;
+static planner_setup_hook_type prev_planner_setup_hook;
+static planner_shutdown_hook_type prev_planner_shutdown_hook;
+static set_join_pathlist_hook_type prev_set_join_pathlist_hook;
 
 /*
  * Initialization we do when this module is loaded.
@@ -70,6 +98,9 @@ _PG_init(void)
 	/* Get an ID that we can use to cache data in an ExplainState. */
 	es_extension_id = GetExplainExtensionId("pg_overexplain");
 
+	/* Get an ID that we can use to cache data in the planner. */
+	planner_extension_id = GetPlannerExtensionId("pg_overexplain");
+
 	/* Register the new EXPLAIN options implemented by this module. */
 	RegisterExtensionExplainOption("debug", overexplain_debug_handler);
 	RegisterExtensionExplainOption("range_table",
@@ -80,6 +111,16 @@ _PG_init(void)
 	explain_per_node_hook = overexplain_per_node_hook;
 	prev_explain_per_plan_hook = explain_per_plan_hook;
 	explain_per_plan_hook = overexplain_per_plan_hook;
+
+	/* Example of planner_setup_hook/planner_shutdown_hook use */
+	prev_planner_setup_hook = planner_setup_hook;
+	planner_setup_hook = overexplain_planner_setup_hook;
+	prev_planner_shutdown_hook = planner_shutdown_hook;
+	planner_shutdown_hook = overexplain_planner_shutdown_hook;
+
+	/* Support for above example */
+	prev_set_join_pathlist_hook = set_join_pathlist_hook;
+	set_join_pathlist_hook = overexplain_set_join_pathlist_hook;
 }
 
 /*
@@ -369,6 +410,29 @@ overexplain_debug(PlannedStmt *plannedstmt, ExplainState *es)
 									 plannedstmt->stmt_len),
 							es);
 
+	{
+		DefElem    *elem = NULL;
+
+		foreach_node(DefElem, de, plannedstmt->extension_state)
+		{
+			if (strcmp(de->defname, "pg_overexplain") == 0)
+			{
+				elem = de;
+				break;
+			}
+		}
+
+		if (elem != NULL)
+		{
+			List	   *l = castNode(List, elem->arg);
+
+			ExplainPropertyInteger("Total Joinrel Attempts", NULL,
+								   intVal(linitial(l)), es);
+			ExplainPropertyInteger("Distinct Joinrels", NULL,
+								   intVal(lsecond(l)), es);
+		}
+	}
+
 	/* Done with this group. */
 	if (es->format == EXPLAIN_FORMAT_TEXT)
 		es->indent--;
@@ -772,3 +836,46 @@ overexplain_intlist(const char *qlabel, List *list, ExplainState *es)
 
 	pfree(buf.data);
 }
+
+static void
+overexplain_planner_setup_hook(PlannerGlobal *glob, Query *parse,
+							   const char *query_string,
+							   double *tuple_fraction)
+{
+	overexplain_plannerglobal *g = palloc0_object(overexplain_plannerglobal);
+
+	SetPlannerGlobalExtensionState(glob, planner_extension_id, g);
+}
+
+static void
+overexplain_planner_shutdown_hook(PlannerGlobal *glob, Query *parse,
+								  const char *query_string, PlannedStmt *pstmt)
+{
+	overexplain_plannerglobal *g;
+	DefElem    *elem;
+	List	   *l;
+
+	g = GetPlannerGlobalExtensionState(glob, planner_extension_id);
+	l = list_make2(makeInteger(g->total_joinrel_attempts),
+				   makeInteger(g->distinct_joinrel_count));
+	elem = makeDefElem("pg_overexplain", (Node *) l, -1);
+	pstmt->extension_state = lappend(pstmt->extension_state, elem);
+}
+
+static void
+overexplain_set_join_pathlist_hook(PlannerInfo *root, RelOptInfo *joinrel,
+								   RelOptInfo *outerrel, RelOptInfo *innerrel,
+								   JoinType jointype, JoinPathExtraData *extra)
+{
+	overexplain_plannerglobal *g;
+
+	g = GetPlannerGlobalExtensionState(root->glob, planner_extension_id);
+	g->total_joinrel_attempts++;
+
+	if (GetRelOptInfoExtensionState(joinrel, planner_extension_id) == NULL)
+	{
+		g->distinct_joinrel_count++;
+		/* set any non-NULL value to avoid double-counting */
+		SetRelOptInfoExtensionState(joinrel, planner_extension_id, g);
+	}
+}
-- 
2.39.5 (Apple Git-154)