Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 - Mailing list pgsql-performance

Scott Carey <scott.carey@algonomy.com> writes:
> On Thu, Apr 2, 2026, 10:38 Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I did some bisecting using the attached simplified test case, and found
>> that the query execution time jumps from circa 60ms to circa 7500ms here:
>> ...
>> I'm bemused as to why: the test case has work_mem set high enough that
>> we shouldn't be triggering spill mode, so why did this change affect
>> it at all?

> Even stranger, the more spills induced via smaller work_mem the faster it
> goes.
> This suggests something getting more expensive as the hash table gets
> larger.  Significantly more, like O(n^2) or worse.

Yeah.  I watched this query (at work_mem=200MB) with "perf", and I find
that essentially all of the runtime is spent here:

    --96.39%--agg_fill_hash_table (inlined)
              |
               --95.95%--lookup_hash_entries
                         |
                          --95.77%--initialize_hash_entry (inlined)
                                    |
                                     --95.72%--hash_agg_check_limits
                                               |
                                                --95.72%--MemoryContextMemAllocated
                                                          |
                                                           --83.22%--MemoryContextTraverseNext (inlined)
                                                                     |
                                                                      --3.97%--MemoryContextTraverseNext (inlined)

Drilling down further, the step that is slow is hash_agg_check_limits's

    Size        tval_mem = MemoryContextMemAllocated(aggstate->hashcontext->ecxt_per_tuple_memory,
                                                     true);

and a look at the memory context tree explains why:

      ExecutorState: 32768 total in 3 blocks; 15768 free (5 chunks); 17000 used
        ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
        ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
        HashAgg hashed tuples: 2097040 total in 9 blocks; 1045752 free; 1051288 used
        HashAgg meta context: 1056816 total in 2 blocks; 4328 free (0 chunks); 1052488 used
          ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
        ExprContext: 8192 total in 1 blocks; 7952 free (1 chunks); 240 used
          expanded array: 1024 total in 1 blocks; 448 free (0 chunks); 576 used
          expanded array: 1024 total in 1 blocks; 448 free (0 chunks); 576 used
          expanded array: 1024 total in 1 blocks; 448 free (0 chunks); 576 used
          expanded array: 1024 total in 1 blocks; 448 free (0 chunks); 576 used
          expanded array: 1024 total in 1 blocks; 448 free (0 chunks); 576 used
          expanded array: 1024 total in 1 blocks; 448 free (0 chunks); 576 used
          expanded array: 1024 total in 1 blocks; 448 free (0 chunks); 576 used
          ... quite a few more ...
          26174 more child contexts containing 26802176 total in 26174 blocks; 11725952 free (0 chunks); 15076224 used

So the main problem here is we're leaking the arrays made by
array_agg, and a secondary problem is that that drives the
cost of hash_agg_check_limits to an unacceptable level.

            regards, tom lane