Message-ID: <fae0c8abc541f90c9078c9ffaa2904caad6a20b7.camel@j-davis.com>
Subject: Re: Default setting for enable_hashagg_disk
From: Jeff Davis <pgsql@j-davis.com>
To: Tom Lane <tgl@sss.pgh.pa.us>, Justin Pryzby <pryzby@telsasoft.com>
Cc: Bruce Momjian <bruce@momjian.us>, David Rowley <dgrowleyml@gmail.com>,
 Robert Haas <robertmhaas@gmail.com>, Melanie Plageman
 <melanieplageman@gmail.com>, Tomas Vondra <tomas.vondra@2ndquadrant.com>,
 pgsql-hackers@postgresql.org
Date: Thu, 25 Jun 2020 11:10:58 -0700
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>
Content-Type: text/plain; charset="UTF-8"
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

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