Date: Fri, 24 Jul 2020 21:16:48 +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: <20200724191648.zmdaofoqayi4al23@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>
 <CA+TgmoZHnQ4j0_bf+HH03_SwRxq6Hxh6W6+9xPiKvGbmOXaQPQ@mail.gmail.com>
 <CAH2-WznP_v5dO-vC=GKXoDSDF6KyVR_La4dJVdr=1KxR_TbpMg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Disposition: inline
In-Reply-To: 
 <CAH2-WznP_v5dO-vC=GKXoDSDF6KyVR_La4dJVdr=1KxR_TbpMg@mail.gmail.com>
Precedence: bulk

On Fri, Jul 24, 2020 at 11:03:54AM -0700, Peter Geoghegan wrote:
>On Fri, Jul 24, 2020 at 8:19 AM Robert Haas <robertmhaas@gmail.com> wrote:
>> 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.
>
>Isn't this more or less the expected behavior in the event of
>partitions that are spilled recursively? The case that Tomas tested
>were mostly cases where work_mem was tiny relative to the data being
>aggregated.
>
>The following is an extract from commit 1f39bce0215 showing some stuff
>added to the beginning of nodeAgg.c:
>
>+ * We also specify a min and max number of partitions per spill. Too few might
>+ * mean a lot of wasted I/O from repeated spilling of the same tuples. Too
>+ * many will result in lots of memory wasted buffering the spill files (which
>+ * could instead be spent on a larger hash table).
>+ */
>+#define HASHAGG_PARTITION_FACTOR 1.50
>+#define HASHAGG_MIN_PARTITIONS 4
>+#define HASHAGG_MAX_PARTITIONS 1024
>

Maybe, but we're nowhere close to these limits. See this table which I
posted earlier:

       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

This is from the non-parallel runs on the i5 machine with 32GB data set,
the first column is work_mem. We're nowhere near the 1024 limit, and the
cardinality estimates are pretty good.

OTOH the number o batches is much higher, so clearly there was some
recursive spilling happening. What I find strange is that this grows
with work_mem and only starts dropping after 64MB.

Also, how could the amount of I/O be almost constant in all these cases?
Surely more recursive spilling should do more I/O, but the Disk Usage
reported by explain analyze does not show anything like ...


regards

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