Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xHLnm-00000002xKz-0YAk for pgsql-bugs@arkaria.postgresql.org; Thu, 15 Oct 2026 13:47:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xHLnl-000000090xN-0V3D for pgsql-bugs@arkaria.postgresql.org; Thu, 15 Oct 2026 13:47:21 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xHLlc-000000090kT-3T2D for pgsql-bugs@lists.postgresql.org; Thu, 15 Oct 2026 13:45:09 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xHLla-000000029l9-2qF9 for pgsql-bugs@lists.postgresql.org; Thu, 15 Oct 2026 13:45:07 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=vvsjStPVYBFzXs+bKXdDgoWfBShpjwsRDI9lfcT6c8k=; b=ByoXWNn77SYA3sY/MDDYGRV6gQ FnNuYtdyOorsCOI9o+vXI2F85Er2hi+Msg+YqnouDu0OalUb69OySdNFwvhw+QrcEYsDYr7yYibkz rNBmpYKAquXgMWRMm01sAgEY9SzXqMp8NUk0ykZIOY5OYU7ovNzDEbgVrXcJ0zRpptwF3dnq4tGJt E7AuCMNf6OubU5z/lQRKUD3knJxgid/KrfAXNRfHTmBj/ilVsEWe+F4+H7NIoMxizDr25XGRuTGcO vYQ6/MkvIBc2OcEzB//+bVwXD51U5Nr+D9nozN84cx3L03n/ReOAqJ9MYiGzjHaEvSTd4ilqJ6k4e 3/Sw342g==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xHLla-00000006HHQ-0wM5 for pgsql-bugs@lists.postgresql.org; Thu, 15 Oct 2026 13:45:06 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xHLlY-00000001dvZ-2DGM for pgsql-bugs@lists.postgresql.org; Thu, 15 Oct 2026 13:45:04 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19759: Hashed SetOp / RecursiveUnion pre-allocate up to 64 GB from the row estimate since 8f29467c57f To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: alpkeles99@gmail.com Reply-To: alpkeles99@gmail.com, pgsql-bugs@lists.postgresql.org Date: Thu, 15 Oct 2026 13:44:13 +0000 Message-ID: <19759-acd3f33402e54f36@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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: =20 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 =3D 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 =3D off: BEGIN; SET LOCAL enable_sort =3D 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=3D1600000000 ... 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.