Tomas Vondra <tv(at)fuzzy(dot)cz> writes:
> Anyway the output is a bit strange. It's writing a lot of temp files
> that are significantly smaller (about 3MB) than work_mem (128MB).
The most obvious guess about what's doing that is a hash join that has
a drastic overestimate of how many rows it has to hash, so that it's
batching the join unnecessarily. AFAIR, sorts don't spill to disk until
they've actually reached work_mem, but hash join works differently.
regards, tom lane