Date: Fri, 24 Jul 2020 15:18:52 +0200
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: 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>,
	Robert Haas <robertmhaas@gmail.com>,
	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: <20200724131852.nul7wihayw4uadp7@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>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="7qurzzvvsxnmdciu"
Content-Disposition: inline
In-Reply-To: <20200724084047.hnhigkgagzifuitd@development>
Precedence: bulk


--7qurzzvvsxnmdciu
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Disposition: inline

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

--7qurzzvvsxnmdciu
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="plans.txt"

                                                               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)

--7qurzzvvsxnmdciu--