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:
-
Setting
flags = 1inesqlth_1_partitionsprevents this query from running altogether. Unfortunately, this causes graphs to have empty beginnings due to lack of interpolation. -
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!