sql - 没有 WHERE 只有 ORDER BY 和 LIMIT 的 PostgreSQL 查询不使用索引

Question

我有一个表，其中包含 BIGSERIAL 类型的“id”列。我也有这一列的索引(降序排序，BTREE，唯一)。

我经常需要从包含数百万个条目的表中检索最后 10、20、30 个条目，如下所示:

SELECT * FROM table ORDER BY id DESC LIMIT 10

我本以为这是一个非常明确的案例:这个特定字段有一个索引，排序顺序匹配，与整个表中的数百万个条目相比，我只需要 10 个条目，这个查询肯定使用索引扫描。

但它不会对整个表进行顺序扫描。

我尝试深入挖掘，没有发现任何异常。 Postgres 文档位于 https://www.postgresql.org/docs/9.6/static/indexes-ordering.html说:

An important special case is ORDER BY in combination with LIMIT n: an explicit sort will have to process all the data to identify the first n rows, but if there is an index matching the ORDER BY, the first n rows can be retrieved directly, without scanning the remainder at all.

但是还是不行。有人对我有任何指示吗？也许我只是再也看不到树上的阿甘了……:-(

Answer 1

好吧，大声说出来并尝试收集更多信息以放入我的问题显然让我再次看到了福雷斯特，我发现了真正的问题。在我上面提到的文档的下方是这句话:

An index stored in ascending order with nulls first can satisfy either ORDER BY x ASC NULLS FIRST or ORDER BY x DESC NULLS LAST depending on which direction it is scanned in.

这就是问题所在。我在索引中指定了排序顺序，但我忽略了 NULLS FIRST 与 LAST。

如果你没有在你的查询中明确提到它，Postgres 默认是 NULLS FIRST。所以 Postgres 发现的是 ORDER BY DESC NULLS FIRST 的组合，它没有被我的索引覆盖。重要的是 SORT ORDER 和 NULLS 的组合。

两种可能的解决方案:

• 在查询中相应地提及 NULLS FIRST/LAST 以匹配索引
• ...或将索引更改为 NULLS FIRST(这是我所做的)

现在 Postgres 正在执行适当的索引扫描，并且在查询期间只触及 10 个元素，而不是全部。