explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tp0

Settings
# exclusive inclusive rows x rows loops node
1. 0.240 84.798 ↑ 1.1 1,978 1

Merge Left Join (cost=15,811.34..15,837.81 rows=2,112 width=246) (actual time=83.214..84.798 rows=1,978 loops=1)

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

CTE pp

3. 7.587 69.684 ↑ 1.4 1,854 1

Gather (cost=1,000.00..8,280.96 rows=2,647 width=68) (actual time=0.443..69.684 rows=1,854 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 62.097 62.097 ↑ 1.8 618 3

Parallel Seq Scan on krb_process_param (cost=0.00..7,016.26 rows=1,103 width=68) (actual time=0.107..62.097 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.228 84.209 ↑ 1.1 1,978 1

Merge Left Join (cost=7,470.82..7,491.99 rows=2,112 width=214) (actual time=82.865..84.209 rows=1,978 loops=1)

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

Merge Left Join (cost=7,411.25..7,427.12 rows=2,112 width=182) (actual time=82.523..83.641 rows=1,978 loops=1)

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

Merge Left Join (cost=7,351.68..7,362.26 rows=2,112 width=150) (actual time=82.116..82.980 rows=1,978 loops=1)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

16. 1.309 11.012 ↑ 1.0 5,557 1

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

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

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

18. 0.449 1.216 ↑ 1.0 3,598 1

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

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

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

20. 0.007 0.016 ↑ 1.0 38 1

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

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

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

22. 0.472 65.777 ↓ 1,692.0 1,692 1

Sort (cost=59.57..59.57 rows=1 width=36) (actual time=65.697..65.777 rows=1,692 loops=1)

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

CTE Scan on pp pp1 (cost=0.00..59.56 rows=1 width=36) (actual time=0.447..65.305 rows=1,692 loops=1)

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

Sort (cost=59.57..59.57 rows=1 width=36) (actual time=0.404..0.409 rows=124 loops=1)

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

CTE Scan on pp pp2 (cost=0.00..59.56 rows=1 width=36) (actual time=0.018..0.378 rows=124 loops=1)

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

Sort (cost=59.57..59.57 rows=1 width=36) (actual time=0.340..0.340 rows=2 loops=1)

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

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

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

Sort (cost=59.57..59.57 rows=1 width=36) (actual time=0.347..0.349 rows=36 loops=1)

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

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

  • Filter: (path ~~ '%/General/process\_status\_exempt'::text)
  • Rows Removed by Filter: 1818