MIME-Version: 1.0
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>
From: monika yadav <monika.1234yadav@gmail.com>
Date: Thu, 21 Sep 2017 16:05:13 +0530
Message-ID: 
 <CAO=-HtwbASjLUsSsimQKDyTVpC8YqTNHYHD5x0YTbdzr6rV=wg@mail.gmail.com>
Subject: Re: repeated subplan execution
To: Jeff Janes <jeff.janes@gmail.com>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Content-Type: multipart/alternative; boundary="001a1140492cce61060559b0a533"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--001a1140492cce61060559b0a533
Content-Type: text/plain; charset="UTF-8"

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 <jeff.janes@gmail.com> wrote:

> On Tue, Sep 19, 2017 at 7:31 PM, monika yadav <monika.1234yadav@gmail.com>
> 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
>

--001a1140492cce61060559b0a533
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi Jeff,<br></div><div><br></div>Thanks for the updat=
e and clarification. I will look to see a better alternative to resolve thi=
s twice execution of same plan. <br></div><div class=3D"gmail_extra"><br><d=
iv class=3D"gmail_quote">On Wed, Sep 20, 2017 at 10:16 PM, Jeff Janes <span=
 dir=3D"ltr">&lt;<a href=3D"mailto:jeff.janes@gmail.com" target=3D"_blank">=
jeff.janes@gmail.com</a>&gt;</span> wrote:<br><blockquote class=3D"gmail_qu=
ote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex=
"><div dir=3D"ltr"><div class=3D"gmail_extra"><div class=3D"gmail_quote"><s=
pan class=3D"">On Tue, Sep 19, 2017 at 7:31 PM, monika yadav <span dir=3D"l=
tr">&lt;<a href=3D"mailto:monika.1234yadav@gmail.com" target=3D"_blank">mon=
ika.1234yadav@gmail.com</a>&gt;</span> wrote:<br><blockquote class=3D"gmail=
_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204=
,204);padding-left:1ex"><div><div dir=3D"auto">Hi All,</div><div dir=3D"aut=
o"><br></div><div dir=3D"auto">I didn&#39;t understand why same sub plan fo=
r the sub query executed two times? As per the query it should have been ex=
ecuted only once.</div><div dir=3D"auto"><br></div><div dir=3D"auto">Can so=
meone please explain this behaviour of query execution ?=C2=A0</div></div><=
/blockquote><div><br></div><div><br></div></span><div>The sum_bid at the en=
d of the query is an alias for the entire subselect, so it not entirely sur=
prising that it gets interpolated twice. it is just kind of unfortunate fro=
m a performance perspective.</div><div><br></div><div>The query I originall=
y gave is equivalent to this query:</div><div><br></div><div><br></div><div=
><span class=3D""><div>=C2=A0select</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 a=
id,</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 (select sum(bid) from pgbench_bra=
nches</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 where bbalance be=
tween -10000-abalance and 1+abalance</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =
) as sum_bid</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 from pgbench_accounts</d=
iv><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 where aid between 1 and 1000</div><div>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 group by aid=C2=A0</div></span><div>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 having (select sum(bid) from pgbench_branches where bbala=
nce between -10000-abalance and 1+abalance ) &gt;0;</div></div><div><br></d=
iv><div><br></div><div>In my originally query I just wrapped the whole thin=
g in another select, so that I could use the alias rather than having to me=
chanically repeat the entire subquery again in the HAVING section.=C2=A0 Th=
ey give identical plans.</div><div><br></div><div>Cheers,</div><div><br></d=
iv><div>Jeff</div></div></div></div>
</blockquote></div><br></div>

--001a1140492cce61060559b0a533--