Aaron Birkland <birkie@gmail.com> writes:
> - why did the amount of memory used for the hash join exceed sort_mem?
> Is sort_mem merely a guideline for planning and not an enforced hard
> limit?
Yup. The deal is that hash join has to decide in advance on how many
"batches" it wants to have (because it will divide up the hash key range
into that many sub-joins). It sets the number of batches as
(estimated total table size) / sort_mem
It will then soldier on trying to stuff one batch's worth of tuples into
memory, no matter how much larger or smaller than sort_mem that turns
out to be.
This doesn't usually present that much of a problem in practice, because
with customary values of sort_mem there is a lot of margin for error.
I am wondering if maybe you are using an optimistically large sort_mem
setting?
> - why wasn't the memory cleared after the transaction finished
> (failed)? The process was still 2GB large. Does memory used for
> sorts just stay around, possibly re-used for other sorts or hashes but
> never de-allocated?
That depends on the details of your malloc package and your kernel, but
on many Unixen a process never really shrinks.
regards, tom lane