I'm running Ignition with TimescaleDB at a customer's site and all in all the performance has been good. We're starting to get more data in the system (we're at about 100GB now) and performance is definitely getting noticeably slower when viewing trends.
When I check the database status, Ignition seems to be issueing the following query:
SELECT "tagid","intvalue","floatvalue","stringvalue","datevalue","t_stamp","dataintegrity" FROM sqlth_1_data WHERE "tagid" IN (?,?) and "t_stamp" > ? ORDER BY "t_stamp" ASC LIMIT 1
I can see how this query would be ok if we were checking a single partition managed by Ignition it's self, but when using TimescaleDB (which only looks like a single partition) this query will end up scanning all rows for each tag id for the entire history of the database.
What is this query for and are there any settings on trends/history queries to disable it?
That query gets realtime data for a trend. If you disable it, you get no data. It is different from a historical query only in that it doesn't have a condition for
t_stamp clipping the end of the time frame.
You almost certainly need a BRIN index on your t_stamp column.
I think that if you manually set the
flags column in
sqlth_partitions to 1, we won't issue that query anymore?
Not sure what else might be affected by that, though.
You have to issue that query. That's the one that returns the actual data.
No I think this is a "seeding" query as Paul said actually. There's a "LIMIT 1" on the end of it there. It seems like fetching the actual data is happening pretty quickly.
Ah, missed the limit clause. I wouldn't expect it to matter if the indices are correct, and without the indices, you are toast anyways.