explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dFNu

Settings
# exclusive inclusive rows x rows loops node
1. 110.709 698.663 ↑ 4.0 845 1

Hash Join (cost=26,326.13..26,373.17 rows=3,378 width=8) (actual time=668.332..698.663 rows=845 loops=1)

  • Hash Cond: (c.id = d.id_diag)
2.          

CTE classif

3. 0.007 0.725 ↑ 208.0 1 1

Sort (cost=206.72..207.24 rows=208 width=66) (actual time=0.724..0.725 rows=1 loops=1)

  • Sort Key: diag.lvl, diag.lft
  • Sort Method: quicksort Memory: 25kB
4.          

CTE nodes

5. 0.010 0.010 ↑ 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.009..0.010 rows=1 loops=1)

  • Index Cond: (id = 120)
6. 0.005 0.718 ↑ 208.0 1 1

HashAggregate (cost=188.34..190.42 rows=208 width=66) (actual time=0.717..0.718 rows=1 loops=1)

  • Group Key: diag.id
7. 0.116 0.713 ↑ 208.0 1 1

Nested Loop (cost=0.00..187.82 rows=208 width=66) (actual time=0.096..0.713 rows=1 loops=1)

  • Join Filter: ((diag.lft >= nodes.lft) AND (diag.rgt <= nodes.rgt))
  • Rows Removed by Join Filter: 1,871
8. 0.012 0.012 ↑ 1.0 1 1

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

9. 0.585 0.585 ↑ 1.0 1,872 1

Seq Scan on classif_diag diag (cost=0.00..159.72 rows=1,872 width=66) (actual time=0.006..0.585 rows=1,872 loops=1)

10. 0.727 0.727 ↑ 208.0 1 1

CTE Scan on classif c (cost=0.00..4.16 rows=208 width=4) (actual time=0.725..0.727 rows=1 loops=1)

11. 218.027 587.227 ↓ 51.0 1,347,235 1

Hash (cost=25,788.39..25,788.39 rows=26,439 width=12) (actual time=587.227..587.227 rows=1,347,235 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 8 (originally 1) Memory Usage: 11,060kB
12. 202.583 369.200 ↓ 51.0 1,347,235 1

Gather (cost=1,425.60..25,788.39 rows=26,439 width=12) (actual time=3.887..369.200 rows=1,347,235 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
13. 92.530 166.617 ↓ 40.8 449,078 3 / 3

Hash Join (cost=425.60..22,144.49 rows=11,016 width=12) (actual time=4.434..166.617 rows=449,078 loops=3)

  • Hash Cond: (d.id_place = pr.child)
14. 69.743 69.743 ↑ 1.3 449,078 3 / 3

Parallel Seq Scan on data_diag_2019 d (cost=0.00..19,503.48 rows=561,348 width=12) (actual time=0.013..69.743 rows=449,078 loops=3)

15. 1.158 4.344 ↓ 48.5 7,316 3 / 3

Hash (cost=423.71..423.71 rows=151 width=4) (actual time=4.344..4.344 rows=7,316 loops=3)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 322kB
16. 0.858 3.186 ↓ 48.5 7,316 3 / 3

Nested Loop (cost=0.29..423.71 rows=151 width=4) (actual time=0.278..3.186 rows=7,316 loops=3)

17. 0.291 0.291 ↓ 9.7 97 3 / 3

Seq Scan on place p (cost=0.00..44.11 rows=10 width=4) (actual time=0.243..0.291 rows=97 loops=3)

  • Filter: ((type)::text = 'dep'::text)
  • Rows Removed by Filter: 1,816
18. 2.037 2.037 ↑ 1.3 75 291 / 3

Index Only Scan using place_relation_pkey on place_relation pr (cost=0.29..36.97 rows=99 width=8) (actual time=0.004..0.021 rows=75 loops=291)

  • Index Cond: (parent = p.id)
  • Heap Fetches: 7,316
Planning time : 0.633 ms
Execution time : 698.854 ms