Optimize timescale query

Question

I am using the below query with timescaledb to get the 10 minute candles from a ticks database.

SELECT time_bucket('10minute', time) AS min,
 first(ticks, time) AS open,
 last(ticks, time) AS close,
 max(ticks) AS high,
 min(ticks) AS low,
 last(volume, time)-first(volume, time) AS vol
FROM prices
WHERE asset_code = '".$symbol."'
GROUP BY min
ORDER BY min DESC
LIMIT 100

I want to make sure the query doesn't slow down the after some days as the db grows. At any time I want to run this query on ticks from last two days and not the whole table. So I want to know is there a way I can limit the time_bucket query on last 100000 ticks from db.

I am also using PDO for query db.

Trickish

Answer 1

TimescaleDB uses constraint exclusion to eliminate needing to touch chunks when answering a query. We have some work going on right now to extend the query optimization to more intelligently handle some types of LIMIT queries, as in your example, so that even the above will just touch the necessary chunks.

But for now, there's a very easy fix: use a time predicate in the WHERE clause instead of the LIMIT.

In particular, assuming that you typically have a ticker symbol in each 10 minute interval, and you want 100 intervals:

SELECT time_bucket('10 minutes', time) AS min,
  first(ticks, time) AS open,
  ...
FROM prices
WHERE asset_code = '".$symbol."'
  AND time > NOW() - interval '1000 minutes'
GROUP BY min
ORDER BY min DESC

Failsafe