tldr: With PostgreSQL, changing the historian query for multiple tags to use IN (tagid1, tagid2, …) instead of multiple OR s speeds up the query execution 320 times. Is it possible to change the query to use IN (…)?
We use Ignition 7.9 with PostgreSQL 12 for SQLTag history. Performance is great with 1 tag queried. As soon as we request history for more than 1 tag, performance drops to the floor.
While debugging, we noticed that Ignition issues the following query for tag history:
SELECT "tagid", "intvalue", "floatvalue", "stringvalue", "datevalue", "t_stamp", "dataintegrity" FROM sqlth_1_data WHERE "t_stamp" >= 1580680800000 AND "t_stamp" <= 1581026400000 AND ( "tagid" = 14568 OR "tagid" = 14571 OR "tagid" = 14572 ) ORDER BY "t_stamp" ASC, "tagid" ASC
This query takes 41 seconds on a test system.
Changing the query to use IN instead of OR reduces the query time to 128ms.
---snip WHERE "t_stamp" >= 1580680800000 AND "t_stamp" <= 1581026400000 AND ( "tagid" IN ( 14568, 14571, 14572 ) )
The queries produce the same result. The first one using OR uses a sequential scan, the second using IN uses an index scan.
There may be ways to convince PostgreSQL to use a different query strategy. However, it seems simpler to me to change the query filter that Ignition uses with PostgreSQL databases to use IN instead of OR. See also Avoid OR for better PostgreSQL query performance - Cybertec
My question: Is it possible to change the query that Ignition uses with PostgreSQL databases? Do you have another suggestion?
Note: We use Timescaledb. However, we have observed the same behaviour with standard PostgreSQL.