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

> Not sure if there's any way to force it in the SQL standard.  However
> in term of implementation, PostgreSQL sorts the function
> (generate_series) scan result using a sort key "a.n < 3", which
> results in rows being >= 2 first (as false == 0), then rows being < 3
> (as true == 1). So unless PostgreSQL changes the way to sort boolean
> data type, I think the result should be stable.

Attached is the v5 patch. The difference from v4 is addtion of two
more tests to explain.sql:

1) spils to disk case
2) splis to disk then switch back to memory case

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
From 23d5d62ef3c08117a0e452a57264477a3f9aba08 Mon Sep 17 00:00:00 2001
From: Tatsuo Ishii <ishii@postgresql.org>
Date: Tue, 17 Sep 2024 10:59:07 +0900
Subject: [PATCH v5] 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        | 68 ++++++++++++++++++++-------
 src/test/regress/expected/explain.out | 37 +++++++++++++++
 src/test/regress/sql/explain.sql      |  8 ++++
 3 files changed, 95 insertions(+), 18 deletions(-)

diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 2819e479f8..aaec439892 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -120,6 +120,7 @@ static void show_sort_group_keys(PlanState *planstate, const char *qlabel,
                                  List *ancestors, ExplainState *es);
 static void show_sortorder_options(StringInfo buf, Node *sortexpr,
                                    Oid sortOperator, Oid collation, bool nullsFirst);
+static void show_storage_info(Tuplestorestate *tupstore, ExplainState *es);
 static void show_tablesample(TableSampleClause *tsc, PlanState *planstate,
                              List *ancestors, ExplainState *es);
 static void show_sort_info(SortState *sortstate, ExplainState *es);
@@ -127,6 +128,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);
@@ -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);
@@ -2894,6 +2897,34 @@ show_sortorder_options(StringInfo buf, Node *sortexpr,
     }
 }
 
+/*
+ * Show information on storage method and maximum memory/disk space used.
+ */
+static void
+show_storage_info(Tuplestorestate *tupstore, ExplainState *es)
+{
+    char       *maxStorageType;
+    int64        maxSpaceUsed,
+                maxSpaceUsedKB;
+
+    tuplestore_get_stats(tupstore, &maxStorageType, &maxSpaceUsed);
+    maxSpaceUsedKB = BYTES_TO_KILOBYTES(maxSpaceUsed);
+
+    if (es->format != EXPLAIN_FORMAT_TEXT)
+    {
+        ExplainPropertyText("Storage", maxStorageType, es);
+        ExplainPropertyInteger("Maximum Storage", "kB", maxSpaceUsedKB, es);
+    }
+    else
+    {
+        ExplainIndentText(es);
+        appendStringInfo(es->str,
+                         "Storage: %s  Maximum Storage: " INT64_FORMAT "kB\n",
+                         maxStorageType,
+                         maxSpaceUsedKB);
+    }
+}
+
 /*
  * Show TABLESAMPLE properties
  */
@@ -3350,9 +3381,6 @@ static void
 show_material_info(MaterialState *mstate, ExplainState *es)
 {
     Tuplestorestate *tupstore = mstate->tuplestorestate;
-    char       *maxStorageType;
-    int64        maxSpaceUsed,
-                maxSpaceUsedKB;
 
     /*
      * Nothing to show if ANALYZE option wasn't used or if execution didn't
@@ -3361,22 +3389,26 @@ show_material_info(MaterialState *mstate, ExplainState *es)
     if (!es->analyze || tupstore == NULL)
         return;
 
-    tuplestore_get_stats(tupstore, &maxStorageType, &maxSpaceUsed);
-    maxSpaceUsedKB = BYTES_TO_KILOBYTES(maxSpaceUsed);
+    show_storage_info(tupstore, es);
+}
 
-    if (es->format != EXPLAIN_FORMAT_TEXT)
-    {
-        ExplainPropertyText("Storage", maxStorageType, es);
-        ExplainPropertyInteger("Maximum Storage", "kB", maxSpaceUsedKB, es);
-    }
-    else
-    {
-        ExplainIndentText(es);
-        appendStringInfo(es->str,
-                         "Storage: %s  Maximum Storage: " INT64_FORMAT "kB\n",
-                         maxStorageType,
-                         maxSpaceUsedKB);
-    }
+/*
+ * Show information on WindowAgg node, storage method and maximum memory/disk
+ * space used.
+ */
+static void
+show_windowagg_info(WindowAggState *winstate, ExplainState *es)
+{
+    Tuplestorestate *tupstore = winstate->buffer;
+
+    /*
+     * Nothing to show if ANALYZE option wasn't used or if execution didn't
+     * get as far as creating the tuplestore.
+     */
+    if (!es->analyze || tupstore == NULL)
+        return;
+
+    show_storage_info(tupstore, es);
 }
 
 /*
diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out
index 6585c6a69e..a9ead5b36f 100644
--- a/src/test/regress/expected/explain.out
+++ b/src/test/regress/expected/explain.out
@@ -691,3 +691,40 @@ 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 (memory case)
+select explain_filter('explain (analyze,costs off) select sum(n) over() from generate_series(1,10) a(n)');
+                                 explain_filter                                 
+--------------------------------------------------------------------------------
+ WindowAgg (actual time=N.N..N.N rows=N loops=N)
+   Storage: Memory  Maximum Storage: NkB
+   ->  Function Scan on generate_series a (actual time=N.N..N.N rows=N loops=N)
+ Planning Time: N.N ms
+ Execution Time: N.N ms
+(5 rows)
+
+-- Test tuplestore storage usage in Window aggregate (disk case)
+set work_mem to 64;
+select explain_filter('explain (analyze,costs off) select sum(n) over() from generate_series(1,2000) a(n)');
+                                 explain_filter                                 
+--------------------------------------------------------------------------------
+ WindowAgg (actual time=N.N..N.N rows=N loops=N)
+   Storage: Disk  Maximum Storage: NkB
+   ->  Function Scan on generate_series a (actual time=N.N..N.N rows=N loops=N)
+ Planning Time: N.N ms
+ Execution Time: N.N ms
+(5 rows)
+
+-- Test tuplestore storage usage in Window aggregate (memory and disk case, final result is disk)
+select explain_filter('explain (analyze,costs off) select sum(n) over(partition by m) from (SELECT n < 3 as m, n from
generate_series(1,2000)a(n))');
 
+                                    explain_filter                                    
+--------------------------------------------------------------------------------------
+ WindowAgg (actual time=N.N..N.N rows=N loops=N)
+   Storage: Disk  Maximum Storage: NkB
+   ->  Sort (actual time=N.N..N.N rows=N loops=N)
+         Sort Key: ((a.n < N))
+         Sort Method: external merge  Disk: NkB
+         ->  Function Scan on generate_series a (actual time=N.N..N.N rows=N loops=N)
+ Planning Time: N.N ms
+ Execution Time: N.N ms
+(8 rows)
+
diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql
index c7055f850c..54ecfcba63 100644
--- a/src/test/regress/sql/explain.sql
+++ b/src/test/regress/sql/explain.sql
@@ -169,3 +169,11 @@ 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 (memory case)
+select explain_filter('explain (analyze,costs off) select sum(n) over() from generate_series(1,10) a(n)');
+-- Test tuplestore storage usage in Window aggregate (disk case)
+set work_mem to 64;
+select explain_filter('explain (analyze,costs off) select sum(n) over() from generate_series(1,2000) a(n)');
+-- Test tuplestore storage usage in Window aggregate (memory and disk case, final result is disk)
+select explain_filter('explain (analyze,costs off) select sum(n) over(partition by m) from (SELECT n < 3 as m, n from
generate_series(1,2000)a(n))');
 
-- 
2.25.1