Re: BUG #19363: PostgreSQL shared memory exhaustion during query execution involving views and parallel workers

On Sat, Dec 27, 2025 at 12:35 AM PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
> CREATE TABLE t0(c0 REAL);
> CREATE TABLE t1(c0 REAL);
> CREATE TABLE t2(c0 REAL) WITH (parallel_workers=1);
> CREATE TABLE t3(c0 REAL) WITH (parallel_workers=2);
> CREATE VIEW v0(c0, c1, c2) AS (SELECT t0.c0, t1.c0, t2.c0 FROM t0, t1, t2);
> SELECT t3.c0 FROM t3 WHERE NOT EXISTS (SELECT 1 FROM v0 WHERE t3.c0 =
> v0.c0);
> ERROR: could not resize shared memory segment "/PostgreSQL.3961899888" to
> 33624064 bytes: No space left on device

I don't think this is a resource leak issue. From the snippet of the
plan:

-> Parallel Hash Anti Join (cost=281992257.25..320092889.87 rows=531 width=4)
Hash Cond: (t3.c0 = t0.c0)
-> Parallel Seq Scan on t3 (cost=0.00..20.62 rows=1062 width=4)
-> Parallel Hash (cost=121969796.25..121969796.25
rows=9753750000 width=4)
-> Nested Loop (cost=0.00..121969796.25 rows=9753750000 width=4)

The executor needs to build a hash table for 9.7 billion rows. That
requires a lot of memory. In practice, the executor splits the work
into 8192 batches, that still results in 1.2 million rows per batch.
To manage that many rows, the executor allocated 4194304 buckets.
This means the executor needs to allocate 32 MB just for the bucket
array, assuming you are on a 64-bit system. I guess your available
shared memory was less than 32 MB at that moment.

I kind of wonder why the planner did not choose a Right Anti Join to
build the hash table on the smaller side. This is likely due to a
cost estimation issue. However, I don't feel very motivated to debug
a cost estimation issue on tables that haven't even run ANALYZE.

- Richard