No partition pruning when initializing query plan with LATERAL JOIN and aggregates
Marcin Barczyński <[email protected]> Mon, 13 Jan 2020 16:42:18 +0100
| Newsgroups | gmane.comp.db.postgresql.general |
|---|---|
| Message-ID | <CAP3o3PdcYTbHHX9sz7K6EsWnf2L64omM_de6r1eana0=go3YEw@mail.gmail.com> |
--000000000000b556b7059c0752ac
Content-Type: text/plain; charset="UTF-8"
I wonder why partition pruning doesn't work with LATERAL JOIN and
aggregates.
Below is my example tested on PostgreSQL 12.1 (Ubuntu 12.1-1.pgdg18.04+1):
CREATE TABLE demo(key BIGINT) PARTITION BY LIST (key);
CREATE TABLE demo_key_1 PARTITION OF demo FOR VALUES IN (1);
CREATE TABLE demo_key_2 PARTITION OF demo FOR VALUES IN (2);
INSERT INTO demo(key) VALUES (1), (2);
ANALYZE demo;
CREATE TABLE demo2(key BIGINT) PARTITION BY LIST (key);
CREATE TABLE demo2_key_1 PARTITION OF demo2 FOR VALUES IN (1);
CREATE TABLE demo2_key_2 PARTITION OF demo2 FOR VALUES IN (2);
INSERT INTO demo2(key) VALUES (1), (2);
ANALYZE demo2;
Now, if there are no aggregates in SELECT under LATERAL JOIN, everything
works as expected - only a single partition of each table is scanned:
EXPLAIN ANALYZE
SELECT * FROM demo
JOIN LATERAL (
SELECT key AS key2
FROM demo2
WHERE demo2.key = demo.key
) d ON TRUE
WHERE demo.key = 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..2.03 rows=1 width=16) (actual time=0.007..0.008
rows=1 loops=1)
-> Seq Scan on demo_key_1 (cost=0.00..1.01 rows=1 width=8) (actual
time=0.004..0.005 rows=1 loops=1)
Filter: (key = 1)
-> Seq Scan on demo2_key_1 (cost=0.00..1.01 rows=1 width=8) (actual
time=0.001..0.001 rows=1 loops=1)
Filter: (key = 1)
Planning Time: 0.191 ms
Execution Time: 0.025 ms
(7 rows)
However, when I try a very similar query that contains an aggregate
function, partitions of demo2 are not pruned from the query plan:
EXPLAIN ANALYZE
SELECT * FROM demo
JOIN LATERAL (
SELECT sum(demo2.key) AS sum2
FROM demo2
WHERE demo2.key = demo.key
) d ON TRUE
WHERE demo.key = 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=2.03..3.07 rows=1 width=40) (actual time=0.018..0.018
rows=1 loops=1)
-> Seq Scan on demo_key_1 (cost=0.00..1.01 rows=1 width=8) (actual
time=0.005..0.005 rows=1 loops=1)
Filter: (key = 1)
-> Aggregate (cost=2.03..2.04 rows=1 width=32) (actual
time=0.011..0.011 rows=1 loops=1)
-> Append (cost=0.00..2.03 rows=2 width=8) (actual
time=0.004..0.005 rows=1 loops=1)
-> Seq Scan on demo2_key_1 (cost=0.00..1.01 rows=1
width=8) (actual time=0.002..0.002 rows=1 loops=1)
Filter: (key = demo_key_1.key)
-> Seq Scan on demo2_key_2 (cost=0.00..1.01 rows=1
width=8) (never executed)
Filter: (key = demo_key_1.key)
Planning Time: 0.174 ms
Execution Time: 0.082 ms
(11 rows)
Of course, Seq Scan on demo2_key_2 was never executed, but why wasn't it
pruned from the query plan? More complex queries with hundreds of
partitions are affected badly by that.
The workaround is to add a redundant condition to the subquery:
EXPLAIN ANALYZE
SELECT * FROM demo
JOIN LATERAL (
SELECT sum(demo2.key) AS sum2
FROM demo2
WHERE demo2.key = 1 AND demo2.key = demo.key
) d ON TRUE
WHERE demo.key = 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.01..2.05 rows=1 width=40) (actual time=0.011..0.011
rows=1 loops=1)
-> Seq Scan on demo_key_1 (cost=0.00..1.01 rows=1 width=8) (actual
time=0.004..0.004 rows=1 loops=1)
Filter: (key = 1)
-> Aggregate (cost=1.01..1.02 rows=1 width=32) (actual
time=0.006..0.006 rows=1 loops=1)
-> Result (cost=0.00..1.01 rows=1 width=8) (actual
time=0.002..0.002 rows=1 loops=1)
One-Time Filter: (demo_key_1.key = 1)
-> Seq Scan on demo2_key_1 (cost=0.00..1.01 rows=1
width=8) (actual time=0.001..0.002 rows=1 loops=1)
Filter: (key = 1)
Planning Time: 0.079 ms
Execution Time: 0.031 ms
(10 rows)
--
M.B.
--000000000000b556b7059c0752ac
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
<div dir=3D"ltr">I wonder why partition pruning doesn't work with LATER=
AL JOIN and aggregates.<br>Below is my example tested on PostgreSQL 12.1 (U=
buntu 12.1-1.pgdg18.04+1):<br><br>CREATE TABLE demo(key BIGINT) PARTITION B=
Y LIST (key);<br>CREATE TABLE demo_key_1 PARTITION OF demo FOR VALUES IN (1=
);<br>CREATE TABLE demo_key_2 PARTITION OF demo FOR VALUES IN (2);<br>INSER=
T INTO demo(key) VALUES (1), (2);<br>ANALYZE demo;<br><br>CREATE TABLE demo=
2(key BIGINT) PARTITION BY LIST (key);<br>CREATE TABLE demo2_key_1 PARTITIO=
N OF demo2 FOR VALUES IN (1);<br>CREATE TABLE demo2_key_2 PARTITION OF demo=
2 FOR VALUES IN (2);<br>INSERT INTO demo2(key) VALUES (1), (2);<br>ANALYZE =
demo2;<br><br>Now, if there are no aggregates in SELECT under LATERAL JOIN,=
everything works as expected - only a single partition of each table is sc=
anned:<br><br>EXPLAIN ANALYZE<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0SELECT *=
FROM demo<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0JOIN LATERAL (<br> =C2=A0=
=C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0SELECT key AS=
key2<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0FROM demo2<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0WHERE demo2.key =3D demo.key<br> =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0) d ON TRUE<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WHERE demo.key =
=3D 1;<br>=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 QUERY PLAN<br>-----------------------------=
---------------------------------------------------------------------------=
---<br>=C2=A0Nested Loop =C2=A0(cost=3D0.00..2.03 rows=3D1 width=3D16) (act=
ual time=3D0.007..0.008 rows=3D1 loops=3D1)<br>=C2=A0 =C2=A0-> =C2=A0Seq=
Scan on demo_key_1 =C2=A0(cost=3D0.00..1.01 rows=3D1 width=3D8) (actual ti=
me=3D0.004..0.005 rows=3D1 loops=3D1)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
Filter: (key =3D 1)<br>=C2=A0 =C2=A0-> =C2=A0Seq Scan on demo2_key_1 =C2=
=A0(cost=3D0.00..1.01 rows=3D1 width=3D8) (actual time=3D0.001..0.001 rows=
=3D1 loops=3D1)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Filter: (key =3D 1)<br=
>=C2=A0Planning Time: 0.191 ms<br>=C2=A0Execution Time: 0.025 ms<br>(7 rows=
)<br><br>However, when I try a very similar query that contains an aggregat=
e function, partitions of demo2 are not pruned from the query plan:<br><br>=
EXPLAIN ANALYZE<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0SELECT * FROM demo<br>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0JOIN LATERAL (<br>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0SELECT sum(demo2.key) AS sum=
2<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
FROM demo2<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0WHERE demo2.key =3D demo.key<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0) d ON TRUE<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WHERE demo.key =3D 1;<=
br>=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 QUERY PLAN<br>---------------=
---------------------------------------------------------------------------=
-----------------------------<br>=C2=A0Nested Loop =C2=A0(cost=3D2.03..3.07=
rows=3D1 width=3D40) (actual time=3D0.018..0.018 rows=3D1 loops=3D1)<br>=
=C2=A0 =C2=A0-> =C2=A0Seq Scan on demo_key_1 =C2=A0(cost=3D0.00..1.01 ro=
ws=3D1 width=3D8) (actual time=3D0.005..0.005 rows=3D1 loops=3D1)<br>=C2=A0=
=C2=A0 =C2=A0 =C2=A0 =C2=A0Filter: (key =3D 1)<br>=C2=A0 =C2=A0-> =C2=
=A0Aggregate =C2=A0(cost=3D2.03..2.04 rows=3D1 width=3D32) (actual time=3D0=
.011..0.011 rows=3D1 loops=3D1)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =
=C2=A0Append =C2=A0(cost=3D0.00..2.03 rows=3D2 width=3D8) (actual time=3D0.=
004..0.005 rows=3D1 loops=3D1)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
=C2=A0 =C2=A0-> =C2=A0Seq Scan on demo2_key_1 =C2=A0(cost=3D0.00..1.01 =
rows=3D1 width=3D8) (actual time=3D0.002..0.002 rows=3D1 loops=3D1)<br>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Fi=
lter: (key =3D demo_key_1.key)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
=C2=A0 =C2=A0-> =C2=A0Seq Scan on demo2_key_2 =C2=A0(cost=3D0.00..1.01 =
rows=3D1 width=3D8) (never executed)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Filter: (key =3D demo_key_1.key)<b=
r>=C2=A0Planning Time: 0.174 ms<br>=C2=A0Execution Time: 0.082 ms<br>(11 ro=
ws)<br><br>Of course, Seq Scan on demo2_key_2 was never executed, but why w=
asn't it pruned from the query plan? More complex queries with hundreds=
of partitions are affected badly by that.<br><br>The workaround is to add =
a redundant condition to the subquery:<br><br>EXPLAIN ANALYZE<br>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0SELECT * FROM demo<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0JOIN LATERAL (<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0SELECT sum(demo2.key) AS sum2<br> =C2=A0 =C2=A0 =C2=A0=
=C2=A0 =C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0FROM demo2<br> =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WHERE demo2.key =
=3D 1 AND demo2.key =3D demo.key<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0) d =
ON TRUE<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WHERE demo.key =3D 1;<br>=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 QUERY PLAN<br>----------------------=
---------------------------------------------------------------------------=
----------------------<br>=C2=A0Nested Loop =C2=A0(cost=3D1.01..2.05 rows=
=3D1 width=3D40) (actual time=3D0.011..0.011 rows=3D1 loops=3D1)<br>=C2=A0 =
=C2=A0-> =C2=A0Seq Scan on demo_key_1 =C2=A0(cost=3D0.00..1.01 rows=3D1 =
width=3D8) (actual time=3D0.004..0.004 rows=3D1 loops=3D1)<br>=C2=A0 =C2=A0=
=C2=A0 =C2=A0 =C2=A0Filter: (key =3D 1)<br>=C2=A0 =C2=A0-> =C2=A0Aggreg=
ate =C2=A0(cost=3D1.01..1.02 rows=3D1 width=3D32) (actual time=3D0.006..0.0=
06 rows=3D1 loops=3D1)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Res=
ult =C2=A0(cost=3D0.00..1.01 rows=3D1 width=3D8) (actual time=3D0.002..0.00=
2 rows=3D1 loops=3D1)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0One-Time Filter: (demo_key_1.key =3D 1)<br>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Seq Scan on demo2_key_1 =C2=A0(c=
ost=3D0.00..1.01 rows=3D1 width=3D8) (actual time=3D0.001..0.002 rows=3D1 l=
oops=3D1)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
=C2=A0 =C2=A0Filter: (key =3D 1)<br>=C2=A0Planning Time: 0.079 ms<br>=C2=
=A0Execution Time: 0.031 ms<br>(10 rows)<br><br>--=C2=A0<br>M.B.</div>
--000000000000b556b7059c0752ac--