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 20:34:06 +0200
Message-ID: <20200726183406.qlpunydmex4rxspl@development> (raw)
In-Reply-To: <CAH2-Wzn_Zh9L-NsBmMDbpLTJnOq_9iuUc7+2DAER=cD3qiPzhg@mail.gmail.com>
References: <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>
<20200726000509.vtl4p7vzazdr2dif@development>
<CAH2-Wzn_Zh9L-NsBmMDbpLTJnOq_9iuUc7+2DAER=cD3qiPzhg@mail.gmail.com>
On Sat, Jul 25, 2020 at 05:13:00PM -0700, Peter Geoghegan wrote:
>On Sat, Jul 25, 2020 at 5:05 PM Tomas Vondra
><tomas.vondra@2ndquadrant.com> wrote:
>> 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.
>
>Right.
>
>> 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.
>
>work_mem was set to 200MB, which is more than the reported "Peak
>Memory Usage: 1605334kB". So either the random case significantly
That's 1.6GB, if I read it right. Which is more than 200MB ;-)
>exceeds work_mem and the "Peak Memory Usage" accounting is wrong
>(because it doesn't report this excess), or the random case really
>doesn't exceed work_mem but has a surprising advantage over the sorted
>case.
>
>--
>Peter Geoghegan
--
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: <20200726183406.qlpunydmex4rxspl@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