# [HACKERS] Perfomance bug in v10

```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
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
```