From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: alpkeles99@gmail.com
Subject: BUG #19759: Hashed SetOp / RecursiveUnion pre-allocate up to 64 GB from the row estimate since 8f29467c57f
Date: Thu, 15 Oct 2026 13:44:13 +0000
Message-ID: <19759-acd3f33402e54f36@postgresql.org> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, alpkeles99@gmail.com
Subject: Re: BUG #19759: Hashed SetOp / RecursiveUnion pre-allocate up to 64 GB from the row estimate since 8f29467c57f
In-Reply-To: <19759-acd3f33402e54f36@postgresql.org>
References: <19759-acd3f33402e54f36@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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