explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kZVI

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 6,385.392 ↑ 125.0 8 1

Merge Left Join (cost=206,963.61..206,997.17 rows=1,000 width=12) (actual time=6,385.143..6,385.392 rows=8 loops=1)

  • Merge Cond: (period.period = (time_bucket('1 day'::interval, primary_data_cumulative_day.data_date)))
2. 0.008 0.028 ↑ 125.0 8 1

Sort (cost=59.84..62.34 rows=1,000 width=8) (actual time=0.027..0.028 rows=8 loops=1)

  • Sort Key: period.period
  • Sort Method: quicksort Memory: 25kB
3. 0.020 0.020 ↑ 125.0 8 1

Function Scan on generate_series period (cost=0.01..10.01 rows=1,000 width=8) (actual time=0.019..0.020 rows=8 loops=1)

4. 0.257 6,385.350 ↑ 66.7 3 1

GroupAggregate (cost=206,903.77..206,917.33 rows=200 width=12) (actual time=6,385.110..6,385.350 rows=3 loops=1)

  • Group Key: (time_bucket('1 day'::interval, primary_data_cumulative_day.data_date))
5. 2.246 6,385.093 ↓ 1.0 1,500 1

Sort (cost=206,903.77..206,907.46 rows=1,475 width=8) (actual time=6,384.943..6,385.093 rows=1,500 loops=1)

  • Sort Key: (time_bucket('1 day'::interval, primary_data_cumulative_day.data_date))
  • Sort Method: quicksort Memory: 119kB
6. 0.000 6,382.847 ↓ 1.0 1,500 1

Nested Loop (cost=73,225.11..206,826.14 rows=1,475 width=8) (actual time=3,279.529..6,382.847 rows=1,500 loops=1)

7. 0.014 0.014 ↑ 1.0 1 1

Index Only Scan using facility_pkey on facility (cost=0.28..2.30 rows=1 width=4) (actual time=0.011..0.014 rows=1 loops=1)

  • Index Cond: (facility_id = 2)
  • Heap Fetches: 1
8. 116.537 6,484.890 ↓ 1.0 1,500 1

Gather (cost=73,224.82..206,805.40 rows=1,475 width=12) (actual time=3,279.516..6,484.890 rows=1,500 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 7.601 6,368.353 ↑ 1.2 500 3

Nested Loop (cost=72,224.82..205,657.90 rows=615 width=12) (actual time=5,336.059..6,368.353 rows=500 loops=3)

10. 564.888 6,360.714 ↑ 1.2 167 3

Hash Join (cost=72,224.82..205,220.40 rows=208 width=8) (actual time=5,336.050..6,360.714 rows=167 loops=3)

  • Hash Cond: (equipment.sensor_id = sensor.sensor_id)
11. 2,237.684 2,237.684 ↑ 1.2 1,000,000 3

Parallel Seq Scan on equipment (cost=0.00..128,306.00 rows=1,250,000 width=8) (actual time=0.028..2,237.684 rows=1,000,000 loops=3)

12. 0.105 3,558.142 ↑ 1.0 500 3

Hash (cost=72,218.57..72,218.57 rows=500 width=8) (actual time=3,558.142..3,558.142 rows=500 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
13. 1,283.914 3,558.037 ↑ 1.0 500 3

Hash Join (cost=183.01..72,218.57 rows=500 width=8) (actual time=4.031..3,558.037 rows=500 loops=3)

  • Hash Cond: (sensor.gateway_id = gateway.gateway_id)
14. 2,270.302 2,270.302 ↑ 1.0 3,000,000 3

Seq Scan on sensor (cost=0.00..64,155.00 rows=3,000,000 width=8) (actual time=0.021..2,270.302 rows=3,000,000 loops=3)

15. 0.003 3.821 ↑ 1.0 1 3

Hash (cost=183.00..183.00 rows=1 width=8) (actual time=3.821..3.821 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 3.818 3.818 ↑ 1.0 1 3

Seq Scan on gateway (cost=0.00..183.00 rows=1 width=8) (actual time=0.014..3.818 rows=1 loops=3)

  • Filter: (facility_id = 2)
  • Rows Removed by Filter: 5999
17. 0.002 0.038 ↑ 4.3 3 500

Append (cost=0.00..1.97 rows=13 width=12) (actual time=0.021..0.038 rows=3 loops=500)

18. 0.000 0.000 ↓ 0.0 0 500

Seq Scan on primary_data_cumulative_day (cost=0.00..0.00 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=500)

  • Filter: (equipment.equipment_id = equipment_id)
19. 0.003 0.003 ↑ 3.0 1 500

Index Scan using _hyper_4_190_chunk_primary_data_cumulative_day_equipment_id_dat on _hyper_4_190_chunk (cost=0.43..0.49 rows=3 width=12) (actual time=0.002..0.003 rows=1 loops=500)

  • Index Cond: (equipment_id = equipment.equipment_id)
20. 0.003 0.003 ↑ 3.0 1 500

Index Scan using _hyper_4_191_chunk_primary_data_cumulative_day_equipment_id_dat on _hyper_4_191_chunk (cost=0.43..0.49 rows=3 width=12) (actual time=0.002..0.003 rows=1 loops=500)

  • Index Cond: (equipment_id = equipment.equipment_id)
21. 0.003 0.003 ↑ 3.0 1 500

Index Scan using _hyper_4_192_chunk_primary_data_cumulative_day_equipment_id_dat on _hyper_4_192_chunk (cost=0.43..0.49 rows=3 width=12) (actual time=0.002..0.003 rows=1 loops=500)

  • Index Cond: (equipment_id = equipment.equipment_id)
22. 0.027 0.027 ↑ 3.0 1 500

Index Scan using _hyper_4_193_chunk_primary_data_cumulative_day_equipment_id_dat on _hyper_4_193_chunk (cost=0.43..0.49 rows=3 width=12) (actual time=0.017..0.027 rows=1 loops=500)

  • Index Cond: (equipment_id = equipment.equipment_id)