Repository navigation
Remove nulls in joins during hash table lookup #15784
Description
Is your feature request related to a problem or challenge?
The join implementation currently removes nulls during equality checking - I believe it would be faster / more efficient if we did so during the hash lookup - by hashing NULLs that occur on the probe side to a different value than NULLs that occur on the build side. The hash table lookup would then (very likely) result in a miss
Describe the solution you'd like
No response
Describe alternatives you've considered
No response
Additional context
No response
Reacted by Daniël Heres
Activity
2010YOUY01 commentedon Apr 21, 2025
It's a good idea. However, I think if the build side has a large HT and the probe side has many
NULLs, the existing implementation is likely to be faster. Some statistics might be needed to guide this optimization decision.Dandandan commentedon Apr 21, 2025
Additionally, I think there is an optimization that pushes down null filters down. Maybe it would be worth testing if it can not pushed down to execute it in the join itself (so it doesn't need to loaded in build side or be used for probing).
ctsk commentedon Apr 21, 2025
That does make sense. I found the filter_null_join_keys rule which takes care of adding not-null filters to the keys of joins. I thus consider optimizing this case in the hash join a low priority as I expect the improvement to be small.