pg.ddx.io  pgsql-docs@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Jeff Davis <pgsql@j-davis.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Bruce Momjian <bruce@momjian.us>
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 11:10:58 -0700
Message-ID: <fae0c8abc541f90c9078c9ffaa2904caad6a20b7.camel@j-davis.com> (raw)
In-Reply-To: <2214502.1593019796@sss.pgh.pa.us>
References: <6292f5a766198d5745eb8cc17ac736366056671d.camel@j-davis.com>
	<CAAKRu_aisiENMsPM2gC4oUY1hHG3yrCwY-fXUg22C6_MJUwQdA@mail.gmail.com>
	<20200610021544.GA14879@telsasoft.com>
	<8bff2e4e8020c3caa16b61a46918d21b573eaf78.camel@j-davis.com>
	<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>

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).

As we learn more and optimize more, we can reduce or eliminate the
penalty in a future release. I'm not sure exactly what the penalty
would be, though.

Regards,
	Jeff Davis







view thread (91+ messages)  latest in thread

Message-ID: <fae0c8abc541f90c9078c9ffaa2904caad6a20b7.camel@j-davis.com>
Permalink:  ../fae0c8abc541f90c9078c9ffaa2904caad6a20b7.camel@j-davis.com/
Also on:    postgresql.org/message-id/fae0c8abc541f90c9078c9ffaa2904caad6a20b7.camel@j-davis.com

 · 

reply
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: pgsql@j-davis.com, tgl@sss.pgh.pa.us, pryzby@telsasoft.com, bruce@momjian.us, 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: <fae0c8abc541f90c9078c9ffaa2904caad6a20b7.camel@j-davis.com>

* 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