MIME-Version: 1.0
In-Reply-To: 
 <CAO=-HtyLBwqA7aaMrW823rdFiRAhkvjbAGxWsG+5V2r6UvzRPA@mail.gmail.com>
References: 
 <CAMkU=1zLgZH5nXGhKfyS=Q25nS1YVFq6scUxdHLQNz7nq0dPhg@mail.gmail.com>
 <CAO=-HtyLBwqA7aaMrW823rdFiRAhkvjbAGxWsG+5V2r6UvzRPA@mail.gmail.com>
From: Jeff Janes <jeff.janes@gmail.com>
Date: Wed, 20 Sep 2017 09:46:43 -0700
Message-ID: 
 <CAMkU=1yOhtRXEqLORJ28bHMz_HuBwzSHhaRAq3q1ixOHgWOFoQ@mail.gmail.com>
Subject: Re: repeated subplan execution
To: monika yadav <monika.1234yadav@gmail.com>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Content-Type: multipart/alternative; boundary="001a1144e25a84bef00559a1b815"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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

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

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

<div dir=3D"ltr"><div class=3D"gmail_extra"><div class=3D"gmail_quote">On T=
ue, Sep 19, 2017 at 7:31 PM, monika yadav <span dir=3D"ltr">&lt;<a href=3D"=
mailto:monika.1234yadav@gmail.com" target=3D"_blank">monika.1234yadav@gmail=
.com</a>&gt;</span> wrote:<br><blockquote class=3D"gmail_quote" style=3D"ma=
rgin: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"auto"><br></div><div d=
ir=3D"auto">I didn&#39;t understand why same sub plan for the sub query exe=
cuted two times? As per the query it should have been executed only once.</=
div><div dir=3D"auto"><br></div><div dir=3D"auto">Can someone please explai=
n this behaviour of query execution ?=C2=A0</div></div></blockquote><div><b=
r></div><div><br></div><div>The sum_bid at the end of the query is an alias=
 for the entire subselect, so it not entirely surprising that it gets inter=
polated twice. it is just kind of unfortunate from a performance perspectiv=
e.</div><div><br></div><div>The query I originally gave is equivalent to th=
is query:</div><div><br></div><div><br></div><div><div>=C2=A0select</div><d=
iv>=C2=A0 =C2=A0 =C2=A0 =C2=A0 aid,</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 (=
select sum(bid) from pgbench_branches</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 where bbalance between -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</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 where a=
id between 1 and 1000</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 group by aid=C2=
=A0</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 having (select sum(bid) from pgbe=
nch_branches where bbalance between -10000-abalance and 1+abalance ) &gt;0;=
</div></div><div><br></div><div><br></div><div>In my originally query I jus=
t 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.=C2=A0 They give identical plans.</div><div><br></div><div>C=
heers,</div><div><br></div><div>Jeff</div></div></div></div>

--001a1144e25a84bef00559a1b815--