On Thu, Jul 18, 2024 at 4:03 AM David Rowley <dgrowle...@gmail.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
