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
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Gaab | 2011-04-13 09:15:38 | unnesting of array of different size explodes memory |
Previous Message | 2011-04-08 14:18:21 | Capture insert returning |