Message-ID: <9dfde718f4994c14d88ab269b311b884cf5fa6bb.camel@j-davis.com>
Subject: Re: Default setting for enable_hashagg_disk
From: Jeff Davis <pgsql@j-davis.com>
To: Andres Freund <andres@anarazel.de>
Cc: David Rowley <dgrowleyml@gmail.com>, Robert Haas
 <robertmhaas@gmail.com>,  Justin Pryzby <pryzby@telsasoft.com>, Melanie
 Plageman <melanieplageman@gmail.com>, Tomas Vondra
 <tomas.vondra@2ndquadrant.com>, "pgsql-hackers@postgresql.org"
 <pgsql-hackers@postgresql.org>
Date: Thu, 25 Jun 2020 10:44:42 -0700
In-Reply-To: <20200625163746.laoepc7esanptvxt@alap3.anarazel.de>
References: 
	<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>
	 <20200624191433.5gnqgrxfmucexldm@alap3.anarazel.de>
	 <b1e3b762196bb6288a63c235f78344ead900ea6d.camel@j-davis.com>
	 <20200625163746.laoepc7esanptvxt@alap3.anarazel.de>
Content-Type: text/plain; charset="UTF-8"
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

On Thu, 2020-06-25 at 09:37 -0700, Andres Freund wrote:
> > Let's say you have work_mem=32MB and a query that's expected to use
> > 16MB of memory. In reality, it uses 64MB of memory. So you are
> > saying
> > this query would get to use all 64MB of memory, right?
> > 
> > But then you run ANALYZE. Now the query is (correctly) expected to
> > use
> > 64MB of memory. Are you saying this query, executed again with
> > better
> > stats, would only get to use 32MB of memory, and therefore run
> > slower?
> 
> Yes. I think that's ok, because it was taken into account from a
> costing
> perspective int he second case.

What do you mean by "taken into account"?

There are only two possible paths: HashAgg and Sort+Group, and we need
to pick one. If the planner expects one to spill, it is likely to
expect the other to spill. If one spills in the executor, then the
other is likely to spill, too. (I'm ignoring the case with a lot of
tuples and few groups because that doesn't seem relevant.)

Imagine that there was only one path available to choose. Would you
suggest the same thing, that unexpected spills can exceed work_mem but
expected spills can't?
Regards,
	Jeff Davis