On our database bellow is SELECT statement that consumes majority of time in our database, because SQL is run few 10-million times (with different company, device and time values). I am looking into this SQL how reduce execution time and to speed it up.
SELECT
"some columns"
FROM
mytable
WHERE
company = 1 AND
device = '1-123' AND
time > '2025-02-10' AND
time <= '2025-02-11'
Note: “some columns” in query are plenty of columns, but for this forum question WHERE condition is only relevant.
“mytable” hypertable is partitioned by time and chunk time interval is 86400000000 microseconds = 1 day. From two where conditions we can see time contains exactly one day.
I have performed EXPLAIN of above query and I get the following:
Result (cost=0.57..71.93 rows=121 width=178) (actual time=1.625..4.537 rows=96 loops=1)
Buffers: shared hit=8 read=11
-> Custom Scan (ChunkAppend) on mytable (cost=0.57..69.51 rows=121 width=50) (actual time=1.621..4.484 rows=96 loops=1)
Order: mytable.time
Startup Exclusion: false
Runtime Exclusion: false
Buffers: shared hit=8 read=11
-> Index Scan Backward using _hyper_21_37199_chunk_mytable_i on _timescaledb_internal._hyper_21_37199_chunk (cost=0.57..4.39 rows=4 width=50) (actual time=1.620..1.622 rows=3 loops=1)
Index Cond: ((_hyper_21_37199_chunk.company = 4) AND ((_hyper_21_37199_chunk.device)::text = '4-64958'::text) AND (_hyper_21_37199_chunk.time > '2025-02-10 00:00:00+01'::timestamp with time zone) AND (_hyper_21_37199_chunk.time <= '2025-02-11 00:00:00+01'::timestamp with time zone))
Buffers: shared hit=5 read=4
-> Index Scan Backward using _hyper_21_37200_chunk_mytable_i on _timescaledb_internal._hyper_21_37200_chunk (cost=0.57..65.11 rows=117 width=50) (actual time=1.624..2.853 rows=93 loops=1)
Index Cond: ((_hyper_21_37200_chunk.company = 4) AND ((_hyper_21_37200_chunk.device)::text = '4-64958'::text) AND (_hyper_21_37200_chunk.time > '2025-02-10 00:00:00+01'::timestamp with time zone) AND (_hyper_21_37200_chunk.time <= '2025-02-11 00:00:00+01'::timestamp with time zone))
Buffers: shared hit=3 read=7
If we read explain from bottom up, we see there are two “Index Scan” nodes. Total time of first Index Scan is “1.622” and second is “1.624”. Now I was wondering why are there two chunks read by database if we have chunk time interval set to 1 day. I expected one day data to fit into single chunk.
Lets check what is in first chunk:
select distinct time from _timescaledb_internal._hyper_21_37199_chunk
where
(
(_hyper_21_37199_chunk.company = 1)
AND ((_hyper_21_37199_chunk.device)::text = '1-123'::text)
AND (_hyper_21_37199_chunk.casovna_znacka > '2025-02-10 00:00:00+01'::timestamp with time zone)
AND (_hyper_21_37199_chunk.casovna_znacka <= '2025-02-11 00:00:00+01'::timestamp with time zone)
);
and the result is:
time
-----------------------
2025-02-10 00:15:00+01
2025-02-10 00:30:00+01
2025-02-10 00:45:00+01
Each device stored data into table every 15 min. This leads to 96 records per device per company per day. But three rows are in first chunk and the rest 93 records are in second chunk (second “Index Scan”).
This is little bit strange… but because our time is UTC+1 (see from above output), it looks like that TimescaleDB does not saves each day into its own chunk according to our local time, but instead on UTC time.
QUESTION 1: Is there some way I can set that chunk time interval set to 24 hours is set according to my local time and not UTC? I except in this case we could trim down quite some time and because query is run few 10-million times few hours per few days could be cut off.
Some comment and/or suggestion is much appreciated.
My system:
- TimescaleDB v2.18.2
- PostgreSQL v15
- Red Hat 9
- x86_64
- self-hosted on prem