search

home-assistant / core

:house_with_garden: Open source home automation that puts local control and privacy first.
https://www.home-assistant.io
Apache License 2.0
73.29k stars 30.61k forks source link

Strange CPU usage after some day without reboot (HA virtual machine) #87851

Closed avbor closed 1 year ago

avbor commented 1 year ago

The problem

My HA instance running in KVM virtual machine, VM created with official qcow2 (Home Assistant OS). Home Assistant 2023.2.3 Supervisor 2023.01.1 Operating System 9.5 Frontend 20230202.0 Database: latest version of MariaDB AddOn, DB size ~1.8Gb

After some (2-3) days without HA reboot I observe a very strange floating CPU load (inside VM):

screens from HA: image

Perhaps the problem is somewhere in the database, but I do not have enough knowledge to understand where: image

from external zabbix: image image

After restarting HA (only core) everything goes back to normal for the next couple of days. image

(I don't understand why, but my previous problem was closed due to unsupported configuration - https://github.com/home-assistant/core/issues/87831)

What version of Home Assistant Core has the issue?

core-2023.2.3

What was the last working version of Home Assistant Core?

No response

What type of installation are you running?

Home Assistant OS

Integration causing the issue

No response

Link to integration documentation on our website

No response

Diagnostics information

No response

Example YAML snippet

No response

Anything in the logs that might be useful for us?

No response

Additional information

No response

bdraco commented 1 year ago

Its this query.. I still can't figure out what is triggering it but I can try to make it more efficient

https://github.com/home-assistant/core/blob/17e87556de46158920378b3c8291c155a80d5666/homeassistant/components/recorder/history.py#L607

bdraco commented 1 year ago

If we change the query to be 

SELECT max(states.last_updated_ts) AS max_last_updated_ts FROM states WHERE states.last_updated_ts >= 1676198403.071357e0 AND states.last_updated_ts < 1676296199.999999e0 AND states.entity_id IN ('sensor.average_carbon_dioxide_in_flat', 'sensor.average_pm25_in_flat', 'sensor.average_pm10_in_flat', 'sensor.average_temperature_in_flat', 'sensor.average_humidity_in_flat', 'sensor.average_voltage_in_flat', 'sensor.average_voltage_in_country_house', 'sensor.country_house_avg_temp_kids_rooms', 'sensor.country_house_avg_humidity_kids_rooms', 'sensor.kitchen_stove_temperature_min_max_bt', 'sensor.kitchen_stove_temperature_min_max_mean', 'sensor.kitchen_stove_temperature_median_min_max_mean', 'sensor.kitchen_stove_temperature_median_zb', 'sensor.kitchen_stove_temperature_median_bt', 'sensor.processor_use', 'sensor.memory_use_percent', 'sensor.memory_use', 'sensor.memory_free', 'sensor.energy_flat_main_power', 'sensor.country_house_living_room_dewpoint', 'sensor.country_house_kids_room_dewpoint', 'sensor.aleksandr_bmi', 'sensor.aleksandr_basal_metabolism', 'sensor.aleksandr_visceral_fat', 'sensor.aleksandr_weight', 'sensor.aleksandr_lean_body_mass', 'sensor.aleksandr_body_fat', 'sensor.aleksandr_protein', 'sensor.aleksandr_water', 'sensor.aleksandr_bone_mass', 'sensor.aleksandr_muscle_mass', 'sensor.aleksandr_metabolic_age', 'sensor.aleksandr_body_score', 'sensor.irina_bmi', 'sensor.irina_basal_metabolism', 'sensor.irina_visceral_fat', 'sensor.irina_weight', 'sensor.irina_lean_body_mass', 'sensor.irina_body_fat', 'sensor.irina_protein', 'sensor.irina_water', 'sensor.irina_bone_mass', 'sensor.irina_muscle_mass', 'sensor.irina_metabolic_age', 'sensor.irina_body_score', 'sensor.egor_bmi', 'sensor.egor_basal_metabolism', 'sensor.egor_visceral_fat', 'sensor.egor_weight', 'sensor.kirill_bmi', 'sensor.kirill_basal_metabolism', 'sensor.kirill_visceral_fat', 'sensor.kirill_weight', 'sensor.watchman_missing_entities', 'sensor.watchman_missing_services', 'sensor.ups_battery_charge', 'sensor.ups_battery_voltage', 'sensor.openweathermap_stupino_nivki_2_dew_point', 'sensor.openweathermap_stupino_nivki_2_temperature', 'sensor.openweathermap_stupino_nivki_2_feels_like_temperature', 'sensor.openweathermap_stupino_nivki_2_wind_speed', 'sensor.openweathermap_stupino_nivki_2_wind_bearing', 'sensor.openweathermap_stupino_nivki_2_humidity', 'sensor.openweathermap_stupino_nivki_2_pressure', 'sensor.openweathermap_stupino_nivki_2_cloud_coverage', 'sensor.openweathermap_stupino_nivki_2_rain', 'sensor.openweathermap_stupino_nivki_2_snow', 'sensor.openweathermap_stupino_nivki_2_uv_index', 'sensor.openweathermap_stupino_nivki_2_visibility', 'sensor.openweathermap_moskva_danilovskii_dew_point', 'sensor.openweathermap_moskva_danilovskii_temperature', 'sensor.openweathermap_moskva_danilovskii_feels_like_temperature', 'sensor.openweathermap_moskva_danilovskii_wind_speed', 'sensor.openweathermap_moskva_danilovskii_wind_bearing', 'sensor.openweathermap_moskva_danilovskii_humidity', 'sensor.openweathermap_moskva_danilovskii_pressure', 'sensor.openweathermap_moskva_danilovskii_cloud_coverage', 'sensor.openweathermap_moskva_danilovskii_rain', 'sensor.openweathermap_moskva_danilovskii_snow', 'sensor.openweathermap_moskva_danilovskii_uv_index', 'sensor.openweathermap_moskva_danilovskii_visibility', 'sensor.galaxy_tab_a_8_0_2019_battery', 'sensor.galaxy_tab_a_8_0_2019_internal_storage_free_space', 'sensor.galaxy_tab_a_8_0_2019_internal_storage_total_space', 'sensor.galaxy_tab_a_8_0_2019_free_memory', 'sensor.galaxy_tab_a_8_0_2019_total_memory', 'sensor.kb2003_uroven_zariada_akkumuliatora', 'sensor.kb2003_datchik_osveshchionnosti', 'sensor.kb2003_skorost_soedineniia_wifi', 'sensor.kb2003_chastota_wifi', 'sensor.kb2003_moshchnost_signala_wifi', 'sensor.kb2003_battery_temperature', 'sensor.kb2003_battery_power', 'sensor.sm_t295_hallway_battery_power', 'sensor.gosund_sp111_01_ampere', 'sensor.gosund_sp111_01_watt', 'sensor.gosund_sp111_05_ampere', 'sensor.gosund_sp111_05_watt', 'sensor.gosund_sp111_02_ampere', 'sensor.gosund_sp111_02_watt', 'sensor.gosund_sp111_06_ampere', 'sensor.gosund_sp111_06_watt', 'sensor.gosund_sp111_03_ampere', 'sensor.gosund_sp111_03_watt', 'sensor.gosund_sp111_08_ampere', 'sensor.gosund_sp111_08_watt', 'sensor.gosund_sp111_07_ampere', 'sensor.gosund_sp111_07_watt', 'sensor.gosund_sp111_04_ampere', 'sensor.gosund_sp111_04_watt', 'sensor.0x00158d000413b210_gas_density', 'sensor.tplink_smartplug_02_current_consumption', 'sensor.tplink_smartplug_02_voltage', 'sensor.tplink_smartplug_02_current', 'sensor.tplink_smartplug_01_current_consumption', 'sensor.tplink_smartplug_01_voltage', 'sensor.tplink_smartplug_01_current', 'sensor.gosund_sp111_09_ampere', 'sensor.gosund_sp111_09_watt', 'sensor.gosund_sp111_10_ampere', 'sensor.gosund_sp111_10_watt', 'sensor.esp32_miscale2_01_current_weight', 'sensor.esp32_miscale2_01_current_impedance', 'sensor.air_monitor_lite_cb3b_temperature', 'sensor.air_monitor_lite_cb3b_humidity', 'sensor.air_monitor_lite_cb3b_pm25', 'sensor.air_monitor_lite_cb3b_pm10', 'sensor.air_monitor_lite_cb3b_carbon_dioxide', 'sensor.air_monitor_lite_afb3_temperature', 'sensor.air_monitor_lite_afb3_humidity', 'sensor.air_monitor_lite_afb3_pm25', 'sensor.air_monitor_lite_afb3_pm10', 'sensor.air_monitor_lite_afb3_carbon_dioxide', 'sensor.air_monitor_lite_f6a7_temperature', 'sensor.air_monitor_lite_f6a7_humidity', 'sensor.air_monitor_lite_f6a7_pm25', 'sensor.air_monitor_lite_f6a7_pm10', 'sensor.air_monitor_lite_f6a7_carbon_dioxide', 'sensor.atc_03b4_temperature', 'sensor.atc_03b4_humidity', 'sensor.atc_03b4_battery', 'sensor.atc_3fc4_voltage', 'sensor.atc_4e9a_temperature', 'sensor.atc_4e9a_humidity', 'sensor.atc_4e9a_battery', 'sensor.atc_3d13_voltage', 'sensor.atc_bdae_temperature', 'sensor.atc_bdae_humidity', 'sensor.atc_bdae_battery', 'sensor.th_kukhnia_kholodilnik_niz_voltage', 'sensor.atc_84c8_voltage', 'sensor.th_kukhnia_za_oknom_temperature', 'sensor.th_kukhnia_za_oknom_humidity', 'sensor.th_kukhnia_za_oknom_battery', 'sensor.temperature_humidity_sensor_ac21_temperature', 'sensor.temperature_humidity_sensor_ac21_humidity', 'sensor.fda50693_a4e2_4fb1_afcf_c6eb07647825_10011_2_79ae_estimated_distance', 'sensor.fda50693_a4e2_4fb1_afcf_c6eb07647825_10011_1_2340_estimated_distance', 'sensor.ed5e33eb_0ca3_4263_a681_a68b1045a5e6_100_1_estimated_distance', 'sensor.0x158d00054859d5_battery', 'sensor.0x158d000802df91_temperature', 'sensor.0x158d000802df91_humidity', 'sensor.0x158d000802df91_battery', 'sensor.0x158d000802df91_pressure', 'sensor.0x158d00040e019e_temperature', 'sensor.0x158d00040e019e_humidity', 'sensor.0x158d00040e019e_battery', 'sensor.a4c1380b3a85_temperature', 'sensor.a4c1380b3a85_humidity', 'sensor.a4c1380b3a85_battery', 'sensor.a4c138c7ded2_temperature', 'sensor.a4c138c7ded2_humidity', 'sensor.a4c138c7ded2_battery', 'sensor.lumi_lumi_sensor_cube_power', 'sensor.lumi_lumi_sensor_cube_aqgl01_d92a2905_device_temperature', 'sensor.lumi_lumi_sen_ill_mgl01_power', 'sensor.lumi_lumi_sen_ill_mgl01_illuminance', 'sensor.ikea_of_sweden_tradfri_on_off_switch_46a690fe_power', 'sensor.ikea_of_sweden_tradfri_on_off_switch_06e49efe_power', 'sensor.adurolight_vms_adurolight_f604cd01_power', 'sensor.0x158d00039e2ecb_power', 'sensor.0x158d00039e2ecb_temperature', 'sensor.0x158d00039e2ecb_humidity', 'sensor.0x158d000309920a_battery', 'sensor.0x158d000309920a_illuminance', 'sensor.lumi_lumi_sensor_motion_aq2_0a920903_device_temperature', 'sensor.lumi_lumi_sensor_switch_b1115a04_power', 'sensor.lumi_lumi_sensor_switch_53115a04_power', 'sensor.lumi_lumi_sensor_swit_power', 'sensor.lumi_lumi_sensor_swit_8e46ab04_device_temperature', 'sensor.lumi_lumi_sensor_switch_31f65f03_power', 'sensor.lumi_lumi_sensor_motion_aq2_power', 'sensor.0x158d0003fac0ec_illuminance', 'sensor.lumi_lumi_sensor_motion_aq2_ecc0fa03_device_temperature', 'sensor.lumi_lumi_sens_power', 'sensor.0x158d0003931a55_temperature', 'sensor.0x158d0003931a55_humidity', 'sensor.lumi_lumi_sensor_switch_c59b5a03_power', 'sensor.0x158d0003230618_battery', 'sensor.0x158d0003230618_pressure', 'sensor.0x158d0003230618_temperature', 'sensor.0x158d0003230618_humidity', 'sensor.0x158d0003931b0e_battery', 'sensor.0x158d0003931b0e_temperature', 'sensor.0x158d0003931b0e_humidity', 'sensor.0x158d0003931aec_battery', 'sensor.0x158d0003931aec_temperature', 'sensor.0x158d0003931aec_humidity', 'sensor.lumi_lumi_sensor_switch_9efd3e03_power', 'sensor.lumi_lumi_sensor_motion_aq2_8eea4c04_power', 'sensor.0x158d00044cea8e_illuminance', 'sensor.lumi_lumi_sensor_motion_aq2_8eea4c04_device_temperature', 'sensor.lumi_lumi_sensor_magnet_power', 'sensor.lumi_lumi_sensor_magnet_673b0704_power', 'sensor.lumi_lumi_sensor_magnet_2547d503_power', 'sensor.lumi_lumi_sensor_magnet_74f99c03_power', 'sensor.lumi_th_kitchen_stove_power', 'sensor.lumi_th_kitchen_stove_temperature', 'sensor.lumi_th_kitchen_stove_humidity', 'sensor.lumi_lumi_sensor_magnet_50659603_power', 'sensor.blitzwolf_shp13_02_3ad702fe_electrical_measurement', 'sensor.blitzwolf_shp13_02_3ad702fe_electrical_measurement_rms_current', 'sensor.blitzwolf_shp13_02_3ad702fe_electrical_measurement_rms_voltage', 'sensor.tz3000_g5xawfcq_ts0121_3ad702fe_electrical_measurement_power_factor', 'sensor.lumi_lumi_sensor_switch_005dd103_power', 'sensor.lumi_lumi_sensor_motion_aq2_7b4e5f07_power', 'sensor.lumi_lumi_sensor_motion_aq2_7b4e5f07_illuminance', 'sensor.lumi_lumi_sensor_motion_aq2_7b4e5f07_device_temperature', 'sensor.lumi_lumi_sensor_magnet_724f4f04_power', 'sensor.lumi_lumi_sensor_switch_power', 'sensor.lumi_lumi_sensor_magnet_9bef9603_power', 'sensor.lumi_lumi_sensor_magnet_cd829803_power', 'sensor.lumi_lumi_sen_ill_mgl01_0d117d3c_power', 'sensor.0x4cf8cdf3c7d110d_illuminance', 'sensor.lumi_lumi_sensor_wleak_aq1_power', 'sensor.lumi_lumi_sensor_wleak_aq1_7cadd306_device_temperature', 'sensor.ikea_1_power', 'sensor.ikea_4_power', 'sensor.ikea_5_power', 'sensor.lumi_lumi_weather_power', 'sensor.lumi_lumi_weather_pressure', 'sensor.lumi_lumi_weather_temperature', 'sensor.lumi_lumi_weather_humidity', 'sensor.lumi_lumi_weather_4dad5507_power', 'sensor.lumi_lumi_weather_4dad5507_pressure', 'sensor.lumi_lumi_weather_4dad5507_temperature', 'sensor.lumi_lumi_weather_4dad5507_humidity', 'sensor.lumi_lumi_weather_bf387507_power', 'sensor.lumi_lumi_weather_bf387507_pressure', 'sensor.lumi_lumi_weather_bf387507_temperature', 'sensor.lumi_lumi_weather_bf387507_humidity', 'sensor.lumi_lumi_weather_824e9807_power', 'sensor.lumi_lumi_weather_824e9807_pressure', 'sensor.lumi_lumi_weather_824e9807_temperature', 'sensor.lumi_lumi_weather_824e9807_humidity', 'sensor.lumi_lumi_sensor_wleak_aq1_840d0304_power', 'sensor.lumi_lumi_sensor_wleak_aq1_840d0304_device_temperature', 'sensor.lumi_lumi_sensor_wleak_aq1_7de70004_power', 'sensor.lumi_lumi_sensor_wleak_aq1_7de70004_device_temperature', 'sensor.xiaomi_lumi_sen_ill_mgl01_battery', 'sensor.xiaomi_lumi_sen_ill_mgl01_illuminance', 'sensor.xiaomi_lumi_sen_ill_mgl01_battery_2', 'sensor.xiaomi_lumi_sen_ill_mgl01_illuminance_2', 'sensor.trust_battery', 'sensor.tz3000_g5xawfcq_ts0121_electricalmeasurement', 'sensor.tz3000_g5xawfcq_ts0121_electricalmeasurementrmscurrent', 'sensor.tz3000_g5xawfcq_ts0121_electricalmeasurementrmsvoltage', 'sensor.tz3000_g5xawfcq_ts0121_electricalmeasurementpowerfactor', 'sensor.blitzwolf_shp15_01_electricalmeasurement', 'sensor.blitzwolf_shp15_01_electricalmeasurementrmscurrent', 'sensor.blitzwolf_shp15_01_electricalmeasurementrmsvoltage', 'sensor.blitzwolf_shp15_01_electricalmeasurementpowerfactor', 'sensor.lumi_lumi_sensor_magnet_battery', 'sensor.lumi_lumi_sensor_magnet_devicetemperature', 'sensor.lumi_lumi_relay_c2acn01_electricalmeasurement', 'sensor.lumi_lumi_relay_c2acn01_electricalmeasurementrmsvoltage', 'sensor.lumi_lumi_relay_c2acn01_electricalmeasurementpowerfactor', 'sensor.lumi_lumi_relay_c2acn01_devicetemperature', 'sensor.tz3000_zloso4jk_ts011f_active_power', 'sensor.tz3000_zloso4jk_ts011f_rms_current', 'sensor.tz3000_zloso4jk_ts011f_rms_voltage', 'sensor.tz3000_zloso4jk_ts011f_power_factor', 'sensor.ikea_of_sweden_tradfri_on_off_switch_battery', 'sensor.atc_3fc4_temperature', 'sensor.atc_3fc4_humidity', 'sensor.atc_3fc4_battery', 'sensor.temperature_humidity_sensor_ac21_battery', 'sensor.temperature_humidity_sensor_ac21_voltage', 'sensor.a4c138d358e9_humidity', 'sensor.a4c138d358e9_temperature', 'sensor.a4c138d358e9_battery', 'sensor.a4c1384e2b64_humidity', 'sensor.a4c1384e2b64_temperature', 'sensor.a4c1384e2b64_battery', 'sensor.aleksandr_body_type', 'sensor.irina_body_type', 'sensor.atc_03b4_voltage', 'sensor.atc_4e9a_voltage', 'sensor.atc_3d13_temperature', 'sensor.atc_3d13_humidity', 'sensor.atc_3d13_battery', 'sensor.atc_bdae_voltage', 'sensor.atc_8716_temperature', 'sensor.atc_8716_humidity', 'sensor.atc_8716_battery', 'sensor.atc_84c8_temperature', 'sensor.atc_84c8_humidity', 'sensor.atc_84c8_battery', 'sensor.atc_7f83_voltage', 'sensor.e2c56db5_dffb_48d2_b060_d0f5a71096e0_0_0_estimated_distance', 'sensor.74278bda_b644_4520_8f0c_720eaf059935_0_124_377c_estimated_distance', 'sensor.400225dd_3d15_4a4b_9db3_93c4b2d01eda_0_0_08ff_estimated_distance', 'sensor.389fbc46_bfb4_4d29_a089_bb5b343d069c_100_1_f15f_estimated_distance', 'sensor.74278bda_b644_4520_8f0c_720eaf059935_0_30053_6896_estimated_distance', 'sensor.ikea_3_battery_2') GROUP BY states.entity_id

Its mostly optimized away

+------+-------------+--------+-------+---------------------------------------------------------------+-------------------------------------+---------+------+------+---------------------------------------+
| id   | select_type | table  | type  | possible_keys                                                 | key                                 | key_len | ref  | rows | Extra                                 |
+------+-------------+--------+-------+---------------------------------------------------------------+-------------------------------------+---------+------+------+---------------------------------------+
|    1 | SIMPLE      | states | range | ix_states_entity_id_last_updated_ts,ix_states_last_updated_ts | ix_states_entity_id_last_updated_ts | 1032    | NULL | 3736 | Using where; Using index for group-by |
+------+-------------+--------+-------+---------------------------------------------------------------+-------------------------------------+---------+------+------+---------------------------------------+
1 row in set (0.014 sec)
bdraco commented 1 year ago

The new full query would be


SELECT states.entity_id, states.state, states.last_changed_ts, states.last_updated_ts, states.attributes, state_attributes.shared_attrs FROM (SELECT max(states.last_updated_ts) AS max_last_updated_ts, entity_id FROM states WHERE states.last_updated_ts >= 1676198403.071357e0 AND states.last_updated_ts < 1676296199.999999e0 AND states.entity_id IN ('sensor.average_carbon_dioxide_in_flat', 'sensor.average_pm25_in_flat', 'sensor.average_pm10_in_flat', 'sensor.average_temperature_in_flat', 'sensor.average_humidity_in_flat', 'sensor.average_voltage_in_flat', 'sensor.average_voltage_in_country_house', 'sensor.country_house_avg_temp_kids_rooms', 'sensor.country_house_avg_humidity_kids_rooms', 'sensor.kitchen_stove_temperature_min_max_bt', 'sensor.kitchen_stove_temperature_min_max_mean', 'sensor.kitchen_stove_temperature_median_min_max_mean', 'sensor.kitchen_stove_temperature_median_zb', 'sensor.kitchen_stove_temperature_median_bt', 'sensor.processor_use', 'sensor.memory_use_percent', 'sensor.memory_use', 'sensor.memory_free', 'sensor.energy_flat_main_power', 'sensor.country_house_living_room_dewpoint', 'sensor.country_house_kids_room_dewpoint', 'sensor.aleksandr_bmi', 'sensor.aleksandr_basal_metabolism', 'sensor.aleksandr_visceral_fat', 'sensor.aleksandr_weight', 'sensor.aleksandr_lean_body_mass', 'sensor.aleksandr_body_fat', 'sensor.aleksandr_protein', 'sensor.aleksandr_water', 'sensor.aleksandr_bone_mass', 'sensor.aleksandr_muscle_mass', 'sensor.aleksandr_metabolic_age', 'sensor.aleksandr_body_score', 'sensor.irina_bmi', 'sensor.irina_basal_metabolism', 'sensor.irina_visceral_fat', 'sensor.irina_weight', 'sensor.irina_lean_body_mass', 'sensor.irina_body_fat', 'sensor.irina_protein', 'sensor.irina_water', 'sensor.irina_bone_mass', 'sensor.irina_muscle_mass', 'sensor.irina_metabolic_age', 'sensor.irina_body_score', 'sensor.egor_bmi', 'sensor.egor_basal_metabolism', 'sensor.egor_visceral_fat', 'sensor.egor_weight', 'sensor.kirill_bmi', 'sensor.kirill_basal_metabolism', 'sensor.kirill_visceral_fat', 'sensor.kirill_weight', 'sensor.watchman_missing_entities', 'sensor.watchman_missing_services', 'sensor.ups_battery_charge', 'sensor.ups_battery_voltage', 'sensor.openweathermap_stupino_nivki_2_dew_point', 'sensor.openweathermap_stupino_nivki_2_temperature', 'sensor.openweathermap_stupino_nivki_2_feels_like_temperature', 'sensor.openweathermap_stupino_nivki_2_wind_speed', 'sensor.openweathermap_stupino_nivki_2_wind_bearing', 'sensor.openweathermap_stupino_nivki_2_humidity', 'sensor.openweathermap_stupino_nivki_2_pressure', 'sensor.openweathermap_stupino_nivki_2_cloud_coverage', 'sensor.openweathermap_stupino_nivki_2_rain', 'sensor.openweathermap_stupino_nivki_2_snow', 'sensor.openweathermap_stupino_nivki_2_uv_index', 'sensor.openweathermap_stupino_nivki_2_visibility', 'sensor.openweathermap_moskva_danilovskii_dew_point', 'sensor.openweathermap_moskva_danilovskii_temperature', 'sensor.openweathermap_moskva_danilovskii_feels_like_temperature', 'sensor.openweathermap_moskva_danilovskii_wind_speed', 'sensor.openweathermap_moskva_danilovskii_wind_bearing', 'sensor.openweathermap_moskva_danilovskii_humidity', 'sensor.openweathermap_moskva_danilovskii_pressure', 'sensor.openweathermap_moskva_danilovskii_cloud_coverage', 'sensor.openweathermap_moskva_danilovskii_rain', 'sensor.openweathermap_moskva_danilovskii_snow', 'sensor.openweathermap_moskva_danilovskii_uv_index', 'sensor.openweathermap_moskva_danilovskii_visibility', 'sensor.galaxy_tab_a_8_0_2019_battery', 'sensor.galaxy_tab_a_8_0_2019_internal_storage_free_space', 'sensor.galaxy_tab_a_8_0_2019_internal_storage_total_space', 'sensor.galaxy_tab_a_8_0_2019_free_memory', 'sensor.galaxy_tab_a_8_0_2019_total_memory', 'sensor.kb2003_uroven_zariada_akkumuliatora', 'sensor.kb2003_datchik_osveshchionnosti', 'sensor.kb2003_skorost_soedineniia_wifi', 'sensor.kb2003_chastota_wifi', 'sensor.kb2003_moshchnost_signala_wifi', 'sensor.kb2003_battery_temperature', 'sensor.kb2003_battery_power', 'sensor.sm_t295_hallway_battery_power', 'sensor.gosund_sp111_01_ampere', 'sensor.gosund_sp111_01_watt', 'sensor.gosund_sp111_05_ampere', 'sensor.gosund_sp111_05_watt', 'sensor.gosund_sp111_02_ampere', 'sensor.gosund_sp111_02_watt', 'sensor.gosund_sp111_06_ampere', 'sensor.gosund_sp111_06_watt', 'sensor.gosund_sp111_03_ampere', 'sensor.gosund_sp111_03_watt', 'sensor.gosund_sp111_08_ampere', 'sensor.gosund_sp111_08_watt', 'sensor.gosund_sp111_07_ampere', 'sensor.gosund_sp111_07_watt', 'sensor.gosund_sp111_04_ampere', 'sensor.gosund_sp111_04_watt', 'sensor.0x00158d000413b210_gas_density', 'sensor.tplink_smartplug_02_current_consumption', 'sensor.tplink_smartplug_02_voltage', 'sensor.tplink_smartplug_02_current', 'sensor.tplink_smartplug_01_current_consumption', 'sensor.tplink_smartplug_01_voltage', 'sensor.tplink_smartplug_01_current', 'sensor.gosund_sp111_09_ampere', 'sensor.gosund_sp111_09_watt', 'sensor.gosund_sp111_10_ampere', 'sensor.gosund_sp111_10_watt', 'sensor.esp32_miscale2_01_current_weight', 'sensor.esp32_miscale2_01_current_impedance', 'sensor.air_monitor_lite_cb3b_temperature', 'sensor.air_monitor_lite_cb3b_humidity', 'sensor.air_monitor_lite_cb3b_pm25', 'sensor.air_monitor_lite_cb3b_pm10', 'sensor.air_monitor_lite_cb3b_carbon_dioxide', 'sensor.air_monitor_lite_afb3_temperature', 'sensor.air_monitor_lite_afb3_humidity', 'sensor.air_monitor_lite_afb3_pm25', 'sensor.air_monitor_lite_afb3_pm10', 'sensor.air_monitor_lite_afb3_carbon_dioxide', 'sensor.air_monitor_lite_f6a7_temperature', 'sensor.air_monitor_lite_f6a7_humidity', 'sensor.air_monitor_lite_f6a7_pm25', 'sensor.air_monitor_lite_f6a7_pm10', 'sensor.air_monitor_lite_f6a7_carbon_dioxide', 'sensor.atc_03b4_temperature', 'sensor.atc_03b4_humidity', 'sensor.atc_03b4_battery', 'sensor.atc_3fc4_voltage', 'sensor.atc_4e9a_temperature', 'sensor.atc_4e9a_humidity', 'sensor.atc_4e9a_battery', 'sensor.atc_3d13_voltage', 'sensor.atc_bdae_temperature', 'sensor.atc_bdae_humidity', 'sensor.atc_bdae_battery', 'sensor.th_kukhnia_kholodilnik_niz_voltage', 'sensor.atc_84c8_voltage', 'sensor.th_kukhnia_za_oknom_temperature', 'sensor.th_kukhnia_za_oknom_humidity', 'sensor.th_kukhnia_za_oknom_battery', 'sensor.temperature_humidity_sensor_ac21_temperature', 'sensor.temperature_humidity_sensor_ac21_humidity', 'sensor.fda50693_a4e2_4fb1_afcf_c6eb07647825_10011_2_79ae_estimated_distance', 'sensor.fda50693_a4e2_4fb1_afcf_c6eb07647825_10011_1_2340_estimated_distance', 'sensor.ed5e33eb_0ca3_4263_a681_a68b1045a5e6_100_1_estimated_distance', 'sensor.0x158d00054859d5_battery', 'sensor.0x158d000802df91_temperature', 'sensor.0x158d000802df91_humidity', 'sensor.0x158d000802df91_battery', 'sensor.0x158d000802df91_pressure', 'sensor.0x158d00040e019e_temperature', 'sensor.0x158d00040e019e_humidity', 'sensor.0x158d00040e019e_battery', 'sensor.a4c1380b3a85_temperature', 'sensor.a4c1380b3a85_humidity', 'sensor.a4c1380b3a85_battery', 'sensor.a4c138c7ded2_temperature', 'sensor.a4c138c7ded2_humidity', 'sensor.a4c138c7ded2_battery', 'sensor.lumi_lumi_sensor_cube_power', 'sensor.lumi_lumi_sensor_cube_aqgl01_d92a2905_device_temperature', 'sensor.lumi_lumi_sen_ill_mgl01_power', 'sensor.lumi_lumi_sen_ill_mgl01_illuminance', 'sensor.ikea_of_sweden_tradfri_on_off_switch_46a690fe_power', 'sensor.ikea_of_sweden_tradfri_on_off_switch_06e49efe_power', 'sensor.adurolight_vms_adurolight_f604cd01_power', 'sensor.0x158d00039e2ecb_power', 'sensor.0x158d00039e2ecb_temperature', 'sensor.0x158d00039e2ecb_humidity', 'sensor.0x158d000309920a_battery', 'sensor.0x158d000309920a_illuminance', 'sensor.lumi_lumi_sensor_motion_aq2_0a920903_device_temperature', 'sensor.lumi_lumi_sensor_switch_b1115a04_power', 'sensor.lumi_lumi_sensor_switch_53115a04_power', 'sensor.lumi_lumi_sensor_swit_power', 'sensor.lumi_lumi_sensor_swit_8e46ab04_device_temperature', 'sensor.lumi_lumi_sensor_switch_31f65f03_power', 'sensor.lumi_lumi_sensor_motion_aq2_power', 'sensor.0x158d0003fac0ec_illuminance', 'sensor.lumi_lumi_sensor_motion_aq2_ecc0fa03_device_temperature', 'sensor.lumi_lumi_sens_power', 'sensor.0x158d0003931a55_temperature', 'sensor.0x158d0003931a55_humidity', 'sensor.lumi_lumi_sensor_switch_c59b5a03_power', 'sensor.0x158d0003230618_battery', 'sensor.0x158d0003230618_pressure', 'sensor.0x158d0003230618_temperature', 'sensor.0x158d0003230618_humidity', 'sensor.0x158d0003931b0e_battery', 'sensor.0x158d0003931b0e_temperature', 'sensor.0x158d0003931b0e_humidity', 'sensor.0x158d0003931aec_battery', 'sensor.0x158d0003931aec_temperature', 'sensor.0x158d0003931aec_humidity', 'sensor.lumi_lumi_sensor_switch_9efd3e03_power', 'sensor.lumi_lumi_sensor_motion_aq2_8eea4c04_power', 'sensor.0x158d00044cea8e_illuminance', 'sensor.lumi_lumi_sensor_motion_aq2_8eea4c04_device_temperature', 'sensor.lumi_lumi_sensor_magnet_power', 'sensor.lumi_lumi_sensor_magnet_673b0704_power', 'sensor.lumi_lumi_sensor_magnet_2547d503_power', 'sensor.lumi_lumi_sensor_magnet_74f99c03_power', 'sensor.lumi_th_kitchen_stove_power', 'sensor.lumi_th_kitchen_stove_temperature', 'sensor.lumi_th_kitchen_stove_humidity', 'sensor.lumi_lumi_sensor_magnet_50659603_power', 'sensor.blitzwolf_shp13_02_3ad702fe_electrical_measurement', 'sensor.blitzwolf_shp13_02_3ad702fe_electrical_measurement_rms_current', 'sensor.blitzwolf_shp13_02_3ad702fe_electrical_measurement_rms_voltage', 'sensor.tz3000_g5xawfcq_ts0121_3ad702fe_electrical_measurement_power_factor', 'sensor.lumi_lumi_sensor_switch_005dd103_power', 'sensor.lumi_lumi_sensor_motion_aq2_7b4e5f07_power', 'sensor.lumi_lumi_sensor_motion_aq2_7b4e5f07_illuminance', 'sensor.lumi_lumi_sensor_motion_aq2_7b4e5f07_device_temperature', 'sensor.lumi_lumi_sensor_magnet_724f4f04_power', 'sensor.lumi_lumi_sensor_switch_power', 'sensor.lumi_lumi_sensor_magnet_9bef9603_power', 'sensor.lumi_lumi_sensor_magnet_cd829803_power', 'sensor.lumi_lumi_sen_ill_mgl01_0d117d3c_power', 'sensor.0x4cf8cdf3c7d110d_illuminance', 'sensor.lumi_lumi_sensor_wleak_aq1_power', 'sensor.lumi_lumi_sensor_wleak_aq1_7cadd306_device_temperature', 'sensor.ikea_1_power', 'sensor.ikea_4_power', 'sensor.ikea_5_power', 'sensor.lumi_lumi_weather_power', 'sensor.lumi_lumi_weather_pressure', 'sensor.lumi_lumi_weather_temperature', 'sensor.lumi_lumi_weather_humidity', 'sensor.lumi_lumi_weather_4dad5507_power', 'sensor.lumi_lumi_weather_4dad5507_pressure', 'sensor.lumi_lumi_weather_4dad5507_temperature', 'sensor.lumi_lumi_weather_4dad5507_humidity', 'sensor.lumi_lumi_weather_bf387507_power', 'sensor.lumi_lumi_weather_bf387507_pressure', 'sensor.lumi_lumi_weather_bf387507_temperature', 'sensor.lumi_lumi_weather_bf387507_humidity', 'sensor.lumi_lumi_weather_824e9807_power', 'sensor.lumi_lumi_weather_824e9807_pressure', 'sensor.lumi_lumi_weather_824e9807_temperature', 'sensor.lumi_lumi_weather_824e9807_humidity', 'sensor.lumi_lumi_sensor_wleak_aq1_840d0304_power', 'sensor.lumi_lumi_sensor_wleak_aq1_840d0304_device_temperature', 'sensor.lumi_lumi_sensor_wleak_aq1_7de70004_power', 'sensor.lumi_lumi_sensor_wleak_aq1_7de70004_device_temperature', 'sensor.xiaomi_lumi_sen_ill_mgl01_battery', 'sensor.xiaomi_lumi_sen_ill_mgl01_illuminance', 'sensor.xiaomi_lumi_sen_ill_mgl01_battery_2', 'sensor.xiaomi_lumi_sen_ill_mgl01_illuminance_2', 'sensor.trust_battery', 'sensor.tz3000_g5xawfcq_ts0121_electricalmeasurement', 'sensor.tz3000_g5xawfcq_ts0121_electricalmeasurementrmscurrent', 'sensor.tz3000_g5xawfcq_ts0121_electricalmeasurementrmsvoltage', 'sensor.tz3000_g5xawfcq_ts0121_electricalmeasurementpowerfactor', 'sensor.blitzwolf_shp15_01_electricalmeasurement', 'sensor.blitzwolf_shp15_01_electricalmeasurementrmscurrent', 'sensor.blitzwolf_shp15_01_electricalmeasurementrmsvoltage', 'sensor.blitzwolf_shp15_01_electricalmeasurementpowerfactor', 'sensor.lumi_lumi_sensor_magnet_battery', 'sensor.lumi_lumi_sensor_magnet_devicetemperature', 'sensor.lumi_lumi_relay_c2acn01_electricalmeasurement', 'sensor.lumi_lumi_relay_c2acn01_electricalmeasurementrmsvoltage', 'sensor.lumi_lumi_relay_c2acn01_electricalmeasurementpowerfactor', 'sensor.lumi_lumi_relay_c2acn01_devicetemperature', 'sensor.tz3000_zloso4jk_ts011f_active_power', 'sensor.tz3000_zloso4jk_ts011f_rms_current', 'sensor.tz3000_zloso4jk_ts011f_rms_voltage', 'sensor.tz3000_zloso4jk_ts011f_power_factor', 'sensor.ikea_of_sweden_tradfri_on_off_switch_battery', 'sensor.atc_3fc4_temperature', 'sensor.atc_3fc4_humidity', 'sensor.atc_3fc4_battery', 'sensor.temperature_humidity_sensor_ac21_battery', 'sensor.temperature_humidity_sensor_ac21_voltage', 'sensor.a4c138d358e9_humidity', 'sensor.a4c138d358e9_temperature', 'sensor.a4c138d358e9_battery', 'sensor.a4c1384e2b64_humidity', 'sensor.a4c1384e2b64_temperature', 'sensor.a4c1384e2b64_battery', 'sensor.aleksandr_body_type', 'sensor.irina_body_type', 'sensor.atc_03b4_voltage', 'sensor.atc_4e9a_voltage', 'sensor.atc_3d13_temperature', 'sensor.atc_3d13_humidity', 'sensor.atc_3d13_battery', 'sensor.atc_bdae_voltage', 'sensor.atc_8716_temperature', 'sensor.atc_8716_humidity', 'sensor.atc_8716_battery', 'sensor.atc_84c8_temperature', 'sensor.atc_84c8_humidity', 'sensor.atc_84c8_battery', 'sensor.atc_7f83_voltage', 'sensor.e2c56db5_dffb_48d2_b060_d0f5a71096e0_0_0_estimated_distance', 'sensor.74278bda_b644_4520_8f0c_720eaf059935_0_124_377c_estimated_distance', 'sensor.400225dd_3d15_4a4b_9db3_93c4b2d01eda_0_0_08ff_estimated_distance', 'sensor.389fbc46_bfb4_4d29_a089_bb5b343d069c_100_1_f15f_estimated_distance', 'sensor.74278bda_b644_4520_8f0c_720eaf059935_0_30053_6896_estimated_distance', 'sensor.ikea_3_battery_2') GROUP BY states.entity_id) AS anon_1, states LEFT OUTER JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id WHERE states.last_updated_ts = anon_1.max_last_updated_ts and states.entity_id=anon_1.entity_id;

+------+-------------+------------------+--------+---------------------------------------------------------------+-------------------------------------+---------+---------------------------------+------+------------------------------------------------------------------------+
| id   | select_type | table            | type   | possible_keys                                                 | key                                 | key_len | ref                             | rows | Extra                                                                  |
+------+-------------+------------------+--------+---------------------------------------------------------------+-------------------------------------+---------+---------------------------------+------+------------------------------------------------------------------------+
|    1 | PRIMARY     | <derived2>       | ALL    | NULL                                                          | NULL                                | NULL    | NULL                            | 3735 | Using where                                                            |
|    1 | PRIMARY     | states           | ref    | ix_states_entity_id_last_updated_ts,ix_states_last_updated_ts | ix_states_last_updated_ts           | 9       | anon_1.max_last_updated_ts      | 1    | Using where                                                            |
|    1 | PRIMARY     | state_attributes | eq_ref | PRIMARY                                                       | PRIMARY                             | 4       | issue87851.states.attributes_id | 1    | Using where                                                            |
|    2 | DERIVED     | states           | range  | ix_states_entity_id_last_updated_ts,ix_states_last_updated_ts | ix_states_entity_id_last_updated_ts | 1032    | NULL                            | 3736 | Using where; Using index for group-by; Using temporary; Using filesort |
+------+-------------+------------------+--------+---------------------------------------------------------------+-------------------------------------+---------+---------------------------------+------+------------------------------------------------------------------------+
4 rows in set (0.007 sec)
bdraco commented 1 year ago

3736 is way better than 638778 rows in the original query but we end up with a temp table and a file sort... but is still small so still much faster old


SELECT states.entity_id, states.state, states.last_changed_ts, states.last_updated_ts, states.attributes, state_attributes.shared_attrs FROM (SELECT max(states.state_id) AS max_state_id FROM states WHERE states.last_updated_ts >= 1676198403.071357e0 AND states.last_updated_ts < 1676296199.999999e0 AND states.entity_id IN ('sensor.average_carbon_dioxide_in_flat', 'sensor.average_pm25_in_flat', 'sensor.average_pm10_in_flat', 'sensor.average_temperature_in_flat', 'sensor.average_humidity_in_flat', 'sensor.average_voltage_in_flat', 'sensor.average_voltage_in_country_house', 'sensor.country_house_avg_temp_kids_rooms', 'sensor.country_house_avg_humidity_kids_rooms', 'sensor.kitchen_stove_temperature_min_max_bt', 'sensor.kitchen_stove_temperature_min_max_mean', 'sensor.kitchen_stove_temperature_median_min_max_mean', 'sensor.kitchen_stove_temperature_median_zb', 'sensor.kitchen_stove_temperature_median_bt', 'sensor.processor_use', 'sensor.memory_use_percent', 'sensor.memory_use', 'sensor.memory_free', 'sensor.energy_flat_main_power', 'sensor.country_house_living_room_dewpoint', 'sensor.country_house_kids_room_dewpoint', 'sensor.aleksandr_bmi', 'sensor.aleksandr_basal_metabolism', 'sensor.aleksandr_visceral_fat', 'sensor.aleksandr_weight', 'sensor.aleksandr_lean_body_mass', 'sensor.aleksandr_body_fat', 'sensor.aleksandr_protein', 'sensor.aleksandr_water', 'sensor.aleksandr_bone_mass', 'sensor.aleksandr_muscle_mass', 'sensor.aleksandr_metabolic_age', 'sensor.aleksandr_body_score', 'sensor.irina_bmi', 'sensor.irina_basal_metabolism', 'sensor.irina_visceral_fat', 'sensor.irina_weight', 'sensor.irina_lean_body_mass', 'sensor.irina_body_fat', 'sensor.irina_protein', 'sensor.irina_water', 'sensor.irina_bone_mass', 'sensor.irina_muscle_mass', 'sensor.irina_metabolic_age', 'sensor.irina_body_score', 'sensor.egor_bmi', 'sensor.egor_basal_metabolism', 'sensor.egor_visceral_fat', 'sensor.egor_weight', 'sensor.kirill_bmi', 'sensor.kirill_basal_metabolism', 'sensor.kirill_visceral_fat', 'sensor.kirill_weight', 'sensor.watchman_missing_entities', 'sensor.watchman_missing_services', 'sensor.ups_battery_charge', 'sensor.ups_battery_voltage', 'sensor.openweathermap_stupino_nivki_2_dew_point', 'sensor.openweathermap_stupino_nivki_2_temperature', 'sensor.openweathermap_stupino_nivki_2_feels_like_temperature', 'sensor.openweathermap_stupino_nivki_2_wind_speed', 'sensor.openweathermap_stupino_nivki_2_wind_bearing', 'sensor.openweathermap_stupino_nivki_2_humidity', 'sensor.openweathermap_stupino_nivki_2_pressure', 'sensor.openweathermap_stupino_nivki_2_cloud_coverage', 'sensor.openweathermap_stupino_nivki_2_rain', 'sensor.openweathermap_stupino_nivki_2_snow', 'sensor.openweathermap_stupino_nivki_2_uv_index', 'sensor.openweathermap_stupino_nivki_2_visibility', 'sensor.openweathermap_moskva_danilovskii_dew_point', 'sensor.openweathermap_moskva_danilovskii_temperature', 'sensor.openweathermap_moskva_danilovskii_feels_like_temperature', 'sensor.openweathermap_moskva_danilovskii_wind_speed', 'sensor.openweathermap_moskva_danilovskii_wind_bearing', 'sensor.openweathermap_moskva_danilovskii_humidity', 'sensor.openweathermap_moskva_danilovskii_pressure', 'sensor.openweathermap_moskva_danilovskii_cloud_coverage', 'sensor.openweathermap_moskva_danilovskii_rain', 'sensor.openweathermap_moskva_danilovskii_snow', 'sensor.openweathermap_moskva_danilovskii_uv_index', 'sensor.openweathermap_moskva_danilovskii_visibility', 'sensor.galaxy_tab_a_8_0_2019_battery', 'sensor.galaxy_tab_a_8_0_2019_internal_storage_free_space', 'sensor.galaxy_tab_a_8_0_2019_internal_storage_total_space', 'sensor.galaxy_tab_a_8_0_2019_free_memory', 'sensor.galaxy_tab_a_8_0_2019_total_memory', 'sensor.kb2003_uroven_zariada_akkumuliatora', 'sensor.kb2003_datchik_osveshchionnosti', 'sensor.kb2003_skorost_soedineniia_wifi', 'sensor.kb2003_chastota_wifi', 'sensor.kb2003_moshchnost_signala_wifi', 'sensor.kb2003_battery_temperature', 'sensor.kb2003_battery_power', 'sensor.sm_t295_hallway_battery_power', 'sensor.gosund_sp111_01_ampere', 'sensor.gosund_sp111_01_watt', 'sensor.gosund_sp111_05_ampere', 'sensor.gosund_sp111_05_watt', 'sensor.gosund_sp111_02_ampere', 'sensor.gosund_sp111_02_watt', 'sensor.gosund_sp111_06_ampere', 'sensor.gosund_sp111_06_watt', 'sensor.gosund_sp111_03_ampere', 'sensor.gosund_sp111_03_watt', 'sensor.gosund_sp111_08_ampere', 'sensor.gosund_sp111_08_watt', 'sensor.gosund_sp111_07_ampere', 'sensor.gosund_sp111_07_watt', 'sensor.gosund_sp111_04_ampere', 'sensor.gosund_sp111_04_watt', 'sensor.0x00158d000413b210_gas_density', 'sensor.tplink_smartplug_02_current_consumption', 'sensor.tplink_smartplug_02_voltage', 'sensor.tplink_smartplug_02_current', 'sensor.tplink_smartplug_01_current_consumption', 'sensor.tplink_smartplug_01_voltage', 'sensor.tplink_smartplug_01_current', 'sensor.gosund_sp111_09_ampere', 'sensor.gosund_sp111_09_watt', 'sensor.gosund_sp111_10_ampere', 'sensor.gosund_sp111_10_watt', 'sensor.esp32_miscale2_01_current_weight', 'sensor.esp32_miscale2_01_current_impedance', 'sensor.air_monitor_lite_cb3b_temperature', 'sensor.air_monitor_lite_cb3b_humidity', 'sensor.air_monitor_lite_cb3b_pm25', 'sensor.air_monitor_lite_cb3b_pm10', 'sensor.air_monitor_lite_cb3b_carbon_dioxide', 'sensor.air_monitor_lite_afb3_temperature', 'sensor.air_monitor_lite_afb3_humidity', 'sensor.air_monitor_lite_afb3_pm25', 'sensor.air_monitor_lite_afb3_pm10', 'sensor.air_monitor_lite_afb3_carbon_dioxide', 'sensor.air_monitor_lite_f6a7_temperature', 'sensor.air_monitor_lite_f6a7_humidity', 'sensor.air_monitor_lite_f6a7_pm25', 'sensor.air_monitor_lite_f6a7_pm10', 'sensor.air_monitor_lite_f6a7_carbon_dioxide', 'sensor.atc_03b4_temperature', 'sensor.atc_03b4_humidity', 'sensor.atc_03b4_battery', 'sensor.atc_3fc4_voltage', 'sensor.atc_4e9a_temperature', 'sensor.atc_4e9a_humidity', 'sensor.atc_4e9a_battery', 'sensor.atc_3d13_voltage', 'sensor.atc_bdae_temperature', 'sensor.atc_bdae_humidity', 'sensor.atc_bdae_battery', 'sensor.th_kukhnia_kholodilnik_niz_voltage', 'sensor.atc_84c8_voltage', 'sensor.th_kukhnia_za_oknom_temperature', 'sensor.th_kukhnia_za_oknom_humidity', 'sensor.th_kukhnia_za_oknom_battery', 'sensor.temperature_humidity_sensor_ac21_temperature', 'sensor.temperature_humidity_sensor_ac21_humidity', 'sensor.fda50693_a4e2_4fb1_afcf_c6eb07647825_10011_2_79ae_estimated_distance', 'sensor.fda50693_a4e2_4fb1_afcf_c6eb07647825_10011_1_2340_estimated_distance', 'sensor.ed5e33eb_0ca3_4263_a681_a68b1045a5e6_100_1_estimated_distance', 'sensor.0x158d00054859d5_battery', 'sensor.0x158d000802df91_temperature', 'sensor.0x158d000802df91_humidity', 'sensor.0x158d000802df91_battery', 'sensor.0x158d000802df91_pressure', 'sensor.0x158d00040e019e_temperature', 'sensor.0x158d00040e019e_humidity', 'sensor.0x158d00040e019e_battery', 'sensor.a4c1380b3a85_temperature', 'sensor.a4c1380b3a85_humidity', 'sensor.a4c1380b3a85_battery', 'sensor.a4c138c7ded2_temperature', 'sensor.a4c138c7ded2_humidity', 'sensor.a4c138c7ded2_battery', 'sensor.lumi_lumi_sensor_cube_power', 'sensor.lumi_lumi_sensor_cube_aqgl01_d92a2905_device_temperature', 'sensor.lumi_lumi_sen_ill_mgl01_power', 'sensor.lumi_lumi_sen_ill_mgl01_illuminance', 'sensor.ikea_of_sweden_tradfri_on_off_switch_46a690fe_power', 'sensor.ikea_of_sweden_tradfri_on_off_switch_06e49efe_power', 'sensor.adurolight_vms_adurolight_f604cd01_power', 'sensor.0x158d00039e2ecb_power', 'sensor.0x158d00039e2ecb_temperature', 'sensor.0x158d00039e2ecb_humidity', 'sensor.0x158d000309920a_battery', 'sensor.0x158d000309920a_illuminance', 'sensor.lumi_lumi_sensor_motion_aq2_0a920903_device_temperature', 'sensor.lumi_lumi_sensor_switch_b1115a04_power', 'sensor.lumi_lumi_sensor_switch_53115a04_power', 'sensor.lumi_lumi_sensor_swit_power', 'sensor.lumi_lumi_sensor_swit_8e46ab04_device_temperature', 'sensor.lumi_lumi_sensor_switch_31f65f03_power', 'sensor.lumi_lumi_sensor_motion_aq2_power', 'sensor.0x158d0003fac0ec_illuminance', 'sensor.lumi_lumi_sensor_motion_aq2_ecc0fa03_device_temperature', 'sensor.lumi_lumi_sens_power', 'sensor.0x158d0003931a55_temperature', 'sensor.0x158d0003931a55_humidity', 'sensor.lumi_lumi_sensor_switch_c59b5a03_power', 'sensor.0x158d0003230618_battery', 'sensor.0x158d0003230618_pressure', 'sensor.0x158d0003230618_temperature', 'sensor.0x158d0003230618_humidity', 'sensor.0x158d0003931b0e_battery', 'sensor.0x158d0003931b0e_temperature', 'sensor.0x158d0003931b0e_humidity', 'sensor.0x158d0003931aec_battery', 'sensor.0x158d0003931aec_temperature', 'sensor.0x158d0003931aec_humidity', 'sensor.lumi_lumi_sensor_switch_9efd3e03_power', 'sensor.lumi_lumi_sensor_motion_aq2_8eea4c04_power', 'sensor.0x158d00044cea8e_illuminance', 'sensor.lumi_lumi_sensor_motion_aq2_8eea4c04_device_temperature', 'sensor.lumi_lumi_sensor_magnet_power', 'sensor.lumi_lumi_sensor_magnet_673b0704_power', 'sensor.lumi_lumi_sensor_magnet_2547d503_power', 'sensor.lumi_lumi_sensor_magnet_74f99c03_power', 'sensor.lumi_th_kitchen_stove_power', 'sensor.lumi_th_kitchen_stove_temperature', 'sensor.lumi_th_kitchen_stove_humidity', 'sensor.lumi_lumi_sensor_magnet_50659603_power', 'sensor.blitzwolf_shp13_02_3ad702fe_electrical_measurement', 'sensor.blitzwolf_shp13_02_3ad702fe_electrical_measurement_rms_current', 'sensor.blitzwolf_shp13_02_3ad702fe_electrical_measurement_rms_voltage', 'sensor.tz3000_g5xawfcq_ts0121_3ad702fe_electrical_measurement_power_factor', 'sensor.lumi_lumi_sensor_switch_005dd103_power', 'sensor.lumi_lumi_sensor_motion_aq2_7b4e5f07_power', 'sensor.lumi_lumi_sensor_motion_aq2_7b4e5f07_illuminance', 'sensor.lumi_lumi_sensor_motion_aq2_7b4e5f07_device_temperature', 'sensor.lumi_lumi_sensor_magnet_724f4f04_power', 'sensor.lumi_lumi_sensor_switch_power', 'sensor.lumi_lumi_sensor_magnet_9bef9603_power', 'sensor.lumi_lumi_sensor_magnet_cd829803_power', 'sensor.lumi_lumi_sen_ill_mgl01_0d117d3c_power', 'sensor.0x4cf8cdf3c7d110d_illuminance', 'sensor.lumi_lumi_sensor_wleak_aq1_power', 'sensor.lumi_lumi_sensor_wleak_aq1_7cadd306_device_temperature', 'sensor.ikea_1_power', 'sensor.ikea_4_power', 'sensor.ikea_5_power', 'sensor.lumi_lumi_weather_power', 'sensor.lumi_lumi_weather_pressure', 'sensor.lumi_lumi_weather_temperature', 'sensor.lumi_lumi_weather_humidity', 'sensor.lumi_lumi_weather_4dad5507_power', 'sensor.lumi_lumi_weather_4dad5507_pressure', 'sensor.lumi_lumi_weather_4dad5507_temperature', 'sensor.lumi_lumi_weather_4dad5507_humidity', 'sensor.lumi_lumi_weather_bf387507_power', 'sensor.lumi_lumi_weather_bf387507_pressure', 'sensor.lumi_lumi_weather_bf387507_temperature', 'sensor.lumi_lumi_weather_bf387507_humidity', 'sensor.lumi_lumi_weather_824e9807_power', 'sensor.lumi_lumi_weather_824e9807_pressure', 'sensor.lumi_lumi_weather_824e9807_temperature', 'sensor.lumi_lumi_weather_824e9807_humidity', 'sensor.lumi_lumi_sensor_wleak_aq1_840d0304_power', 'sensor.lumi_lumi_sensor_wleak_aq1_840d0304_device_temperature', 'sensor.lumi_lumi_sensor_wleak_aq1_7de70004_power', 'sensor.lumi_lumi_sensor_wleak_aq1_7de70004_device_temperature', 'sensor.xiaomi_lumi_sen_ill_mgl01_battery', 'sensor.xiaomi_lumi_sen_ill_mgl01_illuminance', 'sensor.xiaomi_lumi_sen_ill_mgl01_battery_2', 'sensor.xiaomi_lumi_sen_ill_mgl01_illuminance_2', 'sensor.trust_battery', 'sensor.tz3000_g5xawfcq_ts0121_electricalmeasurement', 'sensor.tz3000_g5xawfcq_ts0121_electricalmeasurementrmscurrent', 'sensor.tz3000_g5xawfcq_ts0121_electricalmeasurementrmsvoltage', 'sensor.tz3000_g5xawfcq_ts0121_electricalmeasurementpowerfactor', 'sensor.blitzwolf_shp15_01_electricalmeasurement', 'sensor.blitzwolf_shp15_01_electricalmeasurementrmscurrent', 'sensor.blitzwolf_shp15_01_electricalmeasurementrmsvoltage', 'sensor.blitzwolf_shp15_01_electricalmeasurementpowerfactor', 'sensor.lumi_lumi_sensor_magnet_battery', 'sensor.lumi_lumi_sensor_magnet_devicetemperature', 'sensor.lumi_lumi_relay_c2acn01_electricalmeasurement', 'sensor.lumi_lumi_relay_c2acn01_electricalmeasurementrmsvoltage', 'sensor.lumi_lumi_relay_c2acn01_electricalmeasurementpowerfactor', 'sensor.lumi_lumi_relay_c2acn01_devicetemperature', 'sensor.tz3000_zloso4jk_ts011f_active_power', 'sensor.tz3000_zloso4jk_ts011f_rms_current', 'sensor.tz3000_zloso4jk_ts011f_rms_voltage', 'sensor.tz3000_zloso4jk_ts011f_power_factor', 'sensor.ikea_of_sweden_tradfri_on_off_switch_battery', 'sensor.atc_3fc4_temperature', 'sensor.atc_3fc4_humidity', 'sensor.atc_3fc4_battery', 'sensor.temperature_humidity_sensor_ac21_battery', 'sensor.temperature_humidity_sensor_ac21_voltage', 'sensor.a4c138d358e9_humidity', 'sensor.a4c138d358e9_temperature', 'sensor.a4c138d358e9_battery', 'sensor.a4c1384e2b64_humidity', 'sensor.a4c1384e2b64_temperature', 'sensor.a4c1384e2b64_battery', 'sensor.aleksandr_body_type', 'sensor.irina_body_type', 'sensor.atc_03b4_voltage', 'sensor.atc_4e9a_voltage', 'sensor.atc_3d13_temperature', 'sensor.atc_3d13_humidity', 'sensor.atc_3d13_battery', 'sensor.atc_bdae_voltage', 'sensor.atc_8716_temperature', 'sensor.atc_8716_humidity', 'sensor.atc_8716_battery', 'sensor.atc_84c8_temperature', 'sensor.atc_84c8_humidity', 'sensor.atc_84c8_battery', 'sensor.atc_7f83_voltage', 'sensor.e2c56db5_dffb_48d2_b060_d0f5a71096e0_0_0_estimated_distance', 'sensor.74278bda_b644_4520_8f0c_720eaf059935_0_124_377c_estimated_distance', 'sensor.400225dd_3d15_4a4b_9db3_93c4b2d01eda_0_0_08ff_estimated_distance', 'sensor.389fbc46_bfb4_4d29_a089_bb5b343d069c_100_1_f15f_estimated_distance', 'sensor.74278bda_b644_4520_8f0c_720eaf059935_0_30053_6896_estimated_distance', 'sensor.ikea_3_battery_2') GROUP BY states.entity_id) AS anon_1, states LEFT OUTER JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id WHERE states.state_id = anon_1.max_state_id

+------+-------------+------------------+--------+---------------------------------------------------------------+-------------------------------------+---------+---------------------------------+--------+--------------------------+
| id   | select_type | table            | type   | possible_keys                                                 | key                                 | key_len | ref                             | rows   | Extra                    |
+------+-------------+------------------+--------+---------------------------------------------------------------+-------------------------------------+---------+---------------------------------+--------+--------------------------+
|    1 | PRIMARY     | <derived2>       | ALL    | NULL                                                          | NULL                                | NULL    | NULL                            | 638778 | Using where              |
|    1 | PRIMARY     | states           | eq_ref | PRIMARY                                                       | PRIMARY                             | 4       | anon_1.max_state_id             | 1      |                          |
|    1 | PRIMARY     | state_attributes | eq_ref | PRIMARY                                                       | PRIMARY                             | 4       | issue87851.states.attributes_id | 1      | Using where              |
|    2 | DERIVED     | states           | range  | ix_states_entity_id_last_updated_ts,ix_states_last_updated_ts | ix_states_entity_id_last_updated_ts | 1032    | NULL                            | 638778 | Using where; Using index |
+------+-------------+------------------+--------+---------------------------------------------------------------+-------------------------------------+---------+---------------------------------+--------+--------------------------+
bdraco commented 1 year ago

So the new design should reduce the number of rows needed to be examined by at least 99%. It will scale better vs the old over time. The previous design would get slower and slower the longer the instance ran

bdraco commented 1 year ago

To make this change I need to audit every max history query and adjust. This will take some time

avbor commented 1 year ago

Yes, there's a huge difference!

On the same time window Old query took 20.2827 sec., return 312 rows image

SELECT states.entity_id, states.state, states.last_changed_ts, states.last_updated_ts, states.attributes, state_attributes.shared_attrs FROM (SELECT max(states.state_id) AS max_state_id FROM states WHERE states.last_updated_ts >= 1676622590.438751e0 AND states.last_updated_ts < 1676826299.999999e0 AND states.entity_id IN ('sensor.average_carbon_dioxide_in_flat', 'sensor.average_pm25_in_flat', 'sensor.average_pm10_in_flat', 'sensor.average_temperature_in_flat', 'sensor.average_humidity_in_flat', 'sensor.average_voltage_in_flat', 'sensor.average_voltage_in_country_house', 'sensor.country_house_avg_temp_kids_rooms', 'sensor.country_house_avg_humidity_kids_rooms', 'sensor.energy_flat_main_power', 'sensor.kitchen_stove_temperature_min_max_bt', 'sensor.kitchen_stove_temperature_min_max_mean', 'sensor.kitchen_stove_temperature_median_min_max_mean', 'sensor.kitchen_stove_temperature_median_zb', 'sensor.kitchen_stove_temperature_median_bt', 'sensor.processor_use', 'sensor.memory_use_percent', 'sensor.memory_use', 'sensor.memory_free', 'sensor.country_house_living_room_dewpoint', 'sensor.country_house_kids_room_dewpoint', 'sensor.openweathermap_moskva_danilovskii_dew_point', 'sensor.openweathermap_moskva_danilovskii_temperature', 'sensor.openweathermap_moskva_danilovskii_feels_like_temperature', 'sensor.openweathermap_moskva_danilovskii_wind_speed', 'sensor.openweathermap_moskva_danilovskii_wind_bearing', 'sensor.openweathermap_moskva_danilovskii_humidity', 'sensor.openweathermap_moskva_danilovskii_pressure', 'sensor.openweathermap_moskva_danilovskii_cloud_coverage', 'sensor.openweathermap_moskva_danilovskii_rain', 'sensor.openweathermap_moskva_danilovskii_snow', 'sensor.openweathermap_moskva_danilovskii_uv_index', 'sensor.openweathermap_moskva_danilovskii_visibility', 'sensor.openweathermap_stupino_nivki_2_dew_point', 'sensor.openweathermap_stupino_nivki_2_temperature', 'sensor.openweathermap_stupino_nivki_2_feels_like_temperature', 'sensor.openweathermap_stupino_nivki_2_wind_speed', 'sensor.openweathermap_stupino_nivki_2_wind_bearing', 'sensor.openweathermap_stupino_nivki_2_humidity', 'sensor.openweathermap_stupino_nivki_2_pressure', 'sensor.openweathermap_stupino_nivki_2_cloud_coverage', 'sensor.openweathermap_stupino_nivki_2_rain', 'sensor.openweathermap_stupino_nivki_2_snow', 'sensor.openweathermap_stupino_nivki_2_uv_index', 'sensor.openweathermap_stupino_nivki_2_visibility', 'sensor.watchman_missing_entities', 'sensor.watchman_missing_services', 'sensor.aleksandr_bmi', 'sensor.aleksandr_basal_metabolism', 'sensor.aleksandr_visceral_fat', 'sensor.aleksandr_weight', 'sensor.aleksandr_lean_body_mass', 'sensor.aleksandr_body_fat', 'sensor.aleksandr_protein', 'sensor.aleksandr_water', 'sensor.aleksandr_bone_mass', 'sensor.aleksandr_muscle_mass', 'sensor.aleksandr_metabolic_age', 'sensor.aleksandr_body_score', 'sensor.irina_bmi', 'sensor.irina_basal_metabolism', 'sensor.irina_visceral_fat', 'sensor.irina_weight', 'sensor.irina_lean_body_mass', 'sensor.irina_body_fat', 'sensor.irina_protein', 'sensor.irina_water', 'sensor.irina_bone_mass', 'sensor.irina_muscle_mass', 'sensor.irina_metabolic_age', 'sensor.irina_body_score', 'sensor.egor_bmi', 'sensor.egor_basal_metabolism', 'sensor.egor_visceral_fat', 'sensor.egor_weight', 'sensor.kirill_bmi', 'sensor.kirill_basal_metabolism', 'sensor.kirill_visceral_fat', 'sensor.kirill_weight', 'sensor.ups_battery_charge', 'sensor.ups_battery_voltage', 'sensor.galaxy_tab_a_8_0_2019_battery', 'sensor.galaxy_tab_a_8_0_2019_internal_storage_free_space', 'sensor.galaxy_tab_a_8_0_2019_internal_storage_total_space', 'sensor.galaxy_tab_a_8_0_2019_free_memory', 'sensor.galaxy_tab_a_8_0_2019_total_memory', 'sensor.tplink_smartplug_02_current_consumption', 'sensor.tplink_smartplug_02_voltage', 'sensor.tplink_smartplug_02_current', 'sensor.tplink_smartplug_01_current_consumption', 'sensor.tplink_smartplug_01_voltage', 'sensor.tplink_smartplug_01_current', 'sensor.2109119dg_battery_temperature', 'sensor.2109119dg_light_sensor', 'sensor.2109119dg_battery_power', 'sensor.sm_t295_hallway_battery_level', 'sensor.sm_t295_hallway_battery_power', 'sensor.sm_t295_hallway_battery_temperature', 'sensor.kb2003_battery_level', 'sensor.kb2003_battery_temperature', 'sensor.kb2003_battery_power', 'sensor.kb2003_light_sensor', 'sensor.kb2003_wifi_link_speed', 'sensor.kb2003_wifi_frequency', 'sensor.kb2003_wifi_signal_strength', 'sensor.gosund_sp111_01_ampere', 'sensor.gosund_sp111_01_watt', 'sensor.gosund_sp111_02_ampere', 'sensor.gosund_sp111_02_watt', 'sensor.gosund_sp111_06_ampere', 'sensor.gosund_sp111_06_watt', 'sensor.gosund_sp111_05_ampere', 'sensor.gosund_sp111_05_watt', 'sensor.gosund_sp111_04_ampere', 'sensor.gosund_sp111_04_watt', 'sensor.gosund_sp111_07_ampere', 'sensor.gosund_sp111_07_watt', 'sensor.gosund_sp111_03_ampere', 'sensor.gosund_sp111_03_watt', 'sensor.gosund_sp111_08_ampere', 'sensor.gosund_sp111_08_watt', 'sensor.gosund_sp111_09_ampere', 'sensor.gosund_sp111_09_watt', 'sensor.esp32_miscale2_01_current_weight', 'sensor.esp32_miscale2_01_current_impedance', 'sensor.gosund_sp111_10_ampere', 'sensor.gosund_sp111_10_watt', 'sensor.temperature_humidity_sensor_ac21_humidity', 'sensor.air_monitor_lite_cb3b_temperature', 'sensor.air_monitor_lite_cb3b_humidity', 'sensor.air_monitor_lite_cb3b_pm25', 'sensor.air_monitor_lite_cb3b_pm10', 'sensor.air_monitor_lite_cb3b_carbon_dioxide', 'sensor.air_monitor_lite_afb3_temperature', 'sensor.air_monitor_lite_afb3_humidity', 'sensor.air_monitor_lite_afb3_pm25', 'sensor.air_monitor_lite_afb3_pm10', 'sensor.air_monitor_lite_afb3_carbon_dioxide', 'sensor.air_monitor_lite_f6a7_temperature', 'sensor.air_monitor_lite_f6a7_humidity', 'sensor.air_monitor_lite_f6a7_pm25', 'sensor.air_monitor_lite_f6a7_pm10', 'sensor.air_monitor_lite_f6a7_carbon_dioxide', 'sensor.atc_03b4_temperature', 'sensor.atc_03b4_humidity', 'sensor.atc_03b4_battery', 'sensor.atc_3fc4_temperature', 'sensor.atc_3fc4_humidity', 'sensor.atc_3fc4_battery', 'sensor.atc_4e9a_temperature', 'sensor.atc_4e9a_humidity', 'sensor.atc_4e9a_battery', 'sensor.atc_3d13_voltage', 'sensor.atc_bdae_temperature', 'sensor.atc_bdae_humidity', 'sensor.atc_bdae_battery', 'sensor.atc_8716_temperature', 'sensor.atc_8716_humidity', 'sensor.atc_8716_battery', 'sensor.atc_84c8_temperature', 'sensor.atc_84c8_humidity', 'sensor.atc_84c8_battery', 'sensor.th_kukhnia_za_oknom_temperature', 'sensor.th_kukhnia_za_oknom_humidity', 'sensor.th_kukhnia_za_oknom_battery', 'sensor.0x00158d000413b210_gas_density', 'sensor.0x158d00054859d5_battery', 'sensor.0x158d000802df91_temperature', 'sensor.0x158d000802df91_humidity', 'sensor.0x158d000802df91_battery', 'sensor.0x158d000802df91_pressure', 'sensor.0x158d00040e019e_temperature', 'sensor.0x158d00040e019e_humidity', 'sensor.0x158d00040e019e_battery', 'sensor.a4c1380b3a85_temperature', 'sensor.a4c1380b3a85_humidity', 'sensor.a4c1380b3a85_battery', 'sensor.a4c138c7ded2_temperature', 'sensor.a4c138c7ded2_humidity', 'sensor.a4c138c7ded2_battery', 'sensor.temperature_humidity_sensor_ac21_temperature', 'sensor.lumi_lumi_sensor_cube_power', 'sensor.lumi_lumi_sensor_cube_aqgl01_d92a2905_device_temperature', 'sensor.lumi_lumi_sen_ill_mgl01_power', 'sensor.lumi_lumi_sen_ill_mgl01_illuminance', 'sensor.ikea_of_sweden_tradfri_on_off_switch_46a690fe_power', 'sensor.ikea_of_sweden_tradfri_on_off_switch_06e49efe_power', 'sensor.adurolight_vms_adurolight_f604cd01_power', 'sensor.0x158d00039e2ecb_power', 'sensor.0x158d00039e2ecb_temperature', 'sensor.0x158d00039e2ecb_humidity', 'sensor.0x158d000309920a_battery', 'sensor.0x158d000309920a_illuminance', 'sensor.lumi_lumi_sensor_motion_aq2_0a920903_device_temperature', 'sensor.lumi_lumi_sensor_switch_b1115a04_power', 'sensor.lumi_lumi_sensor_switch_53115a04_power', 'sensor.lumi_lumi_sensor_swit_power', 'sensor.lumi_lumi_sensor_swit_8e46ab04_device_temperature', 'sensor.lumi_lumi_sensor_switch_31f65f03_power', 'sensor.lumi_lumi_sensor_motion_aq2_power', 'sensor.0x158d0003fac0ec_illuminance', 'sensor.lumi_lumi_sensor_motion_aq2_ecc0fa03_device_temperature', 'sensor.lumi_lumi_sens_power', 'sensor.0x158d0003931a55_temperature', 'sensor.0x158d0003931a55_humidity', 'sensor.lumi_lumi_sensor_switch_c59b5a03_power', 'sensor.0x158d0003230618_battery', 'sensor.0x158d0003230618_pressure', 'sensor.0x158d0003230618_temperature', 'sensor.0x158d0003230618_humidity', 'sensor.0x158d0003931b0e_battery', 'sensor.0x158d0003931b0e_temperature', 'sensor.0x158d0003931b0e_humidity', 'sensor.0x158d0003931aec_battery', 'sensor.0x158d0003931aec_temperature', 'sensor.0x158d0003931aec_humidity', 'sensor.lumi_lumi_sensor_switch_9efd3e03_power', 'sensor.lumi_lumi_sensor_motion_aq2_8eea4c04_power', 'sensor.0x158d00044cea8e_illuminance', 'sensor.lumi_lumi_sensor_motion_aq2_8eea4c04_device_temperature', 'sensor.lumi_lumi_sensor_magnet_power', 'sensor.lumi_lumi_sensor_magnet_673b0704_power', 'sensor.lumi_lumi_sensor_magnet_2547d503_power', 'sensor.lumi_lumi_sensor_magnet_74f99c03_power', 'sensor.lumi_th_kitchen_stove_power', 'sensor.lumi_th_kitchen_stove_temperature', 'sensor.lumi_th_kitchen_stove_humidity', 'sensor.lumi_lumi_sensor_magnet_50659603_power', 'sensor.blitzwolf_shp13_02_3ad702fe_electrical_measurement', 'sensor.blitzwolf_shp13_02_3ad702fe_electrical_measurement_rms_current', 'sensor.blitzwolf_shp13_02_3ad702fe_electrical_measurement_rms_voltage', 'sensor.tz3000_g5xawfcq_ts0121_3ad702fe_electrical_measurement_power_factor', 'sensor.lumi_lumi_sensor_switch_005dd103_power', 'sensor.lumi_lumi_sensor_motion_aq2_7b4e5f07_power', 'sensor.lumi_lumi_sensor_motion_aq2_7b4e5f07_illuminance', 'sensor.lumi_lumi_sensor_motion_aq2_7b4e5f07_device_temperature', 'sensor.lumi_lumi_sensor_magnet_724f4f04_power', 'sensor.lumi_lumi_sensor_switch_power', 'sensor.lumi_lumi_sensor_magnet_9bef9603_power', 'sensor.lumi_lumi_sensor_magnet_cd829803_power', 'sensor.lumi_lumi_sen_ill_mgl01_0d117d3c_power', 'sensor.0x4cf8cdf3c7d110d_illuminance', 'sensor.lumi_lumi_sensor_wleak_aq1_power', 'sensor.lumi_lumi_sensor_wleak_aq1_7cadd306_device_temperature', 'sensor.ikea_1_power', 'sensor.ikea_4_power', 'sensor.ikea_5_power', 'sensor.lumi_lumi_weather_power', 'sensor.lumi_lumi_weather_pressure', 'sensor.lumi_lumi_weather_temperature', 'sensor.lumi_lumi_weather_humidity', 'sensor.lumi_lumi_weather_4dad5507_power', 'sensor.lumi_lumi_weather_4dad5507_pressure', 'sensor.lumi_lumi_weather_4dad5507_temperature', 'sensor.lumi_lumi_weather_4dad5507_humidity', 'sensor.lumi_lumi_weather_bf387507_power', 'sensor.lumi_lumi_weather_bf387507_pressure', 'sensor.lumi_lumi_weather_bf387507_temperature', 'sensor.lumi_lumi_weather_bf387507_humidity', 'sensor.lumi_lumi_weather_824e9807_power', 'sensor.lumi_lumi_weather_824e9807_pressure', 'sensor.lumi_lumi_weather_824e9807_temperature', 'sensor.lumi_lumi_weather_824e9807_humidity', 'sensor.lumi_lumi_sensor_wleak_aq1_840d0304_power', 'sensor.lumi_lumi_sensor_wleak_aq1_840d0304_device_temperature', 'sensor.lumi_lumi_sensor_wleak_aq1_7de70004_power', 'sensor.lumi_lumi_sensor_wleak_aq1_7de70004_device_temperature', 'sensor.xiaomi_lumi_sen_ill_mgl01_battery', 'sensor.xiaomi_lumi_sen_ill_mgl01_illuminance', 'sensor.xiaomi_lumi_sen_ill_mgl01_battery_2', 'sensor.xiaomi_lumi_sen_ill_mgl01_illuminance_2', 'sensor.trust_battery', 'sensor.tz3000_g5xawfcq_ts0121_electricalmeasurement', 'sensor.tz3000_g5xawfcq_ts0121_electricalmeasurementrmscurrent', 'sensor.tz3000_g5xawfcq_ts0121_electricalmeasurementrmsvoltage', 'sensor.tz3000_g5xawfcq_ts0121_electricalmeasurementpowerfactor', 'sensor.blitzwolf_shp15_01_electricalmeasurement', 'sensor.blitzwolf_shp15_01_electricalmeasurementrmscurrent', 'sensor.blitzwolf_shp15_01_electricalmeasurementrmsvoltage', 'sensor.blitzwolf_shp15_01_electricalmeasurementpowerfactor', 'sensor.lumi_lumi_sensor_magnet_battery', 'sensor.lumi_lumi_sensor_magnet_devicetemperature', 'sensor.lumi_lumi_relay_c2acn01_electricalmeasurement', 'sensor.lumi_lumi_relay_c2acn01_electricalmeasurementrmsvoltage', 'sensor.lumi_lumi_relay_c2acn01_electricalmeasurementpowerfactor', 'sensor.lumi_lumi_relay_c2acn01_devicetemperature', 'sensor.tz3000_zloso4jk_ts011f_active_power', 'sensor.tz3000_zloso4jk_ts011f_rms_current', 'sensor.tz3000_zloso4jk_ts011f_rms_voltage', 'sensor.tz3000_zloso4jk_ts011f_power_factor', 'sensor.ikea_of_sweden_tradfri_on_off_switch_battery', 'sensor.ikea_3_battery_2', 'sensor.temperature_humidity_sensor_ac21_battery', 'sensor.temperature_humidity_sensor_ac21_voltage', 'sensor.a4c138d358e9_humidity', 'sensor.a4c138d358e9_temperature', 'sensor.a4c138d358e9_battery', 'sensor.a4c1384e2b64_humidity', 'sensor.a4c1384e2b64_temperature', 'sensor.a4c1384e2b64_battery', 'sensor.aleksandr_body_type', 'sensor.irina_body_type', 'sensor.atc_03b4_voltage', 'sensor.atc_3fc4_voltage', 'sensor.atc_4e9a_voltage', 'sensor.atc_3d13_temperature', 'sensor.atc_3d13_humidity', 'sensor.atc_3d13_battery', 'sensor.atc_bdae_voltage', 'sensor.th_kukhnia_kholodilnik_niz_voltage', 'sensor.atc_84c8_voltage', 'sensor.atc_7f83_voltage') GROUP BY states.entity_id) AS anon_1, states LEFT OUTER JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id WHERE states.state_id = anon_1.max_state_id

New query took only 0.0418 sec., return 310 rows image

SELECT states.entity_id, states.state, states.last_changed_ts, states.last_updated_ts, states.attributes, state_attributes.shared_attrs FROM (SELECT max(states.last_updated_ts) AS max_last_updated_ts, entity_id FROM states WHERE states.last_updated_ts >= 1676622590.438751e0 AND states.last_updated_ts < 1676826299.999999e0 AND states.entity_id IN ('sensor.average_carbon_dioxide_in_flat', 'sensor.average_pm25_in_flat', 'sensor.average_pm10_in_flat', 'sensor.average_temperature_in_flat', 'sensor.average_humidity_in_flat', 'sensor.average_voltage_in_flat', 'sensor.average_voltage_in_country_house', 'sensor.country_house_avg_temp_kids_rooms', 'sensor.country_house_avg_humidity_kids_rooms', 'sensor.kitchen_stove_temperature_min_max_bt', 'sensor.kitchen_stove_temperature_min_max_mean', 'sensor.kitchen_stove_temperature_median_min_max_mean', 'sensor.kitchen_stove_temperature_median_zb', 'sensor.kitchen_stove_temperature_median_bt', 'sensor.processor_use', 'sensor.memory_use_percent', 'sensor.memory_use', 'sensor.memory_free', 'sensor.energy_flat_main_power', 'sensor.country_house_living_room_dewpoint', 'sensor.country_house_kids_room_dewpoint', 'sensor.aleksandr_bmi', 'sensor.aleksandr_basal_metabolism', 'sensor.aleksandr_visceral_fat', 'sensor.aleksandr_weight', 'sensor.aleksandr_lean_body_mass', 'sensor.aleksandr_body_fat', 'sensor.aleksandr_protein', 'sensor.aleksandr_water', 'sensor.aleksandr_bone_mass', 'sensor.aleksandr_muscle_mass', 'sensor.aleksandr_metabolic_age', 'sensor.aleksandr_body_score', 'sensor.irina_bmi', 'sensor.irina_basal_metabolism', 'sensor.irina_visceral_fat', 'sensor.irina_weight', 'sensor.irina_lean_body_mass', 'sensor.irina_body_fat', 'sensor.irina_protein', 'sensor.irina_water', 'sensor.irina_bone_mass', 'sensor.irina_muscle_mass', 'sensor.irina_metabolic_age', 'sensor.irina_body_score', 'sensor.egor_bmi', 'sensor.egor_basal_metabolism', 'sensor.egor_visceral_fat', 'sensor.egor_weight', 'sensor.kirill_bmi', 'sensor.kirill_basal_metabolism', 'sensor.kirill_visceral_fat', 'sensor.kirill_weight', 'sensor.watchman_missing_entities', 'sensor.watchman_missing_services', 'sensor.ups_battery_charge', 'sensor.ups_battery_voltage', 'sensor.openweathermap_stupino_nivki_2_dew_point', 'sensor.openweathermap_stupino_nivki_2_temperature', 'sensor.openweathermap_stupino_nivki_2_feels_like_temperature', 'sensor.openweathermap_stupino_nivki_2_wind_speed', 'sensor.openweathermap_stupino_nivki_2_wind_bearing', 'sensor.openweathermap_stupino_nivki_2_humidity', 'sensor.openweathermap_stupino_nivki_2_pressure', 'sensor.openweathermap_stupino_nivki_2_cloud_coverage', 'sensor.openweathermap_stupino_nivki_2_rain', 'sensor.openweathermap_stupino_nivki_2_snow', 'sensor.openweathermap_stupino_nivki_2_uv_index', 'sensor.openweathermap_stupino_nivki_2_visibility', 'sensor.openweathermap_moskva_danilovskii_dew_point', 'sensor.openweathermap_moskva_danilovskii_temperature', 'sensor.openweathermap_moskva_danilovskii_feels_like_temperature', 'sensor.openweathermap_moskva_danilovskii_wind_speed', 'sensor.openweathermap_moskva_danilovskii_wind_bearing', 'sensor.openweathermap_moskva_danilovskii_humidity', 'sensor.openweathermap_moskva_danilovskii_pressure', 'sensor.openweathermap_moskva_danilovskii_cloud_coverage', 'sensor.openweathermap_moskva_danilovskii_rain', 'sensor.openweathermap_moskva_danilovskii_snow', 'sensor.openweathermap_moskva_danilovskii_uv_index', 'sensor.openweathermap_moskva_danilovskii_visibility', 'sensor.galaxy_tab_a_8_0_2019_battery', 'sensor.galaxy_tab_a_8_0_2019_internal_storage_free_space', 'sensor.galaxy_tab_a_8_0_2019_internal_storage_total_space', 'sensor.galaxy_tab_a_8_0_2019_free_memory', 'sensor.galaxy_tab_a_8_0_2019_total_memory', 'sensor.kb2003_uroven_zariada_akkumuliatora', 'sensor.kb2003_datchik_osveshchionnosti', 'sensor.kb2003_skorost_soedineniia_wifi', 'sensor.kb2003_chastota_wifi', 'sensor.kb2003_moshchnost_signala_wifi', 'sensor.kb2003_battery_temperature', 'sensor.kb2003_battery_power', 'sensor.sm_t295_hallway_battery_power', 'sensor.gosund_sp111_01_ampere', 'sensor.gosund_sp111_01_watt', 'sensor.gosund_sp111_05_ampere', 'sensor.gosund_sp111_05_watt', 'sensor.gosund_sp111_02_ampere', 'sensor.gosund_sp111_02_watt', 'sensor.gosund_sp111_06_ampere', 'sensor.gosund_sp111_06_watt', 'sensor.gosund_sp111_03_ampere', 'sensor.gosund_sp111_03_watt', 'sensor.gosund_sp111_08_ampere', 'sensor.gosund_sp111_08_watt', 'sensor.gosund_sp111_07_ampere', 'sensor.gosund_sp111_07_watt', 'sensor.gosund_sp111_04_ampere', 'sensor.gosund_sp111_04_watt', 'sensor.0x00158d000413b210_gas_density', 'sensor.tplink_smartplug_02_current_consumption', 'sensor.tplink_smartplug_02_voltage', 'sensor.tplink_smartplug_02_current', 'sensor.tplink_smartplug_01_current_consumption', 'sensor.tplink_smartplug_01_voltage', 'sensor.tplink_smartplug_01_current', 'sensor.gosund_sp111_09_ampere', 'sensor.gosund_sp111_09_watt', 'sensor.gosund_sp111_10_ampere', 'sensor.gosund_sp111_10_watt', 'sensor.esp32_miscale2_01_current_weight', 'sensor.esp32_miscale2_01_current_impedance', 'sensor.air_monitor_lite_cb3b_temperature', 'sensor.air_monitor_lite_cb3b_humidity', 'sensor.air_monitor_lite_cb3b_pm25', 'sensor.air_monitor_lite_cb3b_pm10', 'sensor.air_monitor_lite_cb3b_carbon_dioxide', 'sensor.air_monitor_lite_afb3_temperature', 'sensor.air_monitor_lite_afb3_humidity', 'sensor.air_monitor_lite_afb3_pm25', 'sensor.air_monitor_lite_afb3_pm10', 'sensor.air_monitor_lite_afb3_carbon_dioxide', 'sensor.air_monitor_lite_f6a7_temperature', 'sensor.air_monitor_lite_f6a7_humidity', 'sensor.air_monitor_lite_f6a7_pm25', 'sensor.air_monitor_lite_f6a7_pm10', 'sensor.air_monitor_lite_f6a7_carbon_dioxide', 'sensor.atc_03b4_temperature', 'sensor.atc_03b4_humidity', 'sensor.atc_03b4_battery', 'sensor.atc_3fc4_voltage', 'sensor.atc_4e9a_temperature', 'sensor.atc_4e9a_humidity', 'sensor.atc_4e9a_battery', 'sensor.atc_3d13_voltage', 'sensor.atc_bdae_temperature', 'sensor.atc_bdae_humidity', 'sensor.atc_bdae_battery', 'sensor.th_kukhnia_kholodilnik_niz_voltage', 'sensor.atc_84c8_voltage', 'sensor.th_kukhnia_za_oknom_temperature', 'sensor.th_kukhnia_za_oknom_humidity', 'sensor.th_kukhnia_za_oknom_battery', 'sensor.temperature_humidity_sensor_ac21_temperature', 'sensor.temperature_humidity_sensor_ac21_humidity', 'sensor.fda50693_a4e2_4fb1_afcf_c6eb07647825_10011_2_79ae_estimated_distance', 'sensor.fda50693_a4e2_4fb1_afcf_c6eb07647825_10011_1_2340_estimated_distance', 'sensor.ed5e33eb_0ca3_4263_a681_a68b1045a5e6_100_1_estimated_distance', 'sensor.0x158d00054859d5_battery', 'sensor.0x158d000802df91_temperature', 'sensor.0x158d000802df91_humidity', 'sensor.0x158d000802df91_battery', 'sensor.0x158d000802df91_pressure', 'sensor.0x158d00040e019e_temperature', 'sensor.0x158d00040e019e_humidity', 'sensor.0x158d00040e019e_battery', 'sensor.a4c1380b3a85_temperature', 'sensor.a4c1380b3a85_humidity', 'sensor.a4c1380b3a85_battery', 'sensor.a4c138c7ded2_temperature', 'sensor.a4c138c7ded2_humidity', 'sensor.a4c138c7ded2_battery', 'sensor.lumi_lumi_sensor_cube_power', 'sensor.lumi_lumi_sensor_cube_aqgl01_d92a2905_device_temperature', 'sensor.lumi_lumi_sen_ill_mgl01_power', 'sensor.lumi_lumi_sen_ill_mgl01_illuminance', 'sensor.ikea_of_sweden_tradfri_on_off_switch_46a690fe_power', 'sensor.ikea_of_sweden_tradfri_on_off_switch_06e49efe_power', 'sensor.adurolight_vms_adurolight_f604cd01_power', 'sensor.0x158d00039e2ecb_power', 'sensor.0x158d00039e2ecb_temperature', 'sensor.0x158d00039e2ecb_humidity', 'sensor.0x158d000309920a_battery', 'sensor.0x158d000309920a_illuminance', 'sensor.lumi_lumi_sensor_motion_aq2_0a920903_device_temperature', 'sensor.lumi_lumi_sensor_switch_b1115a04_power', 'sensor.lumi_lumi_sensor_switch_53115a04_power', 'sensor.lumi_lumi_sensor_swit_power', 'sensor.lumi_lumi_sensor_swit_8e46ab04_device_temperature', 'sensor.lumi_lumi_sensor_switch_31f65f03_power', 'sensor.lumi_lumi_sensor_motion_aq2_power', 'sensor.0x158d0003fac0ec_illuminance', 'sensor.lumi_lumi_sensor_motion_aq2_ecc0fa03_device_temperature', 'sensor.lumi_lumi_sens_power', 'sensor.0x158d0003931a55_temperature', 'sensor.0x158d0003931a55_humidity', 'sensor.lumi_lumi_sensor_switch_c59b5a03_power', 'sensor.0x158d0003230618_battery', 'sensor.0x158d0003230618_pressure', 'sensor.0x158d0003230618_temperature', 'sensor.0x158d0003230618_humidity', 'sensor.0x158d0003931b0e_battery', 'sensor.0x158d0003931b0e_temperature', 'sensor.0x158d0003931b0e_humidity', 'sensor.0x158d0003931aec_battery', 'sensor.0x158d0003931aec_temperature', 'sensor.0x158d0003931aec_humidity', 'sensor.lumi_lumi_sensor_switch_9efd3e03_power', 'sensor.lumi_lumi_sensor_motion_aq2_8eea4c04_power', 'sensor.0x158d00044cea8e_illuminance', 'sensor.lumi_lumi_sensor_motion_aq2_8eea4c04_device_temperature', 'sensor.lumi_lumi_sensor_magnet_power', 'sensor.lumi_lumi_sensor_magnet_673b0704_power', 'sensor.lumi_lumi_sensor_magnet_2547d503_power', 'sensor.lumi_lumi_sensor_magnet_74f99c03_power', 'sensor.lumi_th_kitchen_stove_power', 'sensor.lumi_th_kitchen_stove_temperature', 'sensor.lumi_th_kitchen_stove_humidity', 'sensor.lumi_lumi_sensor_magnet_50659603_power', 'sensor.blitzwolf_shp13_02_3ad702fe_electrical_measurement', 'sensor.blitzwolf_shp13_02_3ad702fe_electrical_measurement_rms_current', 'sensor.blitzwolf_shp13_02_3ad702fe_electrical_measurement_rms_voltage', 'sensor.tz3000_g5xawfcq_ts0121_3ad702fe_electrical_measurement_power_factor', 'sensor.lumi_lumi_sensor_switch_005dd103_power', 'sensor.lumi_lumi_sensor_motion_aq2_7b4e5f07_power', 'sensor.lumi_lumi_sensor_motion_aq2_7b4e5f07_illuminance', 'sensor.lumi_lumi_sensor_motion_aq2_7b4e5f07_device_temperature', 'sensor.lumi_lumi_sensor_magnet_724f4f04_power', 'sensor.lumi_lumi_sensor_switch_power', 'sensor.lumi_lumi_sensor_magnet_9bef9603_power', 'sensor.lumi_lumi_sensor_magnet_cd829803_power', 'sensor.lumi_lumi_sen_ill_mgl01_0d117d3c_power', 'sensor.0x4cf8cdf3c7d110d_illuminance', 'sensor.lumi_lumi_sensor_wleak_aq1_power', 'sensor.lumi_lumi_sensor_wleak_aq1_7cadd306_device_temperature', 'sensor.ikea_1_power', 'sensor.ikea_4_power', 'sensor.ikea_5_power', 'sensor.lumi_lumi_weather_power', 'sensor.lumi_lumi_weather_pressure', 'sensor.lumi_lumi_weather_temperature', 'sensor.lumi_lumi_weather_humidity', 'sensor.lumi_lumi_weather_4dad5507_power', 'sensor.lumi_lumi_weather_4dad5507_pressure', 'sensor.lumi_lumi_weather_4dad5507_temperature', 'sensor.lumi_lumi_weather_4dad5507_humidity', 'sensor.lumi_lumi_weather_bf387507_power', 'sensor.lumi_lumi_weather_bf387507_pressure', 'sensor.lumi_lumi_weather_bf387507_temperature', 'sensor.lumi_lumi_weather_bf387507_humidity', 'sensor.lumi_lumi_weather_824e9807_power', 'sensor.lumi_lumi_weather_824e9807_pressure', 'sensor.lumi_lumi_weather_824e9807_temperature', 'sensor.lumi_lumi_weather_824e9807_humidity', 'sensor.lumi_lumi_sensor_wleak_aq1_840d0304_power', 'sensor.lumi_lumi_sensor_wleak_aq1_840d0304_device_temperature', 'sensor.lumi_lumi_sensor_wleak_aq1_7de70004_power', 'sensor.lumi_lumi_sensor_wleak_aq1_7de70004_device_temperature', 'sensor.xiaomi_lumi_sen_ill_mgl01_battery', 'sensor.xiaomi_lumi_sen_ill_mgl01_illuminance', 'sensor.xiaomi_lumi_sen_ill_mgl01_battery_2', 'sensor.xiaomi_lumi_sen_ill_mgl01_illuminance_2', 'sensor.trust_battery', 'sensor.tz3000_g5xawfcq_ts0121_electricalmeasurement', 'sensor.tz3000_g5xawfcq_ts0121_electricalmeasurementrmscurrent', 'sensor.tz3000_g5xawfcq_ts0121_electricalmeasurementrmsvoltage', 'sensor.tz3000_g5xawfcq_ts0121_electricalmeasurementpowerfactor', 'sensor.blitzwolf_shp15_01_electricalmeasurement', 'sensor.blitzwolf_shp15_01_electricalmeasurementrmscurrent', 'sensor.blitzwolf_shp15_01_electricalmeasurementrmsvoltage', 'sensor.blitzwolf_shp15_01_electricalmeasurementpowerfactor', 'sensor.lumi_lumi_sensor_magnet_battery', 'sensor.lumi_lumi_sensor_magnet_devicetemperature', 'sensor.lumi_lumi_relay_c2acn01_electricalmeasurement', 'sensor.lumi_lumi_relay_c2acn01_electricalmeasurementrmsvoltage', 'sensor.lumi_lumi_relay_c2acn01_electricalmeasurementpowerfactor', 'sensor.lumi_lumi_relay_c2acn01_devicetemperature', 'sensor.tz3000_zloso4jk_ts011f_active_power', 'sensor.tz3000_zloso4jk_ts011f_rms_current', 'sensor.tz3000_zloso4jk_ts011f_rms_voltage', 'sensor.tz3000_zloso4jk_ts011f_power_factor', 'sensor.ikea_of_sweden_tradfri_on_off_switch_battery', 'sensor.atc_3fc4_temperature', 'sensor.atc_3fc4_humidity', 'sensor.atc_3fc4_battery', 'sensor.temperature_humidity_sensor_ac21_battery', 'sensor.temperature_humidity_sensor_ac21_voltage', 'sensor.a4c138d358e9_humidity', 'sensor.a4c138d358e9_temperature', 'sensor.a4c138d358e9_battery', 'sensor.a4c1384e2b64_humidity', 'sensor.a4c1384e2b64_temperature', 'sensor.a4c1384e2b64_battery', 'sensor.aleksandr_body_type', 'sensor.irina_body_type', 'sensor.atc_03b4_voltage', 'sensor.atc_4e9a_voltage', 'sensor.atc_3d13_temperature', 'sensor.atc_3d13_humidity', 'sensor.atc_3d13_battery', 'sensor.atc_bdae_voltage', 'sensor.atc_8716_temperature', 'sensor.atc_8716_humidity', 'sensor.atc_8716_battery', 'sensor.atc_84c8_temperature', 'sensor.atc_84c8_humidity', 'sensor.atc_84c8_battery', 'sensor.atc_7f83_voltage', 'sensor.e2c56db5_dffb_48d2_b060_d0f5a71096e0_0_0_estimated_distance', 'sensor.74278bda_b644_4520_8f0c_720eaf059935_0_124_377c_estimated_distance', 'sensor.400225dd_3d15_4a4b_9db3_93c4b2d01eda_0_0_08ff_estimated_distance', 'sensor.389fbc46_bfb4_4d29_a089_bb5b343d069c_100_1_f15f_estimated_distance', 'sensor.74278bda_b644_4520_8f0c_720eaf059935_0_30053_6896_estimated_distance', 'sensor.ikea_3_battery_2') GROUP BY states.entity_id) AS anon_1, states LEFT OUTER JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id WHERE states.last_updated_ts = anon_1.max_last_updated_ts and states.entity_id=anon_1.entity_id;
bdraco commented 1 year ago

stats probably has the same issue

diff --git a/homeassistant/components/recorder/statistics.py b/homeassistant/components/recorder/statistics.py
index 244074cc10..372c779657 100644
--- a/homeassistant/components/recorder/statistics.py
+++ b/homeassistant/components/recorder/statistics.py
@@ -1853,7 +1853,9 @@ def _statistics_at_time(
     stmt = lambda_stmt(lambda: select(columns))

     most_recent_statistic_ids = (
-        lambda_stmt(lambda: select(func.max(table.id).label("max_id")))
+        lambda_stmt(
+            lambda: select(func.max(table.start_ts).label("max_start_ts"), table.metadata_id)
+        )
         .filter(table.start < start_time)
         .filter(table.metadata_id.in_(metadata_ids))
         .group_by(table.metadata_id)
@@ -1862,7 +1864,8 @@ def _statistics_at_time(

     stmt += lambda q: q.join(
         most_recent_statistic_ids,
-        table.id == most_recent_statistic_ids.c.max_id,
+        table.start_ts == most_recent_statistic_ids.c.max_start_ts
+        and table.metadata_id == most_recent_statistic_ids.c.metadata_id,
     )
     return execute_stmt_lambda_element(session, stmt)
bdraco commented 1 year ago

All the history max_state_id have the same issue

bdraco commented 1 year ago

I think all the max_last_updated ones are fine

avbor commented 1 year ago

What an amazing job! Any chance this will make it into the 2023.03 release?

bdraco commented 1 year ago

I will try to get it fixed before beta, but it may slip a month because my "day" job is going to be sucking up a lot of my time this week

bdraco commented 1 year ago

https://github.com/home-assistant/core/pull/88460 will fix the statistics query

bdraco commented 1 year ago

I'm still working on the history queries. They take a long time to test.

bdraco commented 1 year ago

Final query

SELECT 
  states.entity_id, 
  states.state, 
  states.last_changed_ts, 
  states.last_updated_ts, 
  states.attributes, 
  state_attributes.shared_attrs 
FROM 
  states 
  INNER JOIN (
    SELECT 
      states.entity_id AS max_entity_id, 
      max(states.last_updated_ts) AS max_last_updated 
    FROM 
      states 
    WHERE 
      states.last_updated_ts >= 1676198403.071357e0
      AND states.last_updated_ts < 1676296199.999999e0 
      AND states.entity_id IN (
        'sensor.average_carbon_dioxide_in_flat', 
        'sensor.average_pm25_in_flat', 
        'sensor.average_pm10_in_flat', 
        'sensor.average_temperature_in_flat', 
        'sensor.average_humidity_in_flat', 
        'sensor.average_voltage_in_flat', 
        'sensor.average_voltage_in_country_house', 
        'sensor.country_house_avg_temp_kids_rooms', 
        'sensor.country_house_avg_humidity_kids_rooms', 
        'sensor.kitchen_stove_temperature_min_max_bt', 
        'sensor.kitchen_stove_temperature_min_max_mean', 
        'sensor.kitchen_stove_temperature_median_min_max_mean', 
        'sensor.kitchen_stove_temperature_median_zb', 
        'sensor.kitchen_stove_temperature_median_bt', 
        'sensor.processor_use', 
        'sensor.memory_use_percent', 
        'sensor.memory_use', 
        'sensor.memory_free', 
        'sensor.energy_flat_main_power', 
        'sensor.country_house_living_room_dewpoint', 
        'sensor.country_house_kids_room_dewpoint', 
        'sensor.aleksandr_bmi', 
        'sensor.aleksandr_basal_metabolism', 
        'sensor.aleksandr_visceral_fat', 
        'sensor.aleksandr_weight', 
        'sensor.aleksandr_lean_body_mass', 
        'sensor.aleksandr_body_fat', 
        'sensor.aleksandr_protein', 
        'sensor.aleksandr_water', 
        'sensor.aleksandr_bone_mass', 
        'sensor.aleksandr_muscle_mass', 
        'sensor.aleksandr_metabolic_age', 
        'sensor.aleksandr_body_score', 
        'sensor.irina_bmi', 
        'sensor.irina_basal_metabolism', 
        'sensor.irina_visceral_fat', 
        'sensor.irina_weight', 
        'sensor.irina_lean_body_mass', 
        'sensor.irina_body_fat', 
        'sensor.irina_protein', 
        'sensor.irina_water', 
        'sensor.irina_bone_mass', 
        'sensor.irina_muscle_mass', 
        'sensor.irina_metabolic_age', 
        'sensor.irina_body_score', 
        'sensor.egor_bmi', 
        'sensor.egor_basal_metabolism', 
        'sensor.egor_visceral_fat', 
        'sensor.egor_weight', 
        'sensor.kirill_bmi', 
        'sensor.kirill_basal_metabolism', 
        'sensor.kirill_visceral_fat', 
        'sensor.kirill_weight', 
        'sensor.watchman_missing_entities', 
        'sensor.watchman_missing_services', 
        'sensor.ups_battery_charge', 
        'sensor.ups_battery_voltage', 
        'sensor.openweathermap_stupino_nivki_2_dew_point', 
        'sensor.openweathermap_stupino_nivki_2_temperature', 
        'sensor.openweathermap_stupino_nivki_2_feels_like_temperature', 
        'sensor.openweathermap_stupino_nivki_2_wind_speed', 
        'sensor.openweathermap_stupino_nivki_2_wind_bearing', 
        'sensor.openweathermap_stupino_nivki_2_humidity', 
        'sensor.openweathermap_stupino_nivki_2_pressure', 
        'sensor.openweathermap_stupino_nivki_2_cloud_coverage', 
        'sensor.openweathermap_stupino_nivki_2_rain', 
        'sensor.openweathermap_stupino_nivki_2_snow', 
        'sensor.openweathermap_stupino_nivki_2_uv_index', 
        'sensor.openweathermap_stupino_nivki_2_visibility', 
        'sensor.openweathermap_moskva_danilovskii_dew_point', 
        'sensor.openweathermap_moskva_danilovskii_temperature', 
        'sensor.openweathermap_moskva_danilovskii_feels_like_temperature', 
        'sensor.openweathermap_moskva_danilovskii_wind_speed', 
        'sensor.openweathermap_moskva_danilovskii_wind_bearing', 
        'sensor.openweathermap_moskva_danilovskii_humidity', 
        'sensor.openweathermap_moskva_danilovskii_pressure', 
        'sensor.openweathermap_moskva_danilovskii_cloud_coverage', 
        'sensor.openweathermap_moskva_danilovskii_rain', 
        'sensor.openweathermap_moskva_danilovskii_snow', 
        'sensor.openweathermap_moskva_danilovskii_uv_index', 
        'sensor.openweathermap_moskva_danilovskii_visibility', 
        'sensor.galaxy_tab_a_8_0_2019_battery', 
        'sensor.galaxy_tab_a_8_0_2019_internal_storage_free_space', 
        'sensor.galaxy_tab_a_8_0_2019_internal_storage_total_space', 
        'sensor.galaxy_tab_a_8_0_2019_free_memory', 
        'sensor.galaxy_tab_a_8_0_2019_total_memory', 
        'sensor.kb2003_uroven_zariada_akkumuliatora', 
        'sensor.kb2003_datchik_osveshchionnosti', 
        'sensor.kb2003_skorost_soedineniia_wifi', 
        'sensor.kb2003_chastota_wifi', 
        'sensor.kb2003_moshchnost_signala_wifi', 
        'sensor.kb2003_battery_temperature', 
        'sensor.kb2003_battery_power', 
        'sensor.sm_t295_hallway_battery_power', 
        'sensor.gosund_sp111_01_ampere', 
        'sensor.gosund_sp111_01_watt', 
        'sensor.gosund_sp111_05_ampere', 
        'sensor.gosund_sp111_05_watt', 
        'sensor.gosund_sp111_02_ampere', 
        'sensor.gosund_sp111_02_watt', 
        'sensor.gosund_sp111_06_ampere', 
        'sensor.gosund_sp111_06_watt', 
        'sensor.gosund_sp111_03_ampere', 
        'sensor.gosund_sp111_03_watt', 
        'sensor.gosund_sp111_08_ampere', 
        'sensor.gosund_sp111_08_watt', 
        'sensor.gosund_sp111_07_ampere', 
        'sensor.gosund_sp111_07_watt', 
        'sensor.gosund_sp111_04_ampere', 
        'sensor.gosund_sp111_04_watt', 
        'sensor.0x00158d000413b210_gas_density', 
        'sensor.tplink_smartplug_02_current_consumption', 
        'sensor.tplink_smartplug_02_voltage', 
        'sensor.tplink_smartplug_02_current', 
        'sensor.tplink_smartplug_01_current_consumption', 
        'sensor.tplink_smartplug_01_voltage', 
        'sensor.tplink_smartplug_01_current', 
        'sensor.gosund_sp111_09_ampere', 
        'sensor.gosund_sp111_09_watt', 
        'sensor.gosund_sp111_10_ampere', 
        'sensor.gosund_sp111_10_watt', 
        'sensor.esp32_miscale2_01_current_weight', 
        'sensor.esp32_miscale2_01_current_impedance', 
        'sensor.air_monitor_lite_cb3b_temperature', 
        'sensor.air_monitor_lite_cb3b_humidity', 
        'sensor.air_monitor_lite_cb3b_pm25', 
        'sensor.air_monitor_lite_cb3b_pm10', 
        'sensor.air_monitor_lite_cb3b_carbon_dioxide', 
        'sensor.air_monitor_lite_afb3_temperature', 
        'sensor.air_monitor_lite_afb3_humidity', 
        'sensor.air_monitor_lite_afb3_pm25', 
        'sensor.air_monitor_lite_afb3_pm10', 
        'sensor.air_monitor_lite_afb3_carbon_dioxide', 
        'sensor.air_monitor_lite_f6a7_temperature', 
        'sensor.air_monitor_lite_f6a7_humidity', 
        'sensor.air_monitor_lite_f6a7_pm25', 
        'sensor.air_monitor_lite_f6a7_pm10', 
        'sensor.air_monitor_lite_f6a7_carbon_dioxide', 
        'sensor.atc_03b4_temperature', 
        'sensor.atc_03b4_humidity', 
        'sensor.atc_03b4_battery', 
        'sensor.atc_3fc4_voltage', 
        'sensor.atc_4e9a_temperature', 
        'sensor.atc_4e9a_humidity', 
        'sensor.atc_4e9a_battery', 
        'sensor.atc_3d13_voltage', 
        'sensor.atc_bdae_temperature', 
        'sensor.atc_bdae_humidity', 
        'sensor.atc_bdae_battery', 
        'sensor.th_kukhnia_kholodilnik_niz_voltage', 
        'sensor.atc_84c8_voltage', 
        'sensor.th_kukhnia_za_oknom_temperature', 
        'sensor.th_kukhnia_za_oknom_humidity', 
        'sensor.th_kukhnia_za_oknom_battery', 
        'sensor.temperature_humidity_sensor_ac21_temperature', 
        'sensor.temperature_humidity_sensor_ac21_humidity', 
        'sensor.fda50693_a4e2_4fb1_afcf_c6eb07647825_10011_2_79ae_estimated_distance', 
        'sensor.fda50693_a4e2_4fb1_afcf_c6eb07647825_10011_1_2340_estimated_distance', 
        'sensor.ed5e33eb_0ca3_4263_a681_a68b1045a5e6_100_1_estimated_distance', 
        'sensor.0x158d00054859d5_battery', 
        'sensor.0x158d000802df91_temperature', 
        'sensor.0x158d000802df91_humidity', 
        'sensor.0x158d000802df91_battery', 
        'sensor.0x158d000802df91_pressure', 
        'sensor.0x158d00040e019e_temperature', 
        'sensor.0x158d00040e019e_humidity', 
        'sensor.0x158d00040e019e_battery', 
        'sensor.a4c1380b3a85_temperature', 
        'sensor.a4c1380b3a85_humidity', 
        'sensor.a4c1380b3a85_battery', 
        'sensor.a4c138c7ded2_temperature', 
        'sensor.a4c138c7ded2_humidity', 
        'sensor.a4c138c7ded2_battery', 
        'sensor.lumi_lumi_sensor_cube_power', 
        'sensor.lumi_lumi_sensor_cube_aqgl01_d92a2905_device_temperature', 
        'sensor.lumi_lumi_sen_ill_mgl01_power', 
        'sensor.lumi_lumi_sen_ill_mgl01_illuminance', 
        'sensor.ikea_of_sweden_tradfri_on_off_switch_46a690fe_power', 
        'sensor.ikea_of_sweden_tradfri_on_off_switch_06e49efe_power', 
        'sensor.adurolight_vms_adurolight_f604cd01_power', 
        'sensor.0x158d00039e2ecb_power', 
        'sensor.0x158d00039e2ecb_temperature', 
        'sensor.0x158d00039e2ecb_humidity', 
        'sensor.0x158d000309920a_battery', 
        'sensor.0x158d000309920a_illuminance', 
        'sensor.lumi_lumi_sensor_motion_aq2_0a920903_device_temperature', 
        'sensor.lumi_lumi_sensor_switch_b1115a04_power', 
        'sensor.lumi_lumi_sensor_switch_53115a04_power', 
        'sensor.lumi_lumi_sensor_swit_power', 
        'sensor.lumi_lumi_sensor_swit_8e46ab04_device_temperature', 
        'sensor.lumi_lumi_sensor_switch_31f65f03_power', 
        'sensor.lumi_lumi_sensor_motion_aq2_power', 
        'sensor.0x158d0003fac0ec_illuminance', 
        'sensor.lumi_lumi_sensor_motion_aq2_ecc0fa03_device_temperature', 
        'sensor.lumi_lumi_sens_power', 
        'sensor.0x158d0003931a55_temperature', 
        'sensor.0x158d0003931a55_humidity', 
        'sensor.lumi_lumi_sensor_switch_c59b5a03_power', 
        'sensor.0x158d0003230618_battery', 
        'sensor.0x158d0003230618_pressure', 
        'sensor.0x158d0003230618_temperature', 
        'sensor.0x158d0003230618_humidity', 
        'sensor.0x158d0003931b0e_battery', 
        'sensor.0x158d0003931b0e_temperature', 
        'sensor.0x158d0003931b0e_humidity', 
        'sensor.0x158d0003931aec_battery', 
        'sensor.0x158d0003931aec_temperature', 
        'sensor.0x158d0003931aec_humidity', 
        'sensor.lumi_lumi_sensor_switch_9efd3e03_power', 
        'sensor.lumi_lumi_sensor_motion_aq2_8eea4c04_power', 
        'sensor.0x158d00044cea8e_illuminance', 
        'sensor.lumi_lumi_sensor_motion_aq2_8eea4c04_device_temperature', 
        'sensor.lumi_lumi_sensor_magnet_power', 
        'sensor.lumi_lumi_sensor_magnet_673b0704_power', 
        'sensor.lumi_lumi_sensor_magnet_2547d503_power', 
        'sensor.lumi_lumi_sensor_magnet_74f99c03_power', 
        'sensor.lumi_th_kitchen_stove_power', 
        'sensor.lumi_th_kitchen_stove_temperature', 
        'sensor.lumi_th_kitchen_stove_humidity', 
        'sensor.lumi_lumi_sensor_magnet_50659603_power', 
        'sensor.blitzwolf_shp13_02_3ad702fe_electrical_measurement', 
        'sensor.blitzwolf_shp13_02_3ad702fe_electrical_measurement_rms_current', 
        'sensor.blitzwolf_shp13_02_3ad702fe_electrical_measurement_rms_voltage', 
        'sensor.tz3000_g5xawfcq_ts0121_3ad702fe_electrical_measurement_power_factor', 
        'sensor.lumi_lumi_sensor_switch_005dd103_power', 
        'sensor.lumi_lumi_sensor_motion_aq2_7b4e5f07_power', 
        'sensor.lumi_lumi_sensor_motion_aq2_7b4e5f07_illuminance', 
        'sensor.lumi_lumi_sensor_motion_aq2_7b4e5f07_device_temperature', 
        'sensor.lumi_lumi_sensor_magnet_724f4f04_power', 
        'sensor.lumi_lumi_sensor_switch_power', 
        'sensor.lumi_lumi_sensor_magnet_9bef9603_power', 
        'sensor.lumi_lumi_sensor_magnet_cd829803_power', 
        'sensor.lumi_lumi_sen_ill_mgl01_0d117d3c_power', 
        'sensor.0x4cf8cdf3c7d110d_illuminance', 
        'sensor.lumi_lumi_sensor_wleak_aq1_power', 
        'sensor.lumi_lumi_sensor_wleak_aq1_7cadd306_device_temperature', 
        'sensor.ikea_1_power', 
        'sensor.ikea_4_power', 
        'sensor.ikea_5_power', 
        'sensor.lumi_lumi_weather_power', 
        'sensor.lumi_lumi_weather_pressure', 
        'sensor.lumi_lumi_weather_temperature', 
        'sensor.lumi_lumi_weather_humidity', 
        'sensor.lumi_lumi_weather_4dad5507_power', 
        'sensor.lumi_lumi_weather_4dad5507_pressure', 
        'sensor.lumi_lumi_weather_4dad5507_temperature', 
        'sensor.lumi_lumi_weather_4dad5507_humidity', 
        'sensor.lumi_lumi_weather_bf387507_power', 
        'sensor.lumi_lumi_weather_bf387507_pressure', 
        'sensor.lumi_lumi_weather_bf387507_temperature', 
        'sensor.lumi_lumi_weather_bf387507_humidity', 
        'sensor.lumi_lumi_weather_824e9807_power', 
        'sensor.lumi_lumi_weather_824e9807_pressure', 
        'sensor.lumi_lumi_weather_824e9807_temperature', 
        'sensor.lumi_lumi_weather_824e9807_humidity', 
        'sensor.lumi_lumi_sensor_wleak_aq1_840d0304_power', 
        'sensor.lumi_lumi_sensor_wleak_aq1_840d0304_device_temperature', 
        'sensor.lumi_lumi_sensor_wleak_aq1_7de70004_power', 
        'sensor.lumi_lumi_sensor_wleak_aq1_7de70004_device_temperature', 
        'sensor.xiaomi_lumi_sen_ill_mgl01_battery', 
        'sensor.xiaomi_lumi_sen_ill_mgl01_illuminance', 
        'sensor.xiaomi_lumi_sen_ill_mgl01_battery_2', 
        'sensor.xiaomi_lumi_sen_ill_mgl01_illuminance_2', 
        'sensor.trust_battery', 
        'sensor.tz3000_g5xawfcq_ts0121_electricalmeasurement', 
        'sensor.tz3000_g5xawfcq_ts0121_electricalmeasurementrmscurrent', 
        'sensor.tz3000_g5xawfcq_ts0121_electricalmeasurementrmsvoltage', 
        'sensor.tz3000_g5xawfcq_ts0121_electricalmeasurementpowerfactor', 
        'sensor.blitzwolf_shp15_01_electricalmeasurement', 
        'sensor.blitzwolf_shp15_01_electricalmeasurementrmscurrent', 
        'sensor.blitzwolf_shp15_01_electricalmeasurementrmsvoltage', 
        'sensor.blitzwolf_shp15_01_electricalmeasurementpowerfactor', 
        'sensor.lumi_lumi_sensor_magnet_battery', 
        'sensor.lumi_lumi_sensor_magnet_devicetemperature', 
        'sensor.lumi_lumi_relay_c2acn01_electricalmeasurement', 
        'sensor.lumi_lumi_relay_c2acn01_electricalmeasurementrmsvoltage', 
        'sensor.lumi_lumi_relay_c2acn01_electricalmeasurementpowerfactor', 
        'sensor.lumi_lumi_relay_c2acn01_devicetemperature', 
        'sensor.tz3000_zloso4jk_ts011f_active_power', 
        'sensor.tz3000_zloso4jk_ts011f_rms_current', 
        'sensor.tz3000_zloso4jk_ts011f_rms_voltage', 
        'sensor.tz3000_zloso4jk_ts011f_power_factor', 
        'sensor.ikea_of_sweden_tradfri_on_off_switch_battery', 
        'sensor.atc_3fc4_temperature', 
        'sensor.atc_3fc4_humidity', 
        'sensor.atc_3fc4_battery', 
        'sensor.temperature_humidity_sensor_ac21_battery', 
        'sensor.temperature_humidity_sensor_ac21_voltage', 
        'sensor.a4c138d358e9_humidity', 
        'sensor.a4c138d358e9_temperature', 
        'sensor.a4c138d358e9_battery', 
        'sensor.a4c1384e2b64_humidity', 
        'sensor.a4c1384e2b64_temperature', 
        'sensor.a4c1384e2b64_battery', 
        'sensor.aleksandr_body_type', 
        'sensor.irina_body_type', 
        'sensor.atc_03b4_voltage', 
        'sensor.atc_4e9a_voltage', 
        'sensor.atc_3d13_temperature', 
        'sensor.atc_3d13_humidity', 
        'sensor.atc_3d13_battery', 
        'sensor.atc_bdae_voltage', 
        'sensor.atc_8716_temperature', 
        'sensor.atc_8716_humidity', 
        'sensor.atc_8716_battery', 
        'sensor.atc_84c8_temperature', 
        'sensor.atc_84c8_humidity', 
        'sensor.atc_84c8_battery', 
        'sensor.atc_7f83_voltage', 
        'sensor.e2c56db5_dffb_48d2_b060_d0f5a71096e0_0_0_estimated_distance', 
        'sensor.74278bda_b644_4520_8f0c_720eaf059935_0_124_377c_estimated_distance', 
        'sensor.400225dd_3d15_4a4b_9db3_93c4b2d01eda_0_0_08ff_estimated_distance', 
        'sensor.389fbc46_bfb4_4d29_a089_bb5b343d069c_100_1_f15f_estimated_distance', 
        'sensor.74278bda_b644_4520_8f0c_720eaf059935_0_30053_6896_estimated_distance', 
        'sensor.ikea_3_battery_2'
      ) 
    GROUP BY 
      states.entity_id
  ) AS anon_1 ON states.entity_id = anon_1.max_entity_id 
  AND states.last_updated_ts = anon_1.max_last_updated 
  LEFT OUTER JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id
bdraco commented 1 year ago 
+------+-------------+------------------+--------+---------------------------------------------------------------+-------------------------------------+---------+---------------------------------+------+------------------------------------------------------------------------+
| id   | select_type | table            | type   | possible_keys                                                 | key                                 | key_len | ref                             | rows | Extra                                                                  |
+------+-------------+------------------+--------+---------------------------------------------------------------+-------------------------------------+---------+---------------------------------+------+------------------------------------------------------------------------+
|    1 | PRIMARY     | <derived2>       | ALL    | NULL                                                          | NULL                                | NULL    | NULL                            | 4316 | Using where                                                            |
|    1 | PRIMARY     | states           | ref    | ix_states_entity_id_last_updated_ts,ix_states_last_updated_ts | ix_states_last_updated_ts           | 9       | anon_1.max_last_updated         | 1    | Using where                                                            |
|    1 | PRIMARY     | state_attributes | eq_ref | PRIMARY                                                       | PRIMARY                             | 4       | issue87851.states.attributes_id | 1    | Using where                                                            |
|    2 | DERIVED     | states           | range  | ix_states_entity_id_last_updated_ts,ix_states_last_updated_ts | ix_states_entity_id_last_updated_ts | 1032    | NULL                            | 4316 | Using where; Using index for group-by; Using temporary; Using filesort |
+------+-------------+------------------+--------+---------------------------------------------------------------+-------------------------------------+---------+---------------------------------+------+------------------------------------------------------------------------+