使用 SkipScan 加速 DISTINCT 查询

SkipScan 提高了 DISTINCT 查询的查询速度。它适用于 PostgreSQL 表、Timescale 超表和 Timescale 分布式超表。SkipScan 包含在 TimescaleDB 2.2.1 及更高版本中。

注意

此页面讨论 Timescale Skipscan 功能。SkipScan 目前在标准 PostgreSQL 中不可用。

要查询您的数据库并查找某个项目的最新值，您可以使用 DISTINCT 查询。例如，您可能想要查找您的每项投资的最新股票或加密货币价格。或者您可能有图表和警报，它们重复查询每个设备或服务的最新值。

随着您的表变得越来越大，DISTINCT 查询往往会变慢。这是因为 PostgreSQL 目前没有一个很好的机制来从有序索引中提取唯一值列表。即使您有一个索引与这些查询的确切顺序和列匹配，PostgreSQL 也会扫描整个索引以查找所有唯一值。随着表的增长，此操作会变得越来越慢。

注意

Timescale SkipScan 目前不适用于压缩块。

SkipScan 允许查询从一个有序值增量跳到下一个有序值，而无需读取其间的所有行。在没有此功能的支持下，数据库引擎必须扫描整个有序索引，然后在最后进行去重，这是一个慢得多的过程。

SkipScan 是对 SELECT DISTINCT ON column_name 形式的查询的优化。从概念上讲，SkipScan 是一个常规的 IndexScan，它在索引中跳跃以查找大于当前值的下一个值。

当您发出使用 SkipScan 的查询时，EXPLAIN 输出包含一个新的运算符或节点，它可以从正确排序的索引中快速返回不同的项目。使用 IndexOnly 扫描，PostgreSQL 必须扫描整个索引，但 SkipScan 会在有序索引中增量搜索每个连续的项目。当它找到一个项目时，SkipScan 节点会快速重新启动搜索下一个项目。这是一种在有序索引中查找不同项目的更有效方法。

有关 SkipScan 与常规 DISTINCT 查询的基准测试信息，请参阅 SkipScan 博客文章。

注意

跳过扫描成本基于不同元组与总元组的比率。如果不同元组的数量接近元组总数，则由于其较高的估计成本，不太可能选择跳过扫描。

SkipScan 包含在 TimescaleDB 2.2.1 及更高版本中。本节介绍如何设置数据库索引和查询以使用 SkipScan 节点。

您的索引必须

• 包含 DISTINCT 列作为第一列。
• 是一个 BTREE 索引。
• 与您的查询中使用的 ORDER BY 匹配。

您的查询必须

• 在单个列上使用 DISTINCT 关键字。

如果 DISTINCT 列不是索引的第一列，请确保任何前导列在您的查询中用作约束。这意味着如果您要问诸如“按顺序检索唯一 ID 列表”和“检索每个 ID 的最后一次读取”之类的问题，则您至少需要一个像这样的索引

CREATE INDEX "cpu_customer_tags_id_time_idx" \
ON readings (customer_id, tags_id, time DESC)

复制

正确设置索引后，您应该开始看到 DISTINCT 查询的直接好处。当为您的查询选择 SkipScan 时，EXPLAIN ANALYZE 输出会显示一个或多个 Custom Scan (SkipScan) 节点，如下所示

->  Unique
  ->  Merge Append
    Sort Key: _hyper_8_79_chunk.tags_id, _hyper_8_79_chunk."time" DESC
     ->  Custom Scan (SkipScan) on _hyper_8_79_chunk
      ->  Index Only Scan using _hyper_8_79_chunk_cpu_tags_id_time_idx on _hyper_8_79_chunk
          Index Cond: (tags_id > NULL::integer)
     ->  Custom Scan (SkipScan) on _hyper_8_80_chunk
      ->  Index Only Scan using _hyper_8_80_chunk_cpu_tags_id_time_idx on _hyper_8_80_chunk
         Index Cond: (tags_id > NULL::integer)

复制