Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17

Scott Carey <scott(dot)carey(at)algonomy(dot)com> writes:
> On Thu, Apr 2, 2026, 10:38 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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