-
Incident report
-
Resolution: Unresolved
-
Minor
-
None
-
6.0.29
-
None
-
PostgreSQL 15.6
timescaledb 2.13.1
-
Support backlog
When loading latest data using sub-filter "With data" it works well if amount of items is bellow 950.
We tested with ~900 items and the page loads in 1-2 seconds.
With ~1200 items it initially timed out, but after php/web configuration is updated, it could load in 10-15 minutes.
Main problem is query:
LOG: duration: 928896.744 ms statement: SELECT DISTINCT itemid FROM history_uint WHERE (itemid IN (3898948,4980401,4980402 .......) OR itemid IN (5428917,5428919, ......)) AND clock>1714319213
Query shortened here.
We updated the query to exclude the "OR":
SELECT DISTINCT itemid FROM history_uint WHERE itemid IN (3898948,4980401,4980402,4980403...) AND clock>1714319213
And this executes almost instantly.
This looks like a problem with the specific DB, but when we change the file:
include/db.inc.php
$MAX_NUM_IN = 2000;
(increase form 950)
Now the frontend also loads data very fast, 1-2 seconds for ~1700 items.