Hi, I just finished migrating a huge dataset to Timestream and I'm having a problem with query time slowing down dramatically as the table size grew.
During beta testing everything worked fine. Queries were maybe a bit slower than i would have like, at around 2-3 seconds.
However now that we have multiple terabytes of data in the table, queries are very slow at around 7-9 seconds. Those 7-9 seconds eventually go down to around 2 seconds if I keep running the same query. I assume this is because of caching and warming up of resources.
I'm saving the DeviceId as a dimension, and a number from 1-255 as the measure name.
I'm using both of these as predicates, as well as a time range, some other dimensions and a NOT NULL check on the value.
SELECT measure_name, value_double, time, DeviceId, DimensionA, DimensionB
WHERE DeviceId = 'xxxxxxx'
AND measure_name = '112'
AND time BETWEEN TIMESTAMP '2022-06-01 00:00:00' AND TIMESTAMP '2022-06-07 00:00:00'
AND value_double IS NOT NULL
AND DimensionA = '0'
AND DimensionB = '1'
ORDER BY time DESC
LIMIT 1000000000
I'm only being metered for the data I need, so I Timestream shouldn't be scanning unnecessary data.
Yet it seems like Timestream is bad at pruning data quickly, because every query spends a significant amount of time loading the first page, while every following page is retrieved almost immediately.
Do you have any idea how I can speed this up?
We migrated to Timestream from Azure Table Storage, where the queries were virtually instant, and we might have to drop Timestream altogether after months of development because of the query latency.
