[feature-256]PostgreSQL history query performance

Ignition
,
1

Hello,

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?

Thank you.

Query plans:
First query:

image
image839×667 33.2 KB

Second query:

image
image839×667 31.5 KB

Note: We use Timescaledb. However, we have observed the same behaviour with standard PostgreSQL.

2

It’s not really possible for you to change this, short of a custom module that provides its own historian implementation. On the other hand, this should be pretty trivial to implement in our codebase, and compatibility with the IN operator looks good. I’ll make a ticket internally to see about implementing this change.

1 Like
3

Thanks Paul,

I would really appreciate it if this could be ported to 7.9 and 8.x.

We have a lot of explaining to do for the long loading times :slight_smile:

If I can help with testing please let me know.

4

We have identified a solution for this problem using the following approach (rewrite query):

  • put pgbouncer with the routing-rewrite patch (https://github.com/awslabs/pgbouncer-rr-patch) in a VM (due to running on Windows)
  • use a regex to capture and rewrite the query
  • point ignition historian to the pgbouncer VM which points to PostgreSQL running on Windows

This is fragile, hacked and it introduces a SPOF for the historian system.

Please update this thread if you have an approximate timeframe for implementing this feature for 7.9 and 8.x

Thank you!

5

This makes me very nervous given that we have a customer that wants to switch from MySQL to PostgreSQL.
@pturmel do you know anything about getting around this performance issue?

6

Not particularly. My customers (and I) make far less use of the tag historian than most.

7

@PGriffith Has this been implemented now? It seems I can find some queries that use an IN instead of an OR clause.

8

It looks like the updates for this were released in 8.1.2.

1 Like