Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19708: Hash Join becomes about 300x slower with higher work_mem
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: yanarnold5@gmail.com
Reply-To: yanarnold5@gmail.com, pgsql-bugs@lists.postgresql.org
Date: Sun, 20 Sep 2026 11:57:37 +0000
Message-ID: <19708-bca71f8de0d45605@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19708-bca71f8de0d45605%40postgresql.org>
Precedence: bulk

The following bug has been logged on the website:

Bug reference:      19708
Logged by:          iany
Email address:      yanarnold5@gmail.com
PostgreSQL version: 18.6
Operating system:   Ubuntu 22.04
Description:       =20

Reproduced on PostgreSQL master 20devel, commit
9e17d25e79d4756be08b4a5521b4b58450217137.

Source build used --without-readline --without-zlib.

Reproducer (run with psql -X):

CREATE TABLE a();
INSERT INTO a DEFAULT VALUES;

SET enable_mergejoin =3D off;

SET work_mem =3D '64kB';
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY ON)
WITH x AS (
    SELECT g
    FROM a a1, a a2, generate_series(1,768) g
)
SELECT l.g
FROM x l
JOIN x r USING (g);

SET work_mem =3D '16MB';
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY ON)
WITH x AS (
    SELECT g
    FROM a a1, a a2, generate_series(1,768) g
)
SELECT l.g
FROM x l
JOIN x r USING (g);

Observed runtimes on master:

work_mem    runtime
64kB        0.0248s
256kB       0.1290s
1MB         6.8825s
16MB        7.8372s

All executions returned the same 768 rows. Increasing work_mem from 64kB to
16MB made the query approximately 315x slower.

Could you please confirm whether this degree of slowdown as work_mem
increases is expected for the same Hash Join and cardinality estimate?

At 1MB, EXPLAIN ANALYZE reported 4,194,304 original/final hash buckets and
4,096 original/final hash batches.

This appears related to the earlier "Fix overflow of nbatch"
discussion.[https://www.postgresql.org/message-id/244dc6c1-3b3d-4de2-b3de-b=
1511e6a6d10%40vondra.me]

That discussion noted that initial nbatch can increase with work_mem but
considered it probably harmless because runtime batching could compensate.
Runtime batch growth does not occur in this case.