Problematic Historian Query with TimescaleDB

Ignition
1

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?

2

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.

3

I think that if you manually set the flags column in sqlth_partitions to 1, we won't issue that query anymore?

https://docs.inductiveautomation.com/display/DOC81/Ignition+Database+Table+Reference#IgnitionDatabaseTableReference-sqlth_partitions

Not sure what else might be affected by that, though.

4

You have to issue that query. That's the one that returns the actual data.

5

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.

6

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.

7

I put 1 in flags now the queries are really fast with timescaledb but i have choppy graphics do u know why? Thanks

8

I believe it should skip the hypertable's partitions that don't match the where clause and start with the first one that has a time range above the timestamp passed as parameter, and find the first record where the tagid matches, then return.
From the query browser, it looks like one giant table, but it does have partitions. Assuming things were setup properly.

9

For some reason setting flags = 1 in sqlth_1_partitions made my queries with timescaledb improve from 5 seconds to less than 1 second, but I want to understand why and if there is another way to implement that leaving it as null, since my vision graphs get a little choppy when zooming in setting flags to 1