Hash Semi Join 5,000-50,000x slower on PG18 vs PG17 with 10+ equality columns and NULL values (identical plan, no spill)

Hello,

We ran into an issue where an operation that normally takes milliseconds turned into hours after upgrading to PostgreSQL 18 (previously using 17). We believe it to be a PostgreSQL 18 execution engine defect.

Our workaround is to set enable_hashjoin=off for this specific query.

Below are the details and reproduction steps generated with Claude Code.

Thanks,
-Dan

PostgreSQL 18 Hash Semi Join regression: reproduction steps
Summary
A self-join EXISTS query with 10 equality columns in its join condition, where any one of those 10 columns is NULL on a meaningful fraction of rows, runs 5,000-50,000x slower on PostgreSQL 18 than on PostgreSQL 17, on identical hardware, identical data, and an identical query plan (Hash Semi Join, same bucket count, same batch count, zero disk spill on either version). Reducing the join condition to 9 columns (same data, same NULL content) makes PG18 just as fast as PG17.
Environment tested
PG17
PG18
Version
17.10 (Debian 17.10-0+deb13u1)
18.4 (Debian 18.4-1.pgdg13+1)
OS
Debian 13 (trixie)
Debian 13 (trixie)
Hardware
Identical vSphere VM spec (4 vCPU / 8GB RAM)
Identical vSphere VM spec (4 vCPU / 8GB RAM)
work_mem / hash_mem_multiplier
5MB / 2 (defaults)
5MB / 2 (defaults)

Result is independent of work_mem — confirmed at every setting from 64kB to 4GB, and independent of enable_hashjoin's alternative plans; this is specifically about the Hash Semi Join node's own execution once chosen.
Reproduction
1. Create the test table
CREATE TABLE hashbug_test AS
SELECT i AS id,
('v' || (i%13))::varchar(255) AS discriminator,
(i/2)::bigint AS parent_id,
('name' || (i%500))::text AS name,
(i/3)::bigint AS long1,
(i%5)::integer AS ttl,
(i%3)::integer AS priorpref,
(i%7)::integer AS weight,
(i%2)::integer AS port,
NULL::integer AS importance, -- <-- the only column that's ever NULL, on every row
('rdata' || (i%1000))::text AS rdata,
(i%100)::bigint AS association_id
FROM generate_series(1, 20702) i;

ANALYZE hashbug_test;
20,702 rows, 12 columns, one column (importance) always NULL. No indexes, no constraints, no triggers.
2. Run the 9-column self-join (drop rdata) — fast on both engines
SELECT count(*) FROM hashbug_test THIS WHERE EXISTS (
SELECT 1 FROM hashbug_test THAT
WHERE THAT.discriminator = THIS.discriminator
AND THAT.parent_id = THIS.parent_id
AND THAT.name = THIS.name
AND THAT.long1 = THIS.long1
AND THAT.ttl = THIS.ttl
AND THAT.priorpref = THIS.priorpref
AND THAT.weight = THIS.weight
AND THAT.port = THIS.port
AND THAT.importance = THIS.importance -- still includes the always-NULL column
AND THAT.id <> THIS.id
);
Expected and actual on both PG17 and PG18: a few milliseconds.
3. Run the 10-column self-join (add rdata back) — catastrophic on PG18 only
SELECT count(*) FROM hashbug_test THIS WHERE EXISTS (
SELECT 1 FROM hashbug_test THAT
WHERE THAT.discriminator = THIS.discriminator
AND THAT.parent_id = THIS.parent_id
AND THAT.name = THIS.name
AND THAT.long1 = THIS.long1
AND THAT.ttl = THIS.ttl
AND THAT.priorpref = THIS.priorpref
AND THAT.weight = THIS.weight
AND THAT.port = THIS.port
AND THAT.importance = THIS.importance
AND THAT.rdata = THIS.rdata -- the only change from step 2
AND THAT.id <> THIS.id
);
Expected (and actual on PG17): a few milliseconds. Actual on PG18: 23,000-29,000 ms — a single query, on 20,702 rows, taking 20+ seconds.
Only difference between steps 2 and 3 is adding one more equality column to the join condition — the query plan is a plain, non-parallel Hash Semi Join in both cases (confirmed via EXPLAIN, no Gather/parallel workers at this row count).
What the plans look like
Run with:
EXPLAIN (ANALYZE, BUFFERS, TIMING) <query from step 3>;

PG17 (10 columns):
Aggregate (cost=2290.04..2290.05 rows=1 width=8) (actual time=9.619..9.622 rows=1 loops=1)
-> Hash Semi Join (cost=1235.59..2290.04 rows=1 width=0) (actual time=9.614..9.616 rows=0 loops=1)
Hash Cond: (((this.discriminator)::text = (that.discriminator)::text) AND (this.parent_id = that.parent_id) AND (this.name = that.name) AND (this.long1 = that.long1) AND (this.ttl = that.ttl) AND (this.priorpref = that.priorpref) AND (this.weight = that.weight) AND (this.port = that.port) AND (this.importance = that.importance) AND (this.rdata = that.rdata))
Join Filter: (that.id <> this.id)
-> Seq Scan on hashbug_test this (cost=0.00..511.02 rows=20702 width=66) (actual time=0.018..0.018 rows=1 loops=1)
-> Hash (cost=511.02..511.02 rows=20702 width=66) (actual time=9.397..9.399 rows=0 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 256kB
-> Seq Scan on hashbug_test that (cost=0.00..511.02 rows=20702 width=66) (actual time=0.014..5.337 rows=20702 loops=1)
Execution Time: 9.806 ms

PG18 (10 columns, otherwise identical query/data):
Aggregate (... actual time=27944.9.. rows=1 loops=1)
-> Hash Semi Join (... actual time=27944.9.. rows=0 loops=1)
Hash Cond: (identical text to PG17 above)
Join Filter: (that.id <> this.id)
-> Seq Scan on hashbug_test this (... actual rows=20702 loops=1) -- note: full scan, vs PG17's rows=1
-> Hash (... actual rows=0 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 2513kB -- note: ~9.8x more than PG17's 256kB
-> Seq Scan on hashbug_test that (... actual rows=20702 loops=1)
Execution Time: 27944.946 ms

Same Hash Cond, same Buckets, same Batches (both single-batch, no disk spill on either version) — but PG18's Hash node reports ~9.8x more memory usage for the identical 20,702-row input, and the outer Seq Scan node reports scanning the full 20,702 rows (rows=20702) where PG17 short-circuits after just 1 row (rows=1) — consistent with PG17's semi-join early-exit optimization working correctly and PG18's not kicking in for this plan.
Isolating the NULL-rate dose-response
The importance column's NULL rate directly drives the execution time — this isn't a hard on/off trigger, it's proportional:
importance NULL rate
PG18 Execution Time
0%
~4-20 ms
5% (1,035 of 20,702 rows)
22 ms
50% (10,351 rows)
1,904 ms
100% (20,702 rows, as in step 1 above)
27,945 ms
To reproduce any row in this table, rebuild the table with a different fraction of rows set to NULL for importance, e.g. for 50%:
UPDATE hashbug_test SET importance = (id % 4)::integer WHERE id % 2 = 0;
ANALYZE hashbug_test;
Live profiling evidence (PG18)
Attaching gdb to the PostgreSQL backend process during the slow query and sampling its stack repeatedly (5 samples across the 20+ second execution) shows ExecScanHashBucket — the function that walks a hash bucket's candidate-tuple chain during the probe phase — on every single sample, with callees doing per-candidate tuple comparison work (texteq, toast_raw_datum_size, slot_getsomeattrs_int). With Buckets: 32768 and only 20,702 tuples on either side of the join, a correctly-functioning hash join should average under 1 candidate per bucket and resolve almost instantly — the profiling shows PG18 is instead spending the entire execution walking much longer bucket chains than that.
#0 0x00005652e458f148 in ?? ()
#1 0x00005652e45b203f in ExecScanHashBucket ()
#2 0x00005652e45b51b2 in ?? ()
#3 0x00005652e45a572c in ?? ()
#4 0x00005652e45a8c69 in ?? ()
#5 0x00005652e459289b in standard_ExecutorRun ()
What's been ruled out

* Not a planner/costing issue — the plan shape, Hash Cond, Buckets, and Batches are identical between PG17 and PG18; the planner's own cost estimate for Hash Semi Join was cheaper than the alternative (Merge Semi Join) on both versions.
* Not disk I/O or spilling — both versions run Batches: 1 (fully in-memory); forcing genuine multi-batch spilling on PG18 (via a tiny work_mem) adds only ~8-10% on top of the baseline, nowhere near explaining the gap.
* Not work_mem/hash_mem_multiplier — no effect at any setting from 64kB to 4GB.
* Not JIT — confirmed off (and forcing it on made no difference either).
* Not parallelism — this plan has no Gather/parallel workers at this row count; disabling parallelism explicitly on a larger table made no difference either.
* Not collation, column type, or data skew/duplication — checked directly; none differ between fast and slow cases beyond the one deliberate change (NULL rate / column count).
* Not the keep_nulls NULL-retention logic in ExecBuildHash32Expr/ExecInitHashJoin (src/backend/executor/execExpr.c, src/backend/executor/nodeHashjoin.c) — that decision is made purely by join type, not by column count, so it doesn't explain the 9-vs-10 threshold directly, though the actual regression is very likely somewhere in this general area of PG18's freshly-reworked semi-join hash execution code (PG18 added Hash Right Semi Join this release, and already shipped two unrelated fixes in this exact subsystem in 18.1).
Column-count threshold, confirmed on this exact NULL-heavy data
Columns in join condition
Hash node Memory Usage
Execution Time (PG18)
9 (drop rdata)
256kB (same as PG17)
10.4 ms
10 (all columns)
2,513kB
28,897 ms
Same data, same always-NULL column, same everything except crossing 10 total join columns — confirming the column-count cliff and the NULL-rate dose-response are the same underlying mechanism, not two separate effects.
Checked for existing reports before filing
Manually browsed every monthly pgsql-bugs archive from PG18's release (September 2025) through the present (July 2026) — 11 months — and read the full text of every thread with even a superficially matching keyword ("hash join," "semi join," "join performance," "NULL," "PG18 regression," "slow"). (The site's own search widget appears to be JS-rendered and returns "no hits" for even trivial common-term queries when fetched programmatically — confirmed broken, not a genuine empty-result signal, so this was done by direct archive browsing instead.)
No existing report matches. Closest candidates, all confirmed to be different bugs on inspection:

* BUG #19449 (Apr 2026) — "few seconds to multiple hours" on PG16+, actively investigated by core PG developers, but attributed to the planner choosing a different plan across versions (multi-table query, no self-join, no column-count threshold) — a planning regression, not an execution-only regression on an unchanged plan.
* BUG #19105 / BUG #19416 — Parallel Hash Join-related, but both are crashes, not slowdowns.
* BUG #19517 (PG19beta1) — mentions "Hash Semi Join," but is a correctness bug (wrong count(*)) from the new Eager Aggregation feature, single-column join, no NULLs.
Confirmed still present on PostgreSQL 19 Beta 2
Re-ran this exact reproduction (identical CREATE TABLE, identical 9- and 10-column queries) against PostgreSQL 19 Beta 2 (19~beta2-1~20260728.1415.g70dad584e83.pgdg13+1, from PGDG's trixie-pgdg-snapshot repo), installed side-by-side on its own port/data directory, independent of the PG17/PG18 instances used elsewhere in this investigation.
Not fixed. Same signature as PG18:
Columns
PG19 Beta 2 Hash Memory Usage
Execution Time
9
256kB
4.8 ms
10
2,351kB
23,392 ms