Get max timestamps efficiently for large table for a set of ids

46 views Asked by At

I have a large PostgreSQL db table (Actually lots of partition tables divided up by yearly quarters) that for simplicity sake is defined something like

id bigint
ts (timestamp)
value (float)

For a particular set of ids what is an efficient way of finding the last timestamp in the table for each specified id ?

The table is indexed by (id, timestamp)

If I do something naive like

SELECT sensor_id, MAX(ts) 
FROM sensor_values 
WHERE ts >= (NOW() + INTERVAL '-100 days') :: TIMESTAMPTZ 
GROUP BY 1;

Things are pretty slow.

Is there a way of perhaps narrowing down the times first by a binary search of one id (I can assume the timestamps are similar for a particular set of ids)

I am accessing the db through psycopg so the solution can be in code or SQL if I am missing something easy to speed this up.

The explain for the query can be seen here. https://explain.depesz.com/s/PVqg

Any ideas appreciated.

0

There are 0 answers