explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dj77 : Optimization for: plan #QE7t

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.024 477.337 ↓ 12.0 12 1

Nested Loop (cost=32,861.08..32,869.13 rows=1 width=53) (actual time=437.689..477.337 rows=12 loops=1)

2.          

CTE test_places

3. 0.466 79.905 ↓ 218.0 218 1

Nested Loop (cost=44.81..236.73 rows=1 width=12) (actual time=0.714..79.905 rows=218 loops=1)

4. 1.051 4.011 ↓ 218.0 218 1

Nested Loop (cost=44.52..109.34 rows=1 width=16) (actual time=0.512..4.011 rows=218 loops=1)

  • Join Filter: (pe.id = pre.child)
5. 0.506 1.290 ↓ 208.8 1,670 1

Hash Join (cost=44.24..102.01 rows=8 width=12) (actual time=0.476..1.290 rows=1,670 loops=1)

  • Hash Cond: (pie.id_place = pe.id)
6. 0.322 0.322 ↑ 1.0 1,670 1

Seq Scan on place_info_etab pie (cost=0.00..36.70 rows=1,670 width=8) (actual time=0.004..0.322 rows=1,670 loops=1)

7. 0.174 0.462 ↓ 167.0 1,670 1

Hash (cost=44.11..44.11 rows=10 width=4) (actual time=0.462..0.462 rows=1,670 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 75kB
8. 0.288 0.288 ↓ 167.0 1,670 1

Seq Scan on place pe (cost=0.00..44.11 rows=10 width=4) (actual time=0.020..0.288 rows=1,670 loops=1)

  • Filter: ((type)::text = 'ets'::text)
  • Rows Removed by Filter: 243
9. 1.670 1.670 ↓ 0.0 0 1,670

Index Only Scan using place_relation_pkey on place_relation pre (cost=0.29..0.90 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1,670)

  • Index Cond: ((parent = 127) AND (child = pie.id_place))
  • Heap Fetches: 218
10. 10.028 75.428 ↑ 10.0 1 218

Nested Loop (cost=0.29..127.29 rows=10 width=8) (actual time=0.242..0.346 rows=1 loops=218)

11. 44.254 44.254 ↓ 9.7 97 218

Seq Scan on place p_1 (cost=0.00..44.11 rows=10 width=4) (actual time=0.178..0.203 rows=97 loops=218)

  • Filter: ((type)::text = 'dep'::text)
  • Rows Removed by Filter: 1,816
12. 21.146 21.146 ↓ 0.0 0 21,146

Index Only Scan using place_relation_pkey on place_relation prg (cost=0.29..8.31 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=21,146)

  • Index Cond: ((parent = p_1.id) AND (child = pre.child))
  • Heap Fetches: 218
13.          

CTE classif

14. 1.899 3.478 ↓ 9.0 1,872 1

Sort (cost=164.72..165.24 rows=208 width=66) (actual time=3.367..3.478 rows=1,872 loops=1)

  • Sort Key: diag.lvl, diag.lft
  • Sort Method: quicksort Memory: 293kB
15.          

CTE nodes

16. 0.008 0.008 ↑ 1.0 1 1

Index Scan using classif_diag_pkey on classif_diag (cost=0.28..8.29 rows=1 width=12) (actual time=0.007..0.008 rows=1 loops=1)

  • Index Cond: (id = 1)
17. 0.839 1.579 ↓ 9.0 1,872 1

HashAggregate (cost=146.34..148.42 rows=208 width=66) (actual time=1.285..1.579 rows=1,872 loops=1)

  • Group Key: diag.id
18. 0.250 0.740 ↓ 9.0 1,872 1

Nested Loop (cost=0.00..145.82 rows=208 width=66) (actual time=0.021..0.740 rows=1,872 loops=1)

  • Join Filter: ((diag.lft >= nodes.lft) AND (diag.rgt <= nodes.rgt))
19. 0.009 0.009 ↑ 1.0 1 1

CTE Scan on nodes (cost=0.00..0.02 rows=1 width=8) (actual time=0.007..0.009 rows=1 loops=1)

20. 0.481 0.481 ↑ 1.0 1,872 1

Seq Scan on classif_diag diag (cost=0.00..117.72 rows=1,872 width=66) (actual time=0.012..0.481 rows=1,872 loops=1)

21.          

CTE data

22. 31.749 477.227 ↓ 12.0 12 1

GroupAggregate (cost=32,457.02..32,458.83 rows=1 width=28) (actual time=437.667..477.227 rows=12 loops=1)

  • Group Key: gp.geo
23. 36.305 445.478 ↓ 1,710.1 176,141 1

Sort (cost=32,457.02..32,457.28 rows=103 width=12) (actual time=434.413..445.478 rows=176,141 loops=1)

  • Sort Key: gp.geo
  • Sort Method: quicksort Memory: 12,626kB
24. 33.589 409.173 ↓ 1,710.1 176,141 1

Hash Join (cost=6.79..32,453.58 rows=103 width=12) (actual time=84.255..409.173 rows=176,141 loops=1)

  • Hash Cond: (d_1.id_diag = c.id)
25. 121.898 371.526 ↓ 218.3 176,141 1

Hash Join (cost=0.03..32,422.58 rows=807 width=16) (actual time=80.189..371.526 rows=176,141 loops=1)

  • Hash Cond: (d_1.id_place = gp.id_place)
26. 169.456 169.456 ↑ 1.0 1,347,235 1

Seq Scan on data_diag_2019 d_1 (cost=0.00..27,362.35 rows=1,347,235 width=12) (actual time=0.009..169.456 rows=1,347,235 loops=1)

27. 0.083 80.172 ↓ 218.0 218 1

Hash (cost=0.02..0.02 rows=1 width=12) (actual time=80.172..80.172 rows=218 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
28. 80.089 80.089 ↓ 218.0 218 1

CTE Scan on test_places gp (cost=0.00..0.02 rows=1 width=12) (actual time=0.715..80.089 rows=218 loops=1)

29. 0.204 4.058 ↓ 9.0 1,872 1

Hash (cost=4.16..4.16 rows=208 width=4) (actual time=4.058..4.058 rows=1,872 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 82kB
30. 3.854 3.854 ↓ 9.0 1,872 1

CTE Scan on classif c (cost=0.00..4.16 rows=208 width=4) (actual time=3.369..3.854 rows=1,872 loops=1)

31. 477.241 477.241 ↓ 12.0 12 1

CTE Scan on data d (cost=0.00..0.02 rows=1 width=28) (actual time=437.669..477.241 rows=12 loops=1)

32. 0.072 0.072 ↑ 1.0 1 12

Index Scan using place_pkey on place p (cost=0.28..8.29 rows=1 width=29) (actual time=0.006..0.006 rows=1 loops=12)

  • Index Cond: (id = d.id)
Planning time : 1.667 ms
Execution time : 477.479 ms