时间戳

首页 > 解决方案 > 如何提高timescaledb获取最后一个时间戳的性能

timescaledb - 如何提高timescaledb获取最后一个时间戳的性能

问题描述

SELECT timeseries_id, "timestamp" FROM enhydris_timeseriesrecord WHERE timeseries_id=6661 ORDER BY "timestamp" DESC LIMIT 1;

（该表包含大约 66m 条记录，timeseries_id=6661 的记录大约为 0.5m。）

此查询大约需要 1-2 秒才能运行，我觉得这太多了。

如果它使用简单的 btree 索引，它应该在大约 30 次迭代后找到它正在寻找的内容。据我所见，当我执行EXPLAIN ANALYZE该查询时，它确实使用了索引，但它必须在每个块中这样做，显然有 1374 个块。

查询如何变得更快？

                 Table "public.enhydris_timeseriesrecord"
    Column     |           Type           | Collation | Nullable | Default 
---------------+--------------------------+-----------+----------+---------
 timeseries_id | integer                  |           | not null | 
 timestamp     | timestamp with time zone |           | not null | 
 value         | double precision         |           |          | 
 flags         | character varying(237)   |           | not null | 
Indexes:
    "enhydris_timeseriesrecord_pk" PRIMARY KEY, btree (timeseries_id, "timestamp")
    "enhydris_timeseriesrecord_timeseries_id_idx" btree (timeseries_id)
    "enhydris_timeseriesrecord_timestamp_idx" btree ("timestamp" DESC)
    "enhydris_timeseriesrecord_timestamp_timeseries_id_idx" btree ("timestamp", timeseries_id)
Foreign-key constraints:
    "enhydris_timeseriesrecord_timeseries_fk" FOREIGN KEY (timeseries_id) REFERENCES enhydris_timeseries(id) DEFERRABLE INITIALLY DEFERRED
Triggers:
    ts_insert_blocker BEFORE INSERT ON enhydris_timeseriesrecord FOR EACH ROW EXECUTE PROCEDURE _timescaledb_internal.insert_blocker()
Number of child tables: 1374 (Use \d+ to list them.)

更新：解释计划

标签： timescaledb

解决方案

数据库必须转到每个块的子索引并检索找到 timeseries_id=x 的最新时间戳。数据库正确使用索引（从解释中可以看出）它对每个块中的每个子索引进行索引扫描，而不是完整扫描。所以它会进行 >1000 次索引扫描。无法修剪任何块，因为规划器无法知道哪些块具有该特定 timeseries_id 的条目。

而且你有 1300 个块，只有 66m 条记录-> 每个块约 50k 行。每个块的行数太少。从 Timescale Docs 他们有以下建议：

选择时间间隔的关键属性是属于最近间隔的块（包括索引）（或块，如果使用空间分区）适合内存。因此，我们通常建议设置间隔，以便这些块包含不超过 25% 的主内存。

https://docs.timescale.com/latest/using-timescaledb/hypertables#best-practices

减少块的数量将显着提高查询性能。

此外，如果您使用 TimescaleDB 压缩，您可能会获得更多的查询性能，这将减少需要扫描的块的数量，您可以按 timeseries_id ( https://docs.timescale.com/latest/api#compression）或者您可以定义一个连续聚合，它将保存每个 timeseries_id 的最后一项（https://docs.timescale.com/latest/api#continuous-aggregates）

推荐阅读