Data engineering·Cost, layout and query performance
one side is built into an in-memory hash table and the other is streamed past it, and it is fast right up until the build side does not fit.
Hash join
Also calledshuffle hash join, build and probe
Joining by building a hash table from one input and probing it with the other. It is the fastest option when the build side fits in memory, and its failure mode is spilling, which turns it into something much slower than the sort-based alternative. Which join an engine picks is a planner decision driven by size estimates, so an unexpectedly slow join is usually a statistics problem rather than a SQL one.