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);