Best way to use index on PostgreSQL+Timescale DB

user_1946052 0
Viewed 161

I have a Timescale timeseries table with two time columns: itime and stime corresponding to insert time and source time.

I created the Timescale hypertable on stime because all evaluation will be on the source time. Nevertheless, I also send the most recently stored values to another DB using itime. On a table with about 50 million rows it takes now up to 15 seconds to get the rows that have been inserted in the last e.g. hour because itime is not set in the hypertable.

What would be the best way to cover both applications to have fast responses? Would it be possible and make sense to add a normal index to the column itime?

timescaledb indexing postgresql
1 Answers

Yes, you can just build a standard index on itime, which certainly should help things.

With that setup, when you query for itime, it will run the query on every chunk, although will be able to use the itime index on each chunk to make that fast. Using parallel workers here can help - should be in the standard config of your PG database, but would verify.

One idea: is it the case that itime >= stime always? (I assume source is when an event/thing happens, insert time when added to database, so makes sense that itime always happens after stime?)

In which case one way to potentially reduce query time as well is a little hack:

SELECT * FROM TABLE where X > itime AND X > stime

By adding the extra X > stime, it serves as a "loose constraint" (and is "correct" because X > itime >= stime), but allows the query planner to then not push the query to any chunk < stime. Just an idea.

user_84651660
Related