PostgreSQL 提供多种索引类型:B 树、哈希、GiST、SP-GiST、GIN、BRIN 和扩展 bloom。每种索引类型都使用一种不同的算法,最适合不同类型的可索引子句。默认情况下,CREATE INDEX
命令创建 B 树索引,适合大多数常见情况。通过编写关键字 USING
后跟索引类型名称,可以选择其他索引类型。例如,要创建一个哈希索引
CREATE INDEXname
ONtable
USING HASH (column
);
B 树可以处理可按某种顺序排序的数据的相等性和范围查询。特别是,只要索引列参与了使用以下算符之一进行比较,PostgreSQL 查询规划器就会考虑使用 B 树索引
< <= = >= >
这些操作符组合形式的构造,例如 BETWEEN
和 IN
,也可以用 B 树索引搜索来实现。此外,索引列上的 IS NULL
或 IS NOT NULL
条件可以用 B 树索引。
如果模式是一个常量并且锚定到字符串的开头,那么优化器还可以对涉及模式匹配操作符 LIKE
和 ~
的查询使用 B 树索引 — 例如 col LIKE 'foo%'
或 col ~ '^foo'
,但不是 col LIKE '%bar'
。但是,如果数据库不使用 C 区域设置,则需要使用特殊运算符类创建索引来支持模式匹配查询的索引;请参阅下面的 第 11.10 节。也可以对 ILIKE
和 ~*
使用 B 树索引,但前提是模式以非字母字符开头,即不受大小写转换影响的字符。
B 树索引还可以用于按已排序的顺序检索数据。这并不总是比简单扫描和排序快,但通常会有所帮助。
GiST 索引不是单一类型的索引,而是一个基础设施,可以在其中实现许多不同的索引策略。因此,GiST 索引可用的特定运算符因索引策略而异(运算符类)。举例来说,PostgreSQL 的标准发行版包含几个二维几何数据类型的 GiST 运算符类,它们支持使用这些运算符的索引查询
<< &< &> >> <<| &<| |&> |>> @> <@ ~= &&
(有关这些运算符的含义,请参阅 第 9.11 节。标准发行版中包含的 GiST 运算符类记录在 第 64.1 表 中。在 contrib
集合或作为独立项目可以找到许多其他 GiST 运算符类。有关更多信息,请参阅 第 64.2 节。
GiST 索引还可以优化“最近邻”搜索,例如
SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;
它将找到最接近给定目标点的十个位置。是否可以执行此操作再次取决于正在使用的特定运算符类。在 第 64.1 表 中,以 “排序运算符” 列中列出了可以此方式使用的运算符。
与 GiST 索引类似,SP-GiST 索引提供了支持各种搜索的基础设施。SP-GiST 允许实现各种不同的非平衡磁盘数据结构,例如四叉树、k-d 树和基数树(trie)。举例来说,PostgreSQL 的标准发行版包含用于二维点的 SP-GiST 运算符类,它们支持使用这些运算符的索引查询
<< >> ~= <@ <<| |>>
(有关这些运算符的含义,请参阅 第 9.11 节。标准发行版中包含的 SP-GiST 运算符类记录在 第 64.2 表 中。有关更多信息,请参阅 第 64.3 节。
与 GiST 类似,SP-GiST 也支持“最近邻”搜索。对于支持距离排序的 SP-GiST 运算符类,相应的运算符将在 第 64.2 表 的“排序运算符”列中列出。
GIN 索引是 “倒排索引”,适用于包含多个组件值的数据值,如数组。倒排索引为每个组件值包含一个单独的条目,并且可以高效地处理测试特定组件值的存在性的查询。
与 GiST 和 SP-GiST 一样,GIN 可以支持很多不同的用户定义索引策略,并且可以使用 GIN 索引的特定运算符取决于索引策略。举个例子,PostgreSQL 的标准发行版包含一个面向数组的 GIN 运算符类,它支持使用下列运算符执行索引查询
<@ @> = &&
(有关这些运算符的含义,参见第 9.19 节。)在标准发行版中包含的 GIN 运算符类记录在表 64.3中。很多其他 GIN 运算符类可以在 contrib
集合或作为单独的项目获得。有关详细信息,参见第 64.4 节。