#	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)

Merge Left Join  (cost=206963.61..206997.17 rows=1000 width=12) (actual time=3014.587..3014.804 rows=8 loops=1)
  Merge Cond: (period.period = (time_bucket('1 day'::interval, primary_data_cumulative_day.data_date)))
  ->  Sort  (cost=59.84..62.34 rows=1000 width=8) (actual time=0.017..0.018 rows=8 loops=1)
        Sort Key: period.period
        Sort Method: quicksort  Memory: 25kB
        ->  Function Scan on generate_series period  (cost=0.01..10.01 rows=1000 width=8) (actual time=0.011..0.012 rows=8 loops=1)
  ->  GroupAggregate  (cost=206903.77..206917.33 rows=200 width=12) (actual time=3014.562..3014.772 rows=3 loops=1)
        Group Key: (time_bucket('1 day'::interval, primary_data_cumulative_day.data_date))
        ->  Sort  (cost=206903.77..206907.46 rows=1475 width=8) (actual time=3014.414..3014.542 rows=1500 loops=1)
              Sort Key: (time_bucket('1 day'::interval, primary_data_cumulative_day.data_date))
              Sort Method: quicksort  Memory: 119kB
              ->  Nested Loop  (cost=73225.11..206826.14 rows=1475 width=8) (actual time=1336.141..3014.065 rows=1500 loops=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
                    ->  Gather  (cost=73224.82..206805.40 rows=1475 width=12) (actual time=1336.101..3040.778 rows=1500 loops=1)
                          Workers Planned: 2
                          Workers Launched: 2
                          ->  Nested Loop  (cost=72224.82..205657.90 rows=615 width=12) (actual time=2440.831..2998.423 rows=500 loops=3)
                                ->  Hash Join  (cost=72224.82..205220.40 rows=208 width=8) (actual time=2440.499..2979.451 rows=167 loops=3)
                                      Hash Cond: (equipment.sensor_id = sensor.sensor_id)
                                      ->  Parallel Seq Scan on equipment  (cost=0.00..128306.00 rows=1250000 width=8) (actual time=0.041..1132.246 rows=1000000 loops=3)
                                      ->  Hash  (cost=72218.57..72218.57 rows=500 width=8) (actual time=1631.710..1631.710 rows=500 loops=3)
                                            Buckets: 1024  Batches: 1  Memory Usage: 28kB
                                            ->  Hash Join  (cost=183.01..72218.57 rows=500 width=8) (actual time=1.687..1631.602 rows=500 loops=3)
                                                  Hash Cond: (sensor.gateway_id = gateway.gateway_id)
                                                  ->  Seq Scan on sensor  (cost=0.00..64155.00 rows=3000000 width=8) (actual time=0.015..1058.582 rows=3000000 loops=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
                                                        ->  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
                                ->  Append  (cost=0.00..1.97 rows=13 width=12) (actual time=0.015..0.113 rows=3 loops=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)
                                      ->  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)
                                      ->  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)
                                      ->  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)
                                      ->  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)
Planning time: 3.248 ms
Execution time: 3042.147 ms

Per node type stats

node type	count	sum of times	% of query
Append	1	0.003 ms	0.0 %
Function Scan	1	0.012 ms	0.0 %
Gather	1	42.355 ms	1.4 %
GroupAggregate	1	0.230 ms	0.0 %
Hash	2	0.112 ms	0.0 %
Hash Join	2	787.019 ms	26.1 %
Index Only Scan	1	0.040 ms	0.0 %
Index Scan	4	0.110 ms	0.0 %
Merge Left Join	1	0.014 ms	0.0 %
Nested Loop	2	18.859 ms	0.6 %
Parallel Seq Scan	1	1,132.246 ms	37.6 %
Seq Scan	3	1,060.074 ms	35.2 %
Sort	2	0.483 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
_hyper_4_190_chunk	1	0.009 ms	0.0 %
Index Scan	1	0.009 ms	100.0 %
_hyper_4_191_chunk	1	0.023 ms	0.0 %
Index Scan	1	0.023 ms	100.0 %
_hyper_4_192_chunk	1	0.047 ms	0.0 %
Index Scan	1	0.047 ms	100.0 %
_hyper_4_193_chunk	1	0.031 ms	0.0 %
Index Scan	1	0.031 ms	100.0 %
equipment	1	1,132.246 ms	37.6 %
Parallel Seq Scan	1	1,132.246 ms	100.0 %
facility	1	0.040 ms	0.0 %
Index Only Scan	1	0.040 ms	100.0 %
gateway	1	1.492 ms	0.0 %
Seq Scan	1	1.492 ms	100.0 %
primary_data_cumulative_day	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
sensor	1	1,058.582 ms	35.1 %
Seq Scan	1	1,058.582 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1vT8

Color mode:

Visible columns:

Per node type stats

Per table stats