In my iot use case, I have a table which stores sensor readings. I am using the timescaledb extension.
I have the following table definition:
CREATE TABLE readings (
id BIGSERIAL PRIMARY KEY,
time TIMESTAMPTZ NOT NULL,
value DOUBLE PRECISION NOT NULL,
device VARCHAR(255) NOT NULL,
sensor VARCHAR(255) NOT NULL,
resolution VARCHAR(255) NOT NULL,
UNIQUE(time, device, sensor, resolution)
);
I want last reported time for every (device,sensor)
pair. Here is the query I am running:
select MAX(time), device, sensor from readings group by device, sensor
This query takes a significant time to complete. Is there any way to improve the performance? Or any better query to achieve the same result?
I tried doing this and it did not help:
create index if not exists readings_idx on readings (time, device, sensor, resolution);
Best Answer
(Timescale cofounder here)
It sounds like you are trying to perform a "last point" query, e.g., find the latest record submitted by each (device, sensor). The challenge with the query as above is that the database doesn't know when it has found the last device/sensor, so needs to scan back to the start of time to conclude there are no new devices around.
Much better is the following approach, which performs a LATERAL SCAN from a list of devices against the time-series records:
https://docs.timescale.com/latest/using-timescaledb/reading-data#last-point
Good luck, and you might also find our Slack group at https://slack.timescale.com useful for these types of questions.