Date: Fri, 26 Jun 2020 12:37:26 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Robert Haas <robertmhaas@gmail.com>, Andres Freund <andres@anarazel.de>,
	David Rowley <dgrowleyml@gmail.com>, Jeff Davis <pgsql@j-davis.com>,
	Justin Pryzby <pryzby@telsasoft.com>,
	Melanie Plageman <melanieplageman@gmail.com>,
	"pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: Default setting for enable_hashagg_disk
Message-ID: <20200626163726.GA3337@momjian.us>
References: 
 <CAApHDvrcfOOjRmO5EZk_a_GKd-RUGG+2FTM=Sr4jZUbHaBJqjA@mail.gmail.com>
 <20200624171203.GB17842@momjian.us>
 <20200624191900.t4sbfk236gndwoa3@alap3.anarazel.de>
 <20200624231810.GB13627@momjian.us>
 <20200624233837.GA21436@momjian.us>
 <CA+TgmoYi_03kpoukBzVrsVeNWY_BcLOFjUD8vTtt-fNmNy5NCA@mail.gmail.com>
 <20200625171756.GB12486@momjian.us>
 <20200625235357.udlest4k6i2uekui@development>
 <20200626040210.GA13963@momjian.us>
 <20200626144414.rftlidu34rigiulv@development>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20200626144414.rftlidu34rigiulv@development>
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk

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.

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

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee