explain.depesz.com

PostgreSQL's explain analyze made readable

Result: u9gT

Settings
# exclusive inclusive rows x rows loops node
1. 0.660 13,908.756 ↑ 5,165.2 1,590 1

Merge Join (cost=719,947.90..864,073.57 rows=8,212,594 width=36) (actual time=13,907.784..13,908.756 rows=1,590 loops=1)

  • Output: (date_trunc('minute'::text, sl.eventdate)), 4200, (((sum(sl.value))::double precision) - subtrahend.value), (((max(sl.value))::double precision) - subtrahend.max), (((min(sl.value))::double precision) - subtrahend.min)
  • Inner Unique: true
  • Merge Cond: ((date_trunc('minute'::text, sl.eventdate)) = subtrahend.eventdate)
2.          

CTE sensors

3. 0.057 0.159 ↑ 1.0 1 1

Nested Loop (cost=0.99..10.63 rows=1 width=64) (actual time=0.131..0.159 rows=1 loops=1)

  • Output: ((regexp_split_to_array((asca.value)::text, ';'::text))[1])::integer[], ((regexp_split_to_array((asca.value)::text, ';'::text))[2])::integer[]
  • Inner Unique: true
4. 0.004 0.074 ↓ 7.0 7 1

Nested Loop (cost=0.85..10.44 rows=1 width=5) (actual time=0.063..0.074 rows=7 loops=1)

  • Output: asca.value, asca.sensor_configuration_attributes_id
5. 0.003 0.041 ↑ 1.0 1 1

Nested Loop (cost=0.56..6.48 rows=1 width=4) (actual time=0.040..0.041 rows=1 loops=1)

  • Output: casc.id
  • Inner Unique: true
6. 0.020 0.020 ↑ 1.0 1 1

Index Scan using idx_device_sensors_device_id on client.device_sensors cds_2 (cost=0.28..2.50 rows=1 width=4) (actual time=0.019..0.020 rows=1 loops=1)

  • Output: cds_2.id, cds_2.device_id, cds_2.name, cds_2.address, cds_2."offset", cds_2.last_sensor_history_id, cds_2.sensor_preset_id, cds_2.unit_id, cds_2.max_production, cds_2.effective_production, cds_2.effective_oee, cds_2.inac (...)
  • Index Cond: (cds_2.device_id = 4177)
7. 0.018 0.018 ↑ 1.0 1 1

Index Scan using uk_active_session_configurations_sensor_id on configuration.active_session_configurations casc (cost=0.28..2.50 rows=1 width=8) (actual time=0.018..0.018 rows=1 loops=1)

  • Output: casc.id, casc.port, casc.sensor_type_mode_id, casc.sensor_id, casc.data_modifier, casc.status, casc.cust_calc_detail_id, casc.type
  • Index Cond: (casc.sensor_id = cds_2.id)
  • Filter: (casc.cust_calc_detail_id = 98)
8. 0.029 0.029 ↓ 1.4 7 1

Index Scan using "IDX_C_ASCA_ASCID" on configuration.active_session_conf_attributes asca (cost=0.29..3.91 rows=5 width=9) (actual time=0.021..0.029 rows=7 loops=1)

  • Output: asca.id, asca.sensor_configuration_attributes_id, asca.active_session_configuration_id, asca.value
  • Index Cond: (asca.active_session_configuration_id = casc.id)
9. 0.028 0.028 ↓ 0.0 0 7

Index Scan using pk_sensor_configuration_attributes_id on configuration.sensor_configuration_attributes sca (cost=0.14..0.16 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=7)

  • Output: sca.id, sca.key, sca.description, sca.sensor_type_mode_id, sca.db_conf
  • Index Cond: (sca.id = asca.sensor_configuration_attributes_id)
  • Filter: ((sca.key)::text = 'ROLE'::text)
  • Rows Removed by Filter: 1
10.          

CTE minuend

11. 0.002 0.198 ↑ 100.0 1 1

Nested Loop (cost=2.06..52.79 rows=100 width=4) (actual time=0.197..0.198 rows=1 loops=1)

  • Output: cds_3.id
  • Inner Unique: true
12. 0.007 0.175 ↑ 100.0 1 1

HashAggregate (cost=1.78..2.78 rows=100 width=4) (actual time=0.175..0.175 rows=1 loops=1)

  • Output: (unnest(sensors.minuend))
  • Group Key: unnest(sensors.minuend)
13. 0.005 0.168 ↑ 100.0 1 1

ProjectSet (cost=0.00..0.53 rows=100 width=4) (actual time=0.138..0.168 rows=1 loops=1)

  • Output: unnest(sensors.minuend)
14. 0.163 0.163 ↑ 1.0 1 1

CTE Scan on sensors (cost=0.00..0.02 rows=1 width=32) (actual time=0.134..0.163 rows=1 loops=1)

  • Output: sensors.minuend, sensors.subtrahend
15. 0.021 0.021 ↑ 1.0 1 1

Index Only Scan using "unique_device_sensors-id-device-id" on client.device_sensors cds_3 (cost=0.28..0.51 rows=1 width=4) (actual time=0.021..0.021 rows=1 loops=1)

  • Output: cds_3.id, cds_3.device_id
  • Index Cond: (cds_3.id = (unnest(sensors.minuend)))
  • Heap Fetches: 1
16.          

CTE subtrahend

17. 0.004 0.048 ↑ 100.0 1 1

Nested Loop (cost=2.06..52.79 rows=100 width=4) (actual time=0.046..0.048 rows=1 loops=1)

  • Output: cds_4.id
  • Inner Unique: true
18. 0.007 0.018 ↑ 100.0 1 1

HashAggregate (cost=1.78..2.78 rows=100 width=4) (actual time=0.016..0.018 rows=1 loops=1)

  • Output: (unnest(sensors_1.subtrahend))
  • Group Key: unnest(sensors_1.subtrahend)
19. 0.009 0.011 ↑ 100.0 1 1

ProjectSet (cost=0.00..0.53 rows=100 width=4) (actual time=0.009..0.011 rows=1 loops=1)

  • Output: unnest(sensors_1.subtrahend)
20. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on sensors sensors_1 (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.002 rows=1 loops=1)

  • Output: sensors_1.minuend, sensors_1.subtrahend
21. 0.026 0.026 ↑ 1.0 1 1

Index Only Scan using "unique_device_sensors-id-device-id" on client.device_sensors cds_4 (cost=0.28..0.51 rows=1 width=4) (actual time=0.026..0.026 rows=1 loops=1)

  • Output: cds_4.id, cds_4.device_id
  • Index Cond: (cds_4.id = (unnest(sensors_1.subtrahend)))
  • Heap Fetches: 1
22. 1.446 7,141.711 ↑ 25.5 1,590 1

Sort (cost=359,915.84..360,017.16 rows=40,528 width=32) (actual time=7,141.498..7,141.711 rows=1,590 loops=1)

  • Output: (date_trunc('minute'::text, sl.eventdate)), ((sum(sl.value))::double precision), ((max(sl.value))::double precision), ((min(sl.value))::double precision)
  • Sort Key: (date_trunc('minute'::text, sl.eventdate))
  • Sort Method: quicksort Memory: 173kB
23. 12.578 7,140.265 ↑ 25.5 1,590 1

HashAggregate (cost=355,598.27..356,408.83 rows=40,528 width=32) (actual time=7,139.048..7,140.265 rows=1,590 loops=1)

  • Output: (date_trunc('minute'::text, sl.eventdate)), (sum(sl.value))::double precision, (max(sl.value))::double precision, (min(sl.value))::double precision
  • Group Key: date_trunc('minute'::text, sl.eventdate)
24. 927.268 7,127.687 ↑ 34.7 8,100 1

Hash Join (cost=96.28..352,788.24 rows=281,003 width=12) (actual time=2.260..7,127.687 rows=8,100 loops=1)

  • Output: date_trunc('minute'::text, sl.eventdate), sl.value
  • Hash Cond: (sl.sensor_id = cds.id)
25. 865.945 6,198.849 ↑ 1.1 8,936,291 1

Append (cost=0.42..313,330.89 rows=9,559,720 width=16) (actual time=0.102..6,198.849 rows=8,936,291 loops=1)

26. 0.024 0.024 ↓ 0.0 0 1

Index Scan using idx_sensorlog_eventdate on log.sensorlog sl (cost=0.42..2.64 rows=1 width=16) (actual time=0.024..0.024 rows=0 loops=1)

  • Output: sl.eventdate, sl.value, sl.sensor_id
  • Index Cond: ((sl.eventdate >= '2019-10-02 08:00:00+00'::timestamp with time zone) AND (sl.eventdate <= '2019-10-03 10:30:00+00'::timestamp with time zone))
27. 5,332.880 5,332.880 ↑ 1.1 8,936,291 1

Index Scan using idx_sensorlog_201940_eventdate on log.sensorlog_201940 sl_1 (cost=0.56..313,328.25 rows=9,559,719 width=16) (actual time=0.077..5,332.880 rows=8,936,291 loops=1)

  • Output: sl_1.eventdate, sl_1.value, sl_1.sensor_id
  • Index Cond: ((sl_1.eventdate >= '2019-10-02 08:00:00+00'::timestamp with time zone) AND (sl_1.eventdate <= '2019-10-03 10:30:00+00'::timestamp with time zone))
28. 0.008 1.570 ↑ 100.0 1 1

Hash (cost=94.61..94.61 rows=100 width=8) (actual time=1.570..1.570 rows=1 loops=1)

  • Output: cds.id, minuend.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.591 1.562 ↑ 100.0 1 1

Hash Semi Join (cost=3.25..94.61 rows=100 width=8) (actual time=1.532..1.562 rows=1 loops=1)

  • Output: cds.id, minuend.id
  • Hash Cond: (cds.id = minuend.id)
30. 0.763 0.763 ↓ 1.0 3,439 1

Seq Scan on client.device_sensors cds (cost=0.00..81.32 rows=3,402 width=4) (actual time=0.016..0.763 rows=3,439 loops=1)

  • Output: cds.id, cds.device_id, cds.name, cds.address, cds."offset", cds.last_sensor_history_id, cds.sensor_preset_id, cds.unit_id, cds.max_production, cds.effective_production, cds.effective_oee, cds.inactivity_thresho (...)
31. 0.006 0.208 ↑ 100.0 1 1

Hash (cost=2.00..2.00 rows=100 width=4) (actual time=0.208..0.208 rows=1 loops=1)

  • Output: minuend.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.202 0.202 ↑ 100.0 1 1

CTE Scan on minuend (cost=0.00..2.00 rows=100 width=4) (actual time=0.201..0.202 rows=1 loops=1)

  • Output: minuend.id
33. 0.726 6,766.385 ↑ 25.5 1,590 1

Sort (cost=359,915.84..360,017.16 rows=40,528 width=32) (actual time=6,766.278..6,766.385 rows=1,590 loops=1)

  • Output: subtrahend.value, subtrahend.max, subtrahend.min, subtrahend.eventdate
  • Sort Key: subtrahend.eventdate
  • Sort Method: quicksort Memory: 173kB
34. 0.181 6,765.659 ↑ 25.5 1,591 1

Subquery Scan on subtrahend (cost=355,598.27..356,814.11 rows=40,528 width=32) (actual time=6,764.717..6,765.659 rows=1,591 loops=1)

  • Output: subtrahend.value, subtrahend.max, subtrahend.min, subtrahend.eventdate
35. 11.525 6,765.478 ↑ 25.5 1,591 1

HashAggregate (cost=355,598.27..356,408.83 rows=40,528 width=32) (actual time=6,764.715..6,765.478 rows=1,591 loops=1)

  • Output: (date_trunc('minute'::text, sl_2.eventdate)), (sum(sl_2.value))::double precision, (max(sl_2.value))::double precision, (min(sl_2.value))::double precision
  • Group Key: date_trunc('minute'::text, sl_2.eventdate)
36. 919.914 6,753.953 ↑ 34.1 8,241 1

Hash Join (cost=96.28..352,788.24 rows=281,003 width=12) (actual time=3.052..6,753.953 rows=8,241 loops=1)

  • Output: date_trunc('minute'::text, sl_2.eventdate), sl_2.value
  • Hash Cond: (sl_2.sensor_id = cds_1.id)
37. 882.685 5,832.349 ↑ 1.1 8,936,291 1

Append (cost=0.42..313,330.89 rows=9,559,720 width=16) (actual time=0.066..5,832.349 rows=8,936,291 loops=1)

38. 0.021 0.021 ↓ 0.0 0 1

Index Scan using idx_sensorlog_eventdate on log.sensorlog sl_2 (cost=0.42..2.64 rows=1 width=16) (actual time=0.021..0.021 rows=0 loops=1)

  • Output: sl_2.eventdate, sl_2.value, sl_2.sensor_id
  • Index Cond: ((sl_2.eventdate >= '2019-10-02 08:00:00+00'::timestamp with time zone) AND (sl_2.eventdate <= '2019-10-03 10:30:00+00'::timestamp with time zone))
39. 4,949.643 4,949.643 ↑ 1.1 8,936,291 1

Index Scan using idx_sensorlog_201940_eventdate on log.sensorlog_201940 sl_3 (cost=0.56..313,328.25 rows=9,559,719 width=16) (actual time=0.044..4,949.643 rows=8,936,291 loops=1)

  • Output: sl_3.eventdate, sl_3.value, sl_3.sensor_id
  • Index Cond: ((sl_3.eventdate >= '2019-10-02 08:00:00+00'::timestamp with time zone) AND (sl_3.eventdate <= '2019-10-03 10:30:00+00'::timestamp with time zone))
40. 0.018 1.690 ↑ 100.0 1 1

Hash (cost=94.61..94.61 rows=100 width=8) (actual time=1.690..1.690 rows=1 loops=1)

  • Output: cds_1.id, subtrahend_1.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
41. 0.747 1.672 ↑ 100.0 1 1

Hash Semi Join (cost=3.25..94.61 rows=100 width=8) (actual time=1.665..1.672 rows=1 loops=1)

  • Output: cds_1.id, subtrahend_1.id
  • Hash Cond: (cds_1.id = subtrahend_1.id)
42. 0.865 0.865 ↓ 1.0 3,439 1

Seq Scan on client.device_sensors cds_1 (cost=0.00..81.32 rows=3,402 width=4) (actual time=0.020..0.865 rows=3,439 loops=1)

  • Output: cds_1.id, cds_1.device_id, cds_1.name, cds_1.address, cds_1."offset", cds_1.last_sensor_history_id, cds_1.sensor_preset_id, cds_1.unit_id, cds_1.max_production, cds_1.effective_production, cds_1.effective (...)
43. 0.008 0.060 ↑ 100.0 1 1

Hash (cost=2.00..2.00 rows=100 width=4) (actual time=0.060..0.060 rows=1 loops=1)

  • Output: subtrahend_1.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
44. 0.052 0.052 ↑ 100.0 1 1

CTE Scan on subtrahend subtrahend_1 (cost=0.00..2.00 rows=100 width=4) (actual time=0.050..0.052 rows=1 loops=1)

  • Output: subtrahend_1.id
Planning time : 20.281 ms
Execution time : 13,912.282 ms