9.6.what exactly?
9.6.5
The only thing I can think of offhand that could create temp files far in
excess of the actual data volume is if a hash join repeatedly decides that
it needs to increase the number of hash batches. We have seen that happen
in cases where an inner relation contains a vast number of identical hash
key values: they all go into the same bucket, and the executor keeps
splitting the batch trying to reduce its size, but being all identical
values it will never be the case that some rows go into a different batch
than others. There is logic in there to give up splitting when this
happens, but maybe it's not firing in your case, or maybe somebody broke
it recently :-(.
I think this is exactly the scenario that happened. More below.
I find it suspicious that your plan involves two separate hash joins both
of which have a much larger table on the inside than the outside ---
offhand that does not look very sane. So there's also a question of
why did the planner do that.
What can you tell us about the distributions of the join keys for the two
hash joins --- are those keys unique, or a bit less than unique, or very
much not unique?
We were able to get the query to run, without using much temp space at all, by eliminating this portion of the query:
LEFT JOIN donation d2
ON mca.parent_id = d2.candidate_id AND mca.parent_id IS NOT NULL AND mca.account_id = d2.account_id
AND d2.account_id NOT IN (1, 2, 3, 195, 196, 81)
The resultant full query plan is attached (json format this time).
What was happening is that there is a fairly non-unique "parent_id" value (66k times) that is creating a very large hashkey that it cannot break into a smaller chunks -- so, essentially what your guess was, Tom. Perhaps worth investigating whether that code is still functioning as intended.
Incidentally, I'd also be interested in any suggestions for refactoring this query for better performance. It does complete now if we exclude the problematic account but still takes quite a bit of time and we expect our dataset to only get bigger.
thanks
--Cory