0

Given following SQL query

SELECT dd.* FROM devices_deviceconfiguration_sensors dds
JOIN devices_latest_config dlc ON dlc.config_id = dds.device_configuration_id
JOIN devices_deviceconfiguration dd ON dd.id = dlc.config_id
WHERE dds.sensor_id = $1

what is the right way to set indexes to speed up this query? Do I need dds.sensor_id indexed or (dds.sensor_id, dds.device_configuration_id)?

Query analysis output gives. Avg execution time is 8ms, which is slower than the average queries that we have
enter image description here

Improve this question
3
  • 1
    dds(sensor_id, device_configuration_id), dlc(config_id) and dd(id), it seems. Also I'd recommend you to use not JOIN dlc but WHERE EXISTS.
    – Akina
    Commented Aug 30, 2023 at 7:08
  • Thank you! Can you explain how you would use WHERE EXISTS in this case? dds(sensor_id, device_configuration_id) -> because the sensor_id in the WHERE clause is evaluated first and the JOIN afterwards?
    – Alex Tbk
    Commented Aug 30, 2023 at 7:13
  • 1
    SELECT dd.* FROM dd JOIN dds ON dd.id = dds.device_configuration_id WHERE dds.sensor_id = $1 AND EXISTS (SELECT NULL FROM dlc WHERE dlc.config_id = dds.device_configuration_id)
    – Akina
    Commented Aug 30, 2023 at 7:21

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.