explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nig5

Settings
# exclusive inclusive rows x rows loops node
1. 0.225 3,766.453 ↑ 1.1 1,978 1

Merge Left Join (cost=803,138.63..803,165.10 rows=2,112 width=246) (actual time=3,764.809..3,766.453 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.177 ms, Inlining 0.399 ms, Optimization 471.889 ms, Emission 402.011 ms, Total 883.477 ms
2.          

CTE pp

3. 2,872.795 2,872.795 ↑ 1.4 1,854 1

Seq Scan on process_param (cost=0.00..795,609.78 rows=2,630 width=69) (actual time=2,241.302..2,872.795 rows=1,854 loops=1)

  • Filter: ((path ~~ '%/General/http_port'::text) OR (path ~~ '%/General/port'::text) OR (path ~~ '%/General/ExternalOnly'::text) OR (path ~~ '%/General/process_status_exempt'::text))
  • Rows Removed by Filter: 256537
4. 0.213 3,765.901 ↑ 1.1 1,978 1

Merge Left Join (cost=7,469.67..7,490.84 rows=2,112 width=214) (actual time=3,764.474..3,765.901 rows=1,978 loops=1)

  • Merge Cond: (p.id = pp3.process_id)
5. 0.216 3,765.380 ↑ 1.1 1,978 1

Merge Left Join (cost=7,410.48..7,426.36 rows=2,112 width=182) (actual time=3,764.157..3,765.380 rows=1,978 loops=1)

  • Merge Cond: (p.id = pp2.process_id)
6. 0.761 3,764.801 ↑ 1.1 1,978 1

Merge Left Join (cost=7,351.30..7,361.88 rows=2,112 width=150) (actual time=3,763.789..3,764.801 rows=1,978 loops=1)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

15. 1.113 10.031 ↑ 1.0 5,557 1

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

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

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

17. 0.404 1.052 ↑ 1.0 3,598 1

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

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

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

19. 0.018 875.125 ↑ 1.0 38 1

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

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

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

21. 0.459 2,874.185 ↓ 1,692.0 1,692 1

Sort (cost=59.18..59.19 rows=1 width=36) (actual time=2,874.078..2,874.185 rows=1,692 loops=1)

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

CTE Scan on pp pp1 (cost=0.00..59.17 rows=1 width=36) (actual time=2,241.314..2,873.726 rows=1,692 loops=1)

  • Filter: (path ~~ '%/General/http_port'::text)
  • Rows Removed by Filter: 162
23. 0.034 0.363 ↓ 124.0 124 1

Sort (cost=59.18..59.19 rows=1 width=36) (actual time=0.357..0.363 rows=124 loops=1)

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

CTE Scan on pp pp2 (cost=0.00..59.17 rows=1 width=36) (actual time=0.024..0.329 rows=124 loops=1)

  • Filter: (path ~~ '%/General/port'::text)
  • Rows Removed by Filter: 1730
25. 0.003 0.308 ↓ 2.0 2 1

Sort (cost=59.18..59.19 rows=1 width=36) (actual time=0.308..0.308 rows=2 loops=1)

  • Sort Key: pp3.process_id
  • Sort Method: quicksort Memory: 25kB
26. 0.305 0.305 ↓ 2.0 2 1

CTE Scan on pp pp3 (cost=0.00..59.17 rows=1 width=36) (actual time=0.048..0.305 rows=2 loops=1)

  • Filter: (path ~~ '%/General/ExternalOnly'::text)
  • Rows Removed by Filter: 1852
27. 0.015 0.327 ↓ 36.0 36 1

Sort (cost=59.18..59.19 rows=1 width=36) (actual time=0.325..0.327 rows=36 loops=1)

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

CTE Scan on pp pp4 (cost=0.00..59.17 rows=1 width=36) (actual time=0.050..0.312 rows=36 loops=1)

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