0002-EXPLAIN-reports-memory-consumed-for-plannin-20231204.patch

From fdd3919559cd4307536c358095fe3254bad7ab0d Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>
Date: Mon, 4 Dec 2023 12:12:03 +0530
Subject: [PATCH 2/2] EXPLAIN reports memory consumed for planning a query

The memory consumed is reported as "Planner Memory" property in EXPLAIN output
when any of the option MEMORY is specified.

auto_explain does not use this option since it does not planner_hook and has no
way to gather information about planner's memory usage.

Ashutosh Bapat
---
 contrib/auto_explain/auto_explain.c   |  5 ++
 doc/src/sgml/ref/explain.sgml         | 24 ++++++++++
 src/backend/commands/explain.c        | 14 +++---
 src/backend/commands/prepare.c        |  2 +-
 src/include/commands/explain.h        |  1 +
 src/test/regress/expected/explain.out | 68 +++++++++++++++++++++++++++
 src/test/regress/sql/explain.sql      |  6 +++
 7 files changed, 113 insertions(+), 7 deletions(-)

diff --git a/contrib/auto_explain/auto_explain.c b/contrib/auto_explain/auto_explain.c
index c3ac27ae99..8a77e12605 100644
--- a/contrib/auto_explain/auto_explain.c
+++ b/contrib/auto_explain/auto_explain.c
@@ -390,6 +390,11 @@ explain_ExecutorEnd(QueryDesc *queryDesc)
 		{
 			ExplainState *es = NewExplainState();
 
+			/*
+			 * auto_explain doesn't implement planner_hook and hence can not
+			 * gather information about planner's memory usage. Hence we leave
+			 * es->memory = false.
+			 */
 			es->analyze = (queryDesc->instrument_options && auto_explain_log_analyze);
 			es->verbose = auto_explain_log_verbose;
 			es->buffers = (es->analyze && auto_explain_log_buffers);
diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml
index 5ba6486da1..a0a0472e4e 100644
--- a/doc/src/sgml/ref/explain.sgml
+++ b/doc/src/sgml/ref/explain.sgml
@@ -44,6 +44,7 @@ EXPLAIN [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] <rep
     WAL [ <replaceable class="parameter">boolean</replaceable> ]
     TIMING [ <replaceable class="parameter">boolean</replaceable> ]
     SUMMARY [ <replaceable class="parameter">boolean</replaceable> ]
+    MEMORY [ <replaceable class="parameter">boolean</replaceable> ]
     FORMAT { TEXT | XML | JSON | YAML }
 </synopsis>
  </refsynopsisdiv>
@@ -250,6 +251,15 @@ ROLLBACK;
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>MEMORY</literal></term>
+    <listitem>
+     <para>
+      Include information on planner's memory consumption. Specially, include the total memory allocated by the planner and net memory that remains used at the end of the planning. It defaults to <literal>FALSE</literal>.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>FORMAT</literal></term>
     <listitem>
@@ -510,6 +520,20 @@ EXPLAIN (GENERIC_PLAN)
   SELECT sum(bar) FROM test
     WHERE id &gt; $1::integer AND id &lt; $2::integer
     GROUP BY foo;
+</programlisting>
+  </para>
+
+  <para>
+   Here's an example using MEMORY option.
+
+<programlisting>
+EXPLAIN (MEMORY) SELECT * FROM foo;
+
+                       QUERY PLAN
+---------------------------------------------------------
+ Seq Scan on foo  (cost=0.00..155.00 rows=10000 width=4)
+ Planner Memory: used=22688 bytes allocated=32768 bytes
+(2 rows)
 </programlisting>
   </para>
  </refsect1>
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 8c7f27b661..e94da4e7f8 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -122,8 +122,8 @@ static const char *explain_get_index_name(Oid indexId);
 static void show_buffer_usage(ExplainState *es, const BufferUsage *usage,
 							  bool planning);
 static void show_wal_usage(ExplainState *es, const WalUsage *usage);
-static void show_planning_memory(ExplainState *es,
-								 const MemoryUsage *usage);
+static void show_planner_memory(ExplainState *es,
+								const MemoryUsage *usage);
 static void ExplainIndexScanDetails(Oid indexid, ScanDirection indexorderdir,
 									ExplainState *es);
 static void ExplainScanTarget(Scan *plan, ExplainState *es);
@@ -204,6 +204,8 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
 			summary_set = true;
 			es->summary = defGetBoolean(opt);
 		}
+		else if (strcmp(opt->defname, "memory") == 0)
+			es->memory = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "format") == 0)
 		{
 			char	   *p = defGetString(opt);
@@ -402,7 +404,7 @@ ExplainOneQuery(Query *query, int cursorOptions,
 					bufusage;
 		MemoryContextCounters mem_counts_start;
 		MemoryContextCounters mem_counts_end;
-		MemoryUsage	mem_usage;
+		MemoryUsage mem_usage;
 		MemoryContext saved_ctx;
 
 		/*
@@ -653,10 +655,10 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es,
 		ExplainPropertyFloat("Planning Time", "ms", 1000.0 * plantime, 3, es);
 	}
 
-	if (es->summary && mem_usage)
+	if (es->memory && mem_usage)
 	{
 		ExplainOpenGroup("Planner Memory", "Planner Memory", true, es);
-		show_planning_memory(es, mem_usage);
+		show_planner_memory(es, mem_usage);
 		ExplainCloseGroup("Planner Memory", "Planner Memory", true, es);
 	}
 
@@ -3800,7 +3802,7 @@ show_wal_usage(ExplainState *es, const WalUsage *usage)
  * Show planner's memory usage details.
  */
 static void
-show_planning_memory(ExplainState *es, const MemoryUsage *usage)
+show_planner_memory(ExplainState *es, const MemoryUsage *usage)
 {
 	if (es->format == EXPLAIN_FORMAT_TEXT)
 	{
diff --git a/src/backend/commands/prepare.c b/src/backend/commands/prepare.c
index 3d3d0ae6a3..629155fca4 100644
--- a/src/backend/commands/prepare.c
+++ b/src/backend/commands/prepare.c
@@ -585,7 +585,7 @@ ExplainExecuteQuery(ExecuteStmt *execstmt, IntoClause *into, ExplainState *es,
 				bufusage;
 	MemoryContextCounters mem_counts_start;
 	MemoryContextCounters mem_counts_end;
-	MemoryUsage	mem_usage;
+	MemoryUsage mem_usage;
 	MemoryContext planner_ctx;
 	MemoryContext saved_ctx;
 
diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h
index 6947cbae8b..f2efc7741f 100644
--- a/src/include/commands/explain.h
+++ b/src/include/commands/explain.h
@@ -47,6 +47,7 @@ typedef struct ExplainState
 	bool		summary;		/* print total planning and execution timing */
 	bool		settings;		/* print modified settings */
 	bool		generic;		/* generate a generic plan */
+	bool		memory;			/* print planner's memory usage information */
 	ExplainFormat format;		/* output format */
 	/* state for output formatting --- not reset for each new plan tree */
 	int			indent;			/* current indentation level */
diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out
index 809655e16e..86bfdfd29e 100644
--- a/src/test/regress/expected/explain.out
+++ b/src/test/regress/expected/explain.out
@@ -326,6 +326,74 @@ select explain_filter('explain (generic_plan) select unique1 from tenk1 where th
 select explain_filter('explain (analyze, generic_plan) select unique1 from tenk1 where thousand = $1');
 ERROR:  EXPLAIN options ANALYZE and GENERIC_PLAN cannot be used together
 CONTEXT:  PL/pgSQL function explain_filter(text) line 5 at FOR over EXECUTE statement
+-- MEMORY option
+select explain_filter('explain (memory) select * from int8_tbl i8');
+                     explain_filter                      
+---------------------------------------------------------
+ Seq Scan on int8_tbl i8  (cost=N.N..N.N rows=N width=N)
+ Planner Memory: used=N bytes allocated=N bytes
+(2 rows)
+
+select explain_filter('explain (memory, analyze) select * from int8_tbl i8');
+                                        explain_filter                                         
+-----------------------------------------------------------------------------------------------
+ Seq Scan on int8_tbl i8  (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
+ Planning Time: N.N ms
+ Planner Memory: used=N bytes allocated=N bytes
+ Execution Time: N.N ms
+(4 rows)
+
+select explain_filter('explain (memory, summary, format yaml) select * from int8_tbl i8');
+        explain_filter         
+-------------------------------
+ - Plan:                      +
+     Node Type: "Seq Scan"    +
+     Parallel Aware: false    +
+     Async Capable: false     +
+     Relation Name: "int8_tbl"+
+     Alias: "i8"              +
+     Startup Cost: N.N        +
+     Total Cost: N.N          +
+     Plan Rows: N             +
+     Plan Width: N            +
+   Planning Time: N.N         +
+   Planner Memory:            +
+     Used: N                  +
+     Allocated: N
+(1 row)
+
+select explain_filter('explain (memory, analyze, format json) select * from int8_tbl i8');
+           explain_filter           
+------------------------------------
+ [                                 +
+   {                               +
+     "Plan": {                     +
+       "Node Type": "Seq Scan",    +
+       "Parallel Aware": false,    +
+       "Async Capable": false,     +
+       "Relation Name": "int8_tbl",+
+       "Alias": "i8",              +
+       "Startup Cost": N.N,        +
+       "Total Cost": N.N,          +
+       "Plan Rows": N,             +
+       "Plan Width": N,            +
+       "Actual Startup Time": N.N, +
+       "Actual Total Time": N.N,   +
+       "Actual Rows": N,           +
+       "Actual Loops": N           +
+     },                            +
+     "Planning Time": N.N,         +
+     "Planner Memory": {           +
+       "Used": N,                  +
+       "Allocated": N              +
+     },                            +
+     "Triggers": [                 +
+     ],                            +
+     "Execution Time": N.N         +
+   }                               +
+ ]
+(1 row)
+
 -- Test EXPLAIN (GENERIC_PLAN) with partition pruning
 -- partitions should be pruned at plan time, based on constants,
 -- but there should be no pruning based on parameter placeholders
diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql
index b6b7beab27..f719e92f84 100644
--- a/src/test/regress/sql/explain.sql
+++ b/src/test/regress/sql/explain.sql
@@ -94,6 +94,12 @@ select explain_filter('explain (generic_plan) select unique1 from tenk1 where th
 -- should fail
 select explain_filter('explain (analyze, generic_plan) select unique1 from tenk1 where thousand = $1');
 
+-- MEMORY option
+select explain_filter('explain (memory) select * from int8_tbl i8');
+select explain_filter('explain (memory, analyze) select * from int8_tbl i8');
+select explain_filter('explain (memory, summary, format yaml) select * from int8_tbl i8');
+select explain_filter('explain (memory, analyze, format json) select * from int8_tbl i8');
+
 -- Test EXPLAIN (GENERIC_PLAN) with partition pruning
 -- partitions should be pruned at plan time, based on constants,
 -- but there should be no pruning based on parameter placeholders
-- 
2.25.1