explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MjA9

Settings
# exclusive inclusive rows x rows loops node
1. 0.216 3,005.734 ↑ 1.1 1,978 1

Merge Left Join (cost=811,556.40..811,583.16 rows=2,112 width=246) (actual time=3,004.142..3,005.734 rows=1,978 loops=1)

  • Merge Cond: (p.id = pp4.process_id)
  • Functions: 98
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 9.065 ms, Inlining 0.384 ms, Optimization 446.149 ms, Emission 355.972 ms, Total 811.570 ms
2.          

CTE pp

3. 2,072.598 2,072.598 ↑ 1.0 112,507 1

Seq Scan on process_param (cost=0.00..793,685.36 rows=117,534 width=69) (actual time=0.023..2,072.598 rows=112,507 loops=1)

  • Filter: (path ~~ '%/General/%'::text)
  • Rows Removed by Filter: 145884
4. 0.205 2,983.227 ↑ 1.1 1,978 1

Merge Left Join (cost=15,226.30..15,247.69 rows=2,112 width=214) (actual time=2,981.841..2,983.227 rows=1,978 loops=1)

  • Merge Cond: (p.id = pp3.process_id)
5. 0.219 2,961.030 ↑ 1.1 1,978 1

Merge Left Join (cost=12,581.57..12,597.59 rows=2,112 width=182) (actual time=2,959.838..2,961.030 rows=1,978 loops=1)

  • Merge Cond: (p.id = pp2.process_id)
6. 0.739 2,938.064 ↑ 1.1 1,978 1

Merge Left Join (cost=9,936.84..9,947.49 rows=2,112 width=150) (actual time=2,937.084..2,938.064 rows=1,978 loops=1)

  • Merge Cond: (p.id = pp1.process_id)
7. 0.783 818.155 ↑ 1.1 1,978 1

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

  • Sort Key: p.id
  • Sort Method: quicksort Memory: 464kB
8. 0.243 817.372 ↑ 1.1 1,978 1

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

  • Hash Cond: (h.location_id = l.location_id)
9. 0.305 14.263 ↑ 1.1 1,978 1

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

  • Hash Cond: (h.machine_id = m.id)
10. 0.353 12.919 ↑ 1.1 2,007 1

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

  • Hash Cond: (p.hardware_id = h.hardware_id)
11. 0.249 1.924 ↑ 1.1 2,007 1

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

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

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

  • Filter: ((now() >= start_dt) AND (now() < end_dt))
  • Rows Removed by Filter: 5968
13. 0.015 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
14. 0.015 0.015 ↑ 1.0 100 1

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

15. 1.107 10.642 ↑ 1.0 5,557 1

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

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

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

17. 0.395 1.039 ↑ 1.0 3,598 1

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

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

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

19. 0.022 802.866 ↑ 1.0 38 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
20. 802.844 802.844 ↑ 1.0 38 1

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

21. 0.441 2,119.170 ↓ 141.0 1,692 1

Sort (cost=2,644.73..2,644.76 rows=12 width=36) (actual time=2,119.071..2,119.170 rows=1,692 loops=1)

  • Sort Key: pp1.process_id
  • Sort Method: quicksort Memory: 128kB
22. 2,118.729 2,118.729 ↓ 141.0 1,692 1

CTE Scan on pp pp1 (cost=0.00..2,644.51 rows=12 width=36) (actual time=0.103..2,118.729 rows=1,692 loops=1)

  • Filter: (path ~~ '%/General/http_port'::text)
  • Rows Removed by Filter: 110815
23. 0.036 22.747 ↓ 10.3 124 1

Sort (cost=2,644.73..2,644.76 rows=12 width=36) (actual time=22.741..22.747 rows=124 loops=1)

  • Sort Key: pp2.process_id
  • Sort Method: quicksort Memory: 30kB
24. 22.711 22.711 ↓ 10.3 124 1

CTE Scan on pp pp2 (cost=0.00..2,644.51 rows=12 width=36) (actual time=0.973..22.711 rows=124 loops=1)

  • Filter: (path ~~ '%/General/port'::text)
  • Rows Removed by Filter: 112383
25. 0.005 21.992 ↑ 6.0 2 1

Sort (cost=2,644.73..2,644.76 rows=12 width=36) (actual time=21.992..21.992 rows=2 loops=1)

  • Sort Key: pp3.process_id
  • Sort Method: quicksort Memory: 25kB
26. 21.987 21.987 ↑ 6.0 2 1

CTE Scan on pp pp3 (cost=0.00..2,644.51 rows=12 width=36) (actual time=1.432..21.987 rows=2 loops=1)

  • Filter: (path ~~ '%/General/ExternalOnly'::text)
  • Rows Removed by Filter: 112505
27. 0.029 22.291 ↓ 3.0 36 1

Sort (cost=2,644.73..2,644.76 rows=12 width=36) (actual time=22.289..22.291 rows=36 loops=1)

  • Sort Key: pp4.process_id
  • Sort Method: quicksort Memory: 26kB
28. 22.262 22.262 ↓ 3.0 36 1

CTE Scan on pp pp4 (cost=0.00..2,644.51 rows=12 width=36) (actual time=1.482..22.262 rows=36 loops=1)

  • Filter: (path ~~ '%/General/process_status_exempt'::text)
  • Rows Removed by Filter: 112471
Planning time : 1.556 ms
Execution time : 3,017.172 ms