I have a table named “history” that represents equipment consumption data. In short, each piece of data is composed of a value linked to a station_id (place where we retrieve the information) and a tag_id (represents the type of data). I tried to set up timescaledb in multi-node but my query is slower with timescaledb than without. Here is a kind of query we often make :
with cte as (
SELECT
time_bucket_gapfill(‘15 minutes’, history.time) as bucket,
interpolate(avg(history.value)/max(tags.factor)) as value,
locf(max(history.value)/max(tags.factor)) as index,
max(history.value)/max(tags.factor) as indexO
FROM history
LEFT JOIN tags ON history.tag_id = tags.tag_id AND history.station_id = tags.station_id
WHERE
history.station_id = 16
AND history.tag_id = 6647
AND history.time >= ‘2022-11-07T10:36:10.363Z’
AND history.time <= ‘2023-11-07T10:36:10.363Z’
GROUP BY bucket
ORDER BY bucket
)
select
bucket as bucket,
case
when
(value - lag(value, 1) over (order by bucket)) > -0.1
and lead(value, 1) over (order by bucket) >= value
and lag(value, 1) over (order by bucket) is not null
and lag(index, 1) over (order by bucket) is not null
and lag(index, 1) over (order by bucket) <= index
and lead(index, 1) over (order by bucket) >= index
and (index - lag(index, 1) over (order by bucket)) > -0.1
then
value * 1 - lag(value * 1, 1) over (order by bucket)
else
null
end as valeur,
index,
indexO
from cte