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

On 7/30/26 22:22, Dan Stefura wrote:
> 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
>

Thanks for the report, and for the reproducer scripts. I've bisected
this, and the regression seems to come from

commit 9ca67658d19e6c258eb4021a326ed7d38b3ab75f
Author: David Rowley <drowley(at)postgresql(dot)org>
Date: Thu Oct 17 14:25:08 2024 +1300

Don't store intermediate hash values in ExprState->resvalue

adf97c156 made it so ExprStates could support hashing and changed
Hash Join to use that instead of manually extracting Datums from
tuples and hashing them one column at a time.

...

I don't quite see why, exactly. David, any ideas?

regards

--
Tomas Vondra