explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bUAW

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

Merge Join (cost=726,079.15..870,204.82 rows=8,212,594 width=36) (actual time=13,640.280..13,641.512 rows=1,590 loops=1)

  • Merge Cond: ((date_trunc('minute'::text, sl.eventdate)) = subtrahend.eventdate)
2.          

CTE sensors

3. 0.042 0.129 ↑ 1.0 1 1

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

4. 0.003 0.066 ↓ 7.0 7 1

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

5. 0.002 0.038 ↑ 1.0 1 1

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

6. 0.017 0.017 ↑ 1.0 1 1

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

  • Index Cond: (device_id = 4177)
7. 0.019 0.019 ↑ 1.0 1 1

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

  • Index Cond: (sensor_id = cds_2.id)
  • Filter: (cust_calc_detail_id = 98)
8. 0.025 0.025 ↓ 1.4 7 1

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

  • Index Cond: (active_session_configuration_id = casc.id)
9. 0.021 0.021 ↓ 0.0 0 7

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

  • Index Cond: (id = asca.sensor_configuration_attributes_id)
  • Filter: ((key)::text = 'ROLE'::text)
  • Rows Removed by Filter: 1
10. 0.859 6,841.331 ↑ 25.5 1,590 1

Sort (cost=363,034.26..363,135.58 rows=40,528 width=32) (actual time=6,841.091..6,841.331 rows=1,590 loops=1)

  • Sort Key: (date_trunc('minute'::text, sl.eventdate))
  • Sort Method: quicksort Memory: 173kB
11. 11.297 6,840.472 ↑ 25.5 1,590 1

HashAggregate (cost=358,716.68..359,527.24 rows=40,528 width=32) (actual time=6,839.729..6,840.472 rows=1,590 loops=1)

  • Group Key: date_trunc('minute'::text, sl.eventdate)
12. 931.866 6,829.175 ↑ 34.5 8,100 1

Hash Join (cost=95.81..355,922.78 rows=279,390 width=12) (actual time=1.380..6,829.175 rows=8,100 loops=1)

  • Hash Cond: (sl.sensor_id = cds.id)
13. 789.076 5,896.367 ↑ 1.1 8,936,214 1

Append (cost=0.42..316,691.86 rows=9,504,842 width=16) (actual time=0.059..5,896.367 rows=8,936,214 loops=1)

14. 0.021 0.021 ↓ 0.0 0 1

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

  • Index Cond: ((eventdate >= '2019-10-02 08:00:00+00'::timestamp with time zone) AND (eventdate < '2019-10-03 10:30:00+00'::timestamp with time zone))
15. 5,107.270 5,107.270 ↑ 1.1 8,936,214 1

Index Scan using idx_sensorlog_201940_eventdate on sensorlog_201940 sl_1 (cost=0.56..316,689.22 rows=9,504,841 width=16) (actual time=0.037..5,107.270 rows=8,936,214 loops=1)

  • Index Cond: ((eventdate >= '2019-10-02 08:00:00+00'::timestamp with time zone) AND (eventdate < '2019-10-03 10:30:00+00'::timestamp with time zone))
16. 0.007 0.942 ↑ 100.0 1 1

Hash (cost=94.14..94.14 rows=100 width=8) (actual time=0.941..0.942 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.356 0.935 ↑ 100.0 1 1

Hash Semi Join (cost=2.78..94.14 rows=100 width=8) (actual time=0.915..0.935 rows=1 loops=1)

  • Hash Cond: (cds.id = (unnest(sensors.minuend)))
18. 0.438 0.438 ↓ 1.0 3,439 1

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

19. 0.004 0.141 ↑ 100.0 1 1

Hash (cost=1.53..1.53 rows=100 width=4) (actual time=0.141..0.141 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.005 0.137 ↑ 100.0 1 1

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

21. 0.132 0.132 ↑ 1.0 1 1

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

22. 0.763 6,799.298 ↑ 25.5 1,590 1

Sort (cost=363,034.26..363,135.58 rows=40,528 width=32) (actual time=6,799.180..6,799.298 rows=1,590 loops=1)

  • Sort Key: subtrahend.eventdate
  • Sort Method: quicksort Memory: 173kB
23. 0.187 6,798.535 ↑ 25.5 1,590 1

Subquery Scan on subtrahend (cost=358,716.68..359,932.52 rows=40,528 width=32) (actual time=6,797.568..6,798.535 rows=1,590 loops=1)

24. 11.774 6,798.348 ↑ 25.5 1,590 1

HashAggregate (cost=358,716.68..359,527.24 rows=40,528 width=32) (actual time=6,797.567..6,798.348 rows=1,590 loops=1)

  • Group Key: date_trunc('minute'::text, sl_2.eventdate)
25. 922.368 6,786.574 ↑ 33.9 8,240 1

Hash Join (cost=95.81..355,922.78 rows=279,390 width=12) (actual time=1.858..6,786.574 rows=8,240 loops=1)

  • Hash Cond: (sl_2.sensor_id = cds_1.id)
26. 894.569 5,863.215 ↑ 1.1 8,936,214 1

Append (cost=0.42..316,691.86 rows=9,504,842 width=16) (actual time=0.052..5,863.215 rows=8,936,214 loops=1)

27. 0.018 0.018 ↓ 0.0 0 1

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

  • Index Cond: ((eventdate >= '2019-10-02 08:00:00+00'::timestamp with time zone) AND (eventdate < '2019-10-03 10:30:00+00'::timestamp with time zone))
28. 4,968.628 4,968.628 ↑ 1.1 8,936,214 1

Index Scan using idx_sensorlog_201940_eventdate on sensorlog_201940 sl_3 (cost=0.56..316,689.22 rows=9,504,841 width=16) (actual time=0.033..4,968.628 rows=8,936,214 loops=1)

  • Index Cond: ((eventdate >= '2019-10-02 08:00:00+00'::timestamp with time zone) AND (eventdate < '2019-10-03 10:30:00+00'::timestamp with time zone))
29. 0.008 0.991 ↑ 100.0 1 1

Hash (cost=94.14..94.14 rows=100 width=8) (actual time=0.991..0.991 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.414 0.983 ↑ 100.0 1 1

Hash Semi Join (cost=2.78..94.14 rows=100 width=8) (actual time=0.979..0.983 rows=1 loops=1)

  • Hash Cond: (cds_1.id = (unnest(sensors_1.subtrahend)))
31. 0.553 0.553 ↓ 1.0 3,439 1

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

32. 0.006 0.016 ↑ 100.0 1 1

Hash (cost=1.53..1.53 rows=100 width=4) (actual time=0.016..0.016 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
33. 0.008 0.010 ↑ 100.0 1 1

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

34. 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)

Planning time : 23.587 ms
Execution time : 13,644.458 ms