postgresql - 使用 order 和 limit 子句的慢查询，但前提是没有记录

Question

我正在运行以下查询:

SELECT * FROM foo WHERE name = 'Bob' ORDER BY address DESC LIMIT 25 OFFSET 1

因为我在表中有 name = 'Bob' 的记录，所以在 10M 记录的表上查询时间很快(<.5 秒)

但是，如果我搜索 name = 'Susan'，则查询需要超过 45 秒。我在 name = 'Susan' 的表中没有记录。

我对每个姓名和地址都有一个索引。我清理了表，对其进行了分析，甚至尝试重写查询:

SELECT * FROM (SELECT * FROM foo WHERE name = 'Bob' ORDER BY address DESC) f LIMIT 25 OFFSET 1

找不到任何解决方案。我不太确定如何进行。请注意这不同于 this post因为我的慢只发生在没有记录的时候。

编辑:如果我取出 ORDER BY 地址然后它运行得很快。显然，我在那里需要它。我试过重写它(没有成功):

SELECT * FROM (SELECT * FROM foo WHERE name = 'Bob') f ORDER BY address DESC LIMIT 25 OFFSET 1

Answer 1

检查 execution plan查看正在使用哪个索引。在这种情况下，名称和地址的单独索引是不够的。您应该为这个查询创建一个名称的组合索引，然后是地址。

将索引视为系统维护的某些列的副本，其顺序与原始顺序不同。在这种情况下，您希望首先按名称查找匹配项，然后在地址上进行决胜局，然后进行直到您有足够的名称匹配项或用完名称匹配项为止。

通过在多列索引中使名称在前，索引将按名称在前排序。然后地址将作为我们的决胜局。

---

在原始索引下，如果选择了地址索引，则查询速度将根据找到匹配项的速度而变化。

计划(英文)是:遍历碰巧已经按地址排序的所有行，丢弃任何与名称不匹配的行，继续进行直到我们有足够的。

因此，如果您没有找到 25 个匹配项，您将阅读整个表格!

使用我提议的多列索引，计划(英文)将是:继续遍历所有名称匹配的行，这些行恰好已经按地址排序。从第一个开始，直到你吃饱为止。如果你用完了，就停下来。