explain.depesz.com

PostgreSQL's explain analyze made readable

Result: f6lr

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 4,687.526 4,687.526 ↑ 10,375.8 1,875,755 1

CTE Scan on ets (cost=712,235,936.40..1,101,483,556.96 rows=19,462,381,028 width=4) (actual time=0.073..4,687.526 rows=1,875,755 loops=1)

2.          

CTE ets

3. 2,151.817 3,966.565 ↑ 10,375.8 1,875,755 1

Recursive Union (cost=19.90..712,235,936.40 rows=19,462,381,028 width=83) (actual time=0.071..3,966.565 rows=1,875,755 loops=1)

4. 209.445 325.658 ↑ 1.1 1,874,740 1

Nested Loop (cost=19.90..24,898.19 rows=1,972,838 width=83) (actual time=0.068..325.658 rows=1,874,740 loops=1)

5. 1.433 1.433 ↑ 1.1 1,913 1

Seq Scan on place p (cost=0.00..39.09 rows=2,009 width=75) (actual time=0.016..1.433 rows=1,913 loops=1)

6. 114.595 114.780 ↑ 1.0 980 1,913

Materialize (cost=19.90..201.08 rows=982 width=8) (actual time=0.000..0.060 rows=980 loops=1,913)

7. 0.144 0.185 ↑ 1.0 980 1

Bitmap Heap Scan on place_relation pr (cost=19.90..196.17 rows=982 width=8) (actual time=0.049..0.185 rows=980 loops=1)

  • Recheck Cond: (parent = 127)
  • Heap Blocks: exact=6
8. 0.041 0.041 ↑ 1.0 980 1

Bitmap Index Scan on place_relation_pkey (cost=0.00..19.65 rows=982 width=0) (actual time=0.041..0.041 rows=980 loops=1)

  • Index Cond: (parent = 127)
9. 393.066 1,489.090 ↑ 2,004.5 970,848 2

Merge Join (cost=3,054,757.20..32,296,341.76 rows=1,946,040,819 width=83) (actual time=329.998..744.545 rows=970,848 loops=2)

  • Merge Cond: (pr_1.parent = ets_1.child)
10. 9.368 18.578 ↑ 1.3 23,042 2

Merge Join (cost=0.56..1,576.19 rows=30,283 width=87) (actual time=0.023..9.289 rows=23,042 loops=2)

  • Merge Cond: (pr_1.parent = p_1.id)
11. 9.072 9.072 ↑ 1.0 30,283 2

Index Scan using place_relation_pkey on place_relation pr_1 (cost=0.29..1,175.47 rows=30,283 width=12) (actual time=0.014..4.536 rows=30,283 loops=2)

12. 0.138 0.138 ↑ 8.2 244 2

Index Scan using place_pkey on place p_1 (cost=0.28..124.30 rows=2,009 width=75) (actual time=0.007..0.069 rows=244 loops=2)

13. 272.224 1,077.446 ↑ 11.8 1,674,238 2

Materialize (cost=3,054,756.63..3,153,398.53 rows=19,728,380 width=4) (actual time=321.343..538.723 rows=1,674,238 loops=2)

14. 542.790 805.222 ↑ 27.1 729,216 2

Sort (cost=3,054,756.63..3,104,077.58 rows=19,728,380 width=4) (actual time=319.483..402.611 rows=729,216 loops=2)

  • Sort Key: ets_1.child
  • Sort Method: quicksort Memory: 72kB
15. 262.432 262.432 ↑ 21.0 937,878 2

WorkTable Scan on ets ets_1 (cost=0.00..394,567.60 rows=19,728,380 width=4) (actual time=0.011..131.216 rows=937,878 loops=2)

Planning time : 0.345 ms
Execution time : 4,769.937 ms