Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2016
    Posts
    1

    Question Unanswered: Slow 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

  2. #2
    Join Date
    Nov 2003
    Posts
    2,988
    Provided Answers: 23
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •