From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jeff Davis <pgsql@j-davis.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: David Rowley <dgrowleyml@gmail.com>
Cc: Stephen Frost <sfrost@snowman.net>
Cc: Andres Freund <andres@anarazel.de>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: Justin Pryzby <pryzby@telsasoft.com>
Cc: Melanie Plageman <melanieplageman@gmail.com>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: Default setting for enable_hashagg_disk
Date: Sun, 26 Jul 2020 02:05:09 +0200
Message-ID: <20200726000509.vtl4p7vzazdr2dif@development> (raw)
In-Reply-To: <CAH2-Wzm=RGSP7aa36_X_zvyx6oeAmbuoNJ8jnn-yF219RK0uoA@mail.gmail.com>
References: <20200720004827.4c46ikcdrfum6xyu@development>
<400617.1595251041@sss.pgh.pa.us>
<20200720172539.6jov6ynljly4wj6i@development>
<20200724012248.y77rpqc73agrsvb3@development>
<CA+TgmoZHnQ4j0_bf+HH03_SwRxq6Hxh6W6+9xPiKvGbmOXaQPQ@mail.gmail.com>
<CAH2-WznP_v5dO-vC=GKXoDSDF6KyVR_La4dJVdr=1KxR_TbpMg@mail.gmail.com>
<20200724191648.zmdaofoqayi4al23@development>
<CAH2-Wzkv7bnoRTkniW4h4RVWbn6mGVySjik7opUiYsc5RGrfBA@mail.gmail.com>
<CAH2-Wz=ur7MQKpaUZJP=Adtg0TPMx5M_WoNE=ke2vUU=amdjPQ@mail.gmail.com>
<CAH2-Wzm=RGSP7aa36_X_zvyx6oeAmbuoNJ8jnn-yF219RK0uoA@mail.gmail.com>
On Sat, Jul 25, 2020 at 10:07:37AM -0700, Peter Geoghegan wrote:
>On Sat, Jul 25, 2020 at 9:39 AM Peter Geoghegan <pg@bowt.ie> wrote:
>> "Peak Memory Usage: 1605334kB"
>>
>> Hash agg avoids spilling entirely (so the planner gets it right this
>> time around). It even uses notably less memory.
>
>I guess that this is because the reported memory usage doesn't reflect
>the space used for transition state, which is presumably most of the
>total -- array_agg() is used in the query.
>
I'm not sure what you mean by "reported memory usage doesn't reflect the
space used for transition state"? Surely it does include that, we've
built the memory accounting stuff pretty much exactly to do that.
I think it's pretty clear what's happening - in the sorted case there's
only a single group getting new values at any moment, so when we decide
to spill we'll only add rows to that group and everything else will be
spilled to disk.
In the unsorted case however we manage to initialize all groups in the
hash table, but at that point the groups are tiny an fit into work_mem.
As we process more and more data the groups grow, but we can't evict
them - at the moment we don't have that capability. So we end up
processing everything in memory, but significantly exceeding work_mem.
FWIW all my tests are done on the same TPC-H data set clustered by
l_shipdate (so probably random with respect to other columns).
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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-hackers@postgresql.org
Cc: tomas.vondra@2ndquadrant.com, pg@bowt.ie, robertmhaas@gmail.com, tgl@sss.pgh.pa.us, pgsql@j-davis.com, alvherre@2ndquadrant.com, dgrowleyml@gmail.com, sfrost@snowman.net, andres@anarazel.de, bruce@momjian.us, pryzby@telsasoft.com, melanieplageman@gmail.com
Subject: Re: Default setting for enable_hashagg_disk
In-Reply-To: <20200726000509.vtl4p7vzazdr2dif@development>
* 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