Hello. I started to work with a Scada with 27000 tags, that saves them to historian each second. It creates tables of about 20Gb of data and 29Gb timestamp index, for each day. It uses PostgreSql.
I have to do a script to export historics of user selected tags. Sometimes ignition client receives a timeout when executing system.tag.queryTagHistory in my script. I have checked that is PostgreSql who is slow to execute the query I see in the log of ignition (I executed the same query in the pgadmin of postgres). Is a simple query that selects the first occurrence of a tag where timestamp>$3. It seems the problem is when the tag doesn’t have a register for this day. I see that Ignition creates a timestamp index. I think that if it doesn’t found the tag, it travel the whole table where timestamp>$3 to determine it cannot found the tag.
How could I solve it? Maybe creating an index for tagid too?
Thank you.
Since it is PostgreSQL, try converting the t_stamp index to use the BRIN algorithm. The index memory consumption should be dramatically reduced, and therefore faster to scan.
The query is more or less:
SELECT “tadid”, “intvalue” FROM sql_data_20190430 WHERE tagid=$1 AND
t_stamp>$2 ORDER BY t_stamp ASC LIMIT 1
But if I have millions of registers with a timestamp index but the query wants to find the first occurrence of a tagid, don’t you think that the problem is that it have to go over all the registers (in an timestamp ordered manner) checking if is the desired tagid? I think the problem is not to order by t_stamp but find the tagid in those t_stamp ordered registers.
I don’t work with PostgreSQL enough to know its query optimizers, but if you’re willing to do a couple of experiments you can narrow it down quite quickly.
Phil’s suggestion is sane, and I’d try it. If you aren’t getting the results you want from that, use a combination of EXPLAIN and ANALYZE to see what’s going on when you add an index on tagid. Some query optimizers can handle indexes on the two different columns to narrow down your search; some want an index on the two columns combined. Remembering of course that each index you add increases insert/update/delete times and potentially increases memory demands.
(Also check that there isn’t already an index on tagid – it should be there by default)
1 Like
You are right Kathy, the primary key is tagid and timestamp, so there’s and index by default.
Phil, I created a BRIN index on timestamp column, removed the existing b_tree index on timestamp column, and queryHistoryTags got me 10 minutes values with 1 scan second for 1440 tags in few seconds when with original index I got a time out at 5 five minutes.
But I went to pgadmin and it said that BRIN index was not used… So, I’m checking other tables . First I see that queryHistoryTags time outs at 5 minutes. Then I remove the timestamp index and it executes in few seconds! So it is using primary key index. I supose 11Gb timestamp index were lowering the performance. I’m not expert in data bases and it’s the first time I work with PostgreSql, but this behaviour surprises me.
I don’t know if there is an option to avoid ignition creates a timestamp index when creates a new historian table.
Thank you!