Re: Default setting for enable_hashagg_disk

On Sat, Jul 11, 2020 at 09:49:43AM -0700, Peter Geoghegan wrote:
>On Sat, Jul 11, 2020 at 7:22 AM Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>> > Have you got a better proposal that is reasonably implementable for v13?
>> > (I do not accept the argument that "do nothing" is a better proposal.)
>
>> So, no, I don't agree that 'do nothing' (except ripping out the one GUC
>> that was already added) is a worse proposal than adding another work_mem
>> like thing that's only for some nodes types.
>
>The question was "Have you got a better proposal that is reasonably
>implementable for v13?".
>
>This is anecdotal, but just today somebody on Twitter reported
>*increasing* work_mem to stop getting OOMs from group aggregate +
>sort:
>
>https://twitter.com/theDressler/status/1281942941133615104
>
>It was possible to fix the problem in this instance, since evidently
>there wasn't anything else that really did try to consume ~5 GB of
>work_mem memory. Evidently the memory isn't available in any general
>sense, so there are no OOMs now. Nevertheless, we can expect OOMs on
>this server just as soon as there is a real need to do a ~5GB sort,
>regardless of anything else.
>

I find that example rather suspicious. I mean, what exactly in the
GroupAgg plan would consume this memory? Surely it'd have to be some
node below the grouping, but sort shouldn't do that, no?

Seems strange.

>I don't think that this kind of perverse effect is uncommon. Hash
>aggregate can naturally be far faster than group agg + sort, Hash agg
>can naturally use a lot less memory in many cases, and we have every
>reason to think that grouping estimates are regularly totally wrong.
>You're significantly underestimating the risk.
>

I agree grouping estimates are often quite off, and I kinda agree with
introducing hash_mem (or at least with the concept that hashing is more
sensitive to amount of memory than sort). Not sure it's the right espace
hatch to the hashagg spill problem, but maybe it is.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services