Re: GroupAggregate and Integer Arrays

On Fri, Oct 23, 2015 at 9:26 AM, David Osborne <david(at)qcode(dot)co(dot)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