我们正在开发GPS监控系统，并使用时间刻度来存储来自我们设备的时间序列数据
我们有“位置”表，存储从设备的位置数据。IMEI是设备ID。我们已经创建了imei-dt索引

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');
CREATE INDEX ix_imei_dt ON locations (imei, dt DESC);

我们正在选择最新的位置与阵列的imei：
SELECT distinct on (imei) * FROM locations WHERE imei IN (…) order by imei, dt desc;
有一段时间一切正常，但当数据达到500万条记录时，服务器开始崩溃。我们在服务器上获得40-100的平均负载。
pg_stat显示了很多状态为'idle'的请求

SELECT distinct on (imei) * FROM locations WHERE imei IN (867232054978003, 867232054980835, 867232054976544, 867232054978474, 867232054980538, 867232054980769, 867232054978268, 867232054980157, 867232054978664, 867232054978102, 867232054980173, 867232054978235, 867232054981015, 867232054981411, 867232054977989, 867232054978367, 867232054977864, 867232054980876, 867232054981544, 867232054981296, 867232054981213) order by imei, dt desc;

在另一个imei数组中一切正常，但在这个数组中，Postgres没有响应。
我们已经通过重新创建索引解决了这个问题：

DROP INDEX ix_imei_dt
CREATE INDEX ix_imei_dt ON locations (imei, dt DESC);

但几天后问题又回来了。
因此，现在我们每隔3天手动重新索引表，因为如果不这样做，平均负载会在某个时刻开始上升，服务器会崩溃
以下是我们的解释：

Unique (cost=9.22…2863.56 rows=331 width=51)
→ Merge Append (cost=9.22…2844.52 rows=7613 width=51)
Sort Key: _hyper_1_4602_chunk.imei, _hyper_1_4602_chunk.dt DESC
→ Custom Scan (SkipScan) on _hyper_1_4602_chunk (cost=0.42…0.42 rows=331 width=41)
→ Index Scan using _hyper_1_4602_chunk_ix_imei_dt on _hyper_1_4602_chunk (cost=0.42…28.70 rows=1 width=41)
Index Cond: (imei = ANY (‘{867232054978003,867232054980835,867232054976544,867232054978474,867232054980538,867232054980769,867232054978268,867232054980157,867232054978664,867232054978102,867232054980173,867232054978235,867232054981015,867232054981411,867232054977989,867232054978367,867232054977864,867232054980876}’::bigint[]))
→ Custom Scan (SkipScan) on _hyper_1_4603_chunk (cost=0.42…0.42 rows=331 width=41)
→ Index Scan using _hyper_1_4603_chunk_ix_imei_dt on _hyper_1_4603_chunk (cost=0.42…28.42 rows=1 width=41)
Index Cond: (imei = ANY (‘{867232054978003,867232054980835,867232054976544,867232054978474,867232054980538,867232054980769,867232054978268,867232054980157,867232054978664,867232054978102,867232054980173,867232054978235,867232054981015,867232054981411,867232054977989,867232054978367,867232054977864,867232054980876}’::bigint[]))
→ Custom Scan (SkipScan) on _hyper_1_4606_chunk (cost=0.29…0.29 rows=331 width=41)
→ Index Scan using _hyper_1_4606_chunk_ix_imei_dt on _hyper_1_4606_chunk (cost=0.29…25.84 rows=1 width=41)
Index Cond: (imei = ANY (‘{867232054978003,867232054980835,867232054976544,867232054978474,867232054980538,867232054980769,867232054978268,867232054980157,867232054978664,867232054978102,867232054980173,867232054978235,867232054981015,867232054981411,867232054977989,867232054978367,867232054977864,867232054980876}’::bigint[]))
→ Custom Scan (SkipScan) on _hyper_1_4607_chunk (cost=0.43…0.43 rows=331 width=42)
→ Index Scan using _hyper_1_4607_chunk_ix_imei_dt on _hyper_1_4607_chunk (cost=0.43…28.74 rows=1 width=42)
Index Cond: (imei = ANY (‘{867232054978003,867232054980835,867232054976544,867232054978474,867232054980538,867232054980769,867232054978268,867232054980157,867232054978664,867232054978102,867232054980173,867232054978235,867232054981015,867232054981411,867232054977989,867232054978367,867232054977864,867232054980876}’::bigint[]))
→ Custom Scan (SkipScan) on _hyper_1_4608_chunk (cost=0.43…433.66 rows=331 width=42)
→ Index Scan using _hyper_1_4608_chunk_ix_imei_dt on _hyper_1_4608_chunk (cost=0.43…1681.96 rows=1911 width=42)
Index Cond: (imei = ANY (‘{867232054978003,867232054980835,867232054976544,867232054978474,867232054980538,867232054980769,867232054978268,867232054980157,867232054978664,867232054978102,867232054980173,867232054978235,867232054981015,867232054981411,867232054977989,867232054978367,867232054977864,867232054980876}’::bigint[]))
→ Custom Scan (SkipScan) on _hyper_1_4609_chunk (cost=0.43…0.43 rows=331 width=42)
→ Index Scan using _hyper_1_4609_chunk_ix_imei_dt on _hyper_1_4609_chunk (cost=0.43…28.43 rows=1 width=42)
Index Cond: (imei = ANY (‘{867232054978003,867232054980835,867232054976544,867232054978474,867232054980538,867232054980769,867232054978268,867232054980157,867232054978664,867232054978102,867232054980173,867232054978235,867232054981015,867232054981411,867232054977989,867232054978367,867232054977864,867232054980876}’::bigint[]))
→ Custom Scan (SkipScan) on _hyper_1_4610_chunk (cost=0.29…0.29 rows=331 width=41)
→ Index Scan using _hyper_1_4610_chunk_ix_imei_dt on _hyper_1_4610_chunk (cost=0.29…23.36 rows=1 width=41)

postgres版本为14.5，时间刻度为2.8.1
我们正在使用timescale/timescaledb：latest-pg 14 docker