[PATCH] Add memory/disk usage for Function Scan nodes in EXPLAIN
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253428psql -h localhost -U postgresBuilt from patchset v1 (message #1), September 09, 2026 at 06:05 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253428_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253428_1 && git checkout t253428_1Patchset v1 (message #1) is on t253428_1
Hi,
1eff8279d4, 95d6e9af07 and 40708acd65 added memory/disk usage for
Materialize, WindowAgg, CTE Scan, Table Function Scan and Recursive
Union in EXPLAIN ANALYZE. [1]Discussion for 40708acd65: /messages/by-id/20240918.211246.1127161704188186085.ishii@postgresql.org
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: /messages/by-id/20240918.211246.1127161704188186085.ishii@postgresql.org
/messages/by-id/20240918.211246.1127161704188186085.ishii@postgresql.org
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@gmail.com>
wrote:
Show quoted text
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:
/messages/by-id/20240918.211246.1127161704188186085.ishii@postgresql.org