explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fmZy

Settings
# exclusive inclusive rows x rows loops node
1. 90.657 425.865 ↑ 15.6 845 1

Hash Join (cost=1,639.94..26,219.20 rows=13,220 width=8) (actual time=33.682..425.865 rows=845 loops=1)

  • Hash Cond: (d.id_diag = "ANY_subquery".id)
2. 171.261 334.602 ↓ 51.0 1,347,235 1

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

  • Workers Planned: 2
  • Workers Launched: 2
3. 91.497 163.341 ↓ 40.8 449,078 3 / 3

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

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

5. 1.037 3.780 ↓ 48.5 7,316 3 / 3

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

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 322kB
6. 0.723 2.743 ↓ 48.5 7,316 3 / 3

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

7. 0.274 0.274 ↓ 9.7 97 3 / 3

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

  • Filter: ((type)::text = 'dep'::text)
  • Rows Removed by Filter: 1,816
8. 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
9. 0.001 0.606 ↑ 200.0 1 1

Hash (cost=211.84..211.84 rows=200 width=4) (actual time=0.606..0.606 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
10. 0.002 0.605 ↑ 200.0 1 1

HashAggregate (cost=209.84..211.84 rows=200 width=4) (actual time=0.605..0.605 rows=1 loops=1)

  • Group Key: "ANY_subquery".id
11. 0.001 0.603 ↑ 208.0 1 1

Subquery Scan on ANY_subquery (cost=206.72..209.32 rows=208 width=4) (actual time=0.602..0.603 rows=1 loops=1)

12. 0.006 0.602 ↑ 208.0 1 1

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

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

CTE nodes

14. 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)
15. 0.004 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
16. 0.120 0.592 ↑ 208.0 1 1

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

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

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

18. 0.460 0.460 ↑ 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.460 rows=1,872 loops=1)

Planning time : 0.622 ms
Execution time : 431.141 ms