Postgresql 中文操作指南

11.4. Indexes and ORDER BY #

除了查找要由查询返回的行外，索引还能够按特定的排序顺序交付这些行。这允许查询的 ORDER BY 规范在无需单独排序步骤的情况下得到满足。在 PostgreSQL 当前支持的索引类型中，只有 B 树可以生成排序输出——其他索引类型按未指定的基于实现的顺序返回匹配的行。

In addition to simply finding the rows to be returned by a query, an index may be able to deliver them in a specific sorted order. This allows a query’s ORDER BY specification to be honored without a separate sorting step. Of the index types currently supported by PostgreSQL, only B-tree can produce sorted output — the other index types return matching rows in an unspecified, implementation-dependent order.

规划程序将通过扫描与规范匹配的可用索引或按物理顺序扫描表并执行显式排序来考虑满足 ORDER BY 规范。对于需要扫描表中大部分内容的查询，显式排序可能比使用索引更快，因为按照顺序访问模式，它需要的磁盘 I/O 较少。当只需要获取几行时，索引更有用。一个重要的特例是 ORDER BY 与 LIMIT n 相结合：显式排序必须处理所有数据才能识别前 n 行，但如果存在与 ORDER BY 匹配的索引，则前 n 行可直接检索，根本无需扫描其余部分。

The planner will consider satisfying an ORDER BY specification either by scanning an available index that matches the specification, or by scanning the table in physical order and doing an explicit sort. For a query that requires scanning a large fraction of the table, an explicit sort is likely to be faster than using an index because it requires less disk I/O due to following a sequential access pattern. Indexes are more useful when only a few rows need be fetched. 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.

默认情况下，B 树索引按升序存储其条目，空值排在最后（表 TID 被视为相等条目之间的差异器列）。这意味着在 x 列上对索引进行正向扫描会生成满足 ORDER BY x 的输出（或更详细的说法，ORDER BY x ASC NULLS LAST）。索引还可以向后扫描，生成满足 ORDER BY x DESC（或更详细的说法，ORDER BY x DESC NULLS FIRST，因为 NULLS FIRST 是 ORDER BY DESC 的默认值）的输出。

By default, B-tree indexes store their entries in ascending order with nulls last (table TID is treated as a tiebreaker column among otherwise equal entries). This means that a forward scan of an index on column x produces output satisfying ORDER BY x (or more verbosely, ORDER BY x ASC NULLS LAST). The index can also be scanned backward, producing output satisfying ORDER BY x DESC (or more verbosely, ORDER BY x DESC NULLS FIRST, since NULLS FIRST is the default for ORDER BY DESC).

您可以在创建索引时通过包含选项 ASC、DESC、NULLS FIRST 和/或 NULLS LAST 来调整 B 树索引的排序；例如：

You can adjust the ordering of a B-tree index by including the options ASC, DESC, NULLS FIRST, and/or NULLS LAST when creating the index; for example:

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

按升序存储、空值排在开头的索引可以满足 ORDER BY x ASC NULLS FIRST 或 ORDER BY x DESC NULLS LAST，具体取决于扫描方向。

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.

您可能会好奇，为什么提供所有四个选项，当两个选项与向后扫描的可能性一起可以使用时，它将涵盖 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)，则可以。

You might wonder why bother providing all four options, when two options together with the possibility of backward scan would cover all the variants of ORDER BY. In single-column indexes the options are indeed redundant, but in multicolumn indexes they can be useful. Consider a two-column index on (x, y): this can satisfy ORDER BY x, y if we scan forward, or ORDER BY x DESC, y DESC if we scan backward. But it might be that the application frequently needs to use ORDER BY x ASC, y DESC. There is no way to get that ordering from a plain index, but it is possible if the index is defined as (x ASC, y DESC) or (x DESC, y ASC).

显然，具有非默认排序顺序的索引是一种相当专门的功能，但有时它们可以为特定查询带来极大的速度提升。是否值得维护此类索引取决于您使用需要特殊排序顺序的查询的频率。

Obviously, indexes with non-default sort orderings are a fairly specialized feature, but sometimes they can produce tremendous speedups for certain queries. Whether it’s worth maintaining such an index depends on how often you use queries that require a special sort ordering.