Re: Default setting for enable_hashagg_disk
От | Tomas Vondra |
---|---|
Тема | Re: Default setting for enable_hashagg_disk |
Дата | |
Msg-id | 20200711232347.ptcgo4snunlkay4g@development обсуждение исходный текст |
Ответ на | Re: Default setting for enable_hashagg_disk (Peter Geoghegan <pg@bowt.ie>) |
Ответы |
Re: Default setting for enable_hashagg_disk
|
Список | pgsql-hackers |
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@snowman.net> wrote: >> * Tom Lane (tgl@sss.pgh.pa.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
В списке pgsql-hackers по дате отправления: