Date: Sat, 06 Jul 2024 20:22:54 +0900 (JST)
Message-Id: <20240706.202254.89740021795421286.ishii@postgresql.org>
To: pgsql-hackers@postgresql.org
Subject: Add memory/disk usage for WindowAgg nodes in EXPLAIN
From: Tatsuo Ishii <ishii@postgresql.org>
Mime-Version: 1.0
Content-Type: Multipart/Mixed;
 boundary="--Next_Part(Sat_Jul__6_20_22_54_2024_404)--"
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/20240706.202254.89740021795421286.ishii%40postgresql.org>
Precedence: bulk

----Next_Part(Sat_Jul__6_20_22_54_2024_404)--
Content-Type: Text/Plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

1eff8279d4 added memory/disk usage for materialize nodes in EXPLAIN
ANALYZE.
In the commit message:
> There are a few other executor node types that use tuplestores, so we
> could also consider adding these details to the EXPLAIN ANALYZE for
> those nodes too.

So I wanted to Add memory/disk usage for WindowAgg. Patch attached.

Since WindowAgg node could create multiple tuplestore for each Window
partition, we need to track each tuplestore storage usage so that the
maximum storage usage is determined. For this purpose I added new
fields to the WindowAggState.
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

----Next_Part(Sat_Jul__6_20_22_54_2024_404)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="v1-0001-Add-memory-disk-usage-for-WindowAgg-nodes-in-EXPL.patch"

From 00c7546659e305be45dbeb13a14bcde5066be76f Mon Sep 17 00:00:00 2001
From: Tatsuo Ishii <ishii@postgresql.org>
Date: Sat, 6 Jul 2024 19:48:30 +0900
Subject: [PATCH v1] Add memory/disk usage for WindowAgg nodes in EXPLAIN.

---
 src/backend/commands/explain.c       | 37 ++++++++++++++++++++++++++++
 src/backend/executor/nodeWindowAgg.c | 19 ++++++++++++++
 src/include/nodes/execnodes.h        |  2 ++
 3 files changed, 58 insertions(+)

diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 1e80fd8b68..177687ea80 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -126,6 +126,7 @@ static void show_incremental_sort_info(IncrementalSortState *incrsortstate,
 									   ExplainState *es);
 static void show_hash_info(HashState *hashstate, ExplainState *es);
 static void show_material_info(MaterialState *mstate, ExplainState *es);
+static void show_windowagg_info(WindowAggState *winstate, ExplainState *es);
 static void show_memoize_info(MemoizeState *mstate, List *ancestors,
 							  ExplainState *es);
 static void show_hashagg_info(AggState *aggstate, ExplainState *es);
@@ -2215,6 +2216,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 										   planstate, es);
 			show_upper_qual(((WindowAgg *) plan)->runConditionOrig,
 							"Run Condition", planstate, ancestors, es);
+			show_windowagg_info(castNode(WindowAggState, planstate), es);
 			break;
 		case T_Group:
 			show_group_keys(castNode(GroupState, planstate), ancestors, es);
@@ -3362,6 +3364,41 @@ show_material_info(MaterialState *mstate, ExplainState *es)
 	}
 }
 
+/*
+ * Show information on WindowAgg node, storage method and maximum memory/disk
+ * space used.
+ */
+static void
+show_windowagg_info(WindowAggState *winstate, ExplainState *es)
+{
+	const char *storageType;
+	int64		spaceUsedKB;
+
+	/*
+	 * Nothing to show if ANALYZE option wasn't used or if execution didn't
+	 * get as far as creating the tuplestore.
+	 */
+	if (!es->analyze || winstate->storageType == NULL)
+		return;
+
+	storageType = winstate->storageType;
+	spaceUsedKB = BYTES_TO_KILOBYTES(winstate->storageSize);
+
+	if (es->format != EXPLAIN_FORMAT_TEXT)
+	{
+		ExplainPropertyText("Storage", storageType, es);
+		ExplainPropertyInteger("Maximum Storage", "kB", spaceUsedKB, es);
+	}
+	else
+	{
+		ExplainIndentText(es);
+		appendStringInfo(es->str,
+						 "Storage: %s  Maximum Storage: " INT64_FORMAT "kB\n",
+						 storageType,
+						 spaceUsedKB);
+	}
+}
+
 /*
  * Show information on memoize hits/misses/evictions and memory usage.
  */
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 3221fa1522..bcfe144511 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -1360,7 +1360,23 @@ release_partition(WindowAggState *winstate)
 	}
 
 	if (winstate->buffer)
+	{
+		int64	spaceUsed = tuplestore_space_used(winstate->buffer);
+
+		/*
+		 * We want to track the max mem/disk usage so that we can use the info
+		 * in EXPLAIN (ANALYZE).
+		 */
+		if (spaceUsed > winstate->storageSize)
+		{
+			if (winstate->storageType != NULL)
+				pfree(winstate->storageType);
+			winstate->storageType =
+				pstrdup(tuplestore_storage_type_name(winstate->buffer));
+			winstate->storageSize = spaceUsed;
+		}
 		tuplestore_end(winstate->buffer);
+	}
 	winstate->buffer = NULL;
 	winstate->partition_spooled = false;
 }
@@ -2671,6 +2687,9 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
 	winstate->partition_spooled = false;
 	winstate->more_partitions = false;
 
+	winstate->storageType = NULL;
+	winstate->storageSize = 0;
+
 	return winstate;
 }
 
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index b62c96f206..7a3dfa2bc3 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -2567,6 +2567,8 @@ typedef struct WindowAggState
 	ExprState  *partEqfunction; /* equality funcs for partition columns */
 	ExprState  *ordEqfunction;	/* equality funcs for ordering columns */
 	Tuplestorestate *buffer;	/* stores rows of current partition */
+	int64		storageSize;	/* max storage size in buffer */
+	char		*storageType;	/* the storage type above */
 	int			current_ptr;	/* read pointer # for current row */
 	int			framehead_ptr;	/* read pointer # for frame head, if used */
 	int			frametail_ptr;	/* read pointer # for frame tail, if used */
-- 
2.25.1


----Next_Part(Sat_Jul__6_20_22_54_2024_404)----