From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jeff Davis <pgsql@j-davis.com>
Cc: Peter Geoghegan <pg@bowt.ie>
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: Fri, 24 Jul 2020 18:01:47 +0200
Message-ID: <20200724160147.l673k4yflz7flkh5@development> (raw)
In-Reply-To: <CA+TgmoZHnQ4j0_bf+HH03_SwRxq6Hxh6W6+9xPiKvGbmOXaQPQ@mail.gmail.com>
References: <6d0aba397625df79bdc3ca85adc11b9e48ecb0da.camel@j-davis.com>
<4118680.1595097043@sss.pgh.pa.us>
<571473265c5d5309f4012855ebd2131c136073ab.camel@j-davis.com>
<32871.1595121333@sss.pgh.pa.us>
<88c0af01ad9ac105e424768808a214d855e29a77.camel@j-davis.com>
<20200720004827.4c46ikcdrfum6xyu@development>
<400617.1595251041@sss.pgh.pa.us>
<20200720172539.6jov6ynljly4wj6i@development>
<20200724012248.y77rpqc73agrsvb3@development>
<CA+TgmoZHnQ4j0_bf+HH03_SwRxq6Hxh6W6+9xPiKvGbmOXaQPQ@mail.gmail.com>
On Fri, Jul 24, 2020 at 11:18:48AM -0400, Robert Haas wrote:
>On Thu, Jul 23, 2020 at 9:22 PM Tomas Vondra
><tomas.vondra@2ndquadrant.com> wrote:
>> 2MB 4MB 8MB 64MB 256MB
>> -----------------------------------------------------------
>> hash 6.71 6.70 6.73 6.44 5.81
>> hash CP_SMALL_TLIST 5.28 5.26 5.24 5.04 4.54
>> sort 3.41 3.41 3.41 3.57 3.45
>>
>> So sort writes ~3.4GB of data, give or take. But hashagg/master writes
>> almost 6-7GB of data, i.e. almost twice as much. Meanwhile, with the
>> original CP_SMALL_TLIST we'd write "only" ~5GB of data. That's still
>> much more than the 3.4GB of data written by sort (which has to spill
>> everything, while hashagg only spills rows not covered by the groups
>> that fit into work_mem).
>>
>> I initially assumed this is due to writing the hash value to the tapes,
>> and the rows are fairly narrow (only about 40B per row), so a 4B hash
>> could make a difference - but certainly not this much. Moreover, that
>> does not explain the difference between master and the now-reverted
>> CP_SMALL_TLIST, I think.
>
>This is all really good analysis, I think, but this seems like the key
>finding. It seems like we don't really understand what's actually
>getting written. Whether we use hash or sort doesn't seem like it
>should have this kind of impact on how much data gets written, and
>whether we use CP_SMALL_TLIST or project when needed doesn't seem like
>it should matter like this either.
>
I think for CP_SMALL_TLIST at least some of the extra data can be
attributed to writing the hash value along with the tuple, which sort
obviously does not do. With the 32GB data set (the i5 machine), there
are ~20M rows in the lineitem table, and with 4B hash values that's
about 732MB of extra data. That's about the 50% of the difference
between sort and CP_SMALL_TLIST, and I'd dare to speculate the other 50%
is due to LogicalTape internals (pointers to the next block, etc.)
The question is why master has 2x the overhead of CP_SMALL_TLIST, if
it's meant to write the same set of columns etc.
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, robertmhaas@gmail.com, tgl@sss.pgh.pa.us, pgsql@j-davis.com, pg@bowt.ie, 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: <20200724160147.l673k4yflz7flkh5@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