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

help / color / mirror / Atom feed
From: Jeff Davis <pgsql@j-davis.com>
To: Robert Haas <robertmhaas@gmail.com>
To: Andres Freund <andres@anarazel.de>
Cc: David Rowley <dgrowleyml@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>
Cc: Melanie Plageman <melanieplageman@gmail.com>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: Default setting for enable_hashagg_disk
Date: Thu, 25 Jun 2020 09:14:56 -0700
Message-ID: <1e75737369e1b6182ebaef7d6bbe098ad620bbb6.camel@j-davis.com> (raw)
In-Reply-To: <CA+TgmobyV9+T-Wjx-cTPdQuRCgt1THz1mL3v1NXC4m4G-H6Rcw@mail.gmail.com>
References: <CA+Tgmoa74xaiZTdLeQ_aiPE7-eJb_Pdkc_K_hv95PdaDjZnxKQ@mail.gmail.com>
	<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>
	<20200624191433.5gnqgrxfmucexldm@alap3.anarazel.de>
	<CA+TgmobyV9+T-Wjx-cTPdQuRCgt1THz1mL3v1NXC4m4G-H6Rcw@mail.gmail.com>

On Wed, 2020-06-24 at 15:28 -0400, Robert Haas wrote:
> On Wed, Jun 24, 2020 at 3:14 PM Andres Freund <andres@anarazel.de>
> wrote:
> > FWIW, my gut feeling is that we'll end up have to separate the
> > "execution time" spilling from using plain work mem, because it'll
> > trigger spilling too often. E.g. if the plan isn't expected to
> > spill,
> > only spill at 10 x work_mem or something like that.  Or we'll need
> > better management of temp file data when there's plenty memory
> > available.

...

> I think that's actually pretty appealing. Separating the memory we
> plan to use from the memory we're willing to use before spilling
> seems
> like a good idea in general, and I think we should probably also do
> it
> in other places - like sorts.

I'm trying to make sense of this. Let's say there are two GUCs:
planner_work_mem=16MB and executor_work_mem=32MB.

And let's say a query comes along and generates a HashAgg path, and the
planner (correctly) thinks if you put all the groups in memory at once,
it would be 24MB. Then the planner, using planner_work_mem, would think
spilling was necessary, and generate a cost that involves spilling.

Then it's going to generate a Sort+Group path, as well. And perhaps it
estimates that sorting all of the tuples in memory would also take
24MB, so it generates a cost that involves spilling to disk.

But it has to choose one of them. We've penalized plans at risk of
spilling to disk, but what's the point? The planner needs to choose one
of them, and both are at risk of spilling to disk.

Regards,
	Jeff Davis







view thread (91+ messages)  latest in thread

Message-ID: <1e75737369e1b6182ebaef7d6bbe098ad620bbb6.camel@j-davis.com>
Permalink:  ../1e75737369e1b6182ebaef7d6bbe098ad620bbb6.camel@j-davis.com/
Also on:    postgresql.org/message-id/1e75737369e1b6182ebaef7d6bbe098ad620bbb6.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, robertmhaas@gmail.com, andres@anarazel.de, dgrowleyml@gmail.com, pryzby@telsasoft.com, melanieplageman@gmail.com, tomas.vondra@2ndquadrant.com, pgsql-hackers@postgresql.org
  Subject: Re: Default setting for enable_hashagg_disk
  In-Reply-To: <1e75737369e1b6182ebaef7d6bbe098ad620bbb6.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