pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.







view thread (6+ messages)  latest in thread

Message-ID: <19759-acd3f33402e54f36@postgresql.org>
Permalink:  ../19759-acd3f33402e54f36@postgresql.org/
Also on:    postgresql.org/message-id/19759-acd3f33402e54f36@postgresql.org

 · 

reply

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