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

> I looked at this and thought that one thing you might want to consider
> is adjusting show_storage_info() to accept the size and type
> parameters so you don't have to duplicate the formatting code in
> show_recursive_union_info().

I agree and made necessary changes. See attached v4 patches.

> The first of the new tests also isn't testing what you want it to
> test. Maybe you could add a "materialized" in there to stop the CTE
> being inlined:
> 
> explain (analyze,costs off) with w(n) as materialized (select n from
> generate_series(1,10) a(n)) select sum(n) from w
> 
> Also, I'm on the fence about if the new tests are worthwhile. I won't
> object to them, however. I just wanted to note that most of the
> complexity is in tuplestore.c of which there's already coverage for.
> The test's value is reduced by the fact that most of the interesting
> details have to be masked out due to possible platform variations in
> the number of bytes. Really the new tests are only testing that we
> display the storage details and maybe that the storage type came out
> as expected. It seems unlikely these would get broken. I'd say it's
> committers preference, however. I just wanted to add my thoughts. You
> have to offset the value against the fact that the expected output is
> likely to change over the years which adds to the burden of making
> changes to the EXPLAIN output.

After thinking more, I lean toward to your opinion. The new tests do
not give big value, but on the other hand they could become a burden
over the years. I do not include the new tests in the v4 patches.

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
From b94c524623a27f526f03de5e90564b185e37ffdd Mon Sep 17 00:00:00 2001
From: Tatsuo Ishii <ishii@postgresql.org>
Date: Mon, 23 Sep 2024 14:57:39 +0900
Subject: [PATCH v4] Add memory/disk usage for more executor nodes.

This commit is similar to 95d6e9af07, expanding the idea to CTE scan,
table function scan and recursive union scan nodes so that the maximum
tuplestore memory or disk usage is shown with EXPLAIN ANALYZE command.

Also adjust show_storage_info() so that it accepts storage type and
storage size arguments instead of Tuplestorestate. This makes it
possible for the node types to share the formatting code in
show_storage_info(). Due to this show_material_info() and
show_windowagg_info() are also modified.
---
 src/backend/commands/explain.c | 107 ++++++++++++++++++++++++++++++---
 1 file changed, 97 insertions(+), 10 deletions(-)

diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index aaec439892..ee1bcb84e2 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -120,7 +120,8 @@ 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_storage_info(char *maxStorageType, int64 maxSpaceUsed,
+                              ExplainState *es);
 static void show_tablesample(TableSampleClause *tsc, PlanState *planstate,
                              List *ancestors, ExplainState *es);
 static void show_sort_info(SortState *sortstate, ExplainState *es);
@@ -129,6 +130,11 @@ 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_recursive_union_info(RecursiveUnionState *rstate,
+                                      ExplainState *es);
 static void show_memoize_info(MemoizeState *mstate, List *ancestors,
                               ExplainState *es);
 static void show_hashagg_info(AggState *aggstate, ExplainState *es);
@@ -2046,6 +2052,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 +2135,8 @@ 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:
             {
@@ -2278,6 +2288,10 @@ ExplainNode(PlanState *planstate, List *ancestors,
             show_memoize_info(castNode(MemoizeState, planstate), ancestors,
                               es);
             break;
+        case T_RecursiveUnion:
+            show_recursive_union_info(castNode(RecursiveUnionState,
+                                               planstate), es);
+            break;
         default:
             break;
     }
@@ -2901,14 +2915,9 @@ 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)
+show_storage_info(char *maxStorageType, int64 maxSpaceUsed, ExplainState *es)
 {
-    char       *maxStorageType;
-    int64        maxSpaceUsed,
-                maxSpaceUsedKB;
-
-    tuplestore_get_stats(tupstore, &maxStorageType, &maxSpaceUsed);
-    maxSpaceUsedKB = BYTES_TO_KILOBYTES(maxSpaceUsed);
+    int64        maxSpaceUsedKB = BYTES_TO_KILOBYTES(maxSpaceUsed);
 
     if (es->format != EXPLAIN_FORMAT_TEXT)
     {
@@ -3380,6 +3389,9 @@ show_hash_info(HashState *hashstate, ExplainState *es)
 static void
 show_material_info(MaterialState *mstate, ExplainState *es)
 {
+    char       *maxStorageType;
+    int64        maxSpaceUsed;
+
     Tuplestorestate *tupstore = mstate->tuplestorestate;
 
     /*
@@ -3389,7 +3401,8 @@ show_material_info(MaterialState *mstate, ExplainState *es)
     if (!es->analyze || tupstore == NULL)
         return;
 
-    show_storage_info(tupstore, es);
+    tuplestore_get_stats(tupstore, &maxStorageType, &maxSpaceUsed);
+    show_storage_info(maxStorageType, maxSpaceUsed, es);
 }
 
 /*
@@ -3399,6 +3412,9 @@ show_material_info(MaterialState *mstate, ExplainState *es)
 static void
 show_windowagg_info(WindowAggState *winstate, ExplainState *es)
 {
+    char       *maxStorageType;
+    int64        maxSpaceUsed;
+
     Tuplestorestate *tupstore = winstate->buffer;
 
     /*
@@ -3408,7 +3424,78 @@ show_windowagg_info(WindowAggState *winstate, ExplainState *es)
     if (!es->analyze || tupstore == NULL)
         return;
 
-    show_storage_info(tupstore, es);
+    tuplestore_get_stats(tupstore, &maxStorageType, &maxSpaceUsed);
+    show_storage_info(maxStorageType, maxSpaceUsed, es);
+}
+
+/*
+ * Show information on CTE Scan node, storage method and maximum memory/disk
+ * space used.
+ */
+static void
+show_ctescan_info(CteScanState *ctescanstate, ExplainState *es)
+{
+    char       *maxStorageType;
+    int64        maxSpaceUsed;
+
+    Tuplestorestate *tupstore = ctescanstate->leader->cte_table;
+
+    if (!es->analyze || tupstore == NULL)
+        return;
+
+    tuplestore_get_stats(tupstore, &maxStorageType, &maxSpaceUsed);
+    show_storage_info(maxStorageType, maxSpaceUsed, 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)
+{
+    char       *maxStorageType;
+    int64        maxSpaceUsed;
+
+    Tuplestorestate *tupstore = tscanstate->tupstore;
+
+    if (!es->analyze || tupstore == NULL)
+        return;
+
+    tuplestore_get_stats(tupstore, &maxStorageType, &maxSpaceUsed);
+    show_storage_info(maxStorageType, maxSpaceUsed, es);
+}
+
+/*
+ * Show information on Recursive Union node, storage method and maximum
+ * memory/disk space used.
+ */
+static void
+show_recursive_union_info(RecursiveUnionState *rstate, ExplainState *es)
+{
+    char       *maxStorageType,
+               *tempStorageType;
+    int64        maxSpaceUsed,
+                tempSpaceUsed;
+
+    if (!es->analyze)
+        return;
+
+    /*
+     * Recursive union node uses two tuplestores.  We employ the storage type
+     * from one of them which consumed more memory/disk than the other.  The
+     * storage size is sum of the two.
+     */
+    tuplestore_get_stats(rstate->working_table, &tempStorageType,
+                         &tempSpaceUsed);
+    tuplestore_get_stats(rstate->intermediate_table, &maxStorageType,
+                         &maxSpaceUsed);
+
+    if (tempSpaceUsed > maxSpaceUsed)
+        maxStorageType = tempStorageType;
+
+    maxSpaceUsed += tempSpaceUsed;
+    show_storage_info(maxStorageType, maxSpaceUsed, es);
 }
 
 /*
-- 
2.25.1