From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Bruce Momjian <bruce@momjian.us>
Cc: Peter Geoghegan <pg@bowt.ie>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: David Rowley <dgrowleyml@gmail.com>
Cc: Jeff Davis <pgsql@j-davis.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: Mon, 29 Jun 2020 23:33:19 +0200
Message-ID: <20200629213319.ldfbi6wiyh6idf6e@development> (raw)
In-Reply-To: <20200629173140.GE17529@momjian.us>
References: <20200626163726.GA3337@momjian.us>
<20200626174513.tmnkic7q5duugvhh@development>
<20200626230020.GD3337@momjian.us>
<20200626235850.gvl3lpfyeobu4evi@development>
<CAH2-Wz=osB4oi_nH8MnosYhVVSNOm5q3=exGe-b3q6gWOgf98w@mail.gmail.com>
<20200627104141.gq7d3hm2tvoqgjjs@development>
<CAH2-Wzn6M7FwwgXe-mSnmCuMuF9OnFZW=m0X6JMOEYt4VfJTgQ@mail.gmail.com>
<20200629150655.ethstxkf3bkqoown@development>
<CAH2-WzmnaKVmZFNzcwAyGzvetLTO_aHkVVptMaBQ-gv1OjFh2g@mail.gmail.com>
<20200629173140.GE17529@momjian.us>
On Mon, Jun 29, 2020 at 01:31:40PM -0400, Bruce Momjian wrote:
>On Mon, Jun 29, 2020 at 10:20:14AM -0700, Peter Geoghegan wrote:
>> I have no reason to believe that the planner is any more or any less
>> likely to conclude that the hash table will fit in memory in v13 as
>> things stand (I don't know if the BufFile issue matters).
>>
>> In general, grouping estimates probably aren't very good compared to
>> join estimates. I imagine that in either v12 or v13 the planner is
>> likely to incorrectly believe that it'll all fit in memory fairly
>> often. v12 was much too permissive about what could happen. But v13 is
>> too conservative.
>
>FYI, we have improved planner statistics estimates for years, which must
>have affected node spill behavior on many node types (except hash_agg),
>and don't remember any complaints about it.
>
I think misestimates for GROUP BY are quite common and very hard to fix.
Firstly, our ndistinct estimator may give pretty bad results depending
e.g. on how is the table correlated.
I've been running some TPC-H benchmarks, and for partsupp.ps_partkey our
estimate was 4338776, when the actual value is 15000000, i.e. ~3.5x
higher. This was with statistics target increased to 1000. I can easily
imagine even worse estimates with lower values.
This ndistinct estimator is used even for extended statistics, so that
can't quite save us. Moreover, the grouping may be on top of a join, in
which case using ndistinct coefficients may not be possible :-(
So I think this is a quite real problem ...
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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-docs@postgresql.org
Cc: tomas.vondra@2ndquadrant.com, bruce@momjian.us, pg@bowt.ie, robertmhaas@gmail.com, andres@anarazel.de, dgrowleyml@gmail.com, pgsql@j-davis.com, pryzby@telsasoft.com, melanieplageman@gmail.com, pgsql-hackers@postgresql.org
Subject: Re: Default setting for enable_hashagg_disk
In-Reply-To: <20200629213319.ldfbi6wiyh6idf6e@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