Comparison
Hash joinvsSpill to disk
Hash join
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.
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.
Full entry →Spill to disk
the sort did not fit in memory, started writing to local disk, and the query went from forty seconds to nineteen minutes.
An operator writing intermediate data to disk or to remote storage because it exceeded available memory. It is not an error, which is why it hides: the query still succeeds, just enormously slower. It is one of the first things to look for in a query that got dramatically worse without the SQL changing, and the usual causes are a data volume increase, a skewed key or a join order the planner got wrong.
Full entry →