# Extremely Slow Queries with TimescaleDB with Many Chunks

**URL:** <https://forum.inductiveautomation.com/t/extremely-slow-queries-with-timescaledb-with-many-chunks/106233>\
**Category:** Ignition\
**Tags:** database, vision, perspective\
**Created:** [May 13, 2025, 10:33pm UTC](https://forum.inductiveautomation.com/t/extremely-slow-queries-with-timescaledb-with-many-chunks/106233 "2025-05-13T22:33:27Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![osdcastillo02](https://avatars.discourse-cdn.com/v4/letter/o/c6cbf5/32.png) [@osdcastillo02](https://forum.inductiveautomation.com/u/osdcastillo02)\
**Post date:** [May 13, 2025, 10:33pm UTC](https://forum.inductiveautomation.com/t/extremely-slow-queries-with-timescaledb-with-many-chunks/106233/1 "2025-05-13T22:33:28Z")

</div>

I've been working extensively over the past month to optimize the performance of TimescaleDB with Ignition's historical queries, but I'm encountering significant performance bottlenecks with one particular query.

The problematic query is:

```sql
SELECT \"tagid\", \"intvalue\", \"floatvalue\", \"stringvalue\", \"datevalue\", \"t_stamp\", \"dataintegrity\"
FROM sqlth_1_data
WHERE \"tagid\" IN (?) AND \"t_stamp\" < ?
ORDER BY \"t_stamp\" DESC
LIMIT 1;

```

This query seems designed to retrieve the last known value before a specific window in order to interpolate historical data. However, this approach causes TimescaleDB to decompress all preceding chunks, significantly impacting performance, despite being largely unnecessary.

A potential solution might be to introduce a left boundary in the query condition, something like:

```sql
AND \"t_stamp\" > ?

```

This boundary would ideally represent the maximum expected interval between consecutive data insertions, ensuring the query quickly finds the previous value without scanning all prior chunks.

I've already attempted the following workarounds:

1. Setting `flags = 1` in `esqlth_1_partitions` prevents this query from running altogether. Unfortunately, this causes graphs to have empty beginnings due to lack of interpolation.

2. Disabling data interpolation and switching to raw mode also avoids running this query, but this significantly degrades the visual quality of the graphs.

Additionally, I've tried creating a BRIN index on the time column (`t_stamp`) and a composite index on (`tagid`, `t_stamp`), but neither of these indexing strategies provided noticeable improvements.

Optimizing this query is crucial for us, as using TimescaleDB drastically reduces our database size from approximately 150 GB to just around 15 GB, providing excellent performance if not for this specific issue.

Any insights or suggestions from experienced developers or database specialists would be greatly appreciated!

---

<div class="post-metadata">

**Author:** ![hventer23](https://avatars.discourse-cdn.com/v4/letter/h/bbce88/32.png) [@hventer23](https://forum.inductiveautomation.com/u/hventer23)\
**Post date:** [March 17, 2026, 9:14am UTC](https://forum.inductiveautomation.com/t/extremely-slow-queries-with-timescaledb-with-many-chunks/106233/2 "2026-03-17T09:14:53Z")

</div>

@osdcastillo02 have you been able to find a solution to this? I am the start of our journey of implementing a historian and want to use TimescaleDB, but it will be useless if I end up running into the same issue you are experiencing.

---

<div class="post-metadata">

**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:** [March 17, 2026, 12:17pm UTC](https://forum.inductiveautomation.com/t/extremely-slow-queries-with-timescaledb-with-many-chunks/106233/3 "2026-03-17T12:17:34Z")

</div>

Hmmm. Missed this. A composite index on `(t_stamp, tag_id)` should greatly help this case. (Order matters--shows up when asking the DB to explain the query plan.)

---

<div class="post-metadata">

**Author:** ![hventer23](https://avatars.discourse-cdn.com/v4/letter/h/bbce88/32.png) [@hventer23](https://forum.inductiveautomation.com/u/hventer23)\
**Post date:** [March 17, 2026, 12:35pm UTC](https://forum.inductiveautomation.com/t/extremely-slow-queries-with-timescaledb-with-many-chunks/106233/4 "2026-03-17T12:35:50Z")

</div>

Thanks for the quick reply @pturmel, I will look at implementing this. Will take some time to actually see if this works since a large amount of historical data is needed.