explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ftTh

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

CTE Scan on data (cost=26,373.17..26,440.73 rows=3,378 width=8) (actual time=668.564..699.688 rows=845 loops=1)

2.          

CTE classif

3. 0.007 0.603 ↑ 208.0 1 1

Sort (cost=206.72..207.24 rows=208 width=66) (actual time=0.602..0.603 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.596 ↑ 208.0 1 1

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

  • Group Key: diag.id
7. 0.109 0.591 ↑ 208.0 1 1

Nested Loop (cost=0.00..187.82 rows=208 width=66) (actual time=0.065..0.591 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.470 0.470 ↑ 1.0 1,872 1

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

10.          

CTE data

11. 113.370 699.531 ↑ 4.0 845 1

Hash Join (cost=26,118.88..26,165.92 rows=3,378 width=8) (actual time=668.560..699.531 rows=845 loops=1)

  • Hash Cond: (c.id = d.id_diag)
12. 0.604 0.604 ↑ 208.0 1 1

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

13. 215.052 585.557 ↓ 51.0 1,347,235 1

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

  • Buckets: 262,144 (originally 32768) Batches: 8 (originally 1) Memory Usage: 11,060kB
14. 205.016 370.505 ↓ 51.0 1,347,235 1

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

  • Workers Planned: 2
  • Workers Launched: 2
15. 93.114 165.489 ↓ 40.8 449,078 3 / 3

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

  • Hash Cond: (d.id_place = pr.child)
16. 68.529 68.529 ↑ 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..68.529 rows=449,078 loops=3)

17. 1.032 3.846 ↓ 48.5 7,316 3 / 3

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

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 322kB
18. 0.793 2.814 ↓ 48.5 7,316 3 / 3

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

19. 0.275 0.275 ↓ 9.7 97 3 / 3

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

  • Filter: ((type)::text = 'dep'::text)
  • Rows Removed by Filter: 1,816
20. 1.746 1.746 ↑ 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.018 rows=75 loops=291)

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