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.
