Date: Fri, 24 Jul 2020 10:40:47 +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: <20200724084047.hnhigkgagzifuitd@development>
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>
 <CAH2-Wz=QFBj=RhsqqC0-1a3f8BxLXP-x39vZ0dLjMyFmDXpDtw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Disposition: inline
In-Reply-To: 
 <CAH2-Wz=QFBj=RhsqqC0-1a3f8BxLXP-x39vZ0dLjMyFmDXpDtw@mail.gmail.com>
Precedence: bulk

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?

>I think that there is something pathological about this spill
>behavior, because it sounds like the precise opposite of what you
>might expect when you make a rough extrapolation of what disk I/O will
>be based on the memory used in no-spill cases (as reported by EXPLAIN
>ANALYZE).
>

Maybe, not sure what exactly you think is pathological? The trouble is
hashagg has to spill input tuples but the memory used in no-spill case
represents aggregated groups, so I'm not sure how you could extrapolate
from that ...

FWIW one more suspicious thing that I forgot to mention is the behavior
of the "planned partitions" depending on work_mem, which looks like
this:

       2MB       Planned Partitions:  64    HashAgg Batches:  4160
       4MB       Planned Partitions: 128    HashAgg Batches: 16512
       8MB       Planned Partitions: 256    HashAgg Batches: 21488
      64MB       Planned Partitions:  32    HashAgg Batches:  2720
     256MB       Planned Partitions:   8    HashAgg Batches:     8

I'd expect the number of planned partitions to decrease (slowly) as
work_mem increases, but it seems to increase initially. Seems a bit
strange, but maybe it's expected.

>> What I find really surprising is the costing - despite writing about
>> twice as much data, the hashagg cost is estimated to be much lower than
>> the sort. For example on the i5 machine, the hashagg cost is ~10M, while
>> sort cost is almost 42M. Despite using almost twice as much disk. And
>> the costing is exactly the same for master and the CP_SMALL_TLIST.
>
>That does make it sound like the costs of the hash agg aren't being
>represented. I suppose it isn't clear if this is a costing issue
>because it isn't clear if the execution time performance itself is
>pathological or is instead something that must be accepted as the cost
>of spilling the hash agg in a general kind of way.
>

Not sure, but I think we need to spill roughly as much as sort, so it
seems a bit strange that (a) we're spilling 2x as much data and yet the
cost is so much lower.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services