2025年9月25日: PostgreSQL 18 发布!
支持的版本: 当前 (18) / 17 / 16 / 15 / 14 / 13
开发版本: devel
不支持的版本: 12 / 11 / 10 / 9.6 / 9.5 / 9.4 / 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3

11.4. 索引和 ORDER BY #

除了简单地查找查询要返回的行之外,索引还可以按特定的排序顺序返回这些行。这使得查询的 ORDER BY 规范可以在不进行单独排序步骤的情况下得到满足。在 PostgreSQL 当前支持的索引类型中,只有 B-tree 能够产生排序输出——其他索引类型返回匹配的行时,顺序是不确定的、依赖于实现的。

查询规划器会考虑通过扫描匹配该规范的可用索引,或者通过按物理顺序扫描表并执行显式排序来满足 ORDER BY 规范。对于需要扫描表中大部分行的情况,显式排序可能比使用索引更快,因为它由于顺序访问模式而需要的磁盘 I/O 更少。当只需要获取少量行时,索引更有用。一个重要的特殊情况是 ORDER BYLIMIT n 结合使用:显式排序需要处理所有数据来确定前 n 行,但如果存在匹配 ORDER BY 的索引,则可以直接检索前 n 行,而无需扫描剩余的部分。

默认情况下,B-tree 索引按升序存储其条目,NULL 值排在最后(在条目相等的情况下,表 TID 被用作排序的决定性列)。这意味着对列 x 的索引进行正向扫描会产生满足 ORDER BY x(或更详细地说是 ORDER BY x ASC NULLS LAST)的输出。索引也可以反向扫描,产生满足 ORDER BY x DESC(或更详细地说是 ORDER BY x DESC NULLS FIRST,因为 NULLS FIRSTORDER BY DESC 的默认值)的输出。

您可以通过在创建索引时包含 ASCDESCNULLS FIRST 和/或 NULLS LAST 选项来调整 B-tree 索引的排序顺序;例如:

CREATE INDEX test2_info_nulls_low ON test2 (info NULLS FIRST);
CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);

以升序存储且 NULL 值排在前面的索引,根据扫描方向的不同,可以满足 ORDER BY x ASC NULLS FIRSTORDER BY x DESC NULLS LAST

您可能会问,为什么需要提供所有四种选项,因为两种选项加上反向扫描的可能性就已经涵盖了 ORDER BY 的所有变体。在单列索引中,这些选项确实是冗余的,但在多列索引中它们可能很有用。考虑一个在 (x, y) 上的两列索引:如果我们正向扫描,它可以满足 ORDER BY x, y;如果我们反向扫描,它可以满足 ORDER BY x DESC, y DESC。但是,应用程序可能频繁需要使用 ORDER BY x ASC, y DESC。普通索引无法获得这种排序,但如果索引定义为 (x ASC, y DESC)(x DESC, y ASC),则可以实现。

显然,具有非默认排序顺序的索引是一种相当特殊的特性,但有时它们可以为某些查询带来巨大的速度提升。维护这样一个索引是否值得,取决于您使用需要特殊排序顺序的查询的频率。

提交更正

如果您在文档中发现任何不正确之处、与您在使用该特定功能时的经验不符之处,或者需要进一步说明之处,请使用 此表单 来报告文档问题。