pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19759: Hashed SetOp / RecursiveUnion pre-allocate up to 64 GB from the row estimate since 8f29467c57f
6+ messages / 4 participants
[nested] [flat]
Commits from this thread:
2026-10-27 34d52e6d3d 747dbf30d1 Reintroduce hash_mem cap on buckets in BuildTupleHashTable
* BUG #19759: Hashed SetOp / RecursiveUnion pre-allocate up to 64 GB from the row estimate since 8f29467c57f
@ 2026-10-15 13:44 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 3 replies; 6+ messages in thread
From: PG Bug reporting form @ 2026-10-15 13:44 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: alpkeles99@gmail.com
The following bug has been logged on the website:
Bug reference: 19759
Logged by: Alperen Keles
Email address: alpkeles99@gmail.com
PostgreSQL version: 19beta4
Operating system: Ubuntu 24.04 x86_64 (also macOS 26 arm64)
Description:
Since commit 8f29467c57f ("Change "long" numGroups fields to be Cardinality
(i.e., double)", 2025-11-02), BuildTupleHashTable() no longer limits the
initial size of a TupleHashTable to hash_mem. Its new comment says that
staying within hash_mem is the caller's job. But nodeRecursiveunion.c and
nodeSetOp.c pass the planner's raw group estimate, so the executor now
allocates and zeroes a bucket array of up to 2^32 entries x 16 bytes =
64 GB in ExecInit, before reading a single row, whenever the estimate is
large. PostgreSQL 18 is not affected (it capped nbuckets at
get_hash_memory_limit() / entrysize). The commit is in REL_19_BETA1
through REL_19_RC1.
Two ways to reach it:
1. Recursive UNION, default settings. It can only be hashed, and
generate_recursion_path() estimates its groups as lrows + 10 * rrows,
so an inflated estimate goes straight into the allocation. With an
empty, never-analyzed table:
CREATE TABLE t0(c0 int4);
WITH RECURSIVE r(w, x, y, z) AS (
SELECT a.c0, b.c0, c.c0, d.c0 FROM t0 a, t0 b, t0 c, t0 d
UNION
SELECT w, x, y, z FROM r WHERE false)
SELECT count(*) FROM r;
On master (49103c3cd63, Ubuntu 24.04, x86_64, the server under
ulimit -v 6 GB):
ERROR: 53200: out of memory
DETAIL: Failed on request of size 68719476736 in memory context
"ExecutorState".
LOCATION: MemoryContextAllocationFailure, mcxt.c:1208
Without a memory limit the backend simply allocates it: on macOS its
RSS passed 3 GB within a few milliseconds. On 18.6 the query returns 0
at once.
An accurate estimate is enough to see the difference, without any
tables:
WITH RECURSIVE r(n) AS (
SELECT g FROM generate_series(1, 1000000) g
UNION SELECT n + 1 FROM r WHERE n < 0)
SELECT count(*) FROM r;
Peak backend RSS: 1086 MB on master, 115 MB on 18.6 (same result).
With 10 million rows master allocates 8 GB up front, while 18.6 peaks
at about 1 GB.
2. Hashed set operations whose table the planner itself expects to
exceed hash_mem. create_setop_path() only marks such a path disabled,
so it is chosen when the sorted alternative is disabled even more,
e.g. with enable_sort = off:
BEGIN;
SET LOCAL enable_sort = off;
SELECT a.c0, b.c0, c.c0, d.c0 FROM t0 a, t0 b, t0 c, t0 d
EXCEPT SELECT 1, 2, 3, 4;
ROLLBACK;
EXPLAIN shows "HashSetOp Except ... rows=1600000000 ... Disabled: true".
On master it tries to allocate 32 GB (2^31 buckets) and fails or
exhausts memory; on 18.6 it returns no rows in about 2 ms. EXCEPT ALL,
INTERSECT and INTERSECT ALL behave the same.
Versions tested:
- master 158b6d5109f (macOS 26, arm64, Apple clang 21) and 49103c3cd63
(Ubuntu 24.04, x86_64, gcc 13.3), both --enable-cassert --enable-debug
- REL_18_STABLE a85b5523e67 (18.6, macOS): not affected
Rechecked on 2026-10-15 (Ubuntu 24.04, x86_64, gcc 13.3, --enable-cassert
--enable-debug, server under ulimit -v 6 GB) on 19rc1
(REL_19_STABLE 0fb192f1b02), current master aa7b9cf132b and
REL_18_STABLE 4d62ef92fda (18.6): on 19rc1 and master the three queries
above fail with requests of 34359738368, 68719476736 and 8589934592
bytes, and the 1M-row recursive UNION peaks at 1088 MB; on 18.6 all
three succeed and that query peaks at 103 MB.
Found with a query fuzzer.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19759: Hashed SetOp / RecursiveUnion pre-allocate up to 64 GB from the row estimate since 8f29467c57f
@ 2026-10-15 14:19 Sebastian Webber <sebastian@swebber.me>
parent: PG Bug reporting form <noreply@postgresql.org>
2 siblings, 0 replies; 6+ messages in thread
From: Sebastian Webber @ 2026-10-15 14:19 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: alpkeles99@gmail.com
Hi Alperen,
On Thu, 15 Oct 2026 at 13:44, PG Bug reporting form
<noreply@postgresql.org> wrote:
> Since commit 8f29467c57f ("Change "long" numGroups fields to be
> Cardinality (i.e., double)", 2025-11-02), BuildTupleHashTable() no
> longer limits the initial size of a TupleHashTable to hash_mem.
Confirmed on master aa7b9cf132b and REL_19_STABLE 0fb192f1b02 (19rc1),
meson debug build with cassert, Debian trixie, server under
ulimit -v 6 GB. The recursive UNION asks for 68719476736 bytes and the
EXCEPT with enable_sort = off for 34359738368. The 1M-row recursive
UNION peaks at 1094 MB, against 33 MB for the same query with UNION
ALL. The official 17.11 and 18.6 images run all of them fine.
On macOS 26.6 arm64 (native autoconf debug builds, no ulimit since
macOS ignores it) master grows past 4 GB on the same queries before I
kill the backend. The 10M-row recursive UNION peaks at 8599 MB on
master and 977 MB on REL_18_STABLE.
Two more ways to reach it:
1. Plain EXPLAIN, without ANALYZE, fails the same way, since the table
is built in ExecInitSetOp and ExecInitRecursiveUnion. nodeAgg skips
it under EXEC_FLAG_EXPLAIN_ONLY since 2cccb627f10.
2. A column type that can be hashed but not sorted leaves HashSetOp as
the only plan, so no setting change is needed:
SELECT a.c0::text::xid, b.c0, c.c0, d.c0
FROM t0 a, t0 b, t0 c, t0 d
EXCEPT SELECT '1'::xid, 2, 3, 4;
asks for 34359738368 bytes on master with default settings.
> EXCEPT ALL, INTERSECT and INTERSECT ALL behave the same.
EXCEPT ALL does. INTERSECT and INTERSECT ALL with your query do not
fail for me: the right side has one row, so the planner estimates one
group. With the 4-way cross join on both sides, INTERSECT asks for
34359738368 bytes too.
Everything above also reproduces on the macOS builds: on master the
backend grows past 4 GB and I kill it, while REL_18_STABLE runs it all
fine.
The backtrace (backtrace_functions = 'MemoryContextAllocationFailure')
goes tuplehash_create <- BuildTupleHashTable <- ExecInitRecursiveUnion
or ExecInitSetOp, in both SELECT and EXPLAIN.
For the history: the cap came from 299d1716525 (2011, BUG #6035), whose
message says huge numGroups values reach BuildTupleHashTable "in cases
where we have no alternative to building a hashtable". 1ea5bdb00bf says
the same about RecursiveUnion: it has no non-hashed implementation, and
its estimate of the number of entries is "so suspect". So the planner
cannot keep these tables under hash_mem, as 8f29467c57f expects it to.
nodeAgg still bounds its initial size in hash_choose_num_buckets().
The attached script reproduces the Linux numbers with psql only. It
needs a superuser and reads /proc/self/status for the backend's peak
RSS.
Disclosure: I used Claude Opus 5.5 (reasoning medium) for the analysis
and tests. Codex gpt-6.1-sol (reasoning high) checked independently
whether this could be by design. I reviewed the results.
Regards,
Sebastian
Em qui., 15 de out. de 2026 às 10:47, PG Bug reporting form <
noreply@postgresql.org> escreveu:
> The following bug has been logged on the website:
>
> Bug reference: 19759
> Logged by: Alperen Keles
> Email address: alpkeles99@gmail.com
> PostgreSQL version: 19beta4
> Operating system: Ubuntu 24.04 x86_64 (also macOS 26 arm64)
> Description:
>
> Since commit 8f29467c57f ("Change "long" numGroups fields to be Cardinality
> (i.e., double)", 2025-11-02), BuildTupleHashTable() no longer limits the
> initial size of a TupleHashTable to hash_mem. Its new comment says that
> staying within hash_mem is the caller's job. But nodeRecursiveunion.c and
> nodeSetOp.c pass the planner's raw group estimate, so the executor now
> allocates and zeroes a bucket array of up to 2^32 entries x 16 bytes =
> 64 GB in ExecInit, before reading a single row, whenever the estimate is
> large. PostgreSQL 18 is not affected (it capped nbuckets at
> get_hash_memory_limit() / entrysize). The commit is in REL_19_BETA1
> through REL_19_RC1.
>
> Two ways to reach it:
>
> 1. Recursive UNION, default settings. It can only be hashed, and
> generate_recursion_path() estimates its groups as lrows + 10 * rrows,
> so an inflated estimate goes straight into the allocation. With an
> empty, never-analyzed table:
>
> CREATE TABLE t0(c0 int4);
> WITH RECURSIVE r(w, x, y, z) AS (
> SELECT a.c0, b.c0, c.c0, d.c0 FROM t0 a, t0 b, t0 c, t0 d
> UNION
> SELECT w, x, y, z FROM r WHERE false)
> SELECT count(*) FROM r;
>
> On master (49103c3cd63, Ubuntu 24.04, x86_64, the server under
> ulimit -v 6 GB):
>
> ERROR: 53200: out of memory
> DETAIL: Failed on request of size 68719476736 in memory context
> "ExecutorState".
> LOCATION: MemoryContextAllocationFailure, mcxt.c:1208
>
> Without a memory limit the backend simply allocates it: on macOS its
> RSS passed 3 GB within a few milliseconds. On 18.6 the query returns 0
> at once.
>
> An accurate estimate is enough to see the difference, without any
> tables:
>
> WITH RECURSIVE r(n) AS (
> SELECT g FROM generate_series(1, 1000000) g
> UNION SELECT n + 1 FROM r WHERE n < 0)
> SELECT count(*) FROM r;
>
> Peak backend RSS: 1086 MB on master, 115 MB on 18.6 (same result).
> With 10 million rows master allocates 8 GB up front, while 18.6 peaks
> at about 1 GB.
>
> 2. Hashed set operations whose table the planner itself expects to
> exceed hash_mem. create_setop_path() only marks such a path disabled,
> so it is chosen when the sorted alternative is disabled even more,
> e.g. with enable_sort = off:
>
> BEGIN;
> SET LOCAL enable_sort = off;
> SELECT a.c0, b.c0, c.c0, d.c0 FROM t0 a, t0 b, t0 c, t0 d
> EXCEPT SELECT 1, 2, 3, 4;
> ROLLBACK;
>
> EXPLAIN shows "HashSetOp Except ... rows=1600000000 ... Disabled: true".
> On master it tries to allocate 32 GB (2^31 buckets) and fails or
> exhausts memory; on 18.6 it returns no rows in about 2 ms. EXCEPT ALL,
> INTERSECT and INTERSECT ALL behave the same.
>
> Versions tested:
> - master 158b6d5109f (macOS 26, arm64, Apple clang 21) and 49103c3cd63
> (Ubuntu 24.04, x86_64, gcc 13.3), both --enable-cassert --enable-debug
> - REL_18_STABLE a85b5523e67 (18.6, macOS): not affected
> Rechecked on 2026-10-15 (Ubuntu 24.04, x86_64, gcc 13.3, --enable-cassert
> --enable-debug, server under ulimit -v 6 GB) on 19rc1
> (REL_19_STABLE 0fb192f1b02), current master aa7b9cf132b and
> REL_18_STABLE 4d62ef92fda (18.6): on 19rc1 and master the three queries
> above fail with requests of 34359738368, 68719476736 and 8589934592
> bytes, and the 1M-row recursive UNION peaks at 1088 MB; on 18.6 all
> three succeed and that query peaks at 103 MB.
>
> Found with a query fuzzer.
>
>
>
>
>
--
Sebastian Webber
Attachments:
out-pg17.txt (text/plain, 8.8K) download
Pager usage is off.
version
----------------------------------------------------------------------------------------------------------------------
PostgreSQL 17.11 (Debian 17.11-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)
name | setting
---------------------+---------
enable_sort | on
hash_mem_multiplier | 2
work_mem | 4096
(3 rows)
psql:/repro/repro.sql:12: NOTICE: 00000: table "t0" does not exist, skipping
LOCATION: DropErrorMsgNonExistent, tablecmds.c:1436
DROP TABLE
CREATE TABLE
== case 1: recursive UNION, inflated estimate (default settings)
You are now connected to database "postgres" as user "postgres".
SET
QUERY PLAN
----------------------------------------------------------------------------------------------
Aggregate (cost=1902920129880.20..1902920129880.21 rows=1 width=8)
CTE r
-> Recursive Union (cost=0.00..951563739254.98 rows=42282506250010 width=16)
-> Nested Loop (cost=0.00..528738676754.88 rows=42282506250000 width=16)
-> Nested Loop (cost=0.00..207348588.00 rows=16581375000 width=12)
-> Nested Loop (cost=0.00..81358.62 rows=6502500 width=8)
-> Seq Scan on t0 a (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 b (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 c (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 d (cost=0.00..35.50 rows=2550 width=4)
-> Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: false
-> CTE Scan on r (cost=0.00..845650125000.20 rows=42282506250010 width=0)
JIT:
Functions: 13
Options: Inlining true, Optimization true, Expressions true, Deforming true
(19 rows)
count
-------
0
(1 row)
peak_rss_mb
-------------
107
(1 row)
== control 1: same query, UNION ALL (no hash table)
You are now connected to database "postgres" as user "postgres".
count
-------
0
(1 row)
peak_rss_mb
-------------
94
(1 row)
== case 2: recursive UNION, accurate estimate of 1M rows
You are now connected to database "postgres" as user "postgres".
count
---------
1000000
(1 row)
peak_rss_mb
-------------
206
(1 row)
== control 2: same rows, UNION ALL
You are now connected to database "postgres" as user "postgres".
count
---------
1000000
(1 row)
peak_rss_mb
-------------
102
(1 row)
== case 3: hashed EXCEPT chosen while disabled (enable_sort = off)
You are now connected to database "postgres" as user "postgres".
SET
SET
QUERY PLAN
------------------------------------------------------------------------------------------------------------
SetOp Except (cost=17679564844013.52..18208096172138.54 rows=1600000000 width=20)
-> Sort (cost=17679564844013.52..17785271109638.52 rows=42282506250001 width=20)
Sort Key: "*SELECT* 1".c0, "*SELECT* 1".c0_1, "*SELECT* 1".c0_2, "*SELECT* 1".c0_3
-> Append (cost=0.00..1162976270504.90 rows=42282506250001 width=20)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..951563739254.88 rows=42282506250000 width=20)
-> Nested Loop (cost=0.00..528738676754.88 rows=42282506250000 width=16)
-> Nested Loop (cost=0.00..207348588.00 rows=16581375000 width=12)
-> Nested Loop (cost=0.00..81358.62 rows=6502500 width=8)
-> Seq Scan on t0 a (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 b (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 c (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 d (cost=0.00..35.50 rows=2550 width=4)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..0.02 rows=1 width=20)
-> Result (cost=0.00..0.01 rows=1 width=16)
JIT:
Functions: 11
Options: Inlining true, Optimization true, Expressions true, Deforming true
(20 rows)
c0 | c0 | c0 | c0
----+----+----+----
(0 rows)
peak_rss_mb
-------------
95
(1 row)
== control 3: same query, default settings (sorted SetOp)
You are now connected to database "postgres" as user "postgres".
QUERY PLAN
------------------------------------------------------------------------------------------------------------
SetOp Except (cost=17669564844013.52..18198096172138.54 rows=1600000000 width=20)
-> Sort (cost=17669564844013.52..17775271109638.52 rows=42282506250001 width=20)
Sort Key: "*SELECT* 1".c0, "*SELECT* 1".c0_1, "*SELECT* 1".c0_2, "*SELECT* 1".c0_3
-> Append (cost=0.00..1162976270504.90 rows=42282506250001 width=20)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..951563739254.88 rows=42282506250000 width=20)
-> Nested Loop (cost=0.00..528738676754.88 rows=42282506250000 width=16)
-> Nested Loop (cost=0.00..207348588.00 rows=16581375000 width=12)
-> Nested Loop (cost=0.00..81358.62 rows=6502500 width=8)
-> Seq Scan on t0 a (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 b (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 c (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 d (cost=0.00..35.50 rows=2550 width=4)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..0.02 rows=1 width=20)
-> Result (cost=0.00..0.01 rows=1 width=16)
JIT:
Functions: 11
Options: Inlining true, Optimization true, Expressions true, Deforming true
(20 rows)
c0 | c0 | c0 | c0
----+----+----+----
(0 rows)
peak_rss_mb
-------------
95
(1 row)
== case 4: hashed EXCEPT, default settings, hash-only type (xid)
You are now connected to database "postgres" as user "postgres".
QUERY PLAN
-------------------------------------------------------------------------------------------------------
HashSetOp Except (cost=0.00..2008626395504.91 rows=1600000000 width=20)
-> Append (cost=0.00..1585801333004.90 rows=42282506250001 width=20)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..1374388801754.88 rows=42282506250000 width=20)
-> Nested Loop (cost=0.00..951563739254.88 rows=42282506250000 width=16)
-> Nested Loop (cost=0.00..207348588.00 rows=16581375000 width=12)
-> Nested Loop (cost=0.00..81358.62 rows=6502500 width=8)
-> Seq Scan on t0 a (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 b (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 c (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 d (cost=0.00..35.50 rows=2550 width=4)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..0.02 rows=1 width=20)
-> Result (cost=0.00..0.01 rows=1 width=16)
JIT:
Functions: 12
Options: Inlining true, Optimization true, Expressions true, Deforming true
(18 rows)
c0 | c0 | c0 | c0
----+----+----+----
(0 rows)
peak_rss_mb
-------------
108
(1 row)
== server log (errors and backtraces)
repro.sql (application/sql, 2.7K) download
out-rel19.txt (text/plain, 6.0K) download
Pager usage is off.
version
------------------------------------------------------------------
PostgreSQL 19rc1 on x86_64-linux, compiled by gcc-14.2.0, 64-bit
(1 row)
name | setting
---------------------+---------
enable_sort | on
hash_mem_multiplier | 2
work_mem | 4096
(3 rows)
psql:/repro/repro.sql:12: NOTICE: 00000: table "t0" does not exist, skipping
LOCATION: DropErrorMsgNonExistent, tablecmds.c:1527
DROP TABLE
CREATE TABLE
== case 1: recursive UNION, inflated estimate (default settings)
You are now connected to database "postgres" as user "postgres".
SET
psql:/repro/repro.sql:23: ERROR: 53200: out of memory
DETAIL: Failed on request of size 68719476736 in memory context "ExecutorState".
LOCATION: MemoryContextAllocationFailure, mcxt.c:1207
psql:/repro/repro.sql:27: ERROR: 53200: out of memory
DETAIL: Failed on request of size 68719476736 in memory context "ExecutorState".
LOCATION: MemoryContextAllocationFailure, mcxt.c:1207
peak_rss_mb
-------------
24
(1 row)
== control 1: same query, UNION ALL (no hash table)
You are now connected to database "postgres" as user "postgres".
count
-------
0
(1 row)
peak_rss_mb
-------------
21
(1 row)
== case 2: recursive UNION, accurate estimate of 1M rows
You are now connected to database "postgres" as user "postgres".
count
---------
1000000
(1 row)
peak_rss_mb
-------------
1095
(1 row)
== control 2: same rows, UNION ALL
You are now connected to database "postgres" as user "postgres".
count
---------
1000000
(1 row)
peak_rss_mb
-------------
33
(1 row)
== case 3: hashed EXCEPT chosen while disabled (enable_sort = off)
You are now connected to database "postgres" as user "postgres".
SET
SET
psql:/repro/repro.sql:59: ERROR: 53200: out of memory
DETAIL: Failed on request of size 34359738368 in memory context "ExecutorState".
LOCATION: MemoryContextAllocationFailure, mcxt.c:1207
psql:/repro/repro.sql:61: ERROR: 53200: out of memory
DETAIL: Failed on request of size 34359738368 in memory context "ExecutorState".
LOCATION: MemoryContextAllocationFailure, mcxt.c:1207
peak_rss_mb
-------------
24
(1 row)
== control 3: same query, default settings (sorted SetOp)
You are now connected to database "postgres" as user "postgres".
QUERY PLAN
--------------------------------------------------------------------------------------------
SetOp Except (cost=15879164969983.26..16407700298108.28 rows=1600000000 width=16)
-> Sort (cost=15879164969983.26..15984871235608.26 rows=42282506250000 width=16)
Sort Key: a.c0, b.c0, c.c0, d.c0
-> Nested Loop (cost=0.00..528738676754.88 rows=42282506250000 width=16)
-> Nested Loop (cost=0.00..207348588.00 rows=16581375000 width=12)
-> Nested Loop (cost=0.00..81358.62 rows=6502500 width=8)
-> Seq Scan on t0 a (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 b (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 c (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 d (cost=0.00..35.50 rows=2550 width=4)
-> Result (cost=0.00..0.01 rows=1 width=16)
(14 rows)
c0 | c0 | c0 | c0
----+----+----+----
(0 rows)
peak_rss_mb
-------------
22
(1 row)
== case 4: hashed EXCEPT, default settings, hash-only type (xid)
You are now connected to database "postgres" as user "postgres".
psql:/repro/repro.sql:75: ERROR: 53200: out of memory
DETAIL: Failed on request of size 34359738368 in memory context "ExecutorState".
LOCATION: MemoryContextAllocationFailure, mcxt.c:1207
psql:/repro/repro.sql:77: ERROR: 53200: out of memory
DETAIL: Failed on request of size 34359738368 in memory context "ExecutorState".
LOCATION: MemoryContextAllocationFailure, mcxt.c:1207
peak_rss_mb
-------------
22
(1 row)
== server log (errors and backtraces)
2026-10-15 13:51:49.037 UTC [42] ERROR: out of memory
2026-10-15 13:51:49.037 UTC [42] DETAIL: Failed on request of size 68719476736 in memory context "ExecutorState".
postgres: postgres postgres [local] EXPLAIN(BuildTupleHashTable+0x170) [0x64db63c1e908]
postgres: postgres postgres [local] EXPLAIN(ExecInitRecursiveUnion+0x2c7) [0x64db63c7a8f8]
2026-10-15 13:51:49.041 UTC [42] ERROR: out of memory
2026-10-15 13:51:49.041 UTC [42] DETAIL: Failed on request of size 68719476736 in memory context "ExecutorState".
postgres: postgres postgres [local] SELECT(BuildTupleHashTable+0x170) [0x64db63c1e908]
postgres: postgres postgres [local] SELECT(ExecInitRecursiveUnion+0x2c7) [0x64db63c7a8f8]
2026-10-15 13:51:51.696 UTC [46] ERROR: out of memory
2026-10-15 13:51:51.696 UTC [46] DETAIL: Failed on request of size 34359738368 in memory context "ExecutorState".
postgres: postgres postgres [local] EXPLAIN(BuildTupleHashTable+0x170) [0x64db63c1e908]
postgres: postgres postgres [local] EXPLAIN(ExecInitSetOp+0x313) [0x64db63c7e632]
2026-10-15 13:51:51.699 UTC [46] ERROR: out of memory
2026-10-15 13:51:51.699 UTC [46] DETAIL: Failed on request of size 34359738368 in memory context "ExecutorState".
postgres: postgres postgres [local] SELECT(BuildTupleHashTable+0x170) [0x64db63c1e908]
postgres: postgres postgres [local] SELECT(ExecInitSetOp+0x313) [0x64db63c7e632]
2026-10-15 13:51:51.711 UTC [48] ERROR: out of memory
2026-10-15 13:51:51.711 UTC [48] DETAIL: Failed on request of size 34359738368 in memory context "ExecutorState".
2026-10-15 13:51:51.713 UTC [48] ERROR: out of memory
2026-10-15 13:51:51.713 UTC [48] DETAIL: Failed on request of size 34359738368 in memory context "ExecutorState".
out-pg18.txt (text/plain, 7.3K) download
Pager usage is off.
version
--------------------------------------------------------------------------------------------------------------------
PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)
name | setting
---------------------+---------
enable_sort | on
hash_mem_multiplier | 2
work_mem | 4096
(3 rows)
psql:/repro/repro.sql:12: NOTICE: 00000: table "t0" does not exist, skipping
LOCATION: DropErrorMsgNonExistent, tablecmds.c:1506
DROP TABLE
CREATE TABLE
== case 1: recursive UNION, inflated estimate (default settings)
You are now connected to database "postgres" as user "postgres".
SET
QUERY PLAN
----------------------------------------------------------------------------------------------
Aggregate (cost=1902920129880.20..1902920129880.21 rows=1 width=8)
CTE r
-> Recursive Union (cost=0.00..951563739254.98 rows=42282506250010 width=16)
-> Nested Loop (cost=0.00..528738676754.88 rows=42282506250000 width=16)
-> Nested Loop (cost=0.00..207348588.00 rows=16581375000 width=12)
-> Nested Loop (cost=0.00..81358.62 rows=6502500 width=8)
-> Seq Scan on t0 a (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 b (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 c (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 d (cost=0.00..35.50 rows=2550 width=4)
-> Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: false
-> CTE Scan on r (cost=0.00..845650125000.20 rows=42282506250010 width=0)
JIT:
Functions: 14
Options: Inlining true, Optimization true, Expressions true, Deforming true
(19 rows)
count
-------
0
(1 row)
peak_rss_mb
-------------
112
(1 row)
== control 1: same query, UNION ALL (no hash table)
You are now connected to database "postgres" as user "postgres".
count
-------
0
(1 row)
peak_rss_mb
-------------
95
(1 row)
== case 2: recursive UNION, accurate estimate of 1M rows
You are now connected to database "postgres" as user "postgres".
count
---------
1000000
(1 row)
peak_rss_mb
-------------
184
(1 row)
== control 2: same rows, UNION ALL
You are now connected to database "postgres" as user "postgres".
count
---------
1000000
(1 row)
peak_rss_mb
-------------
105
(1 row)
== case 3: hashed EXCEPT chosen while disabled (enable_sort = off)
You are now connected to database "postgres" as user "postgres".
SET
SET
QUERY PLAN
--------------------------------------------------------------------------------------
HashSetOp Except (cost=951563739254.90..951567739254.90 rows=1600000000 width=16)
Disabled: true
-> Nested Loop (cost=0.00..528738676754.88 rows=42282506250000 width=16)
-> Nested Loop (cost=0.00..207348588.00 rows=16581375000 width=12)
-> Nested Loop (cost=0.00..81358.62 rows=6502500 width=8)
-> Seq Scan on t0 a (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 b (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 c (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 d (cost=0.00..35.50 rows=2550 width=4)
-> Result (cost=0.00..0.01 rows=1 width=16)
JIT:
Functions: 11
Options: Inlining true, Optimization true, Expressions true, Deforming true
(16 rows)
c0 | c0 | c0 | c0
----+----+----+----
(0 rows)
peak_rss_mb
-------------
76
(1 row)
== control 3: same query, default settings (sorted SetOp)
You are now connected to database "postgres" as user "postgres".
QUERY PLAN
--------------------------------------------------------------------------------------------
SetOp Except (cost=15879164969983.26..16407700298108.28 rows=1600000000 width=16)
-> Sort (cost=15879164969983.26..15984871235608.26 rows=42282506250000 width=16)
Sort Key: a.c0, b.c0, c.c0, d.c0
-> Nested Loop (cost=0.00..528738676754.88 rows=42282506250000 width=16)
-> Nested Loop (cost=0.00..207348588.00 rows=16581375000 width=12)
-> Nested Loop (cost=0.00..81358.62 rows=6502500 width=8)
-> Seq Scan on t0 a (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 b (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 c (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 d (cost=0.00..35.50 rows=2550 width=4)
-> Result (cost=0.00..0.01 rows=1 width=16)
JIT:
Functions: 8
Options: Inlining true, Optimization true, Expressions true, Deforming true
(17 rows)
c0 | c0 | c0 | c0
----+----+----+----
(0 rows)
peak_rss_mb
-------------
68
(1 row)
== case 4: hashed EXCEPT, default settings, hash-only type (xid)
You are now connected to database "postgres" as user "postgres".
QUERY PLAN
--------------------------------------------------------------------------------------
HashSetOp Except (cost=1374388801754.90..1374392801754.90 rows=1600000000 width=16)
Disabled: true
-> Nested Loop (cost=0.00..951563739254.88 rows=42282506250000 width=16)
-> Nested Loop (cost=0.00..207348588.00 rows=16581375000 width=12)
-> Nested Loop (cost=0.00..81358.62 rows=6502500 width=8)
-> Seq Scan on t0 a (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 b (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 c (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 d (cost=0.00..35.50 rows=2550 width=4)
-> Result (cost=0.00..0.01 rows=1 width=16)
JIT:
Functions: 11
Options: Inlining true, Optimization true, Expressions true, Deforming true
(16 rows)
c0 | c0 | c0 | c0
----+----+----+----
(0 rows)
peak_rss_mb
-------------
76
(1 row)
== server log (errors and backtraces)
out-master.txt (text/plain, 6.0K) download
Pager usage is off.
version
--------------------------------------------------------------------
PostgreSQL 20devel on x86_64-linux, compiled by gcc-14.2.0, 64-bit
(1 row)
name | setting
---------------------+---------
enable_sort | on
hash_mem_multiplier | 2
work_mem | 4096
(3 rows)
psql:/repro/repro.sql:12: NOTICE: 00000: table "t0" does not exist, skipping
LOCATION: DropErrorMsgNonExistent, tablecmds.c:1527
DROP TABLE
CREATE TABLE
== case 1: recursive UNION, inflated estimate (default settings)
You are now connected to database "postgres" as user "postgres".
SET
psql:/repro/repro.sql:23: ERROR: 53200: out of memory
DETAIL: Failed on request of size 68719476736 in memory context "ExecutorState".
LOCATION: MemoryContextAllocationFailure, mcxt.c:1208
psql:/repro/repro.sql:27: ERROR: 53200: out of memory
DETAIL: Failed on request of size 68719476736 in memory context "ExecutorState".
LOCATION: MemoryContextAllocationFailure, mcxt.c:1208
peak_rss_mb
-------------
24
(1 row)
== control 1: same query, UNION ALL (no hash table)
You are now connected to database "postgres" as user "postgres".
count
-------
0
(1 row)
peak_rss_mb
-------------
21
(1 row)
== case 2: recursive UNION, accurate estimate of 1M rows
You are now connected to database "postgres" as user "postgres".
count
---------
1000000
(1 row)
peak_rss_mb
-------------
1094
(1 row)
== control 2: same rows, UNION ALL
You are now connected to database "postgres" as user "postgres".
count
---------
1000000
(1 row)
peak_rss_mb
-------------
33
(1 row)
== case 3: hashed EXCEPT chosen while disabled (enable_sort = off)
You are now connected to database "postgres" as user "postgres".
SET
SET
psql:/repro/repro.sql:59: ERROR: 53200: out of memory
DETAIL: Failed on request of size 34359738368 in memory context "ExecutorState".
LOCATION: MemoryContextAllocationFailure, mcxt.c:1208
psql:/repro/repro.sql:61: ERROR: 53200: out of memory
DETAIL: Failed on request of size 34359738368 in memory context "ExecutorState".
LOCATION: MemoryContextAllocationFailure, mcxt.c:1208
peak_rss_mb
-------------
24
(1 row)
== control 3: same query, default settings (sorted SetOp)
You are now connected to database "postgres" as user "postgres".
QUERY PLAN
--------------------------------------------------------------------------------------------
SetOp Except (cost=15879164969983.26..16407700298108.28 rows=1600000000 width=16)
-> Sort (cost=15879164969983.26..15984871235608.26 rows=42282506250000 width=16)
Sort Key: a.c0, b.c0, c.c0, d.c0
-> Nested Loop (cost=0.00..528738676754.88 rows=42282506250000 width=16)
-> Nested Loop (cost=0.00..207348588.00 rows=16581375000 width=12)
-> Nested Loop (cost=0.00..81358.62 rows=6502500 width=8)
-> Seq Scan on t0 a (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 b (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 c (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t0 d (cost=0.00..35.50 rows=2550 width=4)
-> Result (cost=0.00..0.01 rows=1 width=16)
(14 rows)
c0 | c0 | c0 | c0
----+----+----+----
(0 rows)
peak_rss_mb
-------------
22
(1 row)
== case 4: hashed EXCEPT, default settings, hash-only type (xid)
You are now connected to database "postgres" as user "postgres".
psql:/repro/repro.sql:75: ERROR: 53200: out of memory
DETAIL: Failed on request of size 34359738368 in memory context "ExecutorState".
LOCATION: MemoryContextAllocationFailure, mcxt.c:1208
psql:/repro/repro.sql:77: ERROR: 53200: out of memory
DETAIL: Failed on request of size 34359738368 in memory context "ExecutorState".
LOCATION: MemoryContextAllocationFailure, mcxt.c:1208
peak_rss_mb
-------------
22
(1 row)
== server log (errors and backtraces)
2026-10-15 13:51:49.035 UTC [42] ERROR: out of memory
2026-10-15 13:51:49.035 UTC [42] DETAIL: Failed on request of size 68719476736 in memory context "ExecutorState".
postgres: postgres postgres [local] EXPLAIN(BuildTupleHashTable+0x164) [0x609aed0af5dd]
postgres: postgres postgres [local] EXPLAIN(ExecInitRecursiveUnion+0x2c7) [0x609aed10b85d]
2026-10-15 13:51:49.038 UTC [42] ERROR: out of memory
2026-10-15 13:51:49.038 UTC [42] DETAIL: Failed on request of size 68719476736 in memory context "ExecutorState".
postgres: postgres postgres [local] SELECT(BuildTupleHashTable+0x164) [0x609aed0af5dd]
postgres: postgres postgres [local] SELECT(ExecInitRecursiveUnion+0x2c7) [0x609aed10b85d]
2026-10-15 13:51:51.715 UTC [46] ERROR: out of memory
2026-10-15 13:51:51.715 UTC [46] DETAIL: Failed on request of size 34359738368 in memory context "ExecutorState".
postgres: postgres postgres [local] EXPLAIN(BuildTupleHashTable+0x164) [0x609aed0af5dd]
postgres: postgres postgres [local] EXPLAIN(ExecInitSetOp+0x314) [0x609aed10f598]
2026-10-15 13:51:51.718 UTC [46] ERROR: out of memory
2026-10-15 13:51:51.718 UTC [46] DETAIL: Failed on request of size 34359738368 in memory context "ExecutorState".
postgres: postgres postgres [local] SELECT(BuildTupleHashTable+0x164) [0x609aed0af5dd]
postgres: postgres postgres [local] SELECT(ExecInitSetOp+0x314) [0x609aed10f598]
2026-10-15 13:51:51.729 UTC [48] ERROR: out of memory
2026-10-15 13:51:51.729 UTC [48] DETAIL: Failed on request of size 34359738368 in memory context "ExecutorState".
2026-10-15 13:51:51.731 UTC [48] ERROR: out of memory
2026-10-15 13:51:51.731 UTC [48] DETAIL: Failed on request of size 34359738368 in memory context "ExecutorState".
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19759: Hashed SetOp / RecursiveUnion pre-allocate up to 64 GB from the row estimate since 8f29467c57f
@ 2026-10-16 01:05 David Rowley <dgrowleyml@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
2 siblings, 0 replies; 6+ messages in thread
From: David Rowley @ 2026-10-16 01:05 UTC (permalink / raw)
To: alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org
On Fri, 16 Oct 2026 at 02:47, PG Bug reporting form
<noreply@postgresql.org> wrote:
> Since commit 8f29467c57f ("Change "long" numGroups fields to be Cardinality
> (i.e., double)", 2025-11-02), BuildTupleHashTable() no longer limits the
> initial size of a TupleHashTable to hash_mem. Its new comment says that
> staying within hash_mem is the caller's job. But nodeRecursiveunion.c and
> nodeSetOp.c pass the planner's raw group estimate, so the executor now
> allocates and zeroes a bucket array of up to 2^32 entries x 16 bytes =
> 64 GB in ExecInit, before reading a single row, whenever the estimate is
> large. PostgreSQL 18 is not affected (it capped nbuckets at
> get_hash_memory_limit() / entrysize). The commit is in REL_19_BETA1
> through REL_19_RC1.
Looking back at the changes made in 8f29467c5 and looking at the
estimation code in generate_recursion_path(), I think we're dreaming
if we think we can avoid capping planner estimates in the executor.
There's an argument for doing that in the planner, but plans can be
cached, and we don't replan when work_mem or the hash_mem_multiplier
changes, so I think keeping some logic in BuildTupleHashTable() is
sensible. Queries being slightly slower due to the hash table having
to grow a few times seems much better than OOM because of the planner
not having enough information to make an informed estimate.
I've tried to do that in the attached and incorporated the capping to
UINT32_MAX that Tom added in 8f29467c5. The calculation still doesn't
account for the tuple width, but if we were to try to include that as
part of the calculation is means making the cap calculation
susceptible to planner estimates. That seems weird since the whole
point of the cap is to protect against crazy planner estimates.
David
Attachments:
readd_hash_mem_limit_on_nbuckets.patch (application/octet-stream, 2.0K) download
diff --git a/src/backend/executor/execGrouping.c b/src/backend/executor/execGrouping.c
index 948aa2f177a..9aaced19759 100644
--- a/src/backend/executor/execGrouping.c
+++ b/src/backend/executor/execGrouping.c
@@ -203,18 +203,35 @@ BuildTupleHashTable(PlanState *parent,
/*
* tuplehash_create requires a uint32 element count, so we had better
- * clamp the given nelements to fit in that. As long as we have to do
- * that, we might as well protect against completely insane input like
- * zero or NaN. But it is not our job here to enforce issues like staying
- * within hash_mem: the caller should have done that, and we don't have
- * enough info to second-guess.
+ * clamp the given nelements to fit in that. We'd also better do
+ * something sane when faced with a zero or NaN estimate. There are
+ * various reasons why the planner might not be able to provide a good
+ * estimate here, for example; recursive CTEs don't base their estimate
+ * on anything reliable and can yield some rather large estimates. Here
+ * we do cap nbuckets to hash_mem to help prevent insane memory
+ * allocations. The hash table can still grow when the bucket count is
+ * insufficient. That will make the query a little slower, but that's
+ * better than outright failing due to OOM if we were to request an insane
+ * bucket count. Queries being slightly slower with smaller work_mem
+ * values is also aligned to user expectation.
*/
if (isnan(nelements) || nelements <= 0)
nbuckets = 1;
- else if (nelements >= PG_UINT32_MAX)
- nbuckets = PG_UINT32_MAX;
else
- nbuckets = (uint32) nelements;
+ {
+ size_t entrysize;
+ size_t hash_mem_limit;
+
+ if (nelements >= PG_UINT32_MAX)
+ nbuckets = PG_UINT32_MAX;
+ else
+ nbuckets = (uint32) nelements;
+
+ entrysize = sizeof(TupleHashEntryData) + additionalsize;
+ hash_mem_limit = get_hash_memory_limit() / entrysize;
+ if (nbuckets > hash_mem_limit)
+ nbuckets = hash_mem_limit;
+ }
/* tuplescxt must be separate, else ResetTupleHashTable breaks things */
Assert(metacxt != tuplescxt);
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19759: Hashed SetOp / RecursiveUnion pre-allocate up to 64 GB from the row estimate since 8f29467c57f
@ 2026-10-19 08:36 David Rowley <dgrowleyml@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
2 siblings, 1 reply; 6+ messages in thread
From: David Rowley @ 2026-10-19 08:36 UTC (permalink / raw)
To: alpkeles99@gmail.com, pgsql-bugs@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
On Fri, 16 Oct 2026 at 02:47, PG Bug reporting form
<noreply@postgresql.org> wrote:
> Since commit 8f29467c57f ("Change "long" numGroups fields to be Cardinality
> (i.e., double)", 2025-11-02), BuildTupleHashTable() no longer limits the
> initial size of a TupleHashTable to hash_mem. Its new comment says that
> staying within hash_mem is the caller's job. But nodeRecursiveunion.c and
> nodeSetOp.c pass the planner's raw group estimate, so the executor now
> allocates and zeroes a bucket array of up to 2^32 entries x 16 bytes =
> 64 GB in ExecInit, before reading a single row, whenever the estimate is
> large. PostgreSQL 18 is not affected (it capped nbuckets at
> get_hash_memory_limit() / entrysize). The commit is in REL_19_BETA1
> through REL_19_RC1.
I've added an open item for this to ensure it's not forgotten.
David
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19759: Hashed SetOp / RecursiveUnion pre-allocate up to 64 GB from the row estimate since 8f29467c57f
@ 2026-10-24 04:47 shihao zhong <zhong950419@gmail.com>
parent: David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: shihao zhong @ 2026-10-24 04:47 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: alpkeles99@gmail.com, pgsql-bugs@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Hi David,
The patch fixes the cases from the report for me. Two small things.
1. simplehash rounds the size up to a power of 2, so the empty table
can still be twice hash_mem. With work_mem = 1GB, a plain EXPLAIN of
the recursive query zeroes 4 GB. Halving the limit fixes it.
hash_choose_num_buckets() does the same.
2. Plain EXPLAIN still builds the table. nodeAgg skips it.
The attached patch on top of yours does both.
Thanks,
Shihao
Attachments:
nocfbot-reduce-mem.patch (application/octet-stream, 2.7K) download
From a8928b26e1c35ec1cea99a3405d927e86efdfbf3 Mon Sep 17 00:00:00 2001
From: shihao zhong <zhong950419@gmail.com>
Date: Sat, 24 Oct 2026 00:27:53 -0400
Subject: [PATCH] Keep initial tuple hash table within hash_mem, skip it for
EXPLAIN
simplehash rounds nbuckets / SH_FILLFACTOR up to a power of 2, so the
hash_mem cap in BuildTupleHashTable could still give an empty bucket
array of twice hash_mem. Halve the limit, as nodeAgg.c does. Also
skip building the table in ExecInitSetOp and ExecInitRecursiveUnion
for EXPLAIN without ANALYZE, as nodeAgg.c does since 2cccb627f10.
---
src/backend/executor/execGrouping.c | 7 ++++++-
src/backend/executor/nodeRecursiveunion.c | 4 +++-
src/backend/executor/nodeSetOp.c | 4 +++-
3 files changed, 12 insertions(+), 3 deletions(-)
diff --git a/src/backend/executor/execGrouping.c b/src/backend/executor/execGrouping.c
index 9aaced19759..714067b4630 100644
--- a/src/backend/executor/execGrouping.c
+++ b/src/backend/executor/execGrouping.c
@@ -227,8 +227,13 @@ BuildTupleHashTable(PlanState *parent,
else
nbuckets = (uint32) nelements;
+ /*
+ * simplehash rounds nelements / SH_FILLFACTOR up to a power of 2, so
+ * the bucket array can be twice what we ask for. Halve the limit to
+ * keep the initial table within hash_mem, as nodeAgg.c does.
+ */
entrysize = sizeof(TupleHashEntryData) + additionalsize;
- hash_mem_limit = get_hash_memory_limit() / entrysize;
+ hash_mem_limit = get_hash_memory_limit() / entrysize / 2;
if (nbuckets > hash_mem_limit)
nbuckets = hash_mem_limit;
}
diff --git a/src/backend/executor/nodeRecursiveunion.c b/src/backend/executor/nodeRecursiveunion.c
index 7166397e59b..5f2cdb57134 100644
--- a/src/backend/executor/nodeRecursiveunion.c
+++ b/src/backend/executor/nodeRecursiveunion.c
@@ -270,7 +270,9 @@ ExecInitRecursiveUnion(RecursiveUnion *node, EState *estate, int eflags)
node->dupOperators,
&rustate->eqfuncoids,
&rustate->hashfunctions);
- build_hash_table(rustate);
+ /* Skip massive memory allocation if we are just doing EXPLAIN */
+ if (!(eflags & EXEC_FLAG_EXPLAIN_ONLY))
+ build_hash_table(rustate);
}
return rustate;
diff --git a/src/backend/executor/nodeSetOp.c b/src/backend/executor/nodeSetOp.c
index 88815259b34..0dcbb60c15c 100644
--- a/src/backend/executor/nodeSetOp.c
+++ b/src/backend/executor/nodeSetOp.c
@@ -673,7 +673,9 @@ ExecInitSetOp(SetOp *node, EState *estate, int eflags)
/* Create a hash table if needed */
if (node->strategy == SETOP_HASHED)
{
- build_hash_table(setopstate);
+ /* Skip massive memory allocation if we are just doing EXPLAIN */
+ if (!(eflags & EXEC_FLAG_EXPLAIN_ONLY))
+ build_hash_table(setopstate);
setopstate->table_filled = false;
}
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19759: Hashed SetOp / RecursiveUnion pre-allocate up to 64 GB from the row estimate since 8f29467c57f
@ 2026-10-27 20:11 David Rowley <dgrowleyml@gmail.com>
parent: shihao zhong <zhong950419@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: David Rowley @ 2026-10-27 20:11 UTC (permalink / raw)
To: shihao zhong <zhong950419@gmail.com>; +Cc: alpkeles99@gmail.com, pgsql-bugs@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Thanks for the review.
On Sat, 24 Oct 2026 at 17:48, shihao zhong <zhong950419@gmail.com> wrote:
> The patch fixes the cases from the report for me. Two small things.
>
> 1. simplehash rounds the size up to a power of 2, so the empty table
> can still be twice hash_mem. With work_mem = 1GB, a plain EXPLAIN of
> the recursive query zeroes 4 GB. Halving the limit fixes it.
> hash_choose_num_buckets() does the same.
While it is true that simplehash will make the bucket count the next
power of 2 value, I didn't do this as I wanted to restore what we did
prior to 8f29467c5 in terms of the hard limit.
> 2. Plain EXPLAIN still builds the table. nodeAgg skips it.
Seems unrelated and I don't recall anyone complaining. Not something I
think we should adjust last minute before v19 goes out the door.
Now pushed.
David
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-10-27 20:11 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-15 13:44 BUG #19759: Hashed SetOp / RecursiveUnion pre-allocate up to 64 GB from the row estimate since 8f29467c57f PG Bug reporting form <noreply@postgresql.org>
2026-10-15 14:19 ` Sebastian Webber <sebastian@swebber.me>
2026-10-16 01:05 ` David Rowley <dgrowleyml@gmail.com>
2026-10-19 08:36 ` David Rowley <dgrowleyml@gmail.com>
2026-10-24 04:47 ` shihao zhong <zhong950419@gmail.com>
2026-10-27 20:11 ` David Rowley <dgrowleyml@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox