Re: Default setting for enable_hashagg_disk

On Sat, Jul 11, 2020 at 3:30 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > I don't see hash_mem as being any kind of proper fix- it's just punting
> > to the user saying "we can't figure this out, how about you do it" and,
> > worse, it's in conflict with how we already ask the user that question.
> > Turning it into a multiplier doesn't change that either.
>
> Have you got a better proposal that is reasonably implementable for v13?
> (I do not accept the argument that "do nothing" is a better proposal.)
>
> I agree that hash_mem is a stopgap, whether it's a multiplier or no,
> but at this point it seems difficult to avoid inventing a stopgap.
> Getting rid of the process-global work_mem setting is a research project,
> and one I wouldn't even count on having results from for v14. In the
> meantime, it seems dead certain that there are applications for which
> the current behavior will be problematic.
>

If this is true then certainly it adds more weight to the argument for
having a solution like hash_mem or some other escape-hatch. I know it
would be difficult to get the real-world data but why not try TPC-H or
similar workloads at a few different scale_factor/size? I was
checking some old results with me for TPC-H runs and I found that many
of the plans were using Finalize GroupAggregate and Partial
GroupAggregate kinds of plans, there were few where I saw Partial
HashAggregate being used but it appears on a random check that
GroupAggregate seems to be used more. It could be that after
parallelism GroupAggregate plans are getting preference but I am not
sure about this. However, even if that is not true, I think after the
parallel aggregates the memory-related thing is taken care of to some
extent automatically because I think after that each worker doing
partial aggregation can be allowed to consume work_mem memory. So,
probably the larger aggregates which are going to give better
performance by consuming more memory would already be parallelized and
would have given the desired results. Now, allowing aggregates to use
more memory via hash_mem kind of thing is beneficial in non-parallel
cases but for cases where parallelism is used it could be worse
because now each work will be entitled to use more memory.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com