From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Stephen Frost <sfrost@snowman.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jeff Davis <pgsql@j-davis.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: David Rowley <dgrowleyml@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>
Cc: Melanie Plageman <melanieplageman@gmail.com>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: Default setting for enable_hashagg_disk
Date: Sun, 12 Jul 2020 01:23:47 +0200
Message-ID: <20200711232347.ptcgo4snunlkay4g@development> (raw)
In-Reply-To: <CAH2-Wz=L1ogoxCZC417SKC+JawT+O7c_hHOcsHzrYy0eLo+O+Q@mail.gmail.com>
References: <20200709225840.GE12375@tamriel.snowman.net>
<CAH2-WzmbycnZnxFTYnwkSkCPTVQbRoLiuEvo8gkZCKiQTL8GAQ@mail.gmail.com>
<20200710000842.GF12375@tamriel.snowman.net>
<CAH2-Wz=9f-gxPmqL9Lak0xddPmH0bgfAEioK+2BwTkuzyrtZEQ@mail.gmail.com>
<20200710141714.GI12375@tamriel.snowman.net>
<CAH2-Wzkd7cWK-mMPMb63JXxj2QFMan6hw+cQjdJKvdtCOpR7Ng@mail.gmail.com>
<20200710215012.GN12375@tamriel.snowman.net>
<2165606.1594418399@sss.pgh.pa.us>
<20200711142213.GP12375@tamriel.snowman.net>
<CAH2-Wz=L1ogoxCZC417SKC+JawT+O7c_hHOcsHzrYy0eLo+O+Q@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tomas.vondra@2ndquadrant.com, pg@bowt.ie, sfrost@snowman.net, tgl@sss.pgh.pa.us, pgsql@j-davis.com, alvherre@2ndquadrant.com, andres@anarazel.de, bruce@momjian.us, robertmhaas@gmail.com, dgrowleyml@gmail.com, pryzby@telsasoft.com, melanieplageman@gmail.com
Subject: Re: Default setting for enable_hashagg_disk
In-Reply-To: <20200711232347.ptcgo4snunlkay4g@development>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox