<p class="p1">I have the following query:<p class="p1">select seta.store_id, avg(seta.sales) avg_seta, avg(setb.sales)
avg_setb<pclass="p1">from<p class="p1">(select store_id, avg(sales) sales<p class="p1">from store where group_id
in(10,11,12)<pclass="p1">group by store_id<p class="p1">) seta,<p class="p1">(select store_id, avg(sales) sales<p
class="p1">fromstore where group_id in(13,14,15)<p class="p1">group by store_id<p class="p1">) setb<p class="p1">where
seta.store_id= setb.store_id;<p class="p2"><br /><p class="p1">I want to have this query in a function, so that I can
passin arrays for the group IDs. I tried the following, but it's much too slow. I would query the following via:<p
class="p1">select* from store_avg('{10,11,12}','{13,14,15}');<p class="p2"><br /><p class="p1">create or replace
functionstore_avg () returns setof store_avg_type as <p class="p1">$$<p class="p1">select seta.store_id,
avg(seta.sales)avg_seta, avg(setb.sales) avg_setb<p class="p1">from<p class="p1">(select store_id, avg(sales) sales<p
class="p1">fromstore<p class="p1">where group_id in(select $1[i] from generate_subscripts($1, 1) g(i))<p
class="p1">groupby store_id<p class="p1">) seta,<p class="p1">(select store_id, avg(sales) sales<p class="p1">from
store<pclass="p1">where group_id in(select $1[i] from generate_subscripts($1, 1) g(i))<p class="p1">group by store_id<p
class="p1">)setb<p class="p1">where seta.store_id = setb.store_id;<p class="p1">$$ language 'sql';<p class="p2"><br
/><pclass="p1">The above are of course fake queries, but in my much more complex case, is 10 seconds when I have the
group_idshard code, and takes 55 seconds when using the gneerate_subscripts. My assumption, is that optimizer doesn't
workwell with generate_subscripts. What is the best way to do this? Should I do this as plpgsql function, and somehow
definethe set of ints at the beginning of the function? How would i do this? <p class="p1"><br /><p class="p1">Thanks
somuch! I appreciate your help.<p class="p2"><br /><p class="p1">Anish