explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r23t

Settings
# exclusive inclusive rows x rows loops node
1. 1.379 393.003 ↑ 1.0 1 1

Aggregate (cost=571.56..571.57 rows=1 width=64) (actual time=393.003..393.003 rows=1 loops=1)

2.          

CTE devce_sensors

3. 0.015 0.123 ↑ 1.0 2 1

Nested Loop (cost=29.75..62.67 rows=2 width=154) (actual time=0.102..0.123 rows=2 loops=1)

  • Join Filter: (sl.sensor_id = cds.id)
4.          

CTE sensors_list

5. 0.000 0.021 ↑ 1.0 2 1

Append (cost=0.14..12.48 rows=2 width=8) (actual time=0.018..0.021 rows=2 loops=1)

6. 0.008 0.008 ↓ 0.0 0 1

Index Only Scan using "PK_C_VIRTSNR" on virtual_sensors cvs (cost=0.14..4.16 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (device_id = ANY ('{662}'::integer[]))
  • Heap Fetches: 0
7. 0.013 0.013 ↓ 2.0 2 1

Index Scan using idx_device_sensors_device_id on device_sensors (cost=0.28..8.30 rows=1 width=8) (actual time=0.010..0.013 rows=2 loops=1)

  • Index Cond: (device_id = ANY ('{662}'::integer[]))
8. 0.010 0.062 ↑ 1.0 2 1

Hash Join (cost=16.99..18.13 rows=2 width=58) (actual time=0.059..0.062 rows=2 loops=1)

  • Hash Cond: (cst.id = cstm.sensor_type_id)
9. 0.007 0.007 ↑ 1.0 9 1

Seq Scan on sensor_type cst (cost=0.00..1.09 rows=9 width=9) (actual time=0.005..0.007 rows=9 loops=1)

10. 0.003 0.045 ↑ 1.0 2 1

Hash (cost=16.96..16.96 rows=2 width=53) (actual time=0.045..0.045 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
11. 0.002 0.042 ↑ 1.0 2 1

Nested Loop (cost=0.42..16.96 rows=2 width=53) (actual time=0.031..0.042 rows=2 loops=1)

12. 0.003 0.036 ↑ 1.0 2 1

Nested Loop (cost=0.28..16.64 rows=2 width=44) (actual time=0.027..0.036 rows=2 loops=1)

13. 0.023 0.023 ↑ 1.0 2 1

CTE Scan on sensors_list sl (cost=0.00..0.04 rows=2 width=8) (actual time=0.019..0.023 rows=2 loops=1)

14. 0.010 0.010 ↑ 1.0 1 2

Index Scan using uk_active_session_configurations_sensor_id on active_session_configurations casc (cost=0.28..8.30 rows=1 width=36) (actual time=0.005..0.005 rows=1 loops=2)

  • Index Cond: (sensor_id = sl.sensor_id)
15. 0.004 0.004 ↑ 1.0 1 2

Index Scan using pk_sensor_type_mode_id on sensor_type_mode cstm (cost=0.14..0.16 rows=1 width=13) (actual time=0.002..0.002 rows=1 loops=2)

  • Index Cond: (id = casc.sensor_type_mode_id)
16. 0.008 0.008 ↑ 1.0 1 2

Index Scan using "unique_device_sensors-id-device-id" on device_sensors cds (cost=0.28..0.37 rows=1 width=52) (actual time=0.004..0.004 rows=1 loops=2)

  • Index Cond: (id = casc.sensor_id)
17.          

SubPlan (forNested Loop)

18. 0.004 0.038 ↑ 1.0 1 2

Aggregate (cost=15.63..15.65 rows=1 width=32) (actual time=0.019..0.019 rows=1 loops=2)

19. 0.008 0.034 ↓ 0.0 0 2

Hash Join (cost=13.84..15.62 rows=1 width=7) (actual time=0.017..0.017 rows=0 loops=2)

  • Hash Cond: (sca.id = asca.sensor_configuration_attributes_id)
20. 0.006 0.006 ↑ 61.0 1 1

Seq Scan on sensor_configuration_attributes sca (cost=0.00..1.61 rows=61 width=10) (actual time=0.006..0.006 rows=1 loops=1)

21. 0.002 0.020 ↓ 0.0 0 2

Hash (cost=13.83..13.83 rows=1 width=5) (actual time=0.010..0.010 rows=0 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
22. 0.018 0.018 ↓ 0.0 0 2

Index Scan using "IDX_C_ASCA_ASCID" on active_session_conf_attributes asca (cost=0.29..13.83 rows=1 width=5) (actual time=0.009..0.009 rows=0 loops=2)

  • Index Cond: (active_session_configuration_id = casc.id)
  • Filter: (((value)::text = ''::text) IS NOT TRUE)
  • Rows Removed by Filter: 4
23.          

CTE sensor_data

24. 22.356 390.000 ↑ 100.0 2 1

GroupAggregate (cost=178.26..193.76 rows=200 width=100) (actual time=377.981..390.000 rows=2 loops=1)

  • Group Key: d_1.id
25. 7.374 367.644 ↓ 20.2 20,160 1

Sort (cost=178.26..180.76 rows=1,000 width=108) (actual time=366.187..367.644 rows=20,160 loops=1)

  • Sort Key: d_1.id
  • Sort Method: quicksort Memory: 3421kB
26. 4.165 360.270 ↓ 20.2 20,160 1

Subquery Scan on d_1 (cost=78.43..128.43 rows=1,000 width=108) (actual time=257.994..360.270 rows=20,160 loops=1)

27. 105.336 356.105 ↓ 20.2 20,160 1

WindowAgg (cost=78.43..118.43 rows=1,000 width=112) (actual time=257.994..356.105 rows=20,160 loops=1)

28. 5.883 250.769 ↓ 20.2 20,160 1

Sort (cost=78.43..80.93 rows=1,000 width=80) (actual time=249.246..250.769 rows=20,160 loops=1)

  • Sort Key: ds_1.unit_id
  • Sort Method: quicksort Memory: 2344kB
29. 5.782 244.886 ↓ 20.2 20,160 1

Nested Loop (cost=0.53..28.60 rows=1,000 width=80) (actual time=122.325..244.886 rows=20,160 loops=1)

30. 0.006 0.030 ↓ 2.0 2 1

Nested Loop (cost=0.28..8.35 rows=1 width=8) (actual time=0.005..0.030 rows=2 loops=1)

31. 0.004 0.004 ↓ 2.0 2 1

CTE Scan on devce_sensors ds_2 (cost=0.00..0.04 rows=1 width=4) (actual time=0.001..0.004 rows=2 loops=1)

  • Filter: (hidden = 0)
32. 0.020 0.020 ↑ 1.0 1 2

Index Scan using "unique_device_sensors-id-device-id" on device_sensors ds_1 (cost=0.28..8.30 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=2)

  • Index Cond: (id = ds_2.id)
33. 239.074 239.074 ↓ 10.1 10,080 2

Function Scan on get_sensor_stats dash (cost=0.25..10.25 rows=1,000 width=72) (actual time=118.626..119.537 rows=10,080 loops=2)

34.          

CTE sensors

35. 0.042 0.367 ↑ 1.0 2 1

GroupAggregate (cost=57.27..57.36 rows=2 width=44) (actual time=0.352..0.367 rows=2 loops=1)

  • Group Key: x_1.id
36. 0.009 0.325 ↑ 1.0 2 1

Sort (cost=57.27..57.28 rows=2 width=116) (actual time=0.324..0.325 rows=2 loops=1)

  • Sort Key: x_1.id
  • Sort Method: quicksort Memory: 25kB
37. 0.010 0.316 ↑ 1.0 2 1

WindowAgg (cost=57.20..57.24 rows=2 width=116) (actual time=0.313..0.316 rows=2 loops=1)

38. 0.006 0.306 ↑ 1.0 2 1

Sort (cost=57.20..57.21 rows=2 width=116) (actual time=0.306..0.306 rows=2 loops=1)

  • Sort Key: x_1.unit
  • Sort Method: quicksort Memory: 25kB
39. 0.012 0.300 ↑ 1.0 2 1

Hash Join (cost=52.42..57.19 rows=2 width=116) (actual time=0.268..0.300 rows=2 loops=1)

  • Hash Cond: (x_1.ordering = rc.id)
40. 0.009 0.231 ↑ 1.0 2 1

Hash Join (cost=50.52..55.29 rows=2 width=108) (actual time=0.201..0.231 rows=2 loops=1)

  • Hash Cond: (sd.id = x_1.id)
41. 0.060 0.060 ↑ 100.0 2 1

CTE Scan on sensor_data sd (cost=0.00..4.00 rows=200 width=68) (actual time=0.031..0.060 rows=2 loops=1)

42. 0.002 0.162 ↑ 1.0 2 1

Hash (cost=50.49..50.49 rows=2 width=44) (actual time=0.162..0.162 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
43. 0.002 0.160 ↑ 1.0 2 1

Subquery Scan on x_1 (cost=48.01..50.49 rows=2 width=44) (actual time=0.152..0.160 rows=2 loops=1)

44. 0.008 0.158 ↑ 1.0 2 1

WindowAgg (cost=48.01..50.47 rows=2 width=80) (actual time=0.151..0.158 rows=2 loops=1)

45. 0.005 0.130 ↑ 1.0 2 1

Sort (cost=48.01..48.01 rows=2 width=12) (actual time=0.130..0.130 rows=2 loops=1)

  • Sort Key: (CASE WHEN (d_2.main_sensor_id = cds_1.id) THEN 0 ELSE cds_1.id END)
  • Sort Method: quicksort Memory: 25kB
46. 0.005 0.125 ↑ 1.0 2 1

Nested Loop (cost=30.03..48.00 rows=2 width=12) (actual time=0.115..0.125 rows=2 loops=1)

47. 0.006 0.108 ↑ 1.0 2 1

Nested Loop (cost=29.75..31.37 rows=2 width=702) (actual time=0.101..0.108 rows=2 loops=1)

  • Join Filter: (sl_1.sensor_id = cds_1.id)
48.          

CTE sensors_list

49. 0.001 0.027 ↑ 1.0 2 1

Append (cost=0.14..12.48 rows=2 width=8) (actual time=0.023..0.027 rows=2 loops=1)

50. 0.011 0.011 ↓ 0.0 0 1

Index Only Scan using "PK_C_VIRTSNR" on virtual_sensors cvs_1 (cost=0.14..4.16 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=1)

  • Index Cond: (device_id = ANY ('{662}'::integer[]))
  • Heap Fetches: 0
51. 0.015 0.015 ↓ 2.0 2 1

Index Scan using idx_device_sensors_device_id on device_sensors device_sensors_1 (cost=0.28..8.30 rows=1 width=8) (actual time=0.012..0.015 rows=2 loops=1)

  • Index Cond: (device_id = ANY ('{662}'::integer[]))
52. 0.008 0.092 ↑ 1.0 2 1

Hash Join (cost=16.99..18.13 rows=2 width=12) (actual time=0.090..0.092 rows=2 loops=1)

  • Hash Cond: (cst_1.id = cstm_1.sensor_type_id)
53. 0.022 0.022 ↑ 1.0 9 1

Seq Scan on sensor_type cst_1 (cost=0.00..1.09 rows=9 width=4) (actual time=0.021..0.022 rows=9 loops=1)

54. 0.002 0.062 ↑ 1.0 2 1

Hash (cost=16.96..16.96 rows=2 width=16) (actual time=0.062..0.062 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
55. 0.003 0.060 ↑ 1.0 2 1

Nested Loop (cost=0.42..16.96 rows=2 width=16) (actual time=0.047..0.060 rows=2 loops=1)

56. 0.018 0.047 ↑ 1.0 2 1

Nested Loop (cost=0.28..16.64 rows=2 width=16) (actual time=0.037..0.047 rows=2 loops=1)

  • -> Index Scan using uk_active_session_configurations_sensor_id on active_session_configurations casc_1 (cost=0.28..8.30 rows=1 width=8) (actual time=0.007 (...)
57. 0.029 0.029 ↑ 1.0 2 1

CTE Scan on sensors_list sl_1 (cost=0.00..0.04 rows=2 width=8) (actual time=0.025..0.029 rows=2 loops=1)

  • Index Cond: (sensor_id = sl_1.sensor_id)
58. 0.010 0.010 ↑ 1.0 1 2

Index Scan using pk_sensor_type_mode_id on sensor_type_mode cstm_1 (cost=0.14..0.16 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=2)

  • Index Cond: (id = casc_1.sensor_type_mode_id)
59. 0.010 0.010 ↑ 1.0 1 2

Index Scan using "unique_device_sensors-id-device-id" on device_sensors cds_1 (cost=0.28..0.37 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=2)

  • Index Cond: (id = casc_1.sensor_id)
  • Filter: (hidden = 0)
60. 0.012 0.012 ↑ 1.0 1 2

Index Scan using "PK_C_DID" on devices d_2 (cost=0.28..8.30 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=2)

  • Index Cond: (id = sl_1.device_id)
61.          

SubPlan (forWindowAgg)

62. 0.020 0.020 ↑ 1.0 1 2

Seq Scan on sensor_units su (cost=0.00..1.21 rows=1 width=3) (actual time=0.009..0.010 rows=1 loops=2)

  • Filter: (id = cds_1.unit_id)
  • Rows Removed by Filter: 16
63. 0.011 0.057 ↑ 1.0 40 1

Hash (cost=1.40..1.40 rows=40 width=12) (actual time=0.057..0.057 rows=40 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
64. 0.046 0.046 ↑ 1.0 40 1

Seq Scan on colors rc (cost=0.00..1.40 rows=40 width=12) (actual time=0.039..0.046 rows=40 loops=1)

65.          

CTE production

66. 0.013 0.357 ↓ 1.5 12 1

Nested Loop Left Join (cost=21.40..235.71 rows=8 width=65) (actual time=0.160..0.357 rows=12 loops=1)

67. 0.007 0.176 ↓ 1.5 12 1

Nested Loop Left Join (cost=4.65..101.51 rows=8 width=37) (actual time=0.115..0.176 rows=12 loops=1)

68. 0.027 0.109 ↓ 1.5 12 1

Bitmap Heap Scan on productionlog pl (cost=4.36..35.05 rows=8 width=24) (actual time=0.092..0.109 rows=12 loops=1)

  • Recheck Cond: ((device_id = 662) AND (tstzrange(start_date, end_date) && '["2018-08-12 21:00:00+00","2018-08-19 21:00:00+00")'::tstzrange))
  • Heap Blocks: exact=7
69. 0.082 0.082 ↓ 1.5 12 1

Bitmap Index Scan on "IDX_L_PLDEVDATERNG" (cost=0.00..4.36 rows=8 width=0) (actual time=0.082..0.082 rows=12 loops=1)

  • Index Cond: ((device_id = 662) AND (tstzrange(start_date, end_date) && '["2018-08-12 21:00:00+00","2018-08-19 21:00:00+00")'::tstzrange))
70. 0.060 0.060 ↑ 1.0 1 12

Index Scan using "PK_PROD_ID" on products cp (cost=0.29..8.31 rows=1 width=21) (actual time=0.005..0.005 rows=1 loops=12)

  • Index Cond: (id = pl.product_id)
71. 0.060 0.168 ↑ 1.0 1 12

Aggregate (cost=16.75..16.76 rows=1 width=32) (actual time=0.014..0.014 rows=1 loops=12)

72. 0.012 0.108 ↑ 1.0 1 12

Nested Loop (cost=0.70..16.74 rows=1 width=13) (actual time=0.008..0.009 rows=1 loops=12)

73. 0.060 0.060 ↑ 1.0 1 12

Index Scan using "IDX_L_PSMPRDLOGID" on productionlog_shift_managers lpsm (cost=0.42..8.44 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=12)

  • Index Cond: (productionlog_id = pl.id)
74. 0.036 0.036 ↑ 1.0 1 12

Index Scan using "PK_SHIFTM_ID" on shift_managers csm (cost=0.28..8.29 rows=1 width=17) (actual time=0.003..0.003 rows=1 loops=12)

  • Index Cond: (id = lpsm.shift_manager_id)
75.          

CTE production_in_json

76. 0.118 0.483 ↑ 1.0 1 1

Aggregate (cost=0.32..0.34 rows=1 width=32) (actual time=0.483..0.483 rows=1 loops=1)

77. 0.365 0.365 ↓ 1.5 12 1

CTE Scan on production p (cost=0.00..0.16 rows=8 width=266) (actual time=0.161..0.365 rows=12 loops=1)

78.          

Initplan (forAggregate)

79. 0.012 0.383 ↑ 1.0 1 1

Aggregate (cost=0.05..0.06 rows=1 width=32) (actual time=0.383..0.383 rows=1 loops=1)

80. 0.371 0.371 ↑ 1.0 2 1

CTE Scan on sensors s (cost=0.00..0.04 rows=2 width=40) (actual time=0.354..0.371 rows=2 loops=1)

81. 0.110 391.241 ↓ 1.5 3 1

Subquery Scan on x (cost=16.68..21.64 rows=2 width=24) (actual time=390.978..391.241 rows=3 loops=1)

82. 0.001 391.131 ↓ 1.5 3 1

Append (cost=16.68..21.62 rows=2 width=216) (actual time=390.922..391.131 rows=3 loops=1)

83. 0.002 391.021 ↓ 2.0 2 1

Subquery Scan on y (cost=16.68..21.25 rows=1 width=216) (actual time=390.922..391.021 rows=2 loops=1)

84. 0.095 391.019 ↓ 2.0 2 1

WindowAgg (cost=16.68..21.24 rows=1 width=220) (actual time=390.921..391.019 rows=2 loops=1)

85.          

Initplan (forWindowAgg)

86. 0.485 0.485 ↑ 1.0 1 1

CTE Scan on production_in_json (cost=0.00..0.02 rows=1 width=32) (actual time=0.485..0.485 rows=1 loops=1)

87. 0.004 0.191 ↓ 2.0 2 1

WindowAgg (cost=16.66..16.69 rows=1 width=56) (actual time=0.189..0.191 rows=2 loops=1)

88. 0.009 0.187 ↓ 2.0 2 1

Sort (cost=16.66..16.67 rows=1 width=48) (actual time=0.186..0.187 rows=2 loops=1)

  • Sort Key: (CASE WHEN (ds.id = d.main_sensor_id) THEN 0 ELSE ds.id END)
  • Sort Method: quicksort Memory: 25kB
89. 0.004 0.178 ↓ 2.0 2 1

Nested Loop (cost=0.56..16.65 rows=1 width=48) (actual time=0.148..0.178 rows=2 loops=1)

90. 0.001 0.136 ↓ 2.0 2 1

Nested Loop (cost=0.28..8.35 rows=1 width=44) (actual time=0.110..0.136 rows=2 loops=1)

91. 0.129 0.129 ↓ 2.0 2 1

CTE Scan on devce_sensors ds (cost=0.00..0.04 rows=1 width=40) (actual time=0.106..0.129 rows=2 loops=1)

  • Filter: (hidden = 0)
92. 0.006 0.006 ↑ 1.0 1 2

Index Scan using uk_active_session_configurations_sensor_id on active_session_configurations ac (cost=0.28..8.30 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=2)

  • Index Cond: (sensor_id = ds.id)
93. 0.038 0.038 ↑ 1.0 1 2

Index Scan using "PK_C_DID" on devices d (cost=0.28..8.30 rows=1 width=8) (actual time=0.019..0.019 rows=1 loops=2)

  • Index Cond: (id = ds.device_id)
94.          

SubPlan (forWindowAgg)

95. 390.248 390.248 ↑ 1.0 1 2

CTE Scan on sensor_data sd_1 (cost=0.00..4.50 rows=1 width=32) (actual time=189.064..195.124 rows=1 loops=2)

  • Filter: (id = ds.id)
  • Rows Removed by Filter: 1
96. 0.001 0.109 ↑ 1.0 1 1

Subquery Scan on *SELECT* 2 (cost=0.34..0.36 rows=1 width=216) (actual time=0.109..0.109 rows=1 loops=1)

97. 0.004 0.108 ↑ 1.0 1 1

Result (cost=0.34..0.35 rows=1 width=208) (actual time=0.107..0.108 rows=1 loops=1)

98.          

Initplan (forResult)

99. 0.101 0.104 ↑ 1.0 1 1

Aggregate (cost=0.32..0.34 rows=1 width=32) (actual time=0.104..0.104 rows=1 loops=1)

100. 0.003 0.003 ↓ 1.5 12 1

CTE Scan on production p_1 (cost=0.00..0.16 rows=8 width=266) (actual time=0.000..0.003 rows=12 loops=1)

Planning time : 4.825 ms
Execution time : 393.875 ms