Report the peak total storage of Recursive Union nodes in EXPLAIN

From: Tatsuya Kawata <kawatatatsuya0913(at)gmail(dot)com>
To: Postgres hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Cc: David Rowley <dgrowleyml(at)gmail(dot)com>
Subject: Report the peak total storage of Recursive Union nodes in EXPLAIN
Date: 2026-11-18 15:41:27
Message-ID: CAHza6qf8KrwCitg35hFmJr_CdxO5JOtNzqrP=dhj0nQnT=GKyw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

In the thread for edbb1d2dcb1 (storage reporting for Function Scan in
EXPLAIN), David pointed out that with multiple functions in ROWS FROM,
summing each tuplestore's maximum isn't accurate, and that we should instead
report the maximum of the total space in use at the same time [1]. I
noticed
that Recursive Union has the same kind of problem: peaks from different
iterations get added together. The attached patch fixes that.

=== The patch

Unlike Function Scan, Recursive Union doesn't recreate its tuplestores; it
clears and reuses them. So tuplestore_get_stats() can't tell us how much
each tuplestore used in a given iteration.

The patch adds tuplestore_get_stats_since_clear(). Recursive Union uses it
to take the total of its two tuplestores just before they are cleared, and
keeps the maximum. As with Function Scan, the storage type is shown as Disk
if either tuplestore ever spilled.

I considered adding a parameter to tuplestore_get_stats() to also return the
values since the last clear, but went with a separate function since that
seemed clearer for callers. Also, as Recursive Union is never parallel, the
statistics are kept as two fields in the node's state rather than in a
separate struct.

=== Example

With the following query, master reports 220kB because it adds up each
tuplestore's maximum, while the patched version reports 126kB.

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
WITH RECURSIVE t(l, n) AS (
SELECT 0, generate_series(1, 2000)
UNION ALL
SELECT t.l + 1, s.m
FROM t,
LATERAL generate_series(1, CASE WHEN t.l = 2 THEN 2000 ELSE 1 END) s(m)
WHERE (t.l = 0 AND t.n = 1) OR t.l IN (1, 2))
SELECT count(*), max(l) FROM t;

master: Storage: Memory Maximum Storage: 220kB
patched: Storage: Memory Maximum Storage: 126kB

Regards,
Tatsuya Kawata

[1]
https://www.postgresql.org/message-id/CAApHDvqj%2B_5kK%2BuF5qosD0m%2B8zkhubyaXGSntpsKLvyrLkM3tQ%40mail.gmail.com

Attachment Content-Type Size
v1-0001-Report-the-peak-total-storage-of-Recursive-Union-.patch application/octet-stream 8.9 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Zhijie Hou 2026-11-18 15:41:52 Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
Previous Message Heikki Linnakangas 2026-11-18 15:16:40 Re: Implicit shell type creation, little fixes