Re: Default setting for enable_hashagg_disk

Greetings,

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> > On Fri, 2020-07-17 at 18:38 -0700, Peter Geoghegan wrote:
> >> There is also the separate question of what to do about the
> >> hashagg_avoid_disk_plan GUC (this is a separate open item that
> >> requires a separate resolution). Tom leans slightly towards removing
> >> it now. Is your position about the same as before?
>
> > Yes, I think we should have that GUC (hashagg_avoid_disk_plan) for at
> > least one release.
>
> You'e being optimistic about it being possible to remove a GUC once
> we ship it. That seems to be a hard sell most of the time.

Agreed.

> I'm honestly a bit baffled about the level of fear being expressed
> around this feature. We have *frequently* made changes that would
> change query plans, perhaps not 100.00% for the better, and never
> before have we had this kind of bikeshedding about whether it was
> necessary to be able to turn it off. I think the entire discussion
> is way out ahead of any field evidence that we need such a knob.
> In the absence of evidence, our default position ought to be to
> keep it simple, not to accumulate backwards-compatibility kluges.

+100

> (The only reason I'm in favor of heap_mem[_multiplier] is that it
> seems like it might be possible to use it to get *better* plans
> than before. I do not see it as a backwards-compatibility knob.)

I still don't think a hash_mem-type thing is really the right direction
to go in, even if making a distinction between memory used for sorting
and memory used for hashing is, and I'm of the general opinion that we'd
be thinking about doing something better and more appropriate- except
for the fact that we're talking about adding this in during beta.

In other words, if we'd stop trying to shoehorn something in, which
we're doing because we're in beta, we'd very likely be talking about all
of this in a very different way and probably be contemplating something
like a query_mem that provides for an overall memory limit and which
favors memory for hashing over memory for sorting, etc.

Thanks,

Stephen