Re: [PATCH] Add memory/disk usage for Function Scan nodes in EXPLAIN

Hi Tatsuya,

I reviewed v1 of this patch on PostgreSQL 20devel on macOS ARM64.

The patch applied cleanly and built successfully. I also ran the full
regression suite using an out-of-tree build configured with --enable-cassert
--enable-debug; all 243 tests passed in my environment.

I tested the Function Scan storage reporting under several scenarios,
including memory-backed and disk-backed tuplestores by varying work_mem, ROWS
FROM with multiple functions, zero-row execution, WITH ORDINALITY, plans
containing both Sort and Function Scan, and user-defined set-returning
functions.

As a basic disk-backed case, with work_mem = '64kB':

EXPLAIN (ANALYZE)SELECT * FROM generate_series(1,10000);

reported:

Storage: Disk Maximum Storage: 137kB
Buffers: temp read=18 written=18

I also tested a SQL-language SRF whose underlying query performs an
internal ORDER BY. With work_mem = '64kB', the Function Scan reported:

Storage: Disk Maximum Storage: 1758kB
Buffers: shared hit=19, temp read=843 written=879

With the same function and work_mem = '16MB', it reported:

Storage: Memory Maximum Storage: 4150kB

with no temporary-buffer activity reported by the outer EXPLAIN.

The Storage / Maximum Storage reporting behaved consistently across the
cases I tested. I did not find a correctness issue with v1.

Regards,
Shashishekar Hullahally Anantharamu

On Thu, Sep 3, 2026 at 10:21 PM Tatsuya Kawata <kawatatatsuya0913(at)gmail(dot)com>
wrote:

> Hi,
>
> 1eff8279d4, 95d6e9af07 and 40708acd65 added memory/disk usage for
> Materialize, WindowAgg, CTE Scan, Table Function Scan and Recursive
> Union in EXPLAIN ANALYZE. [1]
> So I wanted to add memory/disk usage for Function Scan.
>
>
> ## The patch
>
> It follows the shape of the existing five nodes, so there are only two
> things worth mentioning.
>
> 1. Handling of multiple tuplestores
>
> A FunctionScan uses one tuplestore per function, so there can be more
> than one when ROWS FROM is used. I used the same rule as Recursive
> Union: the storage type of whichever one consumed the most
> memory/disk, and the sum of the sizes of them all.
>
> 2. Moving FunctionScanPerFuncState to execnodes.h
>
> Its definition lives in nodeFunctionscan.c and execnodes.h only has a
> forward declaration, so explain.c cannot reach funcstates[i].tstore.
> The state structs of the other five nodes are all in execnodes.h, so
> I moved this one there too.
>
>
> ## Behavior
>
> Measured with work_mem = 64kB. On its own, 1000 rows gives
> "Memory 56kB" and 500000 rows gives "Disk 6836kB".
>
> -- two identical functions: exactly twice the single-function figure
> SELECT count(*) FROM ROWS FROM (generate_series(1,500000),
> generate_series(1,500000)) g;
> Storage: Disk Maximum Storage: 13672kB
>
> -- a small one and a large one: type from the larger, size is the sum
> SELECT count(*) FROM ROWS FROM (generate_series(1,10),
> generate_series(1,500000)) g;
> Storage: Disk Maximum Storage: 6853kB
>
>
> ## What is reported when loops > 1
>
> The Storage line follows the same policy as the Sort Method line of
> Sort, that is, it reports the peak recorded by whichever object is still
> around at EXPLAIN time. The statistics live inside the Tuplestorestate
> (or Tuplesortstate) and are lost along with it when rescan calls end().
> ExecReScanFunctionScan() has the same shape as
> ExecReScanTableFuncScan(), and on master both Sort and Table Function
> Scan already change what they report if you reorder the rows.
> When loops is 1 the value is of course exact.
>
> I could not find a settled policy for how this kind of per-node resource
> statistic should be aggregated when loops > 1. So this patch follows
> Sort. If the consensus is that the maximum across all loops should be
> reported instead, that would be a separate change spanning Sort,
> Incremental Sort, Material, Table Function Scan and Function Scan, and
> I would be happy to work on it separately.
>
>
> make check passes all 245 tests.
> Patch attached.
>
> Regards,
> Tatsuya Kawata
>
> [1] Discussion for 40708acd65:
> https://postgr.es/m/20240918.211246.1127161704188186085.ishii%40postgresql.org
>
>