Postgres Pro
Downloads
Home   >   mailing lists

Re: GroupAggregate and Integer Arrays - Mailing list pgsql-performance

From David Osborne
Subject Re: GroupAggregate and Integer Arrays
Date
Msg-id CAKmpXCdOKQ1PGgBm-vB_FU9gKvjzj4jZ6-xTKtCBBxSHzBxxWg@mail.gmail.com
Whole thread Raw
In response to Re: GroupAggregate and Integer Arrays  (Jeff Janes <jeff.janes@gmail.com>)
Responses Re: GroupAggregate and Integer Arrays  (Jeff Janes <jeff.janes@gmail.com>)
List pgsql-performance
Tree view
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;




On 23 October 2015 at 17:15, Jeff Janes <jeff.janes@gmail.com> wrote:
On Fri, Oct 23, 2015 at 7:29 AM, David Osborne <david@qcode.co.uk> wrote:
 
Hi,

Wondering if anyone could suggest how we could improve the performance of this type of query?
The intensive part is the summing of integer arrays as far as I can see.


Postgres does not ship with any 'sum' function which takes array arguments.

> select sum('{1,2,3,4,5,6}'::int[]);

ERROR:  function sum(integer[]) does not exist

Are you using a user defined function?  If so, how did you define it?

Cheers,

Jeff



pgsql-performance by date:

Previous
From: Jeff Janes
Date:
Subject: Re: GroupAggregate and Integer Arrays
Next
From: Merlin Moncure
Date:
Subject: Re: GroupAggregate and Integer Arrays