I have a query I run a few times a day. It goes over historical data and lifts a lot of hourly partitions. The query takes almost 30 seconds to execute on the cold run, and then it executes in below a second once data has been read and it is already into memory.
SELECT
timestamp, symbol,
first(price) AS open,
last(price) AS close,
min(price),
max(price),
sum(amount) AS volume
FROM trades
SAMPLE BY 15m;
I want to avoid the user experience of waiting 30 seconds the first time the query is executed every day (or if it gets executed later in the afternoon and the data has been evicted from memory). I am considering a cronjob to run my query using the API first thing in the morning, so next time it will be a hot execution, but maybe this is overkill. Any other solutions or any params I could fine tune?
The idea of running a cronjob using the API to execute the query and pre-warm memory is a good one. There is a slightly better solution, which is running using the TOUCH statement.
TOUCH will fetch the same columns that we want to read, and will read all the data exactly as we would, but it finish earlier than a SELECT as it does not need to return the results back, so it is lighter for the server, while still pre-loading all the data we need in memory.
Take into account that when we do a TOUCH we don't include any aggregates or sampling, just the list of columns we need to prefetch.
We would use it like this for the query above:
SELECT touch(SELECT timestamp, symbol, price, amount FROM trades );