Re: GroupAggregate and Integer Arrays

On Fri, Oct 23, 2015 at 9:26 AM, David Osborne <david@qcode.co.uk> wrote:
> Ah yes sorry:
>
> I think these cover it...
>
> CREATE AGGREGATE sum (
>       sfunc = array_add,
>       basetype = INTEGER[],
>       stype = INTEGER[],
>       initcond = '{}'
>    );
>
> CREATE OR REPLACE FUNCTION array_add(int[],int[]) RETURNS int[] AS $$
>    -- Add two arrays.
>    select
>       ARRAY (
>          SELECT coalesce($1[i],0) + coalesce($2[i],0)
>          FROM (
>             select generate_series(least(array_lower($1, 1),array_lower($2,
> 1)), greatest(array_upper($1, 1),array_upper($2, 1)), 1) AS i
>          ) sub
>    GROUP BY i
>    ORDER BY i
>    );
> $$ LANGUAGE sql STRICT IMMUTABLE;

You are paying a lot for the convenience of using a sql language
function here.  If you want much better performance, you would
probably have to rewrite it into C.  But that would be a drag, and I
would try just throwing more CPU at it first.

Cheers,

Jeff