MIME-Version: 1.0
From: Jeff Janes <jeff.janes@gmail.com>
Date: Tue, 19 Sep 2017 16:30:27 -0700
Message-ID: 
 <CAMkU=1zLgZH5nXGhKfyS=Q25nS1YVFq6scUxdHLQNz7nq0dPhg@mail.gmail.com>
Subject: repeated subplan execution
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Content-Type: multipart/alternative; boundary="001a114ce5c087e5b30559933e93"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--001a114ce5c087e5b30559933e93
Content-Type: text/plain; charset="UTF-8"

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

--001a114ce5c087e5b30559933e93
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>I have a complicated query which runs the exact same =
subplan more than once.</div><div><br></div><div>Here is a greatly simplifi=
ed (and rather pointless) query to replicate the issue:=C2=A0</div><br><div=
><div>select aid, sum_bid from</div><div>=C2=A0 =C2=A0 (select</div><div>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 aid,</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 (sel=
ect sum(bid) from pgbench_branches</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 where bbalance between -10000-abalance and 1+abalance</div><d=
iv>=C2=A0 =C2=A0 =C2=A0 =C2=A0 ) as sum_bid</div><div>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 from pgbench_accounts</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 where ai=
d between 1 and 1000</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 group by aid</di=
v><div>=C2=A0 =C2=A0 ) asdfsadf</div><div>where sum_bid &gt;0;</div></div><=
div><br></div><div><div>=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</div><div>------=
---------------------------------------------------------------------------=
--------------------------------------------------------------------</div><=
div>=C2=A0Group =C2=A0(cost=3D0.44..375841.29 rows=3D931 width=3D12) (actua=
l time=3D1.233..691.200 rows=3D679 loops=3D1)</div><div>=C2=A0 =C2=A0Group =
Key: pgbench_accounts.aid</div><div>=C2=A0 =C2=A0Filter: ((SubPlan 2) &gt; =
0)</div><div>=C2=A0 =C2=A0Rows Removed by Filter: 321</div><div>=C2=A0 =C2=
=A0-&gt; =C2=A0Index Scan using pgbench_accounts_pkey on pgbench_accounts =
=C2=A0(cost=3D0.44..634.32 rows=3D931 width=3D8) (actual time=3D0.040..1.78=
3 rows=3D1000 loops=3D1)</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index =
Cond: ((aid &gt;=3D 1) AND (aid &lt;=3D 1000))</div><div>=C2=A0 =C2=A0SubPl=
an 2</div><div>=C2=A0 =C2=A0 =C2=A0-&gt; =C2=A0Aggregate =C2=A0(cost=3D403.=
00..403.01 rows=3D1 width=3D8) (actual time=3D0.406..0.407 rows=3D1 loops=
=3D1000)</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt; =C2=A0Seq=
 Scan on pgbench_branches pgbench_branches_1 =C2=A0(cost=3D0.00..403.00 row=
s=3D1 width=3D4) (actual time=3D0.392..0.402 rows=3D1 loops=3D1000)</div><d=
iv>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Filter: ((=
bbalance &gt;=3D (&#39;-10000&#39;::integer - pgbench_accounts.abalance)) A=
ND (bbalance &lt;=3D (1 + pgbench_accounts.abalance)))</div><div>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Rows Removed by Filter:=
 199</div><div>=C2=A0 =C2=A0SubPlan 1</div><div>=C2=A0 =C2=A0 =C2=A0-&gt; =
=C2=A0Aggregate =C2=A0(cost=3D403.00..403.01 rows=3D1 width=3D8) (actual ti=
me=3D0.407..0.407 rows=3D1 loops=3D679)</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0-&gt; =C2=A0Seq Scan on pgbench_branches =C2=A0(cost=3D0.0=
0..403.00 rows=3D1 width=3D4) (actual time=3D0.388..0.402 rows=3D1 loops=3D=
679)</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0Filter: ((bbalance &gt;=3D (&#39;-10000&#39;::integer - pgbench_accounts=
.abalance)) AND (bbalance &lt;=3D (1 + pgbench_accounts.abalance)))</div><d=
iv>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Rows Remov=
ed by Filter: 199</div><div>=C2=A0Planning time: 0.534 ms</div><div>=C2=A0E=
xecution time: 691.784 ms</div></div><div><br></div><div><br></div><div><a =
href=3D"https://explain.depesz.com/s/Xaib">https://explain.depesz.com/s/Xai=
b</a><br></div><div><br></div><div><br></div><div>The subplan is not so fas=
t that I wish it to be executed again or every row which passes the filter.=
 =C2=A0</div><div><br></div><div>I can prevent this dual execution using a =
CTE, but that creates other problems.=C2=A0 Is there a way to get rid of it=
 without resorting to that?</div><div><br></div><div>Maybe also a question =
for bugs and/or hackers, is why should I need to do anything special to avo=
id dual execution?</div><div><br></div><div>Cheers,</div><div><br></div><di=
v>Jeff</div></div>

--001a114ce5c087e5b30559933e93--