英文:

Timescale indexing issue (huge load average)

问题

我们正在开发GPS监控系统，并使用Timescale来存储来自我们设备的时间序列数据。

我们有一个"locations"表，用于存储设备的位置数据。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显示有很多状态为"空闲"的请求。

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天手动重新索引表，因为如果不这样做，负载平均值会在某一时刻开始上升，服务器会崩溃。

以下是我们的执行计划(EXPLAIN)：

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,

<details>
<summary>英文:</summary>

We are developing gps monitoring system, and using timescale to store time-series data from our devices

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');
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 was ok for a while, but when the data have reached 5M records, server started to crash. We are getting 40-100 load average on server.
pg_stat shows a lot of requests with ‘idle’ status

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;

everything was ok with another array of imei, but with this one, Postgres doesn’t respond.
We have solved this problem with recreating index:

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

But the problem returns after a few days.
So now we manually reindex the table every ~3days, because if we don't, the load average starts to raise at one moment, and server crashes
Here is our EXPLAIN:

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 version is 14.5
timescale is 2.8.1
we are using timescale/timescaledb:latest-pg14 docker
</details>
# 答案1
**得分**: 2
Timescale [hypertables](https://docs.timescale.com/use-timescale/latest/hypertables/about-hypertables/) 按时间戳分成多个块。默认情况下，每个块包含一周的数据。
当您从超表中选择数据而没有指定时间戳范围时，Timescale 必须在每个块中搜索匹配的数据。这就是您的查询计划所显示的内容。您拥有的块越多，查询计划就变得越不合理。
尝试在 WHERE 子句中附加 `AND dt &gt;= CURRENT_TIMESTAMP - INTERVAL &#39;1 WEEK&#39;`。
如果您必须在没有时间戳范围的情况下按 IMEI 进行搜索，那么超表可能不适用于您的应用程序。
<details>
<summary>英文:</summary>
Timescale [hypertables](https://docs.timescale.com/use-timescale/latest/hypertables/about-hypertables/) are partitioned by timestamp into chunks. By default each chunk contains a week&#39;s worth of data.
When you SELECT data from a hypertable without specifying a timestamp range, Timescale must search every chunk for matching data. That&#39;s what your query plan shows. The more chunks you have the more unreasonable the query plan becomes.
Try appending `AND dt &gt;= CURRENT_TIMESTAMP - INTERVAL &#39;1 WEEK&#39;` to your WHERE clause.
If you must search by IMEI without a timestamp range, a hypertable is not suitable for your application.
</details>