解决 Postgres 规划器“不是很聪明”的案例

Heap 是一个数字洞察平台，可自动捕获 Web 和移动行为，例如页面查看、点击和点击。我们最近发布了 Effort Analysis，这是一种让 Heap 客户查看漏斗内每个步骤之间交互次数和秒数的中位数的方法。它的外观如下： 为了构建此功能，我们需要编写一个查询，该查询可以快速扫描超过 10 亿行的事件数据。但是当我们第一次开始研究这个功能时，扫描数据的时间太长了。幸运的是，我们找到了一种方法，可以将该功能的 p90 性能速度提高一倍。为此，我们必须解决 Postgres 规划器根据 Postgres 文档“不是很聪明”的情况。这个 Postgres 怪癖令人惊讶地阻止了现有索引支持仅索引扫描。这篇文章是关于导致我们的性能问题的怪癖以及我们用来实现 2 倍性能胜利的解决方法。首先，快速回顾一下仅索引扫描。索引是数据库中的辅助数据结构，查询规划器可以使用它来加快某些查询的速度。例如，user_id 列上的索引可以更快地查找特定 user_id。仅索引扫描是一种特殊的操作，在这种操作中，数据库可以仅使用索引中的信息来满足查询，而无需读取表的实际行。例如，堆上的一个典型索引包含一个事件的时间和执行该事件的用户的 user_id：给定这个表和索引，查询计划器将对像 SELECT user_id FROM events WHERE 这样的查询使用仅索引扫描time > now() - '7 days'::interval 因为我们需要的唯一值 user_id 在索引中。对于类似 SELECT * FROM events WHERE time > now() - '7 days'::interval 的稍微不同的查询，查询计划器不能使用仅索引扫描，因为我们需要不在此索引中的列。规划器可能 [1] 使用索引扫描代替。

顾名思义，索引扫描会扫描索引以查找满足查询谓词的行。但是，它还从表本身读取数据页，以返回和/或对不在索引中的值执行计算。这个额外的步骤通常会使索引扫描比仅索引扫描慢。我们的许多客户每周记录超过 1 亿个事件，这意味着 90 天的漏斗需要扫描超过 10 亿行。结果，查询的 p90 大约为 20 秒，比我们最初启动时想要的要慢得多。这是支持 Effort Analysis 的初始查询的简化版本： 最初，支持 Effort Analysis 的查询不使用仅索引扫描。这让我们感到惊讶，因为我们有一个如下所示的索引： 这个索引没有产生仅索引扫描的原因是因为 Postgres 的一个令人惊讶的怪癖。这是发生的事情：虽然 data ->> 'type' 在索引中，查询计划器认为它也需要在索引中包含数据，即使数据本身从未在查询中被引用，除了作为查询的一部分数据 ->> '类型' 表达式。如果这对计划者来说似乎不是一种聪明的工作方式，Postgres 文档同意您的看法：只要查询在列上使用函数或运算符，就会出现此问题。 （运算符只是函数的语法糖。[2]）如果您的查询引用 f(x)，则必须在索引中包含 f(x) 和 x 才能获得仅索引扫描。这不是一个随机的黑客攻击；这是 Postgres 文档中针对此问题的建议解决方法。然而，在我们的例子中，x 是数据，并且因为数据包含与事件相关的大部分数据（例如，类型、事件发生的页面 url 等），[3] 这种变通方法会导致无法接受的大索引。

我们刚刚看到，如果您的查询包含一列作为函数参数或操作数，那么该列是否在索引中并不重要；您仍然不会获得仅索引扫描。但是，如果您的列仅用于与部分索引的谓词匹配的谓词中，则规划器足够聪明，可以意识到它不需要访问表来过滤结果，即使您的列是一个函数参数或操作数。 [4] 这是我们为提高性能而采用的解决方法。为了更好地理解它，让我们看看它如何应用于我们上面的示例查询。请注意，在我们的示例查询中，我们仅在查询的 WHERE 子句中引用了数据 ->> 'type'。我们实际上并没有返回数据的值 ->> 'type'。因此，Postgres 需要访问该表的唯一原因是确保 data ->> 'type' IN ('click', 'change', 'touch') 对于返回结果的每一行都为真，但由于部分索引是如何构建的，这次访问是没有必要的。作为复习，部分索引仅包含与谓词匹配的行的条目。例如，这个部分索引只包含数据 ->> 'type' IN ('click', 'change', 'touch') 的条目：因为 Postgres 知道 ea_index 只有数据 ->> 'type' IN ( 'click', 'change', 'touch')，这样的查询：SELECT time FROM funnel_events WHERE data ->> 'type' IN ('click', 'change', 'touch') 将导致仅索引扫描。我们的示例查询引用了 step_num、time、num_actions、time_engaged 和 data 列。 step_num、time、num_actions和time_engaged都在索引中，data只在where子句中通过data ->> 'type' IN ('click', 'change')引用。因为查询谓词匹配部分索引谓词，所以规划器知道索引只包含满足查询谓词的值，不需要访问表来过滤扫描索引的结果。因此，我们最终对示例查询进行了仅索引扫描。此变通方法使 Effort Analysis 的 p90 性能提高了 2 倍，并且还使我们的 p70 和 p50 查询速度更快：只有在查看这些结果后，我们才完全了解导致此性能问题的怪癖以及解决该问题的变通方法。从某种意义上说，我们很幸运。我们希望通过分享这些 Postgres 事实，其他团队可以更加慎重地提高他们的查询性能。

如果您喜欢对优化 Postgres 和/或 citus 查询感兴趣，请在 Twitter @philosohacker 上@-me，并且如果您在当前的工作中没有足够的机会优化查询，我们正在招聘！查看我们的团队和空缺职位。 [1] Postgres 使用索引扫描还是顺序扫描取决于它预计需要从表中获取的行的百分比。 [3] 我们知道严重依赖 JSONB 列有很多缺点。