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
    Aug 30 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
    Aug 30 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
    Aug 30 at 7:21

0

Reset to default

Your Answer

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

Browse other questions tagged or ask your own question.