From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: moj@dshare.de
Subject: BUG #19030: Hash join leads to extremely high memory usage
Date: Sat, 23 Aug 2025 15:30:13 +0000
Message-ID: <19030-944dd78d7ef94c0f@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 19030
Logged by: Marc-Olaf Jaschke
Email address: moj@dshare.de
PostgreSQL version: 17.6
Operating system: Linux
Description:
Description
- Two tables, left join
- The left table has significantly fewer rows than the right table
- The left table has very large rows (many columns with high memory usage)
- The left table has many null values in the join column
- A hash join is used
- The hash node is built from the left table
- The query results in extremely high memory usage (100x work_mem in the
example, > 1000x in real case)
- Reliably causing a PostgreSQL server to crash in production
- With enable_hashjoin = false, the query runs without any issues
Example
- Simplified artificial example – but I hope it simulates a real problem on
a production system well.
- Simulate large rows with one big column
- Running the newest version with mostly default settings
==========================================
select version();
-- > PostgreSQL 17.6 (Debian 17.6-1.pgdg13+1) on aarch64-unknown-linux-gnu,
compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
show work_mem;
-- > 4MB
show hash_mem_multiplier;
-- > 2
-- speed up the test case
set default_toast_compression = lz4;
-- show high memory usage without crashing the server
set max_parallel_workers_per_gather = 0;
create table left_ as
select
case when i% 2 = 0 then i::text end c,
repeat('x', 10_000) big
from
generate_series(1, 10_000_000) i;
create table right_ as
select
case when i% 2 = 0 then i::text end c
from
generate_series(1, 20_000_000) i;
analyze left_, right_;
explain (analyze, memory)
select
*
from
left_
natural left join
right_;
-- > Buckets: 131072 (originally 131072) Batches: 131072 (originally 256)
Memory Usage: 440482kB
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: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, moj@dshare.de
Subject: Re: BUG #19030: Hash join leads to extremely high memory usage
In-Reply-To: <19030-944dd78d7ef94c0f@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