Re: [GENERAL] Query Using Massive Temp Space

On Mon, Nov 20, 2017 at 9:36 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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