Re: Default setting for enable_hashagg_disk

On Fri, Jun 26, 2020 at 12:37:26PM -0400, Bruce Momjian wrote:
>On Fri, Jun 26, 2020 at 04:44:14PM +0200, Tomas Vondra wrote:
>> On Fri, Jun 26, 2020 at 12:02:10AM -0400, Bruce Momjian wrote:
>> > On Fri, Jun 26, 2020 at 01:53:57AM +0200, Tomas Vondra wrote:
>> > > I'm not saying it's not beneficial to use different limits for different
>> > > nodes. Some nodes are less sensitive to the size (e.g. sorting often
>> > > gets faster with smaller work_mem). But I think we should instead have a
>> > > per-session limit, and the planner should "distribute" the memory to
>> > > different nodes. It's a hard problem, of course.
>> >
>> > Yeah, I am actually confused why we haven't developed a global memory
>> > allocation strategy and continue to use per-session work_mem.
>> >
>>
>> I think it's pretty hard problem, actually. One of the reasons is that
>
>Yes, it is a hard problem, because it is balancing memory for shared
>buffers, work_mem, and kernel buffers:
>
> https://momjian.us/main/blogs/pgblog/2018.html#December_7_2018
>
>I think the big problem is that the work_mem value is not one value but
>a floating value that is different per query and session, and concurrent
>session activity.
>
>> the costing of a node depends on the amount of memory available to the
>> node, but as we're building the plan bottom-up, we have no information
>> about the nodes above us. So we don't know if there are operations that
>> will need memory, how sensitive they are, etc.
>>
>> And so far the per-node limit served us pretty well, I think. So I'm not
>> very confused we don't have the per-session limit yet, TBH.
>
>I was thinking more of being able to allocate a single value to be
>shared by all active sesions.
>

Not sure I understand. What "single value" do you mean?

Wasn't the idea was to replace work_mem with something like query_mem?
That'd be nice, but I think it's inherently circular - we don't know how
to distribute this to different nodes until we know which nodes will
need a buffer, but the buffer size is important for costing (so we need
it when constructing the paths).

Plus then there's the question whether all nodes should get the same
fraction, or less sensitive nodes should get smaller chunks, etc.
Ultimately this would be based on costing too, I think, but it makes it
soe much complex ...

>Also, doesn't this blog entry also show that spiling to disk for ORDER
>BY is similarly slow compared to hash aggs?
>
> https://momjian.us/main/blogs/pgblog/2012.html#February_2_2012
>

The post does not mention hashagg at all, so I'm not sure how could it
show that? But I think you're right the spilling itself is not that far
away, in most cases (thanks to the recent fixes made by Jeff).

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services