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

> Thanks. I have added it and pushed the patch.

So I have created patches to do the same for CTE scan and table
function scan node. Patch attached.

Actually there's one more executor node type that uses tuplestore:
recursive union (used in "with recursive"). The particular node type
uses two tuplestore and we cannot simply apply tuplestore_get_stats()
to the node type. We need to modify RecursiveUnionState to track the
maximum tuplestore usage. I am not sure this would be worth the
effort. Opinion?

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
From e2c8d0257d4b0e11a9ef9cfe632d8ea1806a08b7 Mon Sep 17 00:00:00 2001
From: Tatsuo Ishii <ishii@postgresql.org>
Date: Wed, 18 Sep 2024 20:54:58 +0900
Subject: [PATCH v1] Add memory/disk usage for CTE scan and table function scan
 nodes in EXPLAIN.

This commit is similar to 95d6e9af07, expanding the idea to CTE scan
and table function scan nodes so that the maximum tuplestore memory
or disk usage is shown with EXPLAIN ANALYZE command.
---
 src/backend/commands/explain.c        | 35 +++++++++++++++++++++++++++
 src/test/regress/expected/explain.out | 20 +++++++++++++++
 src/test/regress/sql/explain.sql      |  4 +++
 3 files changed, 59 insertions(+)

diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index aaec439892..34a23428c3 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -129,6 +129,8 @@ static void show_incremental_sort_info(IncrementalSortState *incrsortstate,
 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_ctescan_info(CteScanState *ctescanstate, ExplainState *es);
+static void show_table_func_scan_info(TableFuncScanState *tscanstate, ExplainState *es);
 static void show_memoize_info(MemoizeState *mstate, List *ancestors,
                               ExplainState *es);
 static void show_hashagg_info(AggState *aggstate, ExplainState *es);
@@ -2046,6 +2048,8 @@ ExplainNode(PlanState *planstate, List *ancestors,
             if (plan->qual)
                 show_instrumentation_count("Rows Removed by Filter", 1,
                                            planstate, es);
+            if (IsA(plan, CteScan))
+                show_ctescan_info(castNode(CteScanState, planstate), es);
             break;
         case T_Gather:
             {
@@ -2127,6 +2131,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
             if (plan->qual)
                 show_instrumentation_count("Rows Removed by Filter", 1,
                                            planstate, es);
+            show_table_func_scan_info(castNode(TableFuncScanState, planstate), es);
             break;
         case T_TidScan:
             {
@@ -3411,6 +3416,36 @@ show_windowagg_info(WindowAggState *winstate, ExplainState *es)
     show_storage_info(tupstore, es);
 }
 
+/*
+ * Show information on CTE Scan node, storage method and maximum memory/disk
+ * space used.
+ */
+static void
+show_ctescan_info(CteScanState *ctescanstate, ExplainState *es)
+{
+    Tuplestorestate *tupstore = ctescanstate->leader->cte_table;
+
+    if (!es->analyze || tupstore == NULL)
+        return;
+
+    show_storage_info(tupstore, es);
+}
+
+/*
+ * Show information on Table Function Scan node, storage method and maximum
+ * memory/disk space used.
+ */
+static void
+show_table_func_scan_info(TableFuncScanState *tscanstate, ExplainState *es)
+{
+    Tuplestorestate *tupstore = tscanstate->tupstore;
+
+    if (!es->analyze || tupstore == NULL)
+        return;
+
+    show_storage_info(tupstore, 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 d01c304c24..b64e64bb43 100644
--- a/src/test/regress/expected/explain.out
+++ b/src/test/regress/expected/explain.out
@@ -729,3 +729,23 @@ select explain_filter('explain (analyze,costs off) select sum(n) over(partition
 (8 rows)
 
 reset work_mem;
+-- Test tuplestore storage usage in CTE scan
+select explain_filter('explain (analyze,costs off) with w(n) as (select n from generate_series(1,10) a(n)) select
sum(n)from w');
 
+                                 explain_filter                                 
+--------------------------------------------------------------------------------
+ Aggregate (actual time=N.N..N.N rows=N loops=N)
+   ->  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
+(4 rows)
+
+-- Test tuplestore storage usage in table function scan
+select explain_filter('explain (analyze,costs off) select * from json_table(''[]'', ''strict $.a'' columns (i int path
''$''))');
+                              explain_filter                               
+---------------------------------------------------------------------------
+ Table Function Scan on "json_table" (actual time=N.N..N.N rows=N loops=N)
+   Storage: Memory  Maximum Storage: NkB
+ Planning Time: N.N ms
+ Execution Time: N.N ms
+(4 rows)
+
diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql
index b861e2b53d..0623440124 100644
--- a/src/test/regress/sql/explain.sql
+++ b/src/test/regress/sql/explain.sql
@@ -178,3 +178,7 @@ select explain_filter('explain (analyze,costs off) select sum(n) over() from gen
 -- 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))');
 
 reset work_mem;
+-- Test tuplestore storage usage in CTE scan
+select explain_filter('explain (analyze,costs off) with w(n) as (select n from generate_series(1,10) a(n)) select
sum(n)from w');
 
+-- Test tuplestore storage usage in table function scan
+select explain_filter('explain (analyze,costs off) select * from json_table(''[]'', ''strict $.a'' columns (i int path
''$''))');
-- 
2.25.1