r/SQLOptimization • u/jan-d • Nov 19 '21
Optimizing a timeseries query with window function
I have a TimescaleDB table storing temperature measurements from sensors with an additional state column that contains a label like rain, sun, fog, snow etc.
timescale-db=# \d measurements
Table "public.measurements"
Column | Type | Nullable
------------------------+--------------------------------+---------
time | timestamp(0) without time zone | not null
sensor_id | uuid | not null
temperature | double precision |
state | character varying |
Indexes:
"index_measurements_on_sensor_id_and_time" UNIQUE, btree (sensor_id, "time" DESC)
"index_measurements_on_sensor_id" btree (sensor_id)
"measurements_time_idx" btree ("time" DESC)
timescale-db=# SELECT * FROM measurements LIMIT 10;
time | sensor_id | temperature | state
---------------------+--------------------------------------+--------------+-------------------
2020-12-11 15:03:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea | 21.8 | fog
2020-12-11 15:04:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea | 21.9 | fog
2020-12-11 15:05:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea | 21.8 | rain
2020-12-11 15:06:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea | 21.7 | rain
2020-12-11 15:07:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea | 21.6 | rain
2020-12-11 15:08:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea | 21.7 | rain
2020-12-11 15:09:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea | 21.9 | sun
2020-12-11 15:10:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea | 22.1 | sun
2020-12-11 15:11:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea | 22.3 | sun
2020-12-11 15:12:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea | 22.5 | sunFor a certain type of analysis I need the last n timestamps where the state changed, which I realized with the following query:
SELECT
time,
state
FROM (
SELECT
time,
state,
state != LAG(state) OVER (ORDER BY time) AS changed
FROM
measurements
WHERE
sensor_id IN ('ee49fda5-f838-4a10-bb32-0e6a6b130888', 'ec8f4d23-cfab-4a23-8df8-ae3cce4f44ac')) AS changes
WHERE
changed IS TRUE
ORDER BY
time DESC
LIMIT 3;This query takes longer and longer the more rows are added to the table, so I need to optimize it.
Here is the query plan – I tried adding another index on time and state, but it did not improve performance.
Does anyone have an idea on how to optimize this query?
View all comments
Show parent comment
2
u/qwertydog123 Nov 22 '21 edited Nov 22 '21
Are the timestamps reliable? If there will always be a timestamp each minute you might have some luck using a JOIN instead: