explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2Mfy

Settings
# exclusive inclusive rows x rows loops node
1. 0.244 94.332 ↑ 1.1 1,978 1

Merge Left Join (cost=14,962.79..14,989.26 rows=2,112 width=246) (actual time=92.753..94.332 rows=1,978 loops=1)

  • Merge Cond: (p.id = pp4.process_id)
2.          

CTE pp

3. 5.370 66.378 ↓ 18.2 1,854 1

Gather (cost=1,000.00..7,661.46 rows=102 width=68) (actual time=0.487..66.378 rows=1,854 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 61.008 61.008 ↓ 14.7 618 3

Parallel Seq Scan on process_param (cost=0.00..6,651.26 rows=42 width=68) (actual time=0.099..61.008 rows=618 loops=3)

  • 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: 85512
5. 0.222 93.733 ↑ 1.1 1,978 1

Merge Left Join (cost=7,299.03..7,320.20 rows=2,112 width=214) (actual time=92.398..93.733 rows=1,978 loops=1)

  • Merge Cond: (p.id = pp3.process_id)
6. 0.252 93.170 ↑ 1.1 1,978 1

Merge Left Join (cost=7,296.72..7,312.60 rows=2,112 width=182) (actual time=92.055..93.170 rows=1,978 loops=1)

  • Merge Cond: (p.id = pp2.process_id)
7. 0.662 92.522 ↑ 1.1 1,978 1

Merge Left Join (cost=7,294.42..7,305.00 rows=2,112 width=150) (actual time=91.661..92.522 rows=1,978 loops=1)

  • Merge Cond: (p.id = pp1.process_id)
8. 0.914 27.417 ↑ 1.1 1,978 1

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

  • Sort Key: p.id
  • Sort Method: quicksort Memory: 464kB
9. 0.328 26.503 ↑ 1.1 1,978 1

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

  • Hash Cond: (h.location_id = l.location_id)
10. 0.394 26.160 ↑ 1.1 1,978 1

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

  • Hash Cond: (h.machine_id = m.id)
11. 0.471 24.008 ↑ 1.1 2,007 1

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

  • Hash Cond: (p.hardware_id = h.hardware_id)
12. 0.345 2.300 ↑ 1.1 2,007 1

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

  • Hash Cond: (p.process_type_id = tp.id)
13. 1.926 1.926 ↑ 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.926 rows=2,007 loops=1)

  • Filter: ((now() >= start_dt) AND (now() < end_dt))
  • Rows Removed by Filter: 5968
14. 0.018 0.029 ↑ 1.0 100 1

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

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

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

16. 1.371 21.237 ↑ 1.0 5,557 1

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

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

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

18. 0.488 1.758 ↑ 1.0 3,598 1

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

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

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

20. 0.007 0.015 ↑ 1.0 38 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
21. 0.008 0.008 ↑ 1.0 38 1

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

22. 0.473 64.443 ↓ 1,692.0 1,692 1

Sort (cost=2.30..2.31 rows=1 width=36) (actual time=64.367..64.443 rows=1,692 loops=1)

  • Sort Key: pp1.process_id
  • Sort Method: quicksort Memory: 128kB
23. 63.970 63.970 ↓ 1,692.0 1,692 1

CTE Scan on pp pp1 (cost=0.00..2.29 rows=1 width=36) (actual time=0.490..63.970 rows=1,692 loops=1)

  • Filter: (path ~~ '%/General/http\_port'::text)
  • Rows Removed by Filter: 162
24. 0.034 0.396 ↓ 124.0 124 1

Sort (cost=2.30..2.31 rows=1 width=36) (actual time=0.391..0.396 rows=124 loops=1)

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

CTE Scan on pp pp2 (cost=0.00..2.29 rows=1 width=36) (actual time=0.017..0.362 rows=124 loops=1)

  • Filter: (path ~~ '%/General/port'::text)
  • Rows Removed by Filter: 1730
26. 0.005 0.341 ↓ 2.0 2 1

Sort (cost=2.30..2.31 rows=1 width=36) (actual time=0.341..0.341 rows=2 loops=1)

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

CTE Scan on pp pp3 (cost=0.00..2.29 rows=1 width=36) (actual time=0.047..0.336 rows=2 loops=1)

  • Filter: (path ~~ '%/General/ExternalOnly'::text)
  • Rows Removed by Filter: 1852
28. 0.013 0.355 ↓ 36.0 36 1

Sort (cost=2.30..2.31 rows=1 width=36) (actual time=0.353..0.355 rows=36 loops=1)

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

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

  • Filter: (path ~~ '%/General/process\_status\_exempt'::text)
  • Rows Removed by Filter: 1818
Planning time : 4.018 ms
Execution time : 97.863 ms