Re: Add mention of execution time memory for enable_partitionwise_* GUCs

On Thu, Jul 18, 2024 at 4:03 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> Over on [1], there's a complaint about a query OOMing because the use
> of enable_partitionwise_aggregate caused a plan with 1000 Hash
> Aggregate nodes.
>
> The only mention in the docs is the additional memory requirements and
> CPU for query planning when that GUC is enabled. There's no mention
> that execution could use work_mem * nparts more memory to be used. I
> think that's bad and we should fix it.
>
> I've attached my proposal to fix that.

If those GUCs are enabled, the planner consumes large amount of memory
and also takes longer irrespective of whether partitionwise plan is
used or not. That's why the default is false. If majority of those
joins use nested loop memory, or use index scans instead sorting,
memory consumption won't be as large. Saying that it "can" result in
large increase in execution memory is not accurate. But I agree that
we need to mention the effect of work_mem on partitionwise
join/aggregation.

I had an offlist email exchange with Dimitrios where I suggested that
we should mention this in the work_mem description. I.e. in work_mem
description change "Note that a complex query might perform several
sort and hash operations"
to "Note that a complex query or a query using partitionwise
aggregates or joins might perform several sort and hash operations' '.
And in the description of enable_partitionwise_* GUCs mention that
"Each of the partitionwise join or aggregation which performs
sorting/hashing may consume work_mem worth of memory increasing the
total memory consumed during query execution.

--
Best Wishes,
Ashutosh Bapat