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

Question

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 时，它确实使用了索引，但它必须在每个 block 中这样做，显然有 1374 个 block 。

如何让查询变得更快？

                 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.)

更新:EXPLAIN plan

Answer 1

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

而且您只有 6600 万条记录有 1300 个 block -> 每个 block 约 50k 行。每个 block 的行太少。从 Timescale Docs 他们有以下建议:

The key property of choosing the time interval is that the chunk (including indexes) belonging to the most recent interval (or chunks if using space partitions) fit into memory. As such, we typically recommend setting the interval so that these chunk(s) comprise no more than 25% of main memory.

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

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

此外，如果您使用 TimescaleDB 压缩，您可能会获得更高的查询性能，这将进一步减少需要扫描的 block 数，您可以按 timeseries_id ( https://docs.timescale.com/latest/api#compression) 进行分段，或者您可以定义一个连续聚合将保留每个 timeseries_id ( https://docs.timescale.com/latest/api#continuous-aggregates) 的最后一项