BUG #19708: Hash Join becomes about 300x slower with higher work_mem

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.[/messages/by-id/244dc6c1-3b3d-4de2-b3de-b1511e6a6d10@vondra.me%5D

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.