explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KqO4

Settings
# exclusive inclusive rows x rows loops node
1. 0.046 1.181 ↑ 1.7 30 1

Hash Join (cost=339.78..341.80 rows=50 width=36) (actual time=1.156..1.181 rows=30 loops=1)

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

CTE minuend

3. 0.002 0.176 ↑ 1.0 1 1

Nested Loop (cost=0.85..20.69 rows=1 width=4) (actual time=0.160..0.176 rows=1 loops=1)

  • Join Filter: (asca.sensor_configuration_attributes_id = sca.id)
4. 0.005 0.147 ↑ 1.0 1 1

Nested Loop (cost=0.85..18.73 rows=1 width=8) (actual time=0.132..0.147 rows=1 loops=1)

5. 0.005 0.082 ↑ 1.0 5 1

Nested Loop (cost=0.56..16.51 rows=5 width=8) (actual time=0.056..0.082 rows=5 loops=1)

6. 0.037 0.037 ↑ 1.0 5 1

Index Scan using idx_device_sensors_device_id on device_sensors cds_2 (cost=0.28..4.02 rows=5 width=4) (actual time=0.031..0.037 rows=5 loops=1)

  • Index Cond: (device_id = 3861)
  • Filter: (id <> 4200)
  • Rows Removed by Filter: 1
7. 0.040 0.040 ↑ 1.0 1 5

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.008..0.008 rows=1 loops=5)

  • Index Cond: (sensor_id = cds_2.id)
8. 0.060 0.060 ↓ 0.0 0 5

Index Scan using "IDX_C_ASCA_ASCID" on active_session_conf_attributes asca (cost=0.29..0.43 rows=1 width=8) (actual time=0.011..0.012 rows=0 loops=5)

  • Index Cond: (active_session_configuration_id = casc.id)
  • Filter: ((value)::text = 'minuend'::text)
  • Rows Removed by Filter: 5
9. 0.027 0.027 ↑ 2.0 1 1

Seq Scan on sensor_configuration_attributes sca (cost=0.00..1.94 rows=2 width=4) (actual time=0.027..0.027 rows=1 loops=1)

  • Filter: ((key)::text = 'ROLE'::text)
  • Rows Removed by Filter: 59
10.          

CTE subtrahend

11. 0.002 0.111 ↑ 1.0 1 1

Nested Loop (cost=0.85..20.69 rows=1 width=4) (actual time=0.063..0.111 rows=1 loops=1)

  • Join Filter: (asca_1.sensor_configuration_attributes_id = sca_1.id)
12. 0.004 0.094 ↑ 1.0 1 1

Nested Loop (cost=0.85..18.73 rows=1 width=8) (actual time=0.047..0.094 rows=1 loops=1)

13. 0.005 0.050 ↑ 1.0 5 1

Nested Loop (cost=0.56..16.51 rows=5 width=8) (actual time=0.031..0.050 rows=5 loops=1)

14. 0.020 0.020 ↑ 1.0 5 1

Index Scan using idx_device_sensors_device_id on device_sensors cds_3 (cost=0.28..4.02 rows=5 width=4) (actual time=0.016..0.020 rows=5 loops=1)

  • Index Cond: (device_id = 3861)
  • Filter: (id <> 4200)
  • Rows Removed by Filter: 1
15. 0.025 0.025 ↑ 1.0 1 5

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

  • Index Cond: (sensor_id = cds_3.id)
16. 0.040 0.040 ↓ 0.0 0 5

Index Scan using "IDX_C_ASCA_ASCID" on active_session_conf_attributes asca_1 (cost=0.29..0.43 rows=1 width=8) (actual time=0.007..0.008 rows=0 loops=5)

  • Index Cond: (active_session_configuration_id = casc_1.id)
  • Filter: ((value)::text = 'subtrahend'::text)
  • Rows Removed by Filter: 5
17. 0.015 0.015 ↑ 2.0 1 1

Seq Scan on sensor_configuration_attributes sca_1 (cost=0.00..1.94 rows=2 width=4) (actual time=0.015..0.015 rows=1 loops=1)

  • Filter: ((key)::text = 'ROLE'::text)
  • Rows Removed by Filter: 59
18. 0.089 0.608 ↑ 1.7 30 1

HashAggregate (cost=148.14..149.14 rows=50 width=32) (actual time=0.597..0.608 rows=30 loops=1)

  • Group Key: date_trunc('minute'::text, sl.eventdate)
19. 0.092 0.519 ↓ 2.9 147 1

Nested Loop (cost=0.72..147.64 rows=50 width=12) (actual time=0.300..0.519 rows=147 loops=1)

20. 0.001 0.209 ↑ 1.0 1 1

Nested Loop (cost=0.30..2.53 rows=1 width=8) (actual time=0.209..0.209 rows=1 loops=1)

21. 0.005 0.186 ↑ 1.0 1 1

HashAggregate (cost=0.02..0.03 rows=1 width=4) (actual time=0.186..0.186 rows=1 loops=1)

  • Group Key: minuend.sensor_ids
22. 0.181 0.181 ↑ 1.0 1 1

CTE Scan on minuend (cost=0.00..0.02 rows=1 width=4) (actual time=0.165..0.181 rows=1 loops=1)

23. 0.022 0.022 ↑ 1.0 1 1

Index Only Scan using "unique_device_sensors-id-device-id" on device_sensors cds (cost=0.28..2.50 rows=1 width=4) (actual time=0.022..0.022 rows=1 loops=1)

  • Index Cond: (id = minuend.sensor_ids)
  • Heap Fetches: 1
24. 0.037 0.218 ↑ 1.1 147 1

Append (cost=0.42..143.41 rows=157 width=16) (actual time=0.072..0.218 rows=147 loops=1)

25. 0.023 0.023 ↓ 0.0 0 1

Index Scan using "PK_L_SLSNRDATE" on sensorlog sl (cost=0.42..2.17 rows=1 width=16) (actual time=0.023..0.023 rows=0 loops=1)

  • Index Cond: ((sensor_id = cds.id) AND (eventdate >= '2019-10-02 15:00:00+00'::timestamp with time zone) AND (eventdate < '2019-10-02 15:30:00+00'::timestamp with time zone))
26. 0.158 0.158 ↑ 1.1 147 1

Index Scan using "PK_L_SLSNRDATE_201940" on sensorlog_201940 sl_1 (cost=0.56..141.25 rows=156 width=16) (actual time=0.048..0.158 rows=147 loops=1)

  • Index Cond: ((sensor_id = cds.id) AND (eventdate >= '2019-10-02 15:00:00+00'::timestamp with time zone) AND (eventdate < '2019-10-02 15:30:00+00'::timestamp with time zone))
27. 0.024 0.527 ↑ 1.7 30 1

Hash (cost=149.64..149.64 rows=50 width=32) (actual time=0.527..0.527 rows=30 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
28. 0.006 0.503 ↑ 1.7 30 1

Subquery Scan on subtrahend (cost=148.14..149.64 rows=50 width=32) (actual time=0.486..0.503 rows=30 loops=1)

29. 0.087 0.497 ↑ 1.7 30 1

HashAggregate (cost=148.14..149.14 rows=50 width=32) (actual time=0.485..0.497 rows=30 loops=1)

  • Group Key: date_trunc('minute'::text, sl_2.eventdate)
30. 0.088 0.410 ↓ 3.0 150 1

Nested Loop (cost=0.72..147.64 rows=50 width=12) (actual time=0.225..0.410 rows=150 loops=1)

31. 0.002 0.174 ↑ 1.0 1 1

Nested Loop (cost=0.30..2.53 rows=1 width=8) (actual time=0.173..0.174 rows=1 loops=1)

32. 0.039 0.153 ↑ 1.0 1 1

HashAggregate (cost=0.02..0.03 rows=1 width=4) (actual time=0.153..0.153 rows=1 loops=1)

  • Group Key: subtrahend_1.sensor_ids
33. 0.114 0.114 ↑ 1.0 1 1

CTE Scan on subtrahend subtrahend_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.066..0.114 rows=1 loops=1)

34. 0.019 0.019 ↑ 1.0 1 1

Index Only Scan using "unique_device_sensors-id-device-id" on device_sensors cds_1 (cost=0.28..2.50 rows=1 width=4) (actual time=0.019..0.019 rows=1 loops=1)

  • Index Cond: (id = subtrahend_1.sensor_ids)
  • Heap Fetches: 1
35. 0.019 0.148 ↑ 1.0 150 1

Append (cost=0.42..143.41 rows=157 width=16) (actual time=0.044..0.148 rows=150 loops=1)

36. 0.015 0.015 ↓ 0.0 0 1

Index Scan using "PK_L_SLSNRDATE" on sensorlog sl_2 (cost=0.42..2.17 rows=1 width=16) (actual time=0.015..0.015 rows=0 loops=1)

  • Index Cond: ((sensor_id = cds_1.id) AND (eventdate >= '2019-10-02 15:00:00+00'::timestamp with time zone) AND (eventdate < '2019-10-02 15:30:00+00'::timestamp with time zone))
37. 0.114 0.114 ↑ 1.0 150 1

Index Scan using "PK_L_SLSNRDATE_201940" on sensorlog_201940 sl_3 (cost=0.56..141.25 rows=156 width=16) (actual time=0.028..0.114 rows=150 loops=1)

  • Index Cond: ((sensor_id = cds_1.id) AND (eventdate >= '2019-10-02 15:00:00+00'::timestamp with time zone) AND (eventdate < '2019-10-02 15:30:00+00'::timestamp with time zone))
Planning time : 22.888 ms
Execution time : 1.788 ms