pg.ddx.io pgsql-docs@postgresql.org mailing list archivehelp / color / mirror / Atom feed
From: Jeff Davis <pgsql@j-davis.com>
To: Andres Freund <andres@anarazel.de>
Cc: David Rowley <dgrowleyml@gmail.com>
Cc: Robert Haas <robertmhaas@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 10:44:42 -0700
Message-ID: <9dfde718f4994c14d88ab269b311b884cf5fa6bb.camel@j-davis.com> (raw)
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>
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
view thread (91+ messages) latest in thread
Message-ID: <9dfde718f4994c14d88ab269b311b884cf5fa6bb.camel@j-davis.com> Permalink: ../9dfde718f4994c14d88ab269b311b884cf5fa6bb.camel@j-davis.com/ Also on: postgresql.org/message-id/9dfde718f4994c14d88ab269b311b884cf5fa6bb.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, andres@anarazel.de, dgrowleyml@gmail.com, robertmhaas@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: <9dfde718f4994c14d88ab269b311b884cf5fa6bb.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