From: Anish Kejariwal <anishkej@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Rob Sargent <robjsargent@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: pass in array to function for use by where clause? how optimize?
Date: Wed, 13 Apr 2011 12:03:51 -0700
Message-ID: <BANLkTikaMxuLMV+q2vFGWQ59SU1VO5VkLA@mail.gmail.com> (raw)
In-Reply-To: <6181.1302711333@sss.pgh.pa.us>
References: <BANLkTinHyTWh-HZPm0XoTqYEtEqJ8T-iRw@mail.gmail.com>
<5079.1302707363@sss.pgh.pa.us>
<4DA5C5CB.3030907@gmail.com>
<6181.1302711333@sss.pgh.pa.us>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: anishkej@gmail.com, tgl@sss.pgh.pa.us, robjsargent@gmail.com
Subject: Re: pass in array to function for use by where clause? how optimize?
In-Reply-To: <BANLkTikaMxuLMV+q2vFGWQ59SU1VO5VkLA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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