9.3 9.4 9.5 9.6 10 11 12 13
阿里云PostgreSQL 问题报告 纠错本页面

11.2. 索引类型

PostgreSQL提供了好几种索引类型:B-tree, Hash, GiST, SP-GiST和GIN 。 每种索引类型都比较适合某些特定的查询类型,因为它们用了不同的算法。缺省时, CREATE INDEX命令将创建 B-tree 索引,它适合大多数情况。

B-tree 适合处理那些能够按顺序存储的数据之上的等于和范围查询。 特别是在一个建立了索引的字段涉及到使用

<
<=
=
>=
>

操作符之一进行比较的时候,PostgreSQL 的查询规划器都会考虑使用 B-tree 索引。等效于这些操作符组合的构造, 比如BETWEENIN,也可以用搜索 B-tree 索引实现。 同样,索引列中的IS NULLIS NOT NULL条件可以和B-tree索引一起使用。

仅当模式是一个常量,并且锚定在字符串开头的时候, 优化器才会把 B-tree 索引用于模式匹配操作符LIKE~, 比如:col LIKE 'foo%'col ~ '^foo', 但是col LIKE '%bar'就不行。同时,如果你的数据库未使用 C 区域设置, 那么你需要用一个特殊的操作符类创建索引来支持模式匹配查询上的索引。 参阅第 11.9 节。还有可能将 B-tree 索引用于ILIKE~*,但是仅当模式以非字母字符(不受大小写影响的字符)开头才可以。

B-tree索引也可以用来按照排序顺序检索数据。这并不总是比一个简单的扫描和排序快, 但通常是有帮助的。

Hash 索引只能处理简单的等于比较。当一个索引了的列涉及到使用= 操作符进行比较的时候,查询规划器会考虑使用 Hash 索引。下面的命令用于创建 Hash 索引:

CREATE INDEX name ON table USING hash (column);

小心

Hash 索引操作目前没有记录 WAL 日志,因此数据库崩溃后如果有未写入的改变, 我们可能需要用REINDEX重建 Hash 索引。另外, 对hash索引的改变在初始的基础备份后不会被实施基于流或者基于文件的复制, 所以对于随后使用它们的查询会给出错误的回复。因为这些原因, 我们并不鼓励使用 Hash 索引。

GiST 索引不是单独一种索引类型,而是一种架构,可以在这种架构上实现很多不同的索引策略。 因此,可以使用 GiST 索引的操作符高度依赖于索引策略(操作符类)。 作为示例,PostgreSQL的标准发布中包含用于二维几何数据类型的 GiST 操作符类,它支持

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

操作符的索引查询。这些操作符的含义参见第 9.11 节。 GiST操作符类包含在表 56-1 中记录的标准发布中。 许多其它 GiST 操作符类被包含在contrib中,或者是单独的项目, 更多信息参见第 56 章

GiST索引也可最优化"nearest-neighbor"检索,例如

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

找出距离给出目标点最近的十个地点。能这样做也是依赖于使用特定的操作符类。 在表 56-1中,可以以这种方式使用的操作符在字段 "Ordering Operators"中列出。

SP-GiST索引类似于GiST索引,提供一个支持不同类型检索的架构。 SP-GiST允许实现许多各种不同的非平衡的基于磁盘的数据结构,例如四叉树,k-d树和基数树(字典树)。 作为示例,PostgreSQL的标准发布中包含用于二维点的SP-GiST操作符类, 它支持

<<
>>
~=
<@
<^
>^

操作符的索引查询。(这些操作符的含义参见第 9.11 节。) SP-GiST操作符类包含在表 57-1 中记录的标准发布中。 更多信息参见第 57 章

GIN 索引是反转索引,它可以处理包含多个键的值(比如数组)。与 GiST和SP-GiST 类似, GIN 支持用户定义的索引策略,可以使用 GIN 索引的操作符根据索引策略的不同而不同。 作为示例,PostgreSQL的标准发布中包含用于一维数组的 GIN 操作符类,它支持

<@
@>
=
&&

操作符的索引查询。这些操作符的含义参见第 9.18 节。 GIN操作符类包含在表 58-1 中记录的标准发布中。 许多其它 GIN 操作符类被包含在contrib集合或作为单独的项目。 更多信息参见第 58 章

<
/BODY >