Material node can report incorrect "Maximum Storage" 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.
This thread has been committed, so CI has stopped here. Anything below is the last result it produced.
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:t254016psql -h localhost -U postgresBuilt from patchset v1 (message #1), October 04, 2026 at 03:46 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 t254016_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 t254016_1 && git checkout t254016_1Patchset v1 (message #1) is on t254016_1
40708acd6 added code to have EXPLAIN ANALYZE show the memory or disk
usage for Material nodes. This isn't quite right as if the material
node is rescanned, tuplestore_end() is called and that will result in
the memory usage for that scan being forgotten. What EXPLAIN reports
is the memory used by the final Material rescan. If that's
significantly less than some other rescan, then that's misleading.
The fix is fairly simple, just use tuplestore_clear() instead of
tuplestore_end(). The existing code seems to handle no longer
NULLifying the tuplestore due to the tuplestore_ateof() check.
The misreporting of the storage likely isn't a big deal. I suspect
rescans of Material nodes are not massively common, so maybe it's not
worth backpatching a fix.
Here's an example query showing the issue (modified regression test query)
EXPLAIN ANALYZE
SELECT count(*) FROM (VALUES(10000),(1)) v1(r1)
LEFT JOIN LATERAL (
SELECT * FROM generate_series(1, 2) gs0
LEFT JOIN LATERAL (
SELECT * FROM generate_series(1, v1.r1) gs1
FULL JOIN generate_series(1, v1.r1) gs4 ON false
) ss on true
) q on true;
The outer Material node reports:
master: Storage: Memory Maximum Storage: 17kB
patched: Storage: Memory Maximum Storage: 725kB
Or you can play around swapping the order of the integers in the
VALUES clause to see that master will report the storage used for the
final value. With the patch the order shouldn't affect the result.
Anyone have any opinions on if this is worthy of fixing in the backbranches?
(Function Scan not supporting mark and restore is another matter of
interest with the resulting plan. I'll look at that separately)
David
40708acd6 added code to have EXPLAIN ANALYZE show the memory or disk
usage for Material nodes.
You mean 1eff8279d?
This isn't quite right as if the material
node is rescanned, tuplestore_end() is called and that will result in
the memory usage for that scan being forgotten. What EXPLAIN reports
is the memory used by the final Material rescan. If that's
significantly less than some other rescan, then that's misleading.The fix is fairly simple, just use tuplestore_clear() instead of
tuplestore_end(). The existing code seems to handle no longer
NULLifying the tuplestore due to the tuplestore_ateof() check.
The patch looks good to me.
The misreporting of the storage likely isn't a big deal. I suspect
rescans of Material nodes are not massively common, so maybe it's not
worth backpatching a fix.
I have no evidence but I feel same as you (rescans of Material nodes
are not massively common). Probably it's not worth backpatching. If we
need backpatching, we could do it later.
Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
Hi David,
I reviewed v1. It applies cleanly to master (3ff475ac12e), and make
check passes.
EXPLAIN ANALYZE
SELECT count(*) FROM (VALUES(10000),(1)) v1(r1)
LEFT JOIN LATERAL (
SELECT * FROM generate_series(1, 2) gs0
LEFT JOIN LATERAL (
SELECT * FROM generate_series(1, v1.r1) gs1
FULL JOIN generate_series(1, v1.r1) gs4 ON false
) ss on true
) q on true;
With your example query, I confirmed that the outer Materialize node
reports the same storage regardless of the order of the VALUES. I also
tried it with work_mem = 64kB, so that the larger rescan spills to disk.
The reported value is again the same regardless of the order, and once
the tuplestore has spilled to disk, it keeps being reported as Disk even
if a later rescan is small. LGTM.
A nit in the commit message: "the repored Material storage" should be
"reported".
The misreporting of the storage likely isn't a big deal. I suspect
rescans of Material nodes are not massively common, so maybe it's not
worth backpatching a fix.I have no evidence but I feel same as you (rescans of Material nodes
are not massively common). Probably it's not worth backpatching. If we
need backpatching, we could do it later.
As for backpatching, I agree with you and Ishii-san.
Table Function Scan has the same problem, since
ExecReScanTableFuncScan() also calls tuplestore_end(), so I've posted a
patch fixing it the same way in a separate thread [1]/messages/by-id/CAHza6qdXjcqXv2aGU7OCakG0OmEsy9-jfP0Gpe2EL+k-AidOhw@mail.gmail.com.
Regards,
Tatsuya Kawata
[1]: /messages/by-id/CAHza6qdXjcqXv2aGU7OCakG0OmEsy9-jfP0Gpe2EL+k-AidOhw@mail.gmail.com
/messages/by-id/CAHza6qdXjcqXv2aGU7OCakG0OmEsy9-jfP0Gpe2EL+k-AidOhw@mail.gmail.com
On Thu, 1 Oct 2026 at 19:49, Tatsuo Ishii <ishii@postgresql.org> wrote:
The patch looks good to me.
The misreporting of the storage likely isn't a big deal. I suspect
rescans of Material nodes are not massively common, so maybe it's not
worth backpatching a fix.I have no evidence but I feel same as you (rescans of Material nodes
are not massively common). Probably it's not worth backpatching. If we
need backpatching, we could do it later.
Thanks for the review. I went with no backpatch for now.
Pushed after addressing the things mentioned by Kawata-san.
David