postgresql - 多列索引和 ORDER BY

Question

PostgreSQL documentation说明如果我们在索引为 ... (x ASC, y ASC) 的表上运行查询 ... ORDER BY x ASC, y DESC，索引无法使用，因为方向不匹配。

1. 这是否意味着这个索引完全没有用，或者数据库引擎可以使用索引对 x ASC 部分进行排序(然后手动对 y DESC 部分进行排序)?
2. 如果我们运行查询 ... WHERE x = 999 ORDER BY y DESC，可以使用这个索引吗？

Answer 1

问题 1 的答案。

Postgres 12 或更早版本

No，不能使用索引，如手册所示。您可以通过在任何表上创建这样的索引来验证，然后仅针对测试 session :

SET enable_seqscan = OFF;

然后:

EXPLAIN
SELECT * FROM tbl ORDER BY ORDER BY x, y DESC;

现在，如果索引可以以任何方式使用，那就可以了。但您仍然会看到顺序扫描。

极端情况异常(exception):如果 index-only scan有可能，如果索引比表小得多，它可能仍会被使用。但是行必须从头开始排序。

相关:

• Optimizing queries on a range of timestamps (two columns)

Postgres 13 或更新版本

Postgres 13 添加了“增量排序”，可以通过 GUC 设置 enable_incremental_sort 控制，默认为 on。 The release notes:

If an intermediate query result is known to be sorted by one or moreleading keys of a required sort ordering, the additional sorting canbe done considering only the remaining keys, if the rows are sorted inbatches that have equal leading keys.

13.1 和 13.2 版已修复极端情况问题。因此 - 一如既往 - 请务必运行最新的小版本。

现在，可以使用索引了。您会在 EXPLAIN 计划中看到类似这样的内容:

Sort Key: x, y DESC
Presorted Key: x

它不如具有匹配(切换)排序顺序的索引高效，后者可以直接从索引中读取易于排序的行(根本没有排序步骤)。但这可能是一个巨大的改进，尤其是在 LIMIT 较小的情况下，Postgres 必须根据历史对所有行进行排序。现在它可以查看每个(一组)前导列，仅对这些列进行排序，并在满足 LIMIT 时立即停止。

问题 2 的答案。

是，索引非常适合。

(即使索引有 y ASC 也能工作。它可以向后扫描。在这种情况下，只有 NULL 放置是一个缺点。)

当然，如果 x = 999 是一个稳定的谓词(它总是 999 我们感兴趣)并且不止几行有不同的 x ，然后是 partial index会更有效率:

CREATE INDEX ON tbl (y DESC) WHERE x = 999;

db<> fiddle here - Postgres 10

db<> fiddle here - Postgres 13(第二个演示现在使用增量排序)