From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Rob Sargent <robjsargent(at)gmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: pass in array to function for use by where clause? how optimize? |
Date: | 2011-04-13 16:15:33 |
Message-ID: | 6181.1302711333@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Rob Sargent <robjsargent(at)gmail(dot)com> writes:
> On 04/13/2011 09:09 AM, Tom Lane wrote:
>> Anish Kejariwal<anishkej(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Charlie | 2011-04-13 16:21:27 | Re: [SQL] unnesting of array of different size explodes memory |
Previous Message | Rob Sargent | 2011-04-13 15:48:27 | Re: pass in array to function for use by where clause? how optimize? |