Re: Trouble with hashagg spill I/O pattern and costing

On Thu, May 21, 2020 at 12:04:19PM -0700, Jeff Davis wrote:
>On Thu, 2020-05-21 at 20:54 +0200, Tomas Vondra wrote:
>> The last column is master with the tlist tweak alone - it's better
>> than
>> hashagg on master alone, but it's not nearly as good as with both
>> tlist
>> and prealloc patches.
>
>Right, I certainly think we should do the prealloc change, as well.
>
>I'm tweaking the patch to be a bit more flexible. I'm thinking we
>should start the preallocation list size ~8 and then double it up to
>~128 (depending on your results). That would reduce the waste in case
>we have a large number of small partitions.
>

You're reading my mind ;-)

I don't think 128 is necessarily the maximum we should use - it's just
that I haven't tested higher values. I wouldn't be surprised if higher
values made it a bit faster. But we can test and tune that, I agree with
growing the number of pre-allocted blocks over time.

regards

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