使用 SkipScan 更快地执行 DISTINCT 查询

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

注意

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

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

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

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

SkipScan 是对 SELECT DISTINCT ON column_name 形式查询的优化。从概念上讲，SkipScan 是一种常规的索引扫描，它会跳过索引，寻找大于当前值的下一个值。

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

有关 SkipScan 与常规 DISTINCT 查询的性能比较信息，请参阅SkipScan 博客文章。

注意

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

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)

复制