explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Yt6R

Settings
# exclusive inclusive rows x rows loops node
1. 89.157 28,886.479 ↑ 2,291.5 56 1

Unique (cost=113,021.01..113,983.45 rows=128,325 width=619) (actual time=28,477.045..28,886.479 rows=56 loops=1)

2. 19,679.205 28,797.322 ↓ 5.0 644,130 1

Sort (cost=113,021.01..113,341.82 rows=128,325 width=619) (actual time=28,477.043..28,797.322 rows=644,130 loops=1)

  • Sort Key: s.id, h.sensor_id, h.received_at DESC
  • Sort Method: external merge Disk: 574,600kB
3. 530.035 9,118.117 ↓ 5.0 644,130 1

Hash Left Join (cost=193.90..31,075.99 rows=128,325 width=619) (actual time=1.485..9,118.117 rows=644,130 loops=1)

  • Hash Cond: (s.id = ls.sensor_id)
  • Join Filter: (ls.connected @> h.received_at)
  • Rows Removed by Join Filter: 461,664
4. 175.549 8,587.457 ↓ 5.0 644,130 1

Merge Right Join (cost=141.17..27,814.61 rows=128,325 width=596) (actual time=0.819..8,587.457 rows=644,130 loops=1)

  • Merge Cond: (r.establishment_id = st.establishment_id)
5. 0.250 0.710 ↑ 10.9 23 1

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

  • Group Key: r.establishment_id, e.name
6. 0.099 0.460 ↑ 1.5 172 1

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

  • Sort Key: r.establishment_id, e.name
  • Sort Method: quicksort Memory: 46kB
7. 0.050 0.361 ↑ 1.0 251 1

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

  • Hash Cond: (r.establishment_id = e.id)
8. 0.053 0.278 ↑ 1.0 251 1

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

  • Hash Cond: (ru.user_id = u.id)
9. 0.054 0.181 ↑ 1.0 251 1

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

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

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

  • Filter: alerts_flag
  • Rows Removed by Filter: 293
11. 0.032 0.060 ↓ 1.0 236 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
12. 0.028 0.028 ↓ 1.0 236 1

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

13. 0.017 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
14. 0.027 0.027 ↓ 1.0 116 1

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

15. 0.017 0.033 ↓ 1.1 47 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
16. 0.016 0.016 ↓ 1.1 47 1

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

17. 7,404.993 8,411.198 ↓ 6.3 644,130 1

Materialize (cost=97.96..26,093.37 rows=102,251 width=541) (actual time=0.324..8,411.198 rows=644,130 loops=1)

18. 115.952 1,006.205 ↓ 6.3 644,130 1

Nested Loop Left Join (cost=97.96..25,837.74 rows=102,251 width=541) (actual time=0.315..1,006.205 rows=644,130 loops=1)

  • Join Filter: s.enumerated
  • Rows Removed by Join Filter: 48
19. 0.350 4.949 ↓ 1.2 56 1

Nested Loop (cost=97.53..408.81 rows=47 width=525) (actual time=0.298..4.949 rows=56 loops=1)

  • Join Filter: (b.gateway_id = g.id)
  • Rows Removed by Join Filter: 3,628
20. 0.014 0.129 ↑ 1.0 3 1

Merge Join (cost=44.21..44.62 rows=3 width=524) (actual time=0.111..0.129 rows=3 loops=1)

  • Merge Cond: (st.establishment_id = g.establishment_id)
21. 0.008 0.061 ↓ 3.0 3 1

Sort (cost=26.94..26.94 rows=1 width=516) (actual time=0.059..0.061 rows=3 loops=1)

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

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

  • Filter: (active AND (NOT archived) AND ((type)::text = 'age'::text))
  • Rows Removed by Filter: 151
23. 0.025 0.054 ↑ 1.4 58 1

Sort (cost=17.28..17.48 rows=79 width=8) (actual time=0.045..0.054 rows=58 loops=1)

  • Sort Key: g.establishment_id
  • Sort Method: quicksort Memory: 28kB
24. 0.029 0.029 ↑ 1.1 75 1

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

25. 3.413 4.470 ↑ 1.0 1,228 3

Materialize (cost=53.31..312.00 rows=1,228 width=9) (actual time=0.054..1.490 rows=1,228 loops=3)

26. 0.532 1.057 ↑ 1.0 1,228 1

Hash Join (cost=53.31..305.86 rows=1,228 width=9) (actual time=0.160..1.057 rows=1,228 loops=1)

  • Hash Cond: (s.bru_id = b.id)
27. 0.379 0.379 ↑ 1.0 1,228 1

Seq Scan on sensors s (cost=0.00..249.28 rows=1,228 width=9) (actual time=0.002..0.379 rows=1,228 loops=1)

28. 0.046 0.146 ↑ 1.0 325 1

Hash (cost=49.25..49.25 rows=325 width=8) (actual time=0.146..0.146 rows=325 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
29. 0.100 0.100 ↑ 1.0 325 1

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

30. 885.304 885.304 ↓ 1.3 11,503 56

Index Scan using heartbeats_sensor_receivedat on heartbeats h (cost=0.43..455.10 rows=8,594 width=16) (actual time=0.017..15.809 rows=11,503 loops=56)

  • Index Cond: (sensor_id = s.id)
31. 0.155 0.625 ↓ 1.1 908 1

Hash (cost=41.99..41.99 rows=860 width=27) (actual time=0.625..0.625 rows=908 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 61kB
32. 0.197 0.470 ↓ 1.1 908 1

Hash Left Join (cost=22.12..41.99 rows=860 width=27) (actual time=0.212..0.470 rows=908 loops=1)

  • Hash Cond: (ls.line_id = l.id)
33. 0.079 0.079 ↓ 1.1 908 1

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

34. 0.095 0.194 ↓ 1.0 686 1

Hash (cost=13.72..13.72 rows=672 width=11) (actual time=0.194..0.194 rows=686 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 38kB
35. 0.099 0.099 ↓ 1.0 686 1

Seq Scan on lines l (cost=0.00..13.72 rows=672 width=11) (actual time=0.007..0.099 rows=686 loops=1)

Planning time : 2.455 ms
Execution time : 72,579.468 ms