explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dhYd

Settings
# exclusive inclusive rows x rows loops node
1. 0.535 850.421 ↑ 8.5 21 1

Sort (cost=143,733.41..143,733.86 rows=179 width=92) (actual time=850.417..850.421 rows=21 loops=1)

  • Sort Key: f1.f1, s0.name
  • Sort Method: quicksort Memory: 26kB
2. 0.235 849.886 ↑ 8.5 21 1

Hash Right Join (cost=137,476.81..143,726.71 rows=179 width=92) (actual time=849.457..849.886 rows=21 loops=1)

  • Hash Cond: ((max((s1.name)::text)) = (s0.name)::text)
  • Join Filter: ((date_trunc('day'::text, e0."timestamp")) = f1.f1)
  • Rows Removed by Join Filter: 786
3. 6.524 848.512 ↑ 376.9 114 1

GroupAggregate (cost=137,445.04..139,700.97 rows=42,970 width=120) (actual time=842.168..848.512 rows=114 loops=1)

  • Group Key: (date_trunc('day'::text, e0."timestamp")), e0.sensor_id
4. 52.884 841.988 ↑ 11.2 3,844 1

Sort (cost=137,445.04..137,552.47 rows=42,970 width=420) (actual time=841.084..841.988 rows=3,844 loops=1)

  • Sort Key: (date_trunc('day'::text, e0."timestamp")), e0.sensor_id
  • Sort Method: quicksort Memory: 2546kB
5. 17.082 789.104 ↑ 11.2 3,844 1

Nested Loop (cost=2,466.13..125,913.28 rows=42,970 width=420) (actual time=4.283..789.104 rows=3,844 loops=1)

6. 0.039 0.086 ↓ 1.5 3 1

Hash Join (cost=2.24..4.76 rows=2 width=325) (actual time=0.042..0.086 rows=3 loops=1)

  • Hash Cond: (s1.sensor_type_id = s2_1.id)
7. 0.035 0.035 ↑ 1.0 40 1

Seq Scan on sensors s1 (cost=0.00..2.40 rows=40 width=341) (actual time=0.018..0.035 rows=40 loops=1)

8. 0.005 0.012 ↓ 3.0 3 1

Hash (cost=2.23..2.23 rows=1 width=16) (actual time=0.012..0.012 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
9. 0.007 0.007 ↓ 3.0 3 1

Seq Scan on sensor_types s2_1 (cost=0.00..2.23 rows=1 width=16) (actual time=0.005..0.007 rows=3 loops=1)

  • Filter: ((kind)::text = 'pv'::text)
  • Rows Removed by Filter: 14
10. 745.791 771.936 ↑ 19.5 1,281 3

Bitmap Heap Scan on events e0 (cost=2,463.89..62,651.04 rows=24,950 width=111) (actual time=8.825..257.312 rows=1,281 loops=3)

  • Recheck Cond: (sensor_id = s1.id)
  • Heap Blocks: exact=2744
11. 26.145 26.145 ↑ 19.5 1,281 3

Bitmap Index Scan on events_sensor_id_index (cost=0.00..2,457.65 rows=24,950 width=0) (actual time=8.715..8.715 rows=1,281 loops=3)

  • Index Cond: (sensor_id = s1.id)
12. 0.012 1.139 ↑ 8.0 21 1

Hash (cost=29.68..29.68 rows=167 width=24) (actual time=1.139..1.139 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
13. 0.010 1.127 ↑ 8.0 21 1

Nested Loop (cost=7.38..29.68 rows=167 width=24) (actual time=1.102..1.127 rows=21 loops=1)

14. 0.006 1.099 ↓ 3.0 3 1

Nested Loop (cost=7.37..9.68 rows=1 width=16) (actual time=1.087..1.099 rows=3 loops=1)

  • Join Filter: (s0.sensor_type_id = s2.id)
  • Rows Removed by Join Filter: 6
15. 0.037 0.037 ↓ 3.0 3 1

Seq Scan on sensor_types s2 (cost=0.00..2.23 rows=1 width=16) (actual time=0.034..0.037 rows=3 loops=1)

  • Filter: ((kind)::text = 'pv'::text)
  • Rows Removed by Filter: 14
16. 0.009 1.056 ↑ 1.0 3 3

Unique (cost=7.37..7.38 rows=3 width=1,756) (actual time=0.349..0.352 rows=3 loops=3)

17. 0.022 1.047 ↑ 1.0 3 3

Sort (cost=7.37..7.38 rows=3 width=1,756) (actual time=0.349..0.349 rows=3 loops=3)

  • Sort Key: s0.id
  • Sort Method: quicksort Memory: 25kB
18. 0.004 1.025 ↑ 1.0 3 1

Nested Loop (cost=1.63..7.34 rows=3 width=1,756) (actual time=1.010..1.025 rows=3 loops=1)

19. 0.006 1.003 ↑ 1.0 3 1

Nested Loop (cost=1.49..5.64 rows=3 width=48) (actual time=0.992..1.003 rows=3 loops=1)

20. 0.005 0.988 ↑ 1.0 3 1

Nested Loop (cost=1.35..3.77 rows=3 width=16) (actual time=0.982..0.988 rows=3 loops=1)

21. 0.524 0.563 ↑ 1.0 1 1

Hash Join (cost=1.21..2.38 rows=1 width=32) (actual time=0.560..0.563 rows=1 loops=1)

  • Hash Cond: (g1.id = g0.id)
22. 0.016 0.016 ↓ 1.5 19 1

Seq Scan on groups g1 (cost=0.00..1.13 rows=13 width=16) (actual time=0.011..0.016 rows=19 loops=1)

23. 0.005 0.023 ↑ 1.0 1 1

Hash (cost=1.20..1.20 rows=1 width=16) (actual time=0.023..0.023 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.018 0.018 ↑ 1.0 1 1

Seq Scan on groups g0 (cost=0.00..1.20 rows=1 width=16) (actual time=0.017..0.018 rows=1 loops=1)

  • Filter: ((path @> '{f64358a4-2eab-465d-8783-a5c344c60403,aeeb4ec4-38b8-4d5f-b6a1-075ecc80ffc6}'::character varying[]) OR (id = 'aeeb4ec4-38b8-4d5f-b6a1-075ecc80ffc6'::uuid))
  • Rows Removed by Filter: 18
25. 0.420 0.420 ↑ 1.0 3 1

Index Only Scan using groups_sensors_sensor_id_group_id_index on groups_sensors g4 (cost=0.14..1.36 rows=3 width=32) (actual time=0.419..0.420 rows=3 loops=1)

  • Index Cond: (group_id = g1.id)
  • Heap Fetches: 3
26. 0.009 0.009 ↑ 1.0 1 3

Index Scan using sensors_pkey on sensors s0 (cost=0.14..0.61 rows=1 width=48) (actual time=0.003..0.003 rows=1 loops=3)

  • Index Cond: (id = g4.sensor_id)
27. 0.018 0.018 ↑ 1.0 1 3

Index Only Scan using sensor_types_pkey on sensor_types s2_2 (cost=0.14..0.56 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=3)

  • Index Cond: (id = s0.sensor_type_id)
  • Heap Fetches: 3
28. 0.018 0.018 ↑ 142.9 7 3

Function Scan on generate_series f1 (cost=0.01..10.01 rows=1,000 width=8) (actual time=0.004..0.006 rows=7 loops=3)

Planning time : 4.209 ms
Execution time : 850.608 ms