Re: slow performance of array_agg after upgrade from 9.2 to 9.5

jaroet <jaroet(at)gmail(dot)com> writes:
> Internally we upgraded from 9.2 to 9.5 en we had defined an median function.
> This became about 7 to 8 times slower using the same functions.
> ...
> We found that the median function that fills an array is the slow part. When
> we change our SQL from median(fieldname) to
> _final_median(array_agg(fieldname)) the performance is even 3 times faster
> than on 9.2.
> So it looks like the array_agg function when used in a self-defined function
> is extremly slow.

But you're not using array_agg, you're using array_append. That isn't
what array_agg is built on. Unfortunately, what array_agg is built on
is something that isn't very convenient to use for custom aggregates,
because it relies on an "internal"-type transition value.

I don't have any magic fix for getting this back to the previous level
of performance, but have started a thread about it on -hackers:
https://www.postgresql.org/message-id/6315.1477677885%40sss.pgh.pa.us

regards, tom lane