Re: Add memory/disk usage for WindowAgg nodes in EXPLAIN - Mailing list pgsql-hackers

Here is the v3 patch. This time I only include patch for the Window
Aggregate node. Patches for other node types will come after this
patch getting committed or come close to commitable state.

David,
In this patch I refactored show_material_info. I divided it into
show_material_info and show_storage_info so that the latter can be
used by other node types including window aggregate node. What do you
think?

I also added a test case in explain.sql per discussion with Maxim
Orlov.

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
From 74ab7724bfdb49dc111eca5d96abed9cd29abdc7 Mon Sep 17 00:00:00 2001
From: Tatsuo Ishii <ishii@postgresql.org>
Date: Thu, 12 Sep 2024 16:15:43 +0900
Subject: [PATCH v3] Add memory/disk usage for Window aggregate nodes in
 EXPLAIN.

This commit is similar to 1eff8279d and expands the idea to Window
aggregate nodes so that users can know how much memory or disk the
tuplestore used.

This commit uses newly introduced tuplestore_get_stats() to query this
information and add some additional output in EXPLAIN ANALYZE to
display the information for the Window aggregate node.
---
 src/backend/commands/explain.c        | 31 +++++++++++++++++++++++----
 src/test/regress/expected/explain.out | 11 ++++++++++
 src/test/regress/sql/explain.sql      |  3 +++
 3 files changed, 41 insertions(+), 4 deletions(-)

diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 2819e479f8..ce6792be8a 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -126,7 +126,9 @@ static void show_sort_info(SortState *sortstate, ExplainState *es);
 static void show_incremental_sort_info(IncrementalSortState *incrsortstate,
                                        ExplainState *es);
 static void show_hash_info(HashState *hashstate, ExplainState *es);
+static void show_storage_info(Tuplestorestate *tupstore, 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);
@@ -2231,6 +2233,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);
@@ -3343,13 +3346,11 @@ show_hash_info(HashState *hashstate, ExplainState *es)
 }
 
 /*
- * Show information on material node, storage method and maximum memory/disk
- * space used.
+ * Show information on storage method and maximum memory/disk space used.
  */
 static void
-show_material_info(MaterialState *mstate, ExplainState *es)
+show_storage_info(Tuplestorestate *tupstore, ExplainState *es)
 {
-    Tuplestorestate *tupstore = mstate->tuplestorestate;
     char       *maxStorageType;
     int64        maxSpaceUsed,
                 maxSpaceUsedKB;
@@ -3379,6 +3380,28 @@ show_material_info(MaterialState *mstate, ExplainState *es)
     }
 }
 
+/*
+ * Show information on material node, storage method and maximum memory/disk
+ * space used.
+ */
+static void
+show_material_info(MaterialState *mstate, ExplainState *es)
+{
+    Tuplestorestate *tupstore = mstate->tuplestorestate;
+
+    show_storage_info(tupstore, es);
+}
+
+/*
+ * Show information on WindowAgg node, storage method and maximum memory/disk
+ * space used.
+ */
+static void
+show_windowagg_info(WindowAggState *winstate, ExplainState *es)
+{
+    show_storage_info(winstate->buffer, es);
+}
+
 /*
  * Show information on memoize hits/misses/evictions and memory usage.
  */
diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out
index 6585c6a69e..d27fbdfebb 100644
--- a/src/test/regress/expected/explain.out
+++ b/src/test/regress/expected/explain.out
@@ -691,3 +691,14 @@ select explain_filter('explain (analyze,serialize) create temp table explain_tem
  Execution Time: N.N ms
 (4 rows)
 
+-- Test tuplestore storage usage in Window aggregate
+select explain_filter('explain (analyze) select sum(n) over() from generate_series(1,100) a(n)');
+                                                 explain_filter                                                 
+----------------------------------------------------------------------------------------------------------------
+ WindowAgg  (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
+   Storage: Memory  Maximum Storage: NkB
+   ->  Function Scan on generate_series a  (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
+ Planning Time: N.N ms
+ Execution Time: N.N ms
+(5 rows)
+
diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql
index c7055f850c..50eaf554eb 100644
--- a/src/test/regress/sql/explain.sql
+++ b/src/test/regress/sql/explain.sql
@@ -169,3 +169,6 @@ select explain_filter('explain (analyze,serialize text,buffers,timing off) selec
 select explain_filter('explain (analyze,serialize binary,buffers,timing) select * from int8_tbl i8');
 -- this tests an edge case where we have no data to return
 select explain_filter('explain (analyze,serialize) create temp table explain_temp as select * from int8_tbl i8');
+
+-- Test tuplestore storage usage in Window aggregate
+select explain_filter('explain (analyze) select sum(n) over() from generate_series(1,100) a(n)');
-- 
2.25.1