Really poor performance when using subquery to get one value
CREATE UNIQUE INDEX sensor_values_sensor_id_timestamp_index
  ON sensor_values (sensor_id, ts);
CREATE INDEX sensor_values_sensor_id_timestamp_value_index
  ON sensor_values (sensor_id, ts, value);

You can do instead:
CREATE UNIQUE INDEX sensor_values_sensor_id_timestamp_index
  ON sensor_values (sensor_id, ts) INCLUDE (value);

This will create your unique constraint index, and store &quot;value&quot; in it too, since I assume you want to use that as a covering index. Drop the second index, which is now useless. You can use the reclaimed disk space to create an index on (ts) which will help your query for the last day's records.

Am I right in thinking I need a partial index for the datetime column ?

You can't create a partial index with a variable where clause like &quot;current day&quot;.
But you can create it only on the last partition only.