Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1duUnc-0007vc-UY for pgsql-performance@arkaria.postgresql.org; Wed, 20 Sep 2017 02:31:41 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1duUnc-0001Ny-CH for pgsql-performance@arkaria.postgresql.org; Wed, 20 Sep 2017 02:31:40 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1duUna-0001Nm-Pc for pgsql-performance@postgresql.org; Wed, 20 Sep 2017 02:31:38 +0000 Received: from mail-it0-x230.google.com ([2607:f8b0:4001:c0b::230]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1duUnW-0006xZ-CE for pgsql-performance@postgresql.org; Wed, 20 Sep 2017 02:31:37 +0000 Received: by mail-it0-x230.google.com with SMTP id o200so1582336itg.0 for ; Tue, 19 Sep 2017 19:31:33 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:references:in-reply-to:from:date:message-id:subject:to; bh=+ib13S/JuM7e3M4FIm10Di58BV8UPgJTY3VodBXygec=; b=HCCwdT5lCKcBJ0ZtvAMNhZXUsG6ugwO4nOn7sNVW+DAN86trqQUm4yNTZjeaT9+PR7 8gbFPbOU2s6SkTgbYSCJuLqwx7BU9SmFrmWaW0bHNowONxT3TWKImlEmguPOin+uZzGE JC9Q2PcMgraDqVV/DTBNDK0ckWG7h2/sDHhVg4rQ7dU7oy8Yv1gHLz5K3obvp8LhX1WV ooXMT5L2b3iD6lDdH8Llro8bdkZ4vOor+8bvQapVzVDzWN62RxPWUcoc5BUqIsBKb0Sg TiBCTwJsYJmblGUD078n8LyyRyUpAHeL5JjLKM7TxydwoSn9IrK1mup3XMOS1RTQ23w3 NrPw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to; bh=+ib13S/JuM7e3M4FIm10Di58BV8UPgJTY3VodBXygec=; b=NPRhBNHaMheb5HGO8RS/Ec8d1yx4EABrVyrpLlzDecm6Ty7sFTVjzLSxHPLctYywlp MFycz0s0kpKl03SXaGND3vlu1XVthxS1L5ZYRCiPSG+cHeTylU7H1K1zIOcy+6sIA/U/ tCEDbL0GrW8OLj5AekOd2Jx2qScLO5xwu9s7EZHH4C2ynlpH1GxP+V7dQpsxheOyu+gB P4jadfwe6gZY3wZfhUYW9ipox3vhI95I7EEbICL136AMyAla9A7gMVZAlatJJDQ9saol Jt3yqoXxzjIAyluRjHnVGeHGUhngVs3u1EXZrOWUnY2Xf6CNA2mTufL4qMUO+mgbj9e0 Tfcw== X-Gm-Message-State: AHPjjUiAM4PL7WsSX0nIm8m7aAID1lUNs0wENaV0p2RuvAQ75Rd+391n MbSKzhHixG2mCtOAajsIxp6eTkoywXtxrXmUz6k= X-Google-Smtp-Source: AOwi7QAO0EBIsHnIF8/5Nf0oNVUkmCE2SZFsrowoZG45FcGYAVNLNurBEIbguAs4gQNFTUU+HtAoVctIjQJE7I8dFHE= X-Received: by 10.36.254.135 with SMTP id w129mr929834ith.93.1505874691539; Tue, 19 Sep 2017 19:31:31 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: monika yadav Date: Wed, 20 Sep 2017 02:31:20 +0000 Message-ID: Subject: Re: repeated subplan execution To: Jeff Janes , "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="94eb2c030f7010c768055995c61e" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --94eb2c030f7010c768055995c61e Content-Type: text/plain; charset="UTF-8" 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 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 > --94eb2c030f7010c768055995c61e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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.
<= div dir=3D"auto">
Can someone please explain thi= s behaviour of query execution ?=C2=A0

Thanks a lot.
On We= d, 20 Sep 2017 at 5:01 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
I have a complicated query which runs the exact same subp= lan more than once.

Here is a greatly simplified (= and rather pointless) query to replicate the issue:=C2=A0

select aid, sum_bid from
=C2=A0 =C2=A0 (select
=C2=A0= =C2=A0 =C2=A0 =C2=A0 aid,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 (select su= m(bid) from pgbench_branches
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 where bbalance between -10000-abalance and 1+abalance
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 ) as sum_bid
=C2=A0 =C2=A0 =C2=A0 =C2=A0= from pgbench_accounts
=C2=A0 =C2=A0 =C2=A0 =C2=A0 where aid betw= een 1 and 1000
=C2=A0 =C2=A0 =C2=A0 =C2=A0 group by aid
=C2=A0 =C2=A0 ) asdfsadf
where sum_bid >0;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0QUERY PLAN
-------------= ---------------------------------------------------------------------------= -------------------------------------------------------------
=C2= =A0Group =C2=A0(cost=3D0.44..375841.29 rows=3D931 width=3D12) (actual time= =3D1.233..691.200 rows=3D679 loops=3D1)
=C2=A0 =C2=A0Group Key: p= gbench_accounts.aid
=C2=A0 =C2=A0Filter: ((SubPlan 2) > 0)
=C2=A0 =C2=A0Rows Removed by Filter: 321
=C2=A0 =C2=A0->= ; =C2=A0Index Scan using pgbench_accounts_pkey on pgbench_accounts =C2=A0(c= ost=3D0.44..634.32 rows=3D931 width=3D8) (actual time=3D0.040..1.783 rows= =3D1000 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: = ((aid >=3D 1) AND (aid <=3D 1000))
=C2=A0 =C2=A0SubPlan 2
=C2=A0 =C2=A0 =C2=A0-> =C2=A0Aggregate =C2=A0(cost=3D403.00..40= 3.01 rows=3D1 width=3D8) (actual time=3D0.406..0.407 rows=3D1 loops=3D1000)=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Seq Scan on= pgbench_branches pgbench_branches_1 =C2=A0(cost=3D0.00..403.00 rows=3D1 wi= dth=3D4) (actual time=3D0.392..0.402 rows=3D1 loops=3D1000)
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Filter: ((bbalan= ce >=3D ('-10000'::integer - pgbench_accounts.abalance)) AND (bb= alance <=3D (1 + pgbench_accounts.abalance)))
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Rows Removed by Filter: 199=
=C2=A0 =C2=A0SubPlan 1
=C2=A0 =C2=A0 =C2=A0-> =C2= =A0Aggregate =C2=A0(cost=3D403.00..403.01 rows=3D1 width=3D8) (actual time= =3D0.407..0.407 rows=3D1 loops=3D679)
=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0-> =C2=A0Seq Scan on pgbench_branches =C2=A0(cost=3D0.00..= 403.00 rows=3D1 width=3D4) (actual time=3D0.388..0.402 rows=3D1 loops=3D679= )
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0F= ilter: ((bbalance >=3D ('-10000'::integer - pgbench_accounts.aba= lance)) AND (bbalance <=3D (1 + pgbench_accounts.abalance)))
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Rows Removed = by Filter: 199
=C2=A0Planning time: 0.534 ms
=C2=A0Exec= ution time: 691.784 ms




The subpla= n is not so fast that I wish it to be executed again or every row which pas= ses the filter. =C2=A0

I can prevent this dual exe= cution using a CTE, but that creates other problems.=C2=A0 Is there a way t= o get rid of it without resorting to that?

Maybe a= lso a question for bugs and/or hackers, is why should I need to do anything= special to avoid dual execution?

Cheers,

Jeff
--94eb2c030f7010c768055995c61e--