Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 4859B1337BB4 for ; Wed, 13 Apr 2011 16:04:01 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 14070-01 for ; Wed, 13 Apr 2011 19:03:53 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-vx0-f174.google.com (mail-vx0-f174.google.com [209.85.220.174]) by mail.postgresql.org (Postfix) with ESMTP id 53CE51337B9C for ; Wed, 13 Apr 2011 16:03:52 -0300 (ADT) Received: by vxi39 with SMTP id 39so704180vxi.19 for ; Wed, 13 Apr 2011 12:03:52 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:in-reply-to:references:date :message-id:subject:from:to:cc:content-type; bh=36iClmXPDhZ1b5CujgLc9MU5/Bz3yvt3LMojUGCEI/o=; b=UepmfO/VHlTbfnJezA19KVhnto56wCucvFPc27CKe5n3SEvTAk1axjjLnIvQPzUM1v T4bVnl9WfHIsUfgEKbJhO6Xqn6uoDB8szFCAQ/EFHHIMXJ3EPhq4Xxh3f+K7BsE46oIq 48jjdhCn/CsNjUiZBSRlzY802c9ESmP7D+uTs= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=dZzb19yOlIeqRDnihTtUMf2123RjziaHr0APfcM5w09mCb8jLm6pujA2qBlwZRKXB3 kUUT3PaLCzJijFBE9RsTywGpR/uGn9qeGTopau4K5cg+aBPwNCBxq0HIF4D5O+0DOc/J 3HUKo8QUVI0zCR3CTvNO02HdwmWWpY8ZJQfJw= MIME-Version: 1.0 Received: by 10.220.203.132 with SMTP id fi4mr2131831vcb.193.1302721431801; Wed, 13 Apr 2011 12:03:51 -0700 (PDT) Received: by 10.220.72.210 with HTTP; Wed, 13 Apr 2011 12:03:51 -0700 (PDT) In-Reply-To: <6181.1302711333@sss.pgh.pa.us> References: <5079.1302707363@sss.pgh.pa.us> <4DA5C5CB.3030907@gmail.com> <6181.1302711333@sss.pgh.pa.us> Date: Wed, 13 Apr 2011 12:03:51 -0700 Message-ID: Subject: Re: pass in array to function for use by where clause? how optimize? From: Anish Kejariwal To: Tom Lane Cc: Rob Sargent , pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=90e6ba4fc4902f100f04a0d17880 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.898 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201104/15 X-Sequence-Number: 35262 --90e6ba4fc4902f100f04a0d17880 Content-Type: text/plain; charset=ISO-8859-1 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 wrote: > Rob Sargent writes: > > On 04/13/2011 09:09 AM, Tom Lane wrote: > >> Anish Kejariwal 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 > --90e6ba4fc4902f100f04a0d17880 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Thanks Tom! =A0I've never used the any function before. =A0It totally f= ixed the issue. =A0It's easier to code, makes more sense, and it fixed = my performance issue.

My guess is that the optimizer doe= s not how to deal with generate_subscripts??

Thanks!!

Anish

<= div class=3D"gmail_quote">On Wed, Apr 13, 2011 at 9:15 AM, Tom Lane <tgl@sss.pgh.pa.us&g= t; wrote:
Rob Sargent <robjsargent@gmail.com> writes:
> On 04/13/2011 09:09 AM, Tom Lane wrote:
>> Anish Kejariwal<anishkej@= gmail.com> =A0writes:
>>> (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. =A0Try=
>> where group_id =3D any($1)
>> Not sure if it'll be a great deal faster, but it's at leas= t easier to
>> write.

> Would adding a subservient function which actu= ally 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, wh= ich was
performance. =A0Hiding 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.

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, 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

--90e6ba4fc4902f100f04a0d17880--