Extremely Slow Queries with TimescaleDB with Many Chunks

Ignition
, ,
1

I've been working extensively over the past month to optimize the performance of TimescaleDB with Ignition's historical queries, but I'm encountering significant performance bottlenecks with one particular query.

The problematic query is:

SELECT \"tagid\", \"intvalue\", \"floatvalue\", \"stringvalue\", \"datevalue\", \"t_stamp\", \"dataintegrity\"
FROM sqlth_1_data
WHERE \"tagid\" IN (?) AND \"t_stamp\" < ?
ORDER BY \"t_stamp\" DESC
LIMIT 1;

This query seems designed to retrieve the last known value before a specific window in order to interpolate historical data. However, this approach causes TimescaleDB to decompress all preceding chunks, significantly impacting performance, despite being largely unnecessary.

A potential solution might be to introduce a left boundary in the query condition, something like:

AND \"t_stamp\" > ?

This boundary would ideally represent the maximum expected interval between consecutive data insertions, ensuring the query quickly finds the previous value without scanning all prior chunks.

I've already attempted the following workarounds:

  1. Setting flags = 1 in esqlth_1_partitions prevents this query from running altogether. Unfortunately, this causes graphs to have empty beginnings due to lack of interpolation.

  2. Disabling data interpolation and switching to raw mode also avoids running this query, but this significantly degrades the visual quality of the graphs.

Additionally, I've tried creating a BRIN index on the time column (t_stamp) and a composite index on (tagid, t_stamp), but neither of these indexing strategies provided noticeable improvements.

Optimizing this query is crucial for us, as using TimescaleDB drastically reduces our database size from approximately 150 GB to just around 15 GB, providing excellent performance if not for this specific issue.

Any insights or suggestions from experienced developers or database specialists would be greatly appreciated!

2

@osdcastillo02 have you been able to find a solution to this? I am the start of our journey of implementing a historian and want to use TimescaleDB, but it will be useless if I end up running into the same issue you are experiencing.

3

Hmmm. Missed this. A composite index on (t_stamp, tag_id) should greatly help this case. (Order matters--shows up when asking the DB to explain the query plan.)

4

Thanks for the quick reply @pturmel, I will look at implementing this. Will take some time to actually see if this works since a large amount of historical data is needed.