How to get the oldest values over each id in a PostgreSQL table efficiently?

Question

How can PostgreSQL return a list of the oldest timestamp values over a table of sensor id measurements?

Let me explain the situation with a sample table:

CREATE TABLE sensor_data(
sensor_id INTEGER,
time TIMESTAMPTZ,
value NUMERIC,
PRIMARY KEY (sensor_id, time)
)

Populated table example:

+-----------+------------------+-------+
| sensor_id |       time       | value |
+-----------+------------------+-------+
|         1 | 2018-01-01 00:00 |     1 |
|         1 | 2018-01-01 01:00 |     2 |
|         3 | 2018-01-01 03:00 |     4 |
|         3 | 2018-01-01 04:00 |     3 |
|         4 | 2018-01-01 03:00 |     5 |
|         4 | 2018-01-01 04:00 |     6 |
+-----------+------------------+-------+

While using something like sensor_id (1,3) inside the query I want it to return something like this:

+-----------+------------------+-------+
| sensor_id |       time       | value |
+-----------+------------------+-------+
|         1 | 2018-01-01 01:00 |     2 |
|         3 | 2018-01-01 04:00 |     3 |
+-----------+------------------+-------+

How can I do that in a query using the PRIMARY KEY index for speeding it up?

Answer 1

There are many possible query styles, most will readily use your PK index on (sensor_id, time) as it fits the task. (Postgres can read indexes backwards practically as fast.) This should be near perfect:

SELECT s.sensor_id, sd.time, sd.value
FROM   unnest ('{1,3}'::int[]) s(sensor_id)
LEFT   JOIN LATERAL (
   SELECT *
   FROM   sensor_data sd
   WHERE  sd.sensor_id = s.sensor_id
   ORDER  BY time DESC
   LIMIT  1
   ) sd ON true;

db<>fiddle here

LEFT JOIN .. ON true keeps sensors without any data entries in the result - with NULL values in place of values.

Related:

• Optimize GROUP BY query to retrieve latest row per user
• What is the difference between LATERAL and a subquery in PostgreSQL?

Since you are on Postgres 11, a covering index might pay:

... PRIMARY KEY (sensor_id, time) INCLUDE (value) 

But it makes the index bigger and writes to the table more expensive, and your names indicate a write-heavy table. And while you only query for few rows at a time, queries don't get much faster anyway. So probably best the way you have it. Related:

• Does a query with a primary key and foreign keys run faster than a query with just primary keys?

Answer 2

Just for educational purposes, here is another way of solving it:

(
    SELECT sensor_id, time, value
    FROM sensor_data
    WHERE sensor_id = 1
    LIMIT 1
)
UNION ALL
(
    SELECT sensor_id, time, value
    FROM sensor_data
    WHERE sensor_id = 3
    LIMIT 1
);

The drawbacks of this query is the need of generating queries dinamically.

Erwin's solution is far more appropriate in this sense.