Query on hypertable runs very slowly on first run only

TimescaleDB and PostgreSQL
1

Hey,

We’ve started a process that basically runs queries throughout the entire hypertable, stepping e.g. 10 days of data at a time.

This has been done in the past, and it was fast enough.

Now for some reason it is horribly slow for a query, but if we re-run the same query later it is lightening fast.

What process(es) can I run to “prepare the whole table” for this process?

For instance, this is such a query:

EXPLAIN
ANALYZE
SELECT
	"sensorId",
	"timestamp",
	"value"
FROM
	"SensorDataPoint"
WHERE
	"aggregationMethodId" = 0
	AND "sensorId" IN (13876, 13877, 19307, 10808, 10810, 10812)
	AND "timestamp" >= '2025-02-01T11:24:00.000Z'
	AND "timestamp" < '2025-02-10T11:24:00.000Z'

And the results are:

"QUERY PLAN"
"Append  (cost=0.56..61693.51 rows=69829 width=20) (actual time=67.876..115385.668 rows=38880 loops=1)"
"  ->  Index Scan using ""_hyper_1_2146_chunk_SensorDataPoint_aggregationMethodId_sensorI"" on _hyper_1_2146_chunk  (cost=0.56..31196.45 rows=35503 width=20) (actual time=67.875..57935.914 rows=19548 loops=1)"
"        Index Cond: ((""aggregationMethodId"" = 0) AND (""sensorId"" = ANY ('{13876,13877,19307,10808,10810,10812}'::integer[])) AND (""timestamp"" >= '2025-02-01 11:24:00+00'::timestamp with time zone) AND (""timestamp"" < '2025-02-10 11:24:00+00'::timestamp with time zone))"
"  ->  Index Scan using ""_hyper_1_2149_chunk_SensorDataPoint_aggregationMethodId_sensorI"" on _hyper_1_2149_chunk  (cost=0.56..30147.91 rows=34326 width=20) (actual time=207.632..57444.029 rows=19332 loops=1)"
"        Index Cond: ((""aggregationMethodId"" = 0) AND (""sensorId"" = ANY ('{13876,13877,19307,10808,10810,10812}'::integer[])) AND (""timestamp"" >= '2025-02-01 11:24:00+00'::timestamp with time zone) AND (""timestamp"" < '2025-02-10 11:24:00+00'::timestamp with time zone))"
"Planning Time: 0.739 ms"
"Execution Time: 115389.752 ms"

Please, help!

2

Hi @birgersp, can you share what version you’re using?

You’re probably using a chunk of one week.
can you run explain (analyze,buffers) <your query> and share the output?

3

Hi,

I just ran this query:

explain
analyze
SELECT
	"sensorId",
	"timestamp",
	"value"
FROM
	"SensorDataPoint"
WHERE
	"aggregationMethodId" = 0
	AND "sensorId" IN (16673, 16672)
	AND "timestamp" >= '2025-03-01T21:59:00.000Z'
	AND "timestamp" < '2025-03-10T06:49:00.000Z'

And these are the results:

"Append  (cost=0.56..19554.73 rows=21600 width=20) (actual time=0.023..40685.172 rows=24100 loops=1)"
"  ->  Index Scan using ""_hyper_1_2158_chunk_SensorDataPoint_aggregationMethodId_sensorI"" on _hyper_1_2158_chunk  (cost=0.56..9630.49 rows=10695 width=20) (actual time=0.022..15647.129 rows=11762 loops=1)"
"        Index Cond: ((""aggregationMethodId"" = 0) AND (""sensorId"" = ANY ('{16673,16672}'::integer[])) AND (""timestamp"" >= '2025-03-01 21:59:00+00'::timestamp with time zone) AND (""timestamp"" < '2025-03-10 06:49:00+00'::timestamp with time zone))"
"  ->  Index Scan using ""_hyper_1_2161_chunk_SensorDataPoint_aggregationMethodId_sensorI"" on _hyper_1_2161_chunk  (cost=0.56..9816.24 rows=10905 width=20) (actual time=41.082..25035.561 rows=12338 loops=1)"
"        Index Cond: ((""aggregationMethodId"" = 0) AND (""sensorId"" = ANY ('{16673,16672}'::integer[])) AND (""timestamp"" >= '2025-03-01 21:59:00+00'::timestamp with time zone) AND (""timestamp"" < '2025-03-10 06:49:00+00'::timestamp with time zone))"
"Planning Time: 0.345 ms"
"Execution Time: 40686.599 ms"

I did a reindex of these two indexes, and then queries (using the same indexes) are very fast again.

That was yesterday. Today, the queries are slow again. What could be the cause of this?

4

Hey @bigersp, can you share more of the data usage? Are you backfilling often? Can you check if there’s any IO contention caused by background jobs?