[PG19] Hash join writes null-keyed outer rows to temp files
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t254266psql -h localhost -U postgresBuilt from patchset v2 (message #2), October 22, 2026 at 07:35 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t254266_2 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t254266_2 && git checkout t254266_2Patchset v2 (message #2) is on t254266_2
Hi hackers,
Since 1811f1af98f, a hash join keeps outer rows with a NULL join key in
a tuplestore and returns them at the end of the batch. Once they pass
work_mem / 16 they go to disk, even for a single-batch join. 18 returned
them right away and never stored them.
With default settings:
CREATE TABLE b (k int PRIMARY KEY);
INSERT INTO b SELECT generate_series(1, 1000);
CREATE TABLE a AS SELECT NULL::int AS k, repeat('x', 100) AS pad
FROM generate_series(1, 1000000);
VACUUM ANALYZE a, b;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT count(b.k) FROM a LEFT JOIN b ON a.k = b.k;
SET temp_file_limit = '10MB';
SELECT count(b.k) FROM a LEFT JOIN b ON a.k = b.k;
On release builds 18 takes 22 ms with no temp files. 19 takes 110 ms
and writes 106 MB of temp files.
The original mail said returning them right away would need a big
rewrite [1]/messages/by-id/3061845.1746486714@sss.pgh.pa.us. The patch gives such a row a dummy hash value of 0 and
lets it probe as usual. That value always maps to the current batch,
and the row cannot match, so it comes out null-extended right away,
like in 18. Only the multi-batch parallel case still uses the
tuplestore, since the outer side is partitioned before probing.
Another way is to return such a row with a flag and jump straight to
HJ_FILL_OUTER_TUPLE, skipping the probe. That is twice the size, so I
went with the smaller one in the hope it can go into 19. I can post the
other one if people prefer it.
[1]: /messages/by-id/3061845.1746486714@sss.pgh.pa.us
Thanks,
Shihao
shihao zhong <zhong950419@gmail.com> writes:
The original mail said returning them right away would need a big
rewrite [1]. The patch gives such a row a dummy hash value of 0 and
lets it probe as usual. That value always maps to the current batch,
and the row cannot match, so it comes out null-extended right away,
like in 18.
I do not think I believe this patch. In particular, it assumes that
hashvalue 0 will map to the current batch, which is only accidentally
true for ExecHashGetBucketAndBatch (but okay, we could add a provision
to its API that hashvalue 0 must map to batch 0) and more importantly
can fail entirely for ExecHashGetSkewBucket. There's no reason to
assume there couldn't be a real tuple with hashvalue 0, and if there
were one already in the skew table then the wrong things would happen.
Also, trading off storing to a tuplestore for scanning a hash bucket
is not obviously a win.
Another way is to return such a row with a flag and jump straight to
HJ_FILL_OUTER_TUPLE, skipping the probe.
I like this idea substantially better. More or less as attached?
regards, tom lane