# Problematic Historian Query with TimescaleDB **URL:** \ **Category:** Ignition\ **Created:** [January 18, 2023, 10:10pm UTC](https://forum.inductiveautomation.com/t/problematic-historian-query-with-timescaledb/68760 "2023-01-18T22:10:35Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![cody.tamaki](https://avatars.discourse-cdn.com/v4/letter/c/b487fb/32.png) [@cody.tamaki](https://forum.inductiveautomation.com/u/cody.tamaki)\ **Post date:** [January 18, 2023, 10:10pm UTC](https://forum.inductiveautomation.com/t/problematic-historian-query-with-timescaledb/68760/1 "2023-01-18T22:10:36Z")
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: ```auto 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? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [January 18, 2023, 10:35pm UTC](https://forum.inductiveautomation.com/t/problematic-historian-query-with-timescaledb/68760/2 "2023-01-18T22:35:31Z")
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. ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [January 18, 2023, 10:55pm UTC](https://forum.inductiveautomation.com/t/problematic-historian-query-with-timescaledb/68760/3 "2023-01-18T22:55:40Z")
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](https://docs.inductiveautomation.com/display/DOC81/Ignition+Database+Table+Reference#IgnitionDatabaseTableReference-sqlth_partitions) Not sure what else might be affected by that, though. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [January 18, 2023, 10:59pm UTC](https://forum.inductiveautomation.com/t/problematic-historian-query-with-timescaledb/68760/4 "2023-01-18T22:59:38Z")
> [@paul-griffith](#): > > we won't issue that query anymore? You have to issue that query. That's the one that returns the actual data. ---
**Author:** ![cody.tamaki](https://avatars.discourse-cdn.com/v4/letter/c/b487fb/32.png) [@cody.tamaki](https://forum.inductiveautomation.com/u/cody.tamaki)\ **Post date:** [January 18, 2023, 11:04pm UTC](https://forum.inductiveautomation.com/t/problematic-historian-query-with-timescaledb/68760/5 "2023-01-18T23:04:08Z")
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. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [January 18, 2023, 11:10pm UTC](https://forum.inductiveautomation.com/t/problematic-historian-query-with-timescaledb/68760/6 "2023-01-18T23:10:07Z")
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. ---
**Author:** ![osdcastillo02](https://avatars.discourse-cdn.com/v4/letter/o/c6cbf5/32.png) [@osdcastillo02](https://forum.inductiveautomation.com/u/osdcastillo02)\ **Post date:** [May 7, 2025, 3:47pm UTC](https://forum.inductiveautomation.com/t/problematic-historian-query-with-timescaledb/68760/7 "2025-05-07T15:47:50Z")
> [@pturmel](#): > > o I put 1 in flags now the queries are really fast with timescaledb but i have choppy graphics do u know why? Thanks ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [May 7, 2025, 6:17pm UTC](https://forum.inductiveautomation.com/t/problematic-historian-query-with-timescaledb/68760/8 "2025-05-07T18:17:03Z")
> [@cody.tamaki](#): > > 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. 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. ---
**Author:** ![osdcastillo02](https://avatars.discourse-cdn.com/v4/letter/o/c6cbf5/32.png) [@osdcastillo02](https://forum.inductiveautomation.com/u/osdcastillo02)\ **Post date:** [May 8, 2025, 4:12pm UTC](https://forum.inductiveautomation.com/t/problematic-historian-query-with-timescaledb/68760/9 "2025-05-08T16:12:13Z")
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