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