From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Guo <guofenglinux@gmail.com>
cc: jinhui.lai@qq.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19363: PostgreSQL shared memory exhaustion during query
 execution involving views and parallel workers
In-reply-to: 
 <CAMbWs4_Wp7D8k53OuQNhW9=iN2NQF+W0KPD+za0ehf6-F=Dcyg@mail.gmail.com>
References: <19363-8dd32fc7600a1153@postgresql.org>
 <CAMbWs4_Wp7D8k53OuQNhW9=iN2NQF+W0KPD+za0ehf6-F=Dcyg@mail.gmail.com>
Comments: In-reply-to Richard Guo <guofenglinux@gmail.com>
	message dated "Sat, 27 Dec 2025 14:12:06 +0900"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1712092.1766854937.1@sss.pgh.pa.us>
Date: Sat, 27 Dec 2025 12:02:17 -0500
Message-ID: <1712093.1766854937@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1712093.1766854937%40sss.pgh.pa.us>
Precedence: bulk

Richard Guo <guofenglinux@gmail.com> writes:
> 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.

On my machine, the query does complete, but it's ridiculously slow:
about 15 sec to compute an empty result.  AFAICT nearly all of that
is being spent in hash table setup.

What I'm wondering is why this plan was accepted at all.  Does PHJ
not consider work_mem or a similar limit on hash table size?

			regards, tom lane