Re: [PERFORM] GroupAggregate and Integer Arrays

2015-10-26 Thread Merlin Moncure
On Mon, Oct 26, 2015 at 12:45 PM, David Osborne wrote: > Physical memory is 61GB at the moment. > > work_mem is 1,249,104kB I'm not sure if this query is a candidate because of the function, but you can try progressively cranking work_mem and running explain to see what it'd

Re: [PERFORM] GroupAggregate and Integer Arrays

2015-10-24 Thread Marc Mamin
> 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)),

Re: [PERFORM] GroupAggregate and Integer Arrays

2015-10-24 Thread Jeff Janes
On Fri, Oct 23, 2015 at 9:26 AM, David Osborne wrote: > Ah yes sorry: > > I think these cover it... > > CREATE AGGREGATE sum ( > sfunc = array_add, > basetype = INTEGER[], > stype = INTEGER[], > initcond = '{}' >); > > CREATE OR REPLACE FUNCTION

Re: [PERFORM] GroupAggregate and Integer Arrays

2015-10-23 Thread Jeff Janes
On Fri, Oct 23, 2015 at 7:29 AM, David Osborne 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'

Re: [PERFORM] GroupAggregate and Integer Arrays

2015-10-23 Thread David Osborne
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 (

Re: [PERFORM] GroupAggregate and Integer Arrays

2015-10-23 Thread Merlin Moncure
On Friday, October 23, 2015, David Osborne 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. > We're thinking there's not much we can do to

[PERFORM] GroupAggregate and Integer Arrays

2015-10-23 Thread David Osborne
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. We're thinking there's not much we can do to improve performance apart from throw more CPU at it... would love to be proven