Re: Default setting for enable_hashagg_disk

Greetings,

* Peter Geoghegan (pg(at)bowt(dot)ie) wrote:
> On Fri, Jul 10, 2020 at 7:17 AM Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > > The hash_mem design (as it stands) would affect both hash join and
> > > hash aggregate. I believe that it makes most sense to have hash-based
> > > nodes under the control of a single GUC. I believe that this
> > > granularity will cause the least problems. It certainly represents a
> > > trade-off.
> >
> > So, now this has moved from being a hack to deal with a possible
> > regression for a small number of users due to new behavior in one node,
> > to a change that has impacts on other nodes that hadn't been changed,
> > all happening during beta.
>
> The common goal is ameliorating or avoiding predictable negative
> consequences for our users. One proposal is an ambitious and
> comprehensive way of dealing with that, that certainly has unique
> risks. The other is much less ambitious, and clearly a kludge -- but
> it's also much less risky. The discussion hasn't really moved at all.

Neither seem to be going in a direction which looks appropriate for a
beta-time change, particuarly given that none of this is *actually* new
territory. Having to increase work_mem to get a HashAgg or HashJoin
that hasn't got a bunch of batches is routine and while it'd be nicer if
PG had a way to, overall, manage memory usage to stay within some
particular value, we don't. When we start going in that direction it'll
be interesting to discuss how much we should favor trying to do an
in-memory HashAgg by reducing the amount of memory allocated to a Sort
node.

> > I'm still not thrilled with the 'hash_mem' kind of idea as it's going in
> > the wrong direction because what's actually needed is a way to properly
> > consider and track overall memory usage- a redesign of work_mem (or some
> > new parameter, but it wouldn't be 'hash_mem') as you say, but all of
> > this discussion should be targeting v14.
>
> It's certainly possible that hash_mem is too radical, and yet not
> radical enough -- in any timeframe (i.e. a total redesign of work_mem
> is the only thing that will be acceptable). I don't understand why you
> refuse to engage with the idea at all, though. The mere fact that
> hash_mem could in theory fix this problem comprehensively *usefully
> frames the problem*. This is the kind of issue where developing a
> shared understanding is very important.

I don't see hash_mem as being any kind of proper fix- it's just punting
to the user saying "we can't figure this out, how about you do it" and,
worse, it's in conflict with how we already ask the user that question.
Turning it into a multiplier doesn't change that either.

> Andres said to me privately that hash_mem could be a good idea, even
> though he opposes it as a fix to the open item for Postgres 13. I
> understand that proposing such a thing during beta is controversial,
> whatever the specifics are. It is a proposal made in the spirit of
> trying to move things forward. Hand wringing about ignoring the
> community's process is completely counterproductive.

I disagree that caring about the fact that we're in beta is
counterproductive. Saying that we should ignore that we're in beta
isn't appropriate and I could say that's counterproductive- though that
hardly seems to be helpful, so I wonder why that comment was chosen.

> There are about 3 general approaches to addressing this problem, and
> hash_mem is one of them. Am I not allowed to point that out? I have
> been completely open and honest about the risks.

I don't think I said at any point that you weren't allowed to suggest
something. I do think, and continue to feel, that not enough
consideration is being given to the fact that we're well past the point
where this kind of development should be happening- and commenting on
how leveling that concern at your proposed solution is mere 'hand
wringing' certainly doesn't reduce my feeling that we're being far too
cavalier with this.

> > > Like I said, the escape hatch GUC is not my preferred solution. But at
> > > least it acknowledges the problem. I don't think that anyone (or
> > > anyone else) believes that work_mem doesn't have serious limitations.
> >
> > work_mem obviously has serious limitations, but that's not novel or new
> > or unexpected by anyone.
>
> In your other email from this morning, you wrote:
>
> "And those workloads would be addressed by increasing work_mem, no?
> Why are we inventing something new here for something that'll only
> impact a small fraction of users in a small fraction of cases and
> where there's already a perfectly workable way to address the issue?"
>
> Which one is it?

Uh, it's clearly both. Those two statements are not contractictory at
all- I agree that work_mem isn't good, and it has limitations, but this
isn't one of those- people can increase work_mem and get the same
HashAgg they got before and have it use all that memory just as it did
before if they want to.

> > > > I'm really rather astounded at the direction this has been going in.
> > >
> > > Why?
> >
> > Due to the fact that we're in beta and now is not the time to be
> > redesigning this feature.
>
> Did you read the discussion?

This is not productive to the discussion. I'd ask that you stop.

Nothing of what you've said thus far has shown me that there were
material bits of the discussion that I've missed. No, that other people
feel differently or have made comments supporting one thing or another
isn't what I would consider material- I'm as allowed my opinions as much
as others, even when I disagree with the majority (or so claimed anyhow-
I've not gone back to count, but I don't claim it to be otherwise
either).

Thanks,

Stephen