Date: Sun, 26 Jul 2020 20:34:06 +0200
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Robert Haas <robertmhaas@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	Jeff Davis <pgsql@j-davis.com>,
	Alvaro Herrera <alvherre@2ndquadrant.com>,
	David Rowley <dgrowleyml@gmail.com>,
	Stephen Frost <sfrost@snowman.net>,
	Andres Freund <andres@anarazel.de>,
	Bruce Momjian <bruce@momjian.us>,
	Justin Pryzby <pryzby@telsasoft.com>,
	Melanie Plageman <melanieplageman@gmail.com>,
	"pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: Default setting for enable_hashagg_disk
Message-ID: <20200726183406.qlpunydmex4rxspl@development>
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>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Disposition: inline
In-Reply-To: 
 <CAH2-Wzn_Zh9L-NsBmMDbpLTJnOq_9iuUc7+2DAER=cD3qiPzhg@mail.gmail.com>
Precedence: bulk

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