explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1vT8

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 3,014.804 ↑ 125.0 8 1

Merge Left Join (cost=206,963.61..206,997.17 rows=1,000 width=12) (actual time=3,014.587..3,014.804 rows=8 loops=1)

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

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

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

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

4. 0.230 3,014.772 ↑ 66.7 3 1

GroupAggregate (cost=206,903.77..206,917.33 rows=200 width=12) (actual time=3,014.562..3,014.772 rows=3 loops=1)

  • Group Key: (time_bucket('1 day'::interval, primary_data_cumulative_day.data_date))
5. 0.477 3,014.542 ↓ 1.0 1,500 1

Sort (cost=206,903.77..206,907.46 rows=1,475 width=8) (actual time=3,014.414..3,014.542 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 3,014.065 ↓ 1.0 1,500 1

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

7. 0.040 0.040 ↑ 1.0 1 1

Index Only Scan using facility_facility_id_idx on facility (cost=0.28..2.30 rows=1 width=4) (actual time=0.037..0.040 rows=1 loops=1)

  • Index Cond: (facility_id = 2)
  • Heap Fetches: 1
8. 42.355 3,040.778 ↓ 1.0 1,500 1

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

  • Workers Planned: 2
  • Workers Launched: 2
9. 18.859 2,998.423 ↑ 1.2 500 3

Nested Loop (cost=72,224.82..205,657.90 rows=615 width=12) (actual time=2,440.831..2,998.423 rows=500 loops=3)

10. 215.495 2,979.451 ↑ 1.2 167 3

Hash Join (cost=72,224.82..205,220.40 rows=208 width=8) (actual time=2,440.499..2,979.451 rows=167 loops=3)

  • Hash Cond: (equipment.sensor_id = sensor.sensor_id)
11. 1,132.246 1,132.246 ↑ 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.041..1,132.246 rows=1,000,000 loops=3)

12. 0.108 1,631.710 ↑ 1.0 500 3

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

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

Hash Join (cost=183.01..72,218.57 rows=500 width=8) (actual time=1.687..1,631.602 rows=500 loops=3)

  • Hash Cond: (sensor.gateway_id = gateway.gateway_id)
14. 1,058.582 1,058.582 ↑ 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.015..1,058.582 rows=3,000,000 loops=3)

15. 0.004 1.496 ↑ 1.0 1 3

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

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

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

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

Append (cost=0.00..1.97 rows=13 width=12) (actual time=0.015..0.113 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.009 0.009 ↑ 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.005..0.009 rows=1 loops=500)

  • Index Cond: (equipment_id = equipment.equipment_id)
20. 0.023 0.023 ↑ 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.011..0.023 rows=1 loops=500)

  • Index Cond: (equipment_id = equipment.equipment_id)
21. 0.047 0.047 ↑ 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.005..0.047 rows=1 loops=500)

  • Index Cond: (equipment_id = equipment.equipment_id)
22. 0.031 0.031 ↑ 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.006..0.031 rows=1 loops=500)

  • Index Cond: (equipment_id = equipment.equipment_id)