From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Peter Geoghegan <pg@bowt.ie>
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: Robert Haas <robertmhaas@gmail.com>
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 15:18:52 +0200
Message-ID: <20200724131852.nul7wihayw4uadp7@development> (raw)
In-Reply-To: <20200724084047.hnhigkgagzifuitd@development>
References: <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>
<CAH2-Wz=QFBj=RhsqqC0-1a3f8BxLXP-x39vZ0dLjMyFmDXpDtw@mail.gmail.com>
<20200724084047.hnhigkgagzifuitd@development>
On Fri, Jul 24, 2020 at 10:40:47AM +0200, Tomas Vondra wrote:
>On Thu, Jul 23, 2020 at 07:33:45PM -0700, Peter Geoghegan wrote:
>>On Thu, Jul 23, 2020 at 6:22 PM Tomas Vondra
>><tomas.vondra@2ndquadrant.com> wrote:
>>>So let me share some fresh I/O statistics collected on the current code
>>>using iosnoop. I've done the tests on two different machines using the
>>>"aggregate part" of TPC-H Q17, i.e. essentially this:
>>>
>>> SELECT * FROM (
>>> SELECT
>>> l_partkey AS agg_partkey,
>>> 0.2 * avg(l_quantity) AS avg_quantity
>>> FROM lineitem GROUP BY l_partkey OFFSET 1000000000
>>> ) part_agg;
>>>
>>>The OFFSET is there just to ensure we don't need to send anything to
>>>the client, etc.
>>
>>Thanks for testing this.
>>
>>>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).
>>
>>What I find when I run your query (with my own TPC-H DB that is
>>smaller than what you used here -- 59,986,052 lineitem tuples) is that
>>the sort required about 7x more memory than the hash agg to do
>>everything in memory: 4,384,711KB for the quicksort vs 630,801KB peak
>>hash agg memory usage. I'd be surprised if the ratio was very
>>different for you -- but can you check?
>>
>
>I can check, but it's not quite clear to me what are we looking for?
>Increase work_mem until there's no need to spill in either case?
>
FWIW the hashagg needs about 4775953kB and the sort 33677586kB. So yeah,
that's about 7x more. I think that's probably built into the TPC-H data
set. It'd be easy to construct cases with much higher/lower factors.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachments:plans.txt (text/plain, 1.8K) download
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=15493271.13..15493271.13 rows=1 width=36) (actual time=331351.099..331351.099 rows=0 loops=1)
-> HashAggregate (cost=15186647.64..15493271.13 rows=20441566 width=36) (actual time=318190.465..330956.383 rows=15000000 loops=1)
Group Key: lineitem.l_partkey
Peak Memory Usage: 4775953kB
-> Seq Scan on lineitem (cost=0.00..12936556.76 rows=450018176 width=9) (actual time=0.156..56051.850 rows=450019701 loops=1)
Planning Time: 0.151 ms
Execution Time: 331931.239 ms
(7 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=81298097.02..81298097.02 rows=1 width=36) (actual time=415344.639..415344.639 rows=0 loops=1)
-> GroupAggregate (cost=77616337.21..81298097.02 rows=20441566 width=36) (actual time=209292.469..414951.954 rows=15000000 loops=1)
Group Key: lineitem.l_partkey
-> Sort (cost=77616337.21..78741382.65 rows=450018176 width=9) (actual time=209292.435..329583.999 rows=450019701 loops=1)
Sort Key: lineitem.l_partkey
Sort Method: quicksort Memory: 33677586kB
-> Seq Scan on lineitem (cost=0.00..12936556.76 rows=450018176 width=9) (actual time=0.096..72474.733 rows=450019701 loops=1)
Planning Time: 0.145 ms
Execution Time: 417157.598 ms
(9 rows)
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, tgl@sss.pgh.pa.us, pgsql@j-davis.com, alvherre@2ndquadrant.com, dgrowleyml@gmail.com, sfrost@snowman.net, andres@anarazel.de, bruce@momjian.us, robertmhaas@gmail.com, pryzby@telsasoft.com, melanieplageman@gmail.com
Subject: Re: Default setting for enable_hashagg_disk
In-Reply-To: <20200724131852.nul7wihayw4uadp7@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