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

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.

They are defined like this:


CREATE OR REPLACE FUNCTION public._final_median(anyarray)
  RETURNS double precision
  LANGUAGE sql
AS
$body$
WITH q AS
  (
     SELECT val
     FROM unnest($1) val
     WHERE VAL IS NOT NULL
     ORDER BY 1
  ),
  cnt AS
  (
    SELECT COUNT(*) AS c FROM q
  )
  SELECT AVG(val)::float8
  FROM
  (
    SELECT val FROM q
    LIMIT  2 - MOD((SELECT c FROM cnt), 2)
    OFFSET GREATEST(CEIL((SELECT c FROM cnt) / 2.0) - 1,0)
  ) q2;
$body$
  IMMUTABLE
  COST 100;


CREATE AGGREGATE median(anyelement)
(
  sfunc = array_append,
  stype = anyarray,
  finalfunc = _final_median,
  initcond = '{}'
);

All SQL still work but a lot slower now. Our tables on which we use this
function are between 5.000 and 150.000 rows with between 18 and 800 columns.

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.

As we have a lot of files in our ETL proces where a lot of median functions
are used we tried to fix this issue instead of altering the median SQL as
mentioned above. But we are not yet succeeding.

Anybody had this issue and knows about a way to solve this gracefully?

Regards,
jaroet



--
View this message in context:
http://postgresql.nabble.com/slow-performance-of-array-agg-after-upgrade-from-9-2-to-9-5-tp5927751.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

jaroet <jaroet@gmail.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