From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Guo <guofenglinux@gmail.com>
Cc: jinhui.lai@qq.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19363: PostgreSQL shared memory exhaustion during query execution involving views and parallel workers
Date: Sat, 27 Dec 2025 12:02:17 -0500
Message-ID: <1712093.1766854937@sss.pgh.pa.us> (raw)
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>
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
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 using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, guofenglinux@gmail.com, 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: <1712093.1766854937@sss.pgh.pa.us>
* 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