explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 107f

Settings
# exclusive inclusive rows x rows loops node
1. 3.554 9,982.536 ↑ 1.1 1,978 1

Nested Loop Left Join (cost=801,122.08..3,184,584.14 rows=2,112 width=154) (actual time=9,957.555..9,982.536 rows=1,978 loops=1)

  • Join Filter: (pp4.process_id = p.id)
  • Rows Removed by Join Filter: 71198
  • Functions: 91
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 8.624 ms, Inlining 32.875 ms, Optimization 402.704 ms, Emission 313.593 ms, Total 757.796 ms
2. 1.198 7,852.632 ↑ 1.1 1,978 1

Nested Loop Left Join (cost=801,122.08..2,390,106.71 rows=2,112 width=145) (actual time=7,833.105..7,852.632 rows=1,978 loops=1)

  • Join Filter: (pp3.process_id = p.id)
  • Rows Removed by Join Filter: 3954
3. 9.425 5,713.216 ↑ 1.1 1,978 1

Nested Loop Left Join (cost=801,122.08..1,595,629.29 rows=2,112 width=136) (actual time=5,694.352..5,713.216 rows=1,978 loops=1)

  • Join Filter: (pp2.process_id = p.id)
  • Rows Removed by Join Filter: 245148
4. 0.734 3,632.825 ↑ 1.1 1,978 1

Merge Left Join (cost=801,122.08..801,151.86 rows=2,112 width=127) (actual time=3,631.805..3,632.825 rows=1,978 loops=1)

  • Merge Cond: (p.id = pp1.process_id)
5. 0.916 774.019 ↑ 1.1 1,978 1

Sort (cost=7,292.11..7,297.39 rows=2,112 width=118) (actual time=773.802..774.019 rows=1,978 loops=1)

  • Sort Key: p.id
  • Sort Method: quicksort Memory: 464kB
6. 0.256 773.103 ↑ 1.1 1,978 1

Hash Join (cost=6,904.09..7,175.49 rows=2,112 width=118) (actual time=770.292..773.103 rows=1,978 loops=1)

  • Hash Cond: (h.location_id = l.location_id)
7. 0.321 22.915 ↑ 1.1 1,978 1

Hash Join (cost=6,902.24..7,167.46 rows=2,112 width=118) (actual time=20.338..22.915 rows=1,978 loops=1)

  • Hash Cond: (h.machine_id = m.id)
8. 0.379 21.130 ↑ 1.1 2,007 1

Hash Join (cost=6,722.28..6,981.96 rows=2,112 width=115) (actual time=18.854..21.130 rows=2,007 loops=1)

  • Hash Cond: (p.hardware_id = h.hardware_id)
9. 0.253 1.980 ↑ 1.1 2,007 1

Hash Join (cost=3.25..257.39 rows=2,112 width=47) (actual time=0.058..1.980 rows=2,007 loops=1)

  • Hash Cond: (p.process_type_id = tp.id)
10. 1.697 1.697 ↑ 1.1 2,007 1

Seq Scan on process p (cost=0.00..248.36 rows=2,112 width=37) (actual time=0.020..1.697 rows=2,007 loops=1)

  • Filter: ((now() >= start_dt) AND (now() < end_dt))
  • Rows Removed by Filter: 5968
11. 0.014 0.030 ↑ 1.0 100 1

Hash (cost=2.00..2.00 rows=100 width=18) (actual time=0.029..0.030 rows=100 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
12. 0.016 0.016 ↑ 1.0 100 1

Seq Scan on type_process tp (cost=0.00..2.00 rows=100 width=18) (actual time=0.009..0.016 rows=100 loops=1)

13. 1.199 18.771 ↑ 1.0 5,557 1

Hash (cost=6,649.57..6,649.57 rows=5,557 width=72) (actual time=18.771..18.771 rows=5,557 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 523kB
14. 17.572 17.572 ↑ 1.0 5,557 1

Seq Scan on hardware h (cost=0.00..6,649.57 rows=5,557 width=72) (actual time=0.040..17.572 rows=5,557 loops=1)

15. 0.415 1.464 ↑ 1.0 3,598 1

Hash (cost=134.98..134.98 rows=3,598 width=11) (actual time=1.464..1.464 rows=3,598 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 191kB
16. 1.049 1.049 ↑ 1.0 3,598 1

Seq Scan on machine m (cost=0.00..134.98 rows=3,598 width=11) (actual time=0.029..1.049 rows=3,598 loops=1)

17. 0.017 749.932 ↑ 1.0 38 1

Hash (cost=1.38..1.38 rows=38 width=8) (actual time=749.932..749.932 rows=38 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
18. 749.915 749.915 ↑ 1.0 38 1

Seq Scan on location l (cost=0.00..1.38 rows=38 width=8) (actual time=749.909..749.915 rows=38 loops=1)

19. 0.535 2,858.072 ↑ 1.5 1,692 1

Sort (cost=793,829.96..793,836.35 rows=2,555 width=13) (actual time=2,857.958..2,858.072 rows=1,692 loops=1)

  • Sort Key: pp1.process_id
  • Sort Method: quicksort Memory: 128kB
20. 2,857.537 2,857.537 ↑ 1.5 1,692 1

Seq Scan on process_param pp1 (cost=0.00..793,685.36 rows=2,555 width=13) (actual time=1,930.332..2,857.537 rows=1,692 loops=1)

  • Filter: (path ~~ '%%/General/http_port'::text)
  • Rows Removed by Filter: 256699
21. 8.553 2,070.966 ↓ 5.0 124 1,978

Materialize (cost=0.00..793,685.49 rows=25 width=13) (actual time=0.590..1.047 rows=124 loops=1,978)

22. 2,062.413 2,062.413 ↓ 5.0 124 1

Seq Scan on process_param pp2 (cost=0.00..793,685.36 rows=25 width=13) (actual time=1,166.079..2,062.413 rows=124 loops=1)

  • Filter: (path ~~ '%%/General/port'::text)
  • Rows Removed by Filter: 258267
23. 0.000 2,138.218 ↑ 12.5 2 1,978

Materialize (cost=0.00..793,685.49 rows=25 width=13) (actual time=0.612..1.081 rows=2 loops=1,978)

24. 2,138.693 2,138.693 ↑ 12.5 2 1

Seq Scan on process_param pp3 (cost=0.00..793,685.36 rows=25 width=13) (actual time=1,210.588..2,138.693 rows=2 loops=1)

  • Filter: (path ~~ '%/General/ExternalOnly'::text)
  • Rows Removed by Filter: 258389
25. 1.978 2,126.350 ↓ 1.4 36 1,978

Materialize (cost=0.00..793,685.49 rows=25 width=13) (actual time=0.602..1.075 rows=36 loops=1,978)

26. 2,124.372 2,124.372 ↓ 1.4 36 1

Seq Scan on process_param pp4 (cost=0.00..793,685.36 rows=25 width=13) (actual time=1,191.190..2,124.372 rows=36 loops=1)

  • Filter: (path ~~ '%/General/process_status_exempt'::text)
  • Rows Removed by Filter: 258355