Re: Add memory/disk usage for WindowAgg nodes in EXPLAIN

>>> Yes, I think so. I'd keep each as a separate patch so they can be
>>> considered independently. Doing all of them should hopefully ensure we
>>> strike the right balance of what code to put in explain.c and what
>>> code to put in tuplestore.c.
>> +1
>>
>> + 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);
>> + }
>>
>> It will be good to move this code to a function which will be called
>> by show_*_info functions().
>
> I have already implemented that in this direction in my working in
> progress patch:

Attached are the v2 patches. As suggested by David, I split them
into multiple patches so that each patch implements the feature for
each node. You need to apply the patches in the order of patch number
(if you want to apply all of them, "git apply v2-*.patch" should
work).

v2-0001-Refactor-show_material_info.patch:
This refactors show_material_info(). The guts are moved to new
show_storage_info() so that it can be shared by not only Materialized
node.

v2-0002-Add-memory-disk-usage-for-CTE-Scan-nodes-in-EXPLA.patch:
This adds memory/disk usage for CTE Scan nodes in EXPLAIN (ANALYZE) command.

v2-0003-Add-memory-disk-usage-for-Table-Function-Scan-nod.patch:
This adds memory/disk usage for Table Function Scan nodes in EXPLAIN (ANALYZE) command.

v2-0004-Add-memory-disk-usage-for-Recursive-Union-nodes-i.patch:
This adds memory/disk usage for Recursive Union nodes in EXPLAIN
(ANALYZE) command. Also show_storage_info() is changed so that it
accepts int64 storage_used, char *storage_type arguments. They are
used if the target node uses multiple tuplestores, in case a simple
call to tuplestore_space_used() does not work. Such executor nodes
need to collect storage_used while running the node. This type of node
includes Recursive Union and Window Aggregate.

v2-0005-Add-memory-disk-usage-for-Window-Aggregate-nodes-.patch: This
adds memory/disk usage for Window Aggregate nodes in EXPLAIN (ANALYZE)
command. Note that if David's proposal
https://www.postgresql.org/message-id/CAHoyFK9n-QCXKTUWT_xxtXninSMEv%2BgbJN66-y6prM3f4WkEHw%40mail.gmail.com
is committed, this will need to be adjusted.

For a demonstration, how storage/memory usage is shown in EXPLAIN
(notice "Storage: Memory Maximum Storage: 120kB" etc.). The script
used is attached (test.sql.txt). The SQLs are shamelessly copied from
David's example and the regression test (some of them were modified by
me).

EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(t1.b) FROM (VALUES(1),(2)) t2(x) LEFT JOIN (SELECT * FROM t1 WHERE a <= 100) t1 ON TRUE;
QUERY PLAN
---------------------------------------------------------------------------------
Aggregate (actual time=0.345..0.346 rows=1 loops=1)
-> Nested Loop Left Join (actual time=0.015..0.330 rows=200 loops=1)
-> Values Scan on "*VALUES*" (actual time=0.001..0.003 rows=2 loops=1)
-> Materialize (actual time=0.006..0.152 rows=100 loops=2)
Storage: Memory Maximum Storage: 120kB
-> Seq Scan on t1 (actual time=0.007..0.213 rows=100 loops=1)
Filter: (a <= 100)
Rows Removed by Filter: 900
Planning Time: 0.202 ms
Execution Time: 0.377 ms
(10 rows)

-- CTE Scan node
EXPLAIN (ANALYZE, COSTS OFF)
WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) OVER() FROM t;
QUERY PLAN
-----------------------------------------------------------------------------------
WindowAgg (actual time=0.151..0.169 rows=100 loops=1)
Storage: Memory Maximum Storage: 20kB
CTE t
-> Recursive Union (actual time=0.001..0.105 rows=100 loops=1)
Storage: Memory Maximum Storage: 17kB
-> Result (actual time=0.001..0.001 rows=1 loops=1)
-> WorkTable Scan on t t_1 (actual time=0.000..0.000 rows=1 loops=100)
Filter: (n < 100)
Rows Removed by Filter: 0
-> CTE Scan on t (actual time=0.002..0.127 rows=100 loops=1)
Storage: Memory Maximum Storage: 20kB
Planning Time: 0.053 ms
Execution Time: 0.192 ms
(13 rows)

-- Table Function Scan node
CREATE OR REPLACE VIEW public.jsonb_table_view6 AS
SELECT js2,
jsb2w,
jsb2q,
ia,
ta,
jba
FROM JSON_TABLE(
'null'::jsonb, '$[*]' AS json_table_path_0
PASSING
1 + 2 AS a,
'"foo"'::json AS "b c"
COLUMNS (
js2 json PATH '$' WITHOUT WRAPPER KEEP QUOTES,
jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER KEEP QUOTES,
jsb2q jsonb PATH '$' WITHOUT WRAPPER OMIT QUOTES,
ia integer[] PATH '$' WITHOUT WRAPPER KEEP QUOTES,
ta text[] PATH '$' WITHOUT WRAPPER KEEP QUOTES,
jba jsonb[] PATH '$' WITHOUT WRAPPER KEEP QUOTES
)
);
CREATE VIEW
EXPLAIN (ANALYZE, COSTS OFF) SELECT * FROM jsonb_table_view6;
QUERY PLAN
-------------------------------------------------------------------------------
Table Function Scan on "json_table" (actual time=0.024..0.025 rows=1 loops=1)
Storage: Memory Maximum Storage: 17kB
Planning Time: 0.100 ms
Execution Time: 0.054 ms
(4 rows)