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.
