Postgresql 中文操作指南

11.2. Index Types #

PostgreSQL 提供了多种索引类型：B 树、哈希、GiST、SP-GiST、GIN、BRIN 以及扩展 bloom 。每种索引类型都使用不同的算法，该算法最适合不同类型的可索引子句。默认情况下， CREATE INDEX 命令创建 B 树索引，这适合最常见的情况。通过编写关键词 USING 后跟索引类型名称，可以选择其他索引类型。例如，创建哈希索引：

PostgreSQL provides several index types: B-tree, Hash, GiST, SP-GiST, GIN, BRIN, and the extension bloom. Each index type uses a different algorithm that is best suited to different types of indexable clauses. By default, the CREATE INDEX command creates B-tree indexes, which fit the most common situations. The other index types are selected by writing the keyword USING followed by the index type name. For example, to create a Hash index:

CREATE INDEX name ON table USING HASH (column);

11.2.1. B-Tree #

B 树可以处理对可按某种顺序排序的数据进行的相等性和范围查询。特别是，只要在使用以下这些运算符之一进行比较时，PostgreSQL 查询规划器便会考虑使用 B 树索引，它涉及某个已编制索引的列：

B-trees can handle equality and range queries on data that can be sorted into some ordering. In particular, the PostgreSQL query planner will consider using a B-tree index whenever an indexed column is involved in a comparison using one of these operators:

<   <=   =   >=   >

也可以用 B 树索引搜索来实现相当于这些运算符组合的构造，例如 BETWEEN 和 IN。而且，对索引列的 IS NULL 或 IS NOT NULL 条件也可以与 B 树索引一起使用。

Constructs equivalent to combinations of these operators, such as BETWEEN and IN, can also be implemented with a B-tree index search. Also, an IS NULL or IS NOT NULL condition on an index column can be used with a B-tree index.

优化器还可以针对涉及模式匹配运算符 LIKE 和 ~ 的查询使用 B 树索引 if 模式是一个常量，并且锚定在字符串的开头——例如，col LIKE 'foo%' 或 col ~ '^foo'，但不是 col LIKE '%bar'。但是，如果你的数据库不使用 C 区域设置，则需要使用特殊运算符类创建索引以支持模式匹配查询的索引编制；请参阅下面的 Section 11.10。也可以对 ILIKE 和 ~* 使用 B 树索引，但仅当模式以非字母字符开头时，即不受大小写转换影响的字符。

The optimizer can also use a B-tree index for queries involving the pattern matching operators LIKE and ~ if the pattern is a constant and is anchored to the beginning of the string — for example, col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar'. However, if your database does not use the C locale you will need to create the index with a special operator class to support indexing of pattern-matching queries; see Section 11.10 below. It is also possible to use B-tree indexes for ILIKE and ~*, but only if the pattern starts with non-alphabetic characters, i.e., characters that are not affected by upper/lower case conversion.

B 树索引还可以用于以已排序顺序检索数据。这并不总是比简单的扫描和排序要快，但常常很有帮助。

B-tree indexes can also be used to retrieve data in sorted order. This is not always faster than a simple scan and sort, but it is often helpful.

11.2.2. Hash #

Hash 索引存储由索引列的值派生的 32 位哈希码。因此，此类索引只能处理简单的相等性比较。只要在使用相等运算符进行比较时，查询规划器便会考虑使用 Hash 索引，它涉及某个已编制索引的列：

Hash indexes store a 32-bit hash code derived from the value of the indexed column. Hence, such indexes can only handle simple equality comparisons. The query planner will consider using a hash index whenever an indexed column is involved in a comparison using the equal operator:

=

11.2.3. GiST #

GiST 索引并非单一种类的索引，而是可以在其中实现许多不同索引策略的基础设施。因此，可以与 GiST 索引一起使用的特定运算符因索引策略（operator class）而异。例如，PostgreSQL 的标准发行版包括适用于多种二维几何数据类型的 GiST 运算符类，它们支持使用以下这些运算符的索引查询：

GiST indexes are not a single kind of index, but rather an infrastructure within which many different indexing strategies can be implemented. Accordingly, the particular operators with which a GiST index can be used vary depending on the indexing strategy (the operator class). As an example, the standard distribution of PostgreSQL includes GiST operator classes for several two-dimensional geometric data types, which support indexed queries using these operators:

<<   &<   &>   >>   <<|   &<|   |&>   |>>   @>   <@   ~=   &&

（有关这些运算符的含义，请参阅 Section 9.11。）标准发行版中包含的 GiST 运算符类记录在 Table 68.1 中。contrib 集合或其他项目中提供了许多其他 GiST 运算符类。有关详细信息，请参阅 Chapter 68。

(See Section 9.11 for the meaning of these operators.) The GiST operator classes included in the standard distribution are documented in Table 68.1. Many other GiST operator classes are available in the contrib collection or as separate projects. For more information see Chapter 68.

GiST 索引还能够优化“最近邻”搜索，例如

GiST indexes are also capable of optimizing “nearest-neighbor” searches, such as

SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;

该命令可查找距离给定目标点最近的十个地点。执行此操作的能力再次取决于所使用的特定运算符类。在 Table 68.1 中，“顺序运算符”列中列出了可以用这种方式使用的运算符。

which finds the ten places closest to a given target point. The ability to do this is again dependent on the particular operator class being used. In Table 68.1, operators that can be used in this way are listed in the column “Ordering Operators”.

11.2.4. SP-GiST #

与 GiST 索引类似，SP-GiST 索引提供了一种支持多种搜索的基础设施。SP-GiST 允许实现广泛的不同非平衡基于磁盘的数据结构，如四叉树、kd 树和基数树（trie）。例如，PostgreSQL 的标准发行版包含适用于二维点的 SP-GiST 运算符类，它们支持使用以下这些运算符的索引查询：

SP-GiST indexes, like GiST indexes, offer an infrastructure that supports various kinds of searches. SP-GiST permits implementation of a wide range of different non-balanced disk-based data structures, such as quadtrees, k-d trees, and radix trees (tries). As an example, the standard distribution of PostgreSQL includes SP-GiST operator classes for two-dimensional points, which support indexed queries using these operators:

<<   >>   ~=   <@   <<|   |>>

（有关这些运算符的含义，请参阅 Section 9.11。）标准发行版中包含的 SP-GiST 运算符类记录在 Table 69.1 中。有关更多信息，请参阅 Chapter 69。

(See Section 9.11 for the meaning of these operators.) The SP-GiST operator classes included in the standard distribution are documented in Table 69.1. For more information see Chapter 69.

与 GiST 一样，SP-GiST 也支持“最近邻”搜索。针对支持距离排序的 SP-GiST 运算符类，其相应运算符列在 Table 69.1 的“排序运算符”列中。

Like GiST, SP-GiST supports “nearest-neighbor” searches. For SP-GiST operator classes that support distance ordering, the corresponding operator is listed in the “Ordering Operators” column in Table 69.1.

11.2.5. GIN #

GIN 索引是“反向索引”，适用于包含多个组成值的数据值，如数组。反向索引包含用于每个组成值的一个单独条目，并且可以有效地处理测试特定组成值的存在的查询。

GIN indexes are “inverted indexes” which are appropriate for data values that contain multiple component values, such as arrays. An inverted index contains a separate entry for each component value, and can efficiently handle queries that test for the presence of specific component values.

与 GiST 和 SP-GiST 一样，GIN 可以支持许多不同的用户定义索引策略，并且可以与 GIN 索引一起使用的特定运算符因索引策略而异。例如，PostgreSQL 的标准发行版包含适用于数组的 GIN 运算符类，它支持使用以下这些运算符的索引查询：

Like GiST and SP-GiST, GIN can support many different user-defined indexing strategies, and the particular operators with which a GIN index can be used vary depending on the indexing strategy. As an example, the standard distribution of PostgreSQL includes a GIN operator class for arrays, which supports indexed queries using these operators:

<@   @>   =   &&

（有关这些运算符的含义，请参阅 Section 9.19。）标准发行版中包含的 GIN 运算符类记录在 Table 70.1 中。contrib 集合或其他项目中提供了许多其他 GIN 运算符类。有关详细信息，请参阅 Chapter 70。

(See Section 9.19 for the meaning of these operators.) The GIN operator classes included in the standard distribution are documented in Table 70.1. Many other GIN operator classes are available in the contrib collection or as separate projects. For more information see Chapter 70.

11.2.6. BRIN #

BRIN 索引（Block Range INdexes 的缩写）存储有关表连续物理块范围中存储的值的汇总。因此，它们最适用于值与表行物理顺序密切相关的列。与 GiST、SP-GiST 和 GIN 类似，BRIN 可以支持许多不同的索引策略，并且 BRIN 索引可以使用的特定运算符因索引策略不同而异。对于具有线性排序顺序的数据类型，索引数据对应于每个块范围中列中值的最小值和最大值。这支持使用以下运算符的索引查询：

BRIN indexes (a shorthand for Block Range INdexes) store summaries about the values stored in consecutive physical block ranges of a table. Thus, they are most effective for columns whose values are well-correlated with the physical order of the table rows. Like GiST, SP-GiST and GIN, BRIN can support many different indexing strategies, and the particular operators with which a BRIN index can be used vary depending on the indexing strategy. For data types that have a linear sort order, the indexed data corresponds to the minimum and maximum values of the values in the column for each block range. This supports indexed queries using these operators:

<   <=   =   >=   >

标准发行版中包含的 BRIN 运算符类记录在 Table 71.1 中。有关更多信息，请参阅 Chapter 71。

The BRIN operator classes included in the standard distribution are documented in Table 71.1. For more information see Chapter 71.