public inbox for [email protected]  

help / color / mirror / Atom feed

From: monika yadav <[email protected]>
To: Jeff Janes <[email protected]>
To: [email protected] <[email protected]>
Subject: Re: repeated subplan execution
Date: Wed, 20 Sep 2017 02:31:20 +0000
Message-ID: <CAO=-HtyLBwqA7aaMrW823rdFiRAhkvjbAGxWsG+5V2r6UvzRPA@mail.gmail.com> (raw)
In-Reply-To: <CAMkU=1zLgZH5nXGhKfyS=Q25nS1YVFq6scUxdHLQNz7nq0dPhg@mail.gmail.com>
References: <CAMkU=1zLgZH5nXGhKfyS=Q25nS1YVFq6scUxdHLQNz7nq0dPhg@mail.gmail.com>
List-Unsubscribe:  <mailto:[email protected]?body=unsub%20pgsql-performance>

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 ?

Thanks a lot.

On Wed, 20 Sep 2017 at 5:01 AM, Jeff Janes <[email protected]> wrote:

> I have a complicated query which runs the exact same subplan more than
> once.
>
> Here is a greatly simplified (and rather pointless) query to replicate the
> issue:
>
> select aid, sum_bid from
>     (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
>     ) asdfsadf
> where sum_bid >0;
>
>                                                                      QUERY
> PLAN
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------
>  Group  (cost=0.44..375841.29 rows=931 width=12) (actual
> time=1.233..691.200 rows=679 loops=1)
>    Group Key: pgbench_accounts.aid
>    Filter: ((SubPlan 2) > 0)
>    Rows Removed by Filter: 321
>    ->  Index Scan using pgbench_accounts_pkey on pgbench_accounts
>  (cost=0.44..634.32 rows=931 width=8) (actual time=0.040..1.783 rows=1000
> loops=1)
>          Index Cond: ((aid >= 1) AND (aid <= 1000))
>    SubPlan 2
>      ->  Aggregate  (cost=403.00..403.01 rows=1 width=8) (actual
> time=0.406..0.407 rows=1 loops=1000)
>            ->  Seq Scan on pgbench_branches pgbench_branches_1
>  (cost=0.00..403.00 rows=1 width=4) (actual time=0.392..0.402 rows=1
> loops=1000)
>                  Filter: ((bbalance >= ('-10000'::integer -
> pgbench_accounts.abalance)) AND (bbalance <= (1 +
> pgbench_accounts.abalance)))
>                  Rows Removed by Filter: 199
>    SubPlan 1
>      ->  Aggregate  (cost=403.00..403.01 rows=1 width=8) (actual
> time=0.407..0.407 rows=1 loops=679)
>            ->  Seq Scan on pgbench_branches  (cost=0.00..403.00 rows=1
> width=4) (actual time=0.388..0.402 rows=1 loops=679)
>                  Filter: ((bbalance >= ('-10000'::integer -
> pgbench_accounts.abalance)) AND (bbalance <= (1 +
> pgbench_accounts.abalance)))
>                  Rows Removed by Filter: 199
>  Planning time: 0.534 ms
>  Execution time: 691.784 ms
>
>
> https://explain.depesz.com/s/Xaib
>
>
> The subplan is not so fast that I wish it to be executed again or every
> row which passes the filter.
>
> I can prevent this dual execution using a CTE, but that creates other
> problems.  Is there a way to get rid of it without resorting to that?
>
> Maybe also a question for bugs and/or hackers, is why should I need to do
> anything special to avoid dual execution?
>
> 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: <CAO=-HtyLBwqA7aaMrW823rdFiRAhkvjbAGxWsG+5V2r6UvzRPA@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