TimescaleDB version: 2.9.2:
PostgreSQL version: PostgreSQL 15.4:
OS: ubuntu:
Platform:
Hello! We are developing gps monitoring system, and using timescale to store time-series data from our devices, we have database with ~ 100gb of coordinates for last 3 months
We have “locations” table, that stores location data from device. IMEI is device ID. We have created imei-dt index
CREATE TABLE locations (imei BIGINT, dt TIMESTAMP, lat REAL, long REAL, las BOOL, los BOOL, velocity INT, course INT, data VARBIT(128));
SELECT create_hypertable('locations', 'dt', chunk_time_interval => INTERVAL '1 WEEK');
CREATE INDEX ix_imei_dt ON locations (imei, dt DESC);
We are selecting latest locations with array of imei:
SELECT distinct on (imei) * FROM locations WHERE imei IN (…) order by imei, dt desc
Everything is ok with our indexes
But it looks like SkipScan doesn’t work on it:
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=4.54..256633.58 rows=200 width=56) (actual time=47.171..227074.765 rows=1 loops=1)
-> Custom Scan (ConstraintAwareAppend) (cost=4.54..256633.58 rows=261821 width=56) (actual time=47.169..227022.731 rows=114393 loops=1)
Hypertable: locations
Chunks excluded during startup: 0
-> Merge Append (cost=4.54..256633.58 rows=261821 width=56) (actual time=47.168..226920.791 rows=114393 loops=1)
Sort Key: _hyper_1_1_chunk.dt DESC
-> Index Scan using _hyper_1_1_chunk_ix_imei_dt on _hyper_1_1_chunk (cost=0.57..1003.08 rows=977 width=39) (actual time=14.709..14.709 rows=0 loops=1)
Index Cond: ((imei = '352672107560484'::bigint) AND (dt >= (CURRENT_TIMESTAMP - '3 mons'::interval)))
-> Index Scan using _hyper_1_2_chunk_ix_imei_dt on _hyper_1_2_chunk (cost=0.57..5605.80 rows=5500 width=39) (actual time=7.198..7.199 rows=0 loops=1)
Index Cond: ((imei = '352672107560484'::bigint) AND (dt >= (CURRENT_TIMESTAMP - '3 mons'::interval)))
-> Index Scan using _hyper_1_3_chunk_ix_imei_dt on _hyper_1_3_chunk (cost=0.57..8123.50 rows=7983 width=39) (actual time=4.527..4.527 rows=0 loops=1)
Index Cond: ((imei = '352672107560484'::bigint) AND (dt >= (CURRENT_TIMESTAMP - '3 mons'::interval)))
-> Index Scan using _hyper_1_4_chunk_ix_imei_dt on _hyper_1_4_chunk (cost=0.57..7032.36 rows=6903 width=39) (actual time=3.284..3.284 rows=0 loops=1)
Index Cond: ((imei = '352672107560484'::bigint) AND (dt >= (CURRENT_TIMESTAMP - '3 mons'::interval)))
-> Index Scan using _hyper_1_5_chunk_ix_imei_dt on _hyper_1_5_chunk (cost=0.57..65373.67 rows=68455 width=58) (actual time=5.165..5.165 rows=0 loops=1)
Index Cond: ((imei = '352672107560484'::bigint) AND (dt >= (CURRENT_TIMESTAMP - '3 mons'::interval)))
-> Index Scan using _hyper_1_6_chunk_ix_imei_dt on _hyper_1_6_chunk (cost=0.57..77875.51 rows=81547 width=58) (actual time=11.782..109265.300 rows=52718 loops=1)
Index Cond: ((imei = '352672107560484'::bigint) AND (dt >= (CURRENT_TIMESTAMP - '3 mons'::interval)))
-> Index Scan using _hyper_1_7_chunk_ix_imei_dt on _hyper_1_7_chunk (cost=0.57..86008.87 rows=90065 width=58) (actual time=0.065..117523.050 rows=60429 loops=1)
Index Cond: ((imei = '352672107560484'::bigint) AND (dt >= (CURRENT_TIMESTAMP - '3 mons'::interval)))
-> Index Scan using _hyper_1_8_chunk_ix_imei_dt on _hyper_1_8_chunk (cost=0.42..374.25 rows=391 width=58) (actual time=0.420..1.878 rows=1246 loops=1)
Index Cond: ((imei = '352672107560484'::bigint) AND (dt >= (CURRENT_TIMESTAMP - '3 mons'::interval)))
Planning Time: 1.530 ms
Execution Time: 227075.954 ms
(24 rows)
It searches every chunk with Index Scan. Are we doing something wrong?