Hi!
I found an example where v10 chooses extremely non-optimal plan:
select
i::int as a,
i::int + 1 as b,
0 as c
into t
from
generate_series(1,32) as i;
create unique index i on t (c, a);
explain analyze
SELECT
t1.a, t1.b,
t2.a, t2.b,
t3.a, t3.b,
t4.a, t4.b,
t5.a, t5.b,
t6.a, t6.b
/*
,
t7.a, t7.b,
t8.a, t8.b,
t9.a, t9.b,
t10.a, t10.b
*/
FROM t T1
LEFT OUTER JOIN t T2
ON T1.b = T2.a AND T2.c = 0
LEFT OUTER JOIN t T3
ON T2.b = T3.a AND T3.c = 0
LEFT OUTER JOIN t T4
ON T3.b = T4.a AND T4.c = 0
LEFT OUTER JOIN t T5
ON T4.b = T5.a AND T5.c = 0
LEFT OUTER JOIN t T6
ON T5.b = T6.a AND T6.c = 0
LEFT OUTER JOIN t T7
ON T6.b = T7.a AND T7.c = 0
LEFT OUTER JOIN t T8
ON T7.b = T8.a AND T8.c = 0
LEFT OUTER JOIN t T9
ON T8.b = T9.a AND T9.c = 0
LEFT OUTER JOIN t T10
ON T9.b = T10.a AND T10.c = 0
WHERE T1.c = 0 AND T1.a = 5
;
It takes 4 seconds on my laptop, uncommenting commented lines causes run
forever. analyzing table or removing index reduces execution time to
milliseconds regardless on commented or uncommented lines.
The commit
commit 9c7f5229ad68d7e0e4dd149e3f80257893e404d4
Author: Tom Lane <t...@sss.pgh.pa.us>
Date: Fri Apr 7 22:20:03 2017 -0400
Optimize joins when the inner relation can be proven unique.
seems a root this problem - before it the query takes milliseconds. In
attachment there is a output of explain analyze with commented lines, my
attention was attracted by a huge number of loops:
-> Materialize (cost=0.00..1.40 rows=1 width=8) (actual time=0.000..0.001
rows=17 loops=1048576)
--
Teodor Sigaev E-mail: teo...@sigaev.ru
WWW: http://www.sigaev.ru/
Timing is on.
DROP TABLE
Time: 5,268 ms
SELECT 32
Time: 5,515 ms
CREATE INDEX
Time: 14,971 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..8.55 rows=1 width=48) (actual
time=818.219..4159.317 rows=1 loops=1)
Join Filter: (t1.b = t2.a)
Rows Removed by Join Filter: 31
-> Seq Scan on t t1 (cost=0.00..1.48 rows=1 width=8) (actual
time=0.026..0.032 rows=1 loops=1)
Filter: ((c = 0) AND (a = 5))
Rows Removed by Filter: 31
-> Nested Loop Left Join (cost=0.00..7.06 rows=1 width=40) (actual
time=10.588..4159.270 rows=32 loops=1)
Join Filter: (t2.b = t3.a)
Rows Removed by Join Filter: 993
-> Seq Scan on t t2 (cost=0.00..1.40 rows=1 width=8) (actual
time=0.008..0.020 rows=32 loops=1)
Filter: (c = 0)
-> Nested Loop Left Join (cost=0.00..5.65 rows=1 width=32) (actual
time=0.142..129.970 rows=32 loops=32)
Join Filter: (t3.b = t4.a)
Rows Removed by Join Filter: 993
-> Seq Scan on t t3 (cost=0.00..1.40 rows=1 width=8) (actual
time=0.002..0.010 rows=32 loops=32)
Filter: (c = 0)
-> Nested Loop Left Join (cost=0.00..4.23 rows=1 width=24)
(actual time=0.007..4.055 rows=32 loops=1024)
Join Filter: (t4.b = t5.a)
Rows Removed by Join Filter: 993
-> Seq Scan on t t4 (cost=0.00..1.40 rows=1 width=8)
(actual time=0.002..0.010 rows=32 loops=1024)
Filter: (c = 0)
-> Nested Loop Left Join (cost=0.00..2.82 rows=1
width=16) (actual time=0.003..0.121 rows=32 loops=32768)
Join Filter: (t5.b = t6.a)
Rows Removed by Join Filter: 528
-> Seq Scan on t t5 (cost=0.00..1.40 rows=1
width=8) (actual time=0.002..0.009 rows=32 loops=32768)
Filter: (c = 0)
-> Materialize (cost=0.00..1.40 rows=1 width=8)
(actual time=0.000..0.001 rows=17 loops=1048576)
-> Seq Scan on t t6 (cost=0.00..1.40 rows=1
width=8) (actual time=0.008..0.031 rows=32 loops=1)
Filter: (c = 0)
Planning time: 3.316 ms
Execution time: 4159.596 ms
(31 rows)
Time: 4165,372 ms (00:04,165)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
