explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2ocl

Settings
# exclusive inclusive rows x rows loops node
1. 6.848 25,885.737 ↑ 8.5 21 1

Sort (cost=224,668.15..224,668.60 rows=179 width=92) (actual time=25,885.733..25,885.737 rows=21 loops=1)

  • Sort Key: f1.f1, s0.name
  • Sort Method: quicksort Memory: 26kB
2. 53.232 25,878.889 ↑ 8.5 21 1

Hash Right Join (cost=214,609.66..224,661.45 rows=179 width=92) (actual time=25,876.707..25,878.889 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. 0.000 25,825.472 ↑ 376.9 114 1

Finalize GroupAggregate (cost=214,578.36..220,636.18 rows=42,970 width=120) (actual time=25,822.668..25,825.472 rows=114 loops=1)

  • Group Key: (date_trunc('day'::text, e0."timestamp")), e0.sensor_id
4. 0.000 25,919.946 ↑ 105.9 338 1

Gather Merge (cost=214,578.36..219,651.46 rows=35,808 width=120) (actual time=25,810.837..25,919.946 rows=338 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 69.432 77,337.915 ↑ 158.4 113 3

Partial GroupAggregate (cost=213,578.34..214,518.30 rows=17,904 width=120) (actual time=25,776.897..25,779.305 rows=113 loops=3)

  • Group Key: (date_trunc('day'::text, e0."timestamp")), e0.sensor_id
6. 474.270 77,268.483 ↑ 14.0 1,281 3

Sort (cost=213,578.34..213,623.10 rows=17,904 width=420) (actual time=25,755.775..25,756.161 rows=1,281 loops=3)

  • Sort Key: (date_trunc('day'::text, e0."timestamp")), e0.sensor_id
  • Sort Method: quicksort Memory: 918kB
7. 2,294.049 76,794.213 ↑ 14.0 1,281 3

Hash Join (cost=5.13..208,883.60 rows=17,904 width=420) (actual time=5,513.512..25,598.071 rows=1,281 loops=3)

  • Hash Cond: (e0.sensor_id = s1.id)
8. 74,499.918 74,499.918 ↑ 1.2 257,825 3

Parallel Seq Scan on events e0 (cost=0.00..207,423.76 rows=322,276 width=111) (actual time=1.335..24,833.306 rows=257,825 loops=3)

9. 0.015 0.246 ↑ 1.3 3 3

Hash (cost=5.08..5.08 rows=4 width=325) (actual time=0.081..0.082 rows=3 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.075 0.231 ↑ 1.3 3 3

Hash Join (cost=2.24..5.08 rows=4 width=325) (actual time=0.054..0.077 rows=3 loops=3)

  • Hash Cond: (s1.sensor_type_id = s2_1.id)
11. 0.087 0.087 ↑ 1.6 40 3

Seq Scan on sensors s1 (cost=0.00..2.64 rows=64 width=341) (actual time=0.014..0.029 rows=40 loops=3)

12. 0.012 0.069 ↓ 3.0 3 3

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

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

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

  • Filter: ((kind)::text = 'pv'::text)
  • Rows Removed by Filter: 14
14. 0.009 0.185 ↑ 8.0 21 1

Hash (cost=29.21..29.21 rows=167 width=24) (actual time=0.185..0.185 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
15. 0.011 0.176 ↑ 8.0 21 1

Nested Loop (cost=6.90..29.21 rows=167 width=24) (actual time=0.151..0.176 rows=21 loops=1)

16. 0.007 0.144 ↓ 3.0 3 1

Nested Loop (cost=6.89..9.20 rows=1 width=16) (actual time=0.132..0.144 rows=3 loops=1)

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

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

  • Filter: ((kind)::text = 'pv'::text)
  • Rows Removed by Filter: 14
18. 0.006 0.117 ↑ 1.0 3 3

Unique (cost=6.89..6.91 rows=3 width=1,756) (actual time=0.037..0.039 rows=3 loops=3)

19. 0.015 0.111 ↑ 1.0 3 3

Sort (cost=6.89..6.90 rows=3 width=1,756) (actual time=0.037..0.037 rows=3 loops=3)

  • Sort Key: s0.id
  • Sort Method: quicksort Memory: 25kB
20. 0.005 0.096 ↑ 1.0 3 1

Nested Loop (cost=1.63..6.87 rows=3 width=1,756) (actual time=0.081..0.096 rows=3 loops=1)

21. 0.003 0.079 ↑ 1.0 3 1

Nested Loop (cost=1.49..5.63 rows=3 width=48) (actual time=0.069..0.079 rows=3 loops=1)

22. 0.004 0.064 ↑ 1.0 3 1

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

23. 0.014 0.040 ↑ 1.0 1 1

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

  • Hash Cond: (g1.id = g0.id)
24. 0.010 0.010 ↓ 1.5 19 1

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

25. 0.005 0.016 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on groups g0 (cost=0.00..1.20 rows=1 width=16) (actual time=0.010..0.011 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
27. 0.020 0.020 ↑ 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.018..0.020 rows=3 loops=1)

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

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

  • Index Cond: (id = g4.sensor_id)
29. 0.012 0.012 ↑ 1.0 1 3

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

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

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

Planning time : 27.909 ms
Execution time : 26,032.556 ms