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
