pass in array to function for use by where clause? how optimize?

From: Anish Kejariwal <anishkej(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: pass in array to function for use by where clause? how optimize?
Date: 2011-04-12 17:08:25
Message-ID: BANLkTinHyTWh-HZPm0XoTqYEtEqJ8T-iRw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I have the following query:

select seta.store_id, avg(seta.sales) avg_seta, avg(setb.sales) avg_setb

from

(select store_id, avg(sales) sales

from store where group_id in(10,11,12)

group by store_id

) seta,

(select store_id, avg(sales) sales

from store where group_id in(13,14,15)

group by store_id

) setb

where seta.store_id = setb.store_id;

I want to have this query in a function, so that I can pass in arrays for
the group IDs. I tried the following, but it's much too slow. I would
query the following via:

select * from store_avg('{10,11,12}','{13,14,15}');

create or replace function store_avg () returns setof store_avg_type as

$$

select seta.store_id, avg(seta.sales) avg_seta, avg(setb.sales) avg_setb

from

(select store_id, avg(sales) sales

from store

where group_id in(select $1[i] from generate_subscripts($1, 1) g(i))

group by store_id

) seta,

(select store_id, avg(sales) sales

from store

where group_id in(select $1[i] from generate_subscripts($1, 1) g(i))

group by store_id

) setb

where seta.store_id = setb.store_id;

$$ language 'sql';

The above are of course fake queries, but in my much more complex case, is
10 seconds when I have the group_ids hard code, and takes 55 seconds when
using the gneerate_subscripts. My assumption, is that optimizer doesn't
work well with generate_subscripts. What is the best way to do this?
Should I do this as plpgsql function, and somehow define the set of ints at
the beginning of the function? How would i do this?

Thanks so much! I appreciate your help.

Anish

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Andreas Gaab 2011-04-13 09:15:38 unnesting of array of different size explodes memory
Previous Message scorpdaddy 2011-04-08 14:18:21 Capture insert returning