public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jeff Janes <[email protected]>
To: monika yadav <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: repeated subplan execution
Date: Wed, 20 Sep 2017 09:46:43 -0700
Message-ID: <CAMkU=1yOhtRXEqLORJ28bHMz_HuBwzSHhaRAq3q1ixOHgWOFoQ@mail.gmail.com> (raw)
In-Reply-To: <CAO=-HtyLBwqA7aaMrW823rdFiRAhkvjbAGxWsG+5V2r6UvzRPA@mail.gmail.com>
References: <CAMkU=1zLgZH5nXGhKfyS=Q25nS1YVFq6scUxdHLQNz7nq0dPhg@mail.gmail.com>
<CAO=-HtyLBwqA7aaMrW823rdFiRAhkvjbAGxWsG+5V2r6UvzRPA@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
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: <CAMkU=1yOhtRXEqLORJ28bHMz_HuBwzSHhaRAq3q1ixOHgWOFoQ@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