Re: Default setting for enable_hashagg_disk

On Sat, Jul 25, 2020 at 1:10 PM Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> On Sat, 2020-07-25 at 11:05 -0700, Peter Geoghegan wrote:
> > What worries me a bit is the sharp discontinuities when spilling with
> > significantly less work_mem than the "optimal" amount. For example,
> > with Tomas' TPC-H query (against my smaller TPC-H dataset), I find
> > that setting work_mem to 6MB looks like this:
>
> ...
>
> > Planned Partitions: 128 Peak Memory Usage: 6161kB Disk
> > Usage: 2478080kB HashAgg Batches: 128
>
> ...
>
> > Planned Partitions: 128 Peak Memory Usage: 5393kB Disk
> > Usage: 2482152kB HashAgg Batches: 11456

> It's not clear to me that overpartitioning is a real problem in this
> case -- but I think the fact that it's causing confusion is enough
> reason to see if we can fix it.

I'm not sure about that either.

FWIW I notice that when I reduce work_mem a little further (to 3MB)
with the same query, the number of partitions is still 128, while the
number of run time batches is 16,512 (an increase from 11,456 from 6MB
work_mem). I notice that 16512/128 is 129, which hints at the nature
of what's going on with the recursion. I guess it would be ideal if
the growth in batches was more gradual as I subtract memory.

--
Peter Geoghegan