From: Jeff Davis <pgsql@j-davis.com>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
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: Sat, 25 Jul 2020 10:23:43 -0700
Message-ID: <6ba7eb1d35f79314685aba5bc891cec63f617ddb.camel@j-davis.com> (raw)
In-Reply-To: <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>
<20200724084047.hnhigkgagzifuitd@development>
On Fri, 2020-07-24 at 10:40 +0200, Tomas Vondra wrote:
> 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.
The space for open-partition buffers is also limited to about 25% of
memory. Each open partition takes BLCKSZ memory, so those numbers are
exactly what I'd expect (64*8192 = 512kB).
There's also another effect at work that can cause the total number of
batches to be higher for larger work_mem values: when we do recurse, we
again need to estimate the number of partitions needed. Right now, we
overestimate the number of partitions needed (to be conservative),
which leads to a wider fan-out and lots of tiny partitions, and
therefore more batches.
I think we can improve this by using something like a HyperLogLog on
the hash values of the spilled tuples to get a better estimate for the
number of groups (and therefore the number of partitions) that we need
when we recurse, which would reduce the number of overall batches at
higher work_mem settings. But I didn't get a chance to implement that
yet.
Regards,
Jeff Davis
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: pgsql@j-davis.com, tomas.vondra@2ndquadrant.com, pg@bowt.ie, tgl@sss.pgh.pa.us, 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: <6ba7eb1d35f79314685aba5bc891cec63f617ddb.camel@j-davis.com>
* 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