agora inbox for pgsql-bugs@postgresql.org  

help / color / mirror / Atom feed

From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: yanarnold5@gmail.com
Subject: BUG #19708: Hash Join becomes about 300x slower with higher work_mem
Date: Sun, 20 Sep 2026 11:57:37 +0000
Message-ID: <19708-bca71f8de0d45605@postgresql.org> (raw)

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:        

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 = off;

SET work_mem = '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 = '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-b1511e6a6d10%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.

view thread (7+ messages)  latest in thread

Message-ID: <19708-bca71f8de0d45605@postgresql.org>
Permalink:  ../19708-bca71f8de0d45605@postgresql.org/
Also on:    postgresql.org/message-id/19708-bca71f8de0d45605@postgresql.org

reply

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, yanarnold5@gmail.com
  Subject: Re: BUG #19708: Hash Join becomes about 300x slower with higher work_mem
  In-Reply-To: <19708-bca71f8de0d45605@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 agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox