Re: repeated subplan execution

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(dot)janes(at)gmail(dot)com> wrote:

> On Tue, Sep 19, 2017 at 7:31 PM, monika yadav <monika(dot)1234yadav(at)gmail(dot)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
>