From: Jeff Davis <pgsql@j-davis.com>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Trouble with hashagg spill I/O pattern and costing
Date: Mon, 25 May 2020 11:36:42 -0700
Message-ID: <978a6fa0bc00071d59ebcddf15ca72ae5aa7a15d.camel@j-davis.com> (raw)
In-Reply-To: <20200525021045.dilgcsmgiu4l5jpa@development>
References: <20200519151202.u2p2gpiawoaznsv2@development>
<88dd39d782178539d7ee46f98b98579940e2a12e.camel@j-davis.com>
<20200519175320.tl4qm6le5dbg2pcf@development>
<acad6f2ba8d560b9dfa9a564c7b7d3162abf8347.camel@j-davis.com>
<20200521191318.gw3xjivrxracvzmg@development>
<8619437df796903e64c8479e299d13ebacb5fbe0.camel@j-davis.com>
<20200521214122.6opovye4mnbqczlc@development>
<20200525021045.dilgcsmgiu4l5jpa@development>
On Mon, 2020-05-25 at 04:10 +0200, Tomas Vondra wrote:
> algorithm master prealloc tlist prealloc-tlist
> --------------------------------------------------
> hash 1365 437 368 213
> sort 226 214 224 215
>
> The sort row simply means "enable_hashagg = off" and AFAIK the
> patches
> should not have a lot of influence here - the prealloc does, but it's
> fairly negligible.
I also say a small speedup from the prealloc patch for Sort. I wrote if
off initially, but I'm wondering if there's something going on there.
Perhaps drawing K elements from the minheap at once is better for
caching? If so, that's good news, because it means the prealloc list is
a win-win.
> -> Finalize HashAggregate
> Group Key: lineitem_1.l_partkey
> -> Gather
> Workers Planned: 2
> -> Partial HashAggregate
> Group Key:
> lineitem_1.l_partkey
> -> Parallel Seq Scan on
> lineitem lineitem_1
> (20 rows)
Although each worker here only gets half the tuples, it will get
(approximately) all of the *groups*. This may partly explain why the
planner moves away from this plan when there are more workers: the
number of hashagg batches doesn't go down much with more workers.
It also might be interesting to know the estimate for the number of
groups relative to the size of the table. If those two are close, it
might look to the planner like processing the whole input in each
worker isn't much worse than processing all of the groups in each
worker.
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
Subject: Re: Trouble with hashagg spill I/O pattern and costing
In-Reply-To: <978a6fa0bc00071d59ebcddf15ca72ae5aa7a15d.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