Date: Thu, 9 Apr 2020 12:24:04 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Jeff Davis <pgsql@j-davis.com>, pgsql-hackers@postgresql.org
Subject: Re: Default setting for enable_hashagg_disk
Message-ID: <20200409172404.GR2228@telsasoft.com>
References: <9d9d1e1252a52ea1bad84ea40dbebfd54e672a0f.camel@j-davis.com>
 <20200407223900.GT2228@telsasoft.com>
 <20200409114855.h4hh7e2wt77p56sv@development>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20200409114855.h4hh7e2wt77p56sv@development>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk

On Thu, Apr 09, 2020 at 01:48:55PM +0200, Tomas Vondra wrote:
> On Tue, Apr 07, 2020 at 05:39:01PM -0500, Justin Pryzby wrote:
> > On Tue, Apr 07, 2020 at 11:20:46AM -0700, Jeff Davis wrote:
> > > The enable_hashagg_disk GUC, if set to true, chooses HashAgg based on
> > > costing. If false, it only generates a HashAgg path if it thinks it will fit
> > > in work_mem, similar to the old behavior (though it wlil now spill to disk if
> > > the planner was wrong about it fitting in work_mem).  The current default is
> > > true.
> > 
> > Are there any other GUCs that behave like that ?  It's confusing to me when I
> > see "Disk Usage: ... kB", despite setting it to "disable", and without the
> > usual disable_cost.  I realize that postgres chose the plan on the hypothesis
> > that it would *not* exceed work_mem, and that spilling to disk is considered
> > preferable to ignoring the setting, and that "going back" to planning phase
> > isn't a possibility.
> 
> It it really any different from our enable_* GUCs? Even if you do e.g.
> enable_sort=off, we may still do a sort. Same for enable_groupagg etc.

Those show that the GUC was disabled by showing disable_cost.  That's what's
different about this one.

Also.. there's no such thing as enable_groupagg?  Unless I've been missing out
on something.

> > "This setting determines whether the planner will elect to use a hash plan
> > which it expects will exceed work_mem and spill to disk.  During execution,
> > hash nodes which exceed work_mem will spill to disk even if this setting is
> > disabled.  To avoid spilling to disk, either increase work_mem (or set
> > enable_hashagg=off)."
> > 
> > For sure the release notes should recommend re-calibrating work_mem.
> 
> I don't follow. Why would the recalibrating be needed?

Because HashAgg plans which used to run fine (because they weren't prevented
from overflowing work_mem) might now run poorly after spilling to disk (because
of overflowing work_mem).

-- 
Justin