explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ez7Y

Settings
# exclusive inclusive rows x rows loops node
1. 0.056 1,778.102 ↓ 3.6 54 1

Nested Loop Left Join (cost=104,447.99..104,473.99 rows=15 width=607) (actual time=1,777.651..1,778.102 rows=54 loops=1)

2. 0.213 1,777.938 ↓ 3.6 54 1

Hash Right Join (cost=104,447.72..104,468.68 rows=15 width=596) (actual time=1,777.626..1,777.938 rows=54 loops=1)

  • Hash Cond: (ls.sensor_id = s.id)
  • Join Filter: (ls.connected @> (max(h.received_at)))
  • Rows Removed by Join Filter: 60
3. 0.140 0.140 ↓ 1.1 908 1

Seq Scan on line_sensors ls (cost=0.00..17.60 rows=860 width=24) (actual time=0.016..0.140 rows=908 loops=1)

4. 0.054 1,777.585 ↓ 3.6 54 1

Hash (cost=104,447.53..104,447.53 rows=15 width=592) (actual time=1,777.585..1,777.585 rows=54 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 56kB
5. 0.076 1,777.531 ↓ 3.6 54 1

Merge Join (cost=104,309.34..104,447.53 rows=15 width=592) (actual time=1,776.896..1,777.531 rows=54 loops=1)

  • Merge Cond: (s.id = h.sensor_id)
6. 0.047 1.902 ↓ 1.5 54 1

Sort (cost=398.94..399.03 rows=36 width=580) (actual time=1.893..1.902 rows=54 loops=1)

  • Sort Key: s.id
  • Sort Method: quicksort Memory: 79kB
7. 0.082 1.855 ↓ 1.5 54 1

Hash Join (cost=145.57..398.01 rows=36 width=580) (actual time=1.172..1.855 rows=54 loops=1)

  • Hash Cond: (s.bru_id = b.id)
8. 0.633 0.633 ↑ 1.0 750 1

Seq Scan on sensors s (cost=0.00..249.28 rows=750 width=9) (actual time=0.010..0.633 rows=750 loops=1)

  • Filter: enumerated
  • Rows Removed by Filter: 478
9. 0.012 1.140 ↑ 1.0 15 1

Hash (cost=145.38..145.38 rows=15 width=579) (actual time=1.140..1.140 rows=15 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
10. 0.082 1.128 ↑ 1.0 15 1

Hash Join (cost=94.75..145.38 rows=15 width=579) (actual time=0.967..1.128 rows=15 loops=1)

  • Hash Cond: (b.gateway_id = g.id)
11. 0.122 0.122 ↑ 1.0 325 1

Seq Scan on brus b (cost=0.00..49.25 rows=325 width=8) (actual time=0.003..0.122 rows=325 loops=1)

12. 0.004 0.924 ↑ 1.3 3 1

Hash (cost=94.70..94.70 rows=4 width=579) (actual time=0.924..0.924 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
13. 0.107 0.920 ↑ 1.3 3 1

Hash Join (cost=79.58..94.70 rows=4 width=579) (actual time=0.791..0.920 rows=3 loops=1)

  • Hash Cond: (g.establishment_id = st.establishment_id)
14. 0.040 0.040 ↑ 1.1 75 1

Seq Scan on gateways g (cost=0.00..14.79 rows=79 width=8) (actual time=0.002..0.040 rows=75 loops=1)

15. 0.005 0.773 ↓ 3.0 3 1

Hash (cost=79.57..79.57 rows=1 width=571) (actual time=0.773..0.773 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
16. 0.011 0.768 ↓ 3.0 3 1

Merge Left Join (cost=70.14..79.57 rows=1 width=571) (actual time=0.549..0.768 rows=3 loops=1)

  • Merge Cond: (st.establishment_id = r.establishment_id)
17. 0.006 0.063 ↓ 3.0 3 1

Sort (cost=26.94..26.94 rows=1 width=516) (actual time=0.062..0.063 rows=3 loops=1)

  • Sort Key: st.establishment_id
  • Sort Method: quicksort Memory: 28kB
18. 0.057 0.057 ↓ 3.0 3 1

Seq Scan on statistic_tests st (cost=0.00..26.93 rows=1 width=516) (actual time=0.010..0.057 rows=3 loops=1)

  • Filter: (active AND (NOT archived) AND ((type)::text = 'age'::text))
  • Rows Removed by Filter: 151
19. 0.014 0.694 ↑ 10.9 23 1

Materialize (cost=43.21..51.99 rows=251 width=59) (actual time=0.443..0.694 rows=23 loops=1)

20. 0.239 0.680 ↑ 10.9 23 1

GroupAggregate (cost=43.21..48.85 rows=251 width=59) (actual time=0.441..0.680 rows=23 loops=1)

  • Group Key: r.establishment_id, e.name
21. 0.087 0.441 ↑ 1.5 172 1

Sort (cost=43.21..43.83 rows=251 width=38) (actual time=0.426..0.441 rows=172 loops=1)

  • Sort Key: r.establishment_id, e.name
  • Sort Method: quicksort Memory: 46kB
22. 0.068 0.354 ↑ 1.0 251 1

Hash Join (cost=20.69..33.20 rows=251 width=38) (actual time=0.149..0.354 rows=251 loops=1)

  • Hash Cond: (r.establishment_id = e.id)
23. 0.045 0.263 ↑ 1.0 251 1

Hash Join (cost=14.72..26.51 rows=251 width=15) (actual time=0.122..0.263 rows=251 loops=1)

  • Hash Cond: (ru.user_id = u.id)
24. 0.055 0.174 ↑ 1.0 251 1

Hash Join (cost=8.13..19.24 rows=251 width=8) (actual time=0.074..0.174 rows=251 loops=1)

  • Hash Cond: (ru.role_id = r.id)
25. 0.064 0.064 ↑ 1.0 251 1

Seq Scan on role_users ru (cost=0.00..10.44 rows=251 width=8) (actual time=0.005..0.064 rows=251 loops=1)

  • Filter: alerts_flag
  • Rows Removed by Filter: 293
26. 0.025 0.055 ↓ 1.0 236 1

Hash (cost=5.28..5.28 rows=228 width=8) (actual time=0.055..0.055 rows=236 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
27. 0.030 0.030 ↓ 1.0 236 1

Seq Scan on roles r (cost=0.00..5.28 rows=228 width=8) (actual time=0.005..0.030 rows=236 loops=1)

28. 0.014 0.044 ↓ 1.0 116 1

Hash (cost=5.15..5.15 rows=115 width=15) (actual time=0.044..0.044 rows=116 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
29. 0.030 0.030 ↓ 1.0 116 1

Seq Scan on users u (cost=0.00..5.15 rows=115 width=15) (actual time=0.004..0.030 rows=116 loops=1)

30. 0.010 0.023 ↓ 1.1 47 1

Hash (cost=5.43..5.43 rows=43 width=27) (actual time=0.022..0.023 rows=47 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
31. 0.013 0.013 ↓ 1.1 47 1

Seq Scan on establishments e (cost=0.00..5.43 rows=43 width=27) (actual time=0.003..0.013 rows=47 loops=1)

32. 0.107 1,775.553 ↑ 1.4 354 1

Finalize GroupAggregate (cost=103,910.40..104,041.90 rows=509 width=16) (actual time=1,774.995..1,775.553 rows=354 loops=1)

  • Group Key: h.sensor_id
33. 8.764 1,775.446 ↓ 1.0 1,033 1

Gather Merge (cost=103,910.40..104,029.17 rows=1,018 width=16) (actual time=1,774.985..1,775.446 rows=1,033 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
34. 0.271 1,766.682 ↓ 1.0 518 3 / 3

Sort (cost=102,910.38..102,911.65 rows=509 width=16) (actual time=1,766.634..1,766.682 rows=518 loops=3)

  • Sort Key: h.sensor_id
  • Sort Method: quicksort Memory: 71kB
  • Worker 0: Sort Method: quicksort Memory: 71kB
  • Worker 1: Sort Method: quicksort Memory: 73kB
35. 843.491 1,766.411 ↓ 1.2 602 3 / 3

Partial HashAggregate (cost=102,882.40..102,887.49 rows=509 width=16) (actual time=1,766.260..1,766.411 rows=602 loops=3)

  • Group Key: h.sensor_id
36. 922.920 922.920 ↑ 1.3 1,456,730 3 / 3

Parallel Seq Scan on heartbeats h (cost=0.00..89,202.23 rows=1,824,023 width=16) (actual time=0.007..922.920 rows=1,456,730 loops=3)

37. 0.108 0.108 ↑ 1.0 1 54

Index Scan using lines_pkey on lines l (cost=0.28..0.34 rows=1 width=11) (actual time=0.002..0.002 rows=1 loops=54)

  • Index Cond: (id = ls.line_id)
Planning time : 1.204 ms
Execution time : 1,778.571 ms