From: Bruce Momjian <bruce@momjian.us>
To: Jeff Davis <pgsql@j-davis.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Justin Pryzby <pryzby@telsasoft.com>
Cc: David Rowley <dgrowleyml@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Melanie Plageman <melanieplageman@gmail.com>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Default setting for enable_hashagg_disk
Date: Thu, 25 Jun 2020 15:56:07 -0400
Message-ID: <20200625195607.GA16766@momjian.us> (raw)
In-Reply-To: <20200625192442.GD12486@momjian.us>
References: <CA+TgmoagMLfruY4y_40oKLmFFdBRVSGraZsTa5ss9wH546EYhQ@mail.gmail.com>
<ee1a7be071349b93c01ffb9ef4cf3a6830d9e780.camel@j-davis.com>
<CA+TgmoY4-zSMFf8XbpO6uRsMX4vNPPLXvtajAZpG3f9eoEyjdA@mail.gmail.com>
<129c8f1739957589584d9525e1d8782eae3f412f.camel@j-davis.com>
<CAApHDvqFZikXhAGW=UKZKq1_FzHy+XzmUzAJiNj6RWyTHH4UfA@mail.gmail.com>
<20200624090628.GA19640@momjian.us>
<20200624123843.GX4107@telsasoft.com>
<2214502.1593019796@sss.pgh.pa.us>
<fae0c8abc541f90c9078c9ffaa2904caad6a20b7.camel@j-davis.com>
<20200625192442.GD12486@momjian.us>
On Thu, Jun 25, 2020 at 03:24:42PM -0400, Bruce Momjian wrote:
> On Thu, Jun 25, 2020 at 11:10:58AM -0700, Jeff Davis wrote:
> > On Wed, 2020-06-24 at 13:29 -0400, Tom Lane wrote:
> > > If we feel we need something to let people have the v12 behavior
> > > back, let's have
> > > (1) enable_hashagg on/off --- controls planner, same as it ever was
> > > (2) enable_hashagg_spill on/off --- controls executor by disabling
> > > spill
> > >
> > > But I'm not really convinced that we need (2).
> >
> > If we're not going to have a planner GUC, one alternative is to just
> > penalize the disk costs of HashAgg for a release or two. It would only
> > affect the cost of HashAgg paths that are expected to spill, which
> > weren't even generated in previous releases.
> >
> > In other words, multiply the disk costs by enough that the planner will
> > usually not choose HashAgg if expected to spill unless the average
> > group size is quite large (i.e. there are a lot more tuples than
> > groups, but still enough groups to spill).
>
> Well, the big question is whether this costing is actually more accurate
> than what we have now. What I am hearing is that spilling hash agg is
> expensive, so whatever we can do to reflect the actual costs seems like
> a win. If it can be done, it certainly seems better than a cost setting
> few people will use.
It is my understanding that spill of sorts is mostly read sequentially,
while hash reads are random. Is that right? Is that not being costed
properly?
That doesn't fix the misestimation case, but increasing work mem does
allow pre-PG 13 behavior there.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: bruce@momjian.us, pgsql@j-davis.com, tgl@sss.pgh.pa.us, pryzby@telsasoft.com, dgrowleyml@gmail.com, robertmhaas@gmail.com, melanieplageman@gmail.com, tomas.vondra@2ndquadrant.com, pgsql-hackers@postgresql.org
Subject: Re: Default setting for enable_hashagg_disk
In-Reply-To: <20200625195607.GA16766@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox