public inbox for [email protected]  

help / color / mirror / Atom feed

From: monika yadav <[email protected]>
To: Jeff Janes <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: repeated subplan execution
Date: Thu, 21 Sep 2017 16:05:13 +0530
Message-ID: <CAO=-HtwbASjLUsSsimQKDyTVpC8YqTNHYHD5x0YTbdzr6rV=wg@mail.gmail.com> (raw)
In-Reply-To: <CAMkU=1yOhtRXEqLORJ28bHMz_HuBwzSHhaRAq3q1ixOHgWOFoQ@mail.gmail.com>
References: <CAMkU=1zLgZH5nXGhKfyS=Q25nS1YVFq6scUxdHLQNz7nq0dPhg@mail.gmail.com>
	<CAO=-HtyLBwqA7aaMrW823rdFiRAhkvjbAGxWsG+5V2r6UvzRPA@mail.gmail.com>
	<CAMkU=1yOhtRXEqLORJ28bHMz_HuBwzSHhaRAq3q1ixOHgWOFoQ@mail.gmail.com>
List-Unsubscribe:  <mailto:[email protected]?body=unsub%20pgsql-performance>

Hi Jeff,

Thanks for the update and clarification. I will look to see a better
alternative to resolve this twice execution of same plan.

On Wed, Sep 20, 2017 at 10:16 PM, Jeff Janes <[email protected]> wrote:

> On Tue, Sep 19, 2017 at 7:31 PM, monika yadav <[email protected]>
> wrote:
>
>> Hi All,
>>
>> I didn't understand why same sub plan for the sub query executed two
>> times? As per the query it should have been executed only once.
>>
>> Can someone please explain this behaviour of query execution ?
>>
>
>
> The sum_bid at the end of the query is an alias for the entire subselect,
> so it not entirely surprising that it gets interpolated twice. it is just
> kind of unfortunate from a performance perspective.
>
> The query I originally gave is equivalent to this query:
>
>
>  select
>         aid,
>         (select sum(bid) from pgbench_branches
>             where bbalance between -10000-abalance and 1+abalance
>         ) as sum_bid
>         from pgbench_accounts
>         where aid between 1 and 1000
>         group by aid
>         having (select sum(bid) from pgbench_branches where bbalance
> between -10000-abalance and 1+abalance ) >0;
>
>
> In my originally query I just wrapped the whole thing in another select,
> so that I could use the alias rather than having to mechanically repeat the
> entire subquery again in the HAVING section.  They give identical plans.
>
> Cheers,
>
> Jeff
>

reply

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: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: repeated subplan execution
  In-Reply-To: <CAO=-HtwbASjLUsSsimQKDyTVpC8YqTNHYHD5x0YTbdzr6rV=wg@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 agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox