Re: Default setting for enable_hashagg_disk - Mailing list pgsql-hackers
From | Tomas Vondra |
---|---|
Subject | Re: Default setting for enable_hashagg_disk |
Date | |
Msg-id | 20200712003037.wzrcltujwzwiwlfr@development Whole thread Raw |
In response to | Re: Default setting for enable_hashagg_disk ("David G. Johnston" <david.g.johnston@gmail.com>) |
Responses |
Re: Default setting for enable_hashagg_disk
|
List | pgsql-hackers |
On Sat, Jul 11, 2020 at 09:02:43AM -0700, David G. Johnston wrote: >On Sat, Jul 11, 2020 at 7:22 AM Stephen Frost <sfrost@snowman.net> wrote: > >> There now seems to be some suggestions that not only should we have a >> new GUC, but we should default to having it not be equal to work_mem (or >> 1.0 or whatever) and instead by higher, to be *twice* or larger whatever >> the existing work_mem setting is- meaning that people whose systems are >> working just fine and have good estimates that represent their workload >> and who get the plans they want may then start seeing differences and >> increased memory utilization in places that they *don't* want that, all >> because we're scared that someone, somewhere, might see a regression due >> to HashAgg spilling to disk. >> > >If that increased memory footprint allows the planner to give me a better >plan with faster execution and with no OOM I'd be very happy that this >change happened. While having a more flexible memory allocation framework >is not a primary goal in and of itself it is a nice side-effect. I'm not >going to say "let's only set work_mem to 32MB instead of 48MB so I can >avoid this faster HashAgg node and instead execute a nested loop (or >whatever)". More probable is the user whose current nested loop plan is >fast enough and doesn't even realize that with a bit more memory they could >get an HashAgg that performs 15% faster. For them this is a win on its >face. > >I don't believe this negatively impacts the super-admin in our user-base >and is a decent win for the average and below average admin. > >Do we really have an issue with plans being chosen while having access to >more memory being slower than plans chosen while having less memory? > >The main risk here is that we choose for a user to consume more memory than >they expected and they report OOM issues to us. We tell them to set this >new GUC to 1.0. But that implies they are getting many non-HashAgg plans >produced when with a bit more memory those HashAgg plans would have been >chosen. If they get those faster plans without OOM it's a win, if it OOMs >it's a loss. I'm feeling optimistic here and we'll get considerably more >wins than losses. How loss-averse do we need to be here though? Npte we >can give the upgrading user advance notice of our loss-aversion level and >they can simply disagree and set it to 1.0 and/or perform more thorough >testing. So being optimistic feels like the right choice. > I don't know, but one of the main arguments against simply suggesting people to bump up work_mem (if they're hit by the hashagg spill in v13) was that it'd increase overall memory usage for them. It seems strange to then propose a new GUC set to a default that would result in higher memory usage *for everyone*. Of course, having such GUC with a default a multiple of work_mem might be a win overall - or maybe not. I don't have a very good idea how many people will get bitten by this, and how many will get speedup (and how significant the speedup would be). regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
pgsql-hackers by date: