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

Scott Carey <scott(dot)carey(at)algonomy(dot)com> writes:
> I have discovered the root cause.
> This database is old. It pre-dates Postgres 8.4 which introduced
> array_agg. Apparently, in some version prior to 8.4 array_agg was added
> as a user function, defined as below for bigint:

> create AGGREGATE array_agg(
> BASETYPE = bigint,
> SFUNC = array_append,
> STYPE = bigint[],
> INITCOND = '{}'
> );

> So if you create a test database and run the previous test, performance
> will be fine and the query will be fast. Then run:
> create AGGREGATE array_agg(BASETYPE = bigint, SFUNC = array_append,STYPE =
> bigint[], INITCOND = '{}');

> It will be slow and reproduce this behavior.

Thank you for running that to ground! I confirm your results that v13
and up are far slower for this example than v12 was.

> Why would this run so much more slowly after updating from postgres 12 to
> 17? It is a user defined aggregate, although maybe not as optimized as
> the intrinsic one it shouldn't behave this way.

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:

1f39bce021540fde00990af55b4432c55ef4b3c7 is the first bad commit
commit 1f39bce021540fde00990af55b4432c55ef4b3c7
Author: Jeff Davis <jdavis(at)postgresql(dot)org>
Date: Wed Mar 18 15:42:02 2020 -0700

Disk-based Hash Aggregation.

While performing hash aggregation, track memory usage when adding new
groups to a hash table. If the memory usage exceeds work_mem, enter
"spill mode".

(Times quoted are on a Mac M4 Pro, but in assert-enabled builds so
maybe not directly comparable to production.)

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?

regards, tom lane