Really poor performance when using subquery to get one value Hi I have a table defined like below (Its actually lots of partition tables)
CREATE TABLE sensor_values
(
  ts             TIMESTAMPTZ(35, 6)                    NOT NULL,
  value          FLOAT8(17, 17) DEFAULT 'NaN' :: REAL  NOT NULL,
  sensor_id      INT4(10)                              NOT NULL,
  status         TRIDIUM_STATUS(max) DEFAULT 'unknown' NOT NULL,
  original_value FLOAT8(17, 17)
);
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);

I am try to get the last days values grouped by sensor_id and day
SELECT sensor_id, ts::date, MIN(ts), MAX(ts), MIN(value), MAX(value) FROM sensor_values
  WHERE ts &gt;= '2021-03-22 00:00:00+00:00' GROUP BY 1, 2;

Works acceptable. I was hoping for better . I am guessing I should have a partial index on the day to speed things up. The explain analyze is shown at
https://explain.depesz.com/s/jYsM
If I add a sub query to get the day to start selecting the time is much worst
SELECT sensor_id, ts::date, MIN(ts), MAX(ts), MIN(value), MAX(value) FROM sensor_values
  WHERE ts &gt;= (SELECT MAX(day) + INTERVAL '1 days' FROM sensor_values_days_private LIMIT 1) GROUP BY 1, 2;

https://explain.depesz.com/s/1o4I
Is the way I can make postgres understand that the subquery returns an unchanging constant and
make it equal in time taken to the first query ?
Am I right in thinking I need a partial index for the datetime column ?
Thanks
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.