Message-ID: <978a6fa0bc00071d59ebcddf15ca72ae5aa7a15d.camel@j-davis.com>
Subject: Re: Trouble with hashagg spill I/O pattern and costing
From: Jeff Davis <pgsql@j-davis.com>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: pgsql-hackers@postgresql.org
Date: Mon, 25 May 2020 11:36:42 -0700
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>
Content-Type: text/plain; charset="UTF-8"
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

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