pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpass in array to function for use by where clause? how optimize?
5+ messages / 3 participants
[nested] [flat]
* pass in array to function for use by where clause? how optimize?
@ 2011-04-12 17:08 Anish Kejariwal <anishkej@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Anish Kejariwal @ 2011-04-12 17:08 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pass in array to function for use by where clause? how optimize?
@ 2011-04-13 15:09 Tom Lane <tgl@sss.pgh.pa.us>
parent: Anish Kejariwal <anishkej@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2011-04-13 15:09 UTC (permalink / raw)
To: Anish Kejariwal <anishkej@gmail.com>; +Cc: pgsql-sql
Anish Kejariwal <anishkej@gmail.com> writes:
> (select store_id, avg(sales) sales
> from store
> where group_id in(select $1[i] from generate_subscripts($1, 1) g(i))
Seems like a pretty brute-force way to deal with the array. Try
where group_id = any($1)
Not sure if it'll be a great deal faster, but it's at least easier to
write.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pass in array to function for use by where clause? how optimize?
@ 2011-04-13 15:48 Rob Sargent <robjsargent@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Rob Sargent @ 2011-04-13 15:48 UTC (permalink / raw)
To: pgsql-sql
On 04/13/2011 09:09 AM, Tom Lane wrote:
> Anish Kejariwal<anishkej@gmail.com> writes:
>> (select store_id, avg(sales) sales
>> from store
>> where group_id in(select $1[i] from generate_subscripts($1, 1) g(i))
>
> Seems like a pretty brute-force way to deal with the array. Try
>
> where group_id = any($1)
>
> Not sure if it'll be a great deal faster, but it's at least easier to
> write.
>
> regards, tom lane
>
Would adding a subservient function which actually uses the value of the
iteration (group_id) as a single parameter be of any use?
rjs
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pass in array to function for use by where clause? how optimize?
@ 2011-04-13 16:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rob Sargent <robjsargent@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2011-04-13 16:15 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; +Cc: pgsql-sql
Rob Sargent <robjsargent@gmail.com> writes:
> On 04/13/2011 09:09 AM, Tom Lane wrote:
>> Anish Kejariwal<anishkej@gmail.com> writes:
>>> (select store_id, avg(sales) sales
>>> from store
>>> where group_id in(select $1[i] from generate_subscripts($1, 1) g(i))
>> Seems like a pretty brute-force way to deal with the array. Try
>> where group_id = any($1)
>> Not sure if it'll be a great deal faster, but it's at least easier to
>> write.
> Would adding a subservient function which actually uses the value of the
> iteration (group_id) as a single parameter be of any use?
Well, it'd be unlikely to solve the OP's actual complaint, which was
performance. Hiding the semantics from the planner via a function
(which generally looks like a black box to the planner) is even worse
than hiding the semantics in a sub-SELECT, which I think is probably
the root cause of the performance issue here.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pass in array to function for use by where clause? how optimize?
@ 2011-04-13 19:03 Anish Kejariwal <anishkej@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Anish Kejariwal @ 2011-04-13 19:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Rob Sargent <robjsargent@gmail.com>; pgsql-sql
Thanks Tom! I've never used the any function before. It totally fixed the
issue. It's easier to code, makes more sense, and it fixed my performance
issue.
My guess is that the optimizer does not how to deal with
generate_subscripts??
Thanks!!
Anish
On Wed, Apr 13, 2011 at 9:15 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Rob Sargent <robjsargent@gmail.com> writes:
> > On 04/13/2011 09:09 AM, Tom Lane wrote:
> >> Anish Kejariwal<anishkej@gmail.com> writes:
> >>> (select store_id, avg(sales) sales
> >>> from store
> >>> where group_id in(select $1[i] from generate_subscripts($1, 1) g(i))
>
> >> Seems like a pretty brute-force way to deal with the array. Try
> >> where group_id = any($1)
> >> Not sure if it'll be a great deal faster, but it's at least easier to
> >> write.
>
> > Would adding a subservient function which actually uses the value of the
> > iteration (group_id) as a single parameter be of any use?
>
> Well, it'd be unlikely to solve the OP's actual complaint, which was
> performance. Hiding the semantics from the planner via a function
> (which generally looks like a black box to the planner) is even worse
> than hiding the semantics in a sub-SELECT, which I think is probably
> the root cause of the performance issue here.
>
> regards, tom lane
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-04-13 19:03 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-04-12 17:08 pass in array to function for use by where clause? how optimize? Anish Kejariwal <anishkej@gmail.com>
2011-04-13 15:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-04-13 15:48 ` Rob Sargent <robjsargent@gmail.com>
2011-04-13 16:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-04-13 19:03 ` Anish Kejariwal <anishkej@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox