PostgreSQL 索引类型

本文介绍在 PostgreSQL 中的索引类型以及如何正确的使用它们。

PostgreSQL 支持几种不同类型的索引:B-tree、Hash、GiST、SP-GiST、GIN 和 BRIN。每种索引类型使用不同的存储结构和算法来应对不同类型的查询。不同的数据可能要采用不同的索引类型。

PostgreSQL 默认使用 B-tree 索引类型,因为它最适合最常见的查询。 你在使用 CREATE INDEX 语句创建索引时,如果不知道索引类型,默认采用 B-tree。

B-tree 索引

B-tree 是一种自平衡树,它维护已排序的数据。

每当索引列涉及使用以下运算符之一的比较时,PostgreSQL 查询规划器将考虑使用 B-tree 索引:

  • <
  • <=
  • =
  • >=
  • BETWEEN
  • IN
  • IS NULL
  • IS NOT NULL

此外, 如果模式是一个常量并且在模式的开头是锚点, 查询规划器可以对涉及模式匹配运算符 LIKE~ 的查询使用 B-tree 索引, 例如:

column_name LIKE 'foo%'
column_name LKE 'bar%'
column_name  ~ '^foo'

col LIKE '%bar' 则不会使用 B-tree 索引。

对于模式匹配运算符 ILIKE~*,如果模式以以非字母字符(不受大小写影响的字符)开头,查询规划器将考虑使用 B-tree 索引。

如果您已经开始使用索引来优化 PostgreSQL 数据库,那么 B-tree 可能就是您想要的。

哈希索引

哈希索引只能处理简单的相等比较 (=)。这意味着每当索引列使用 = 运算符进行比较时,查询计划器将考虑使用哈希索引。

要创建哈希索引,请在子句中使用 CREATE INDEX 带有 HASH 索引类型的 USING 语句,如下所示:

CREATE INDEX index_name
ON table_name USING HASH (indexed_column);

GIN 索引

GIN 索引是“倒排索引”。它很适合索引那些复杂的值(比如 array, hstore, jsonrange)。

倒排索引中为每一个组成值都包含一个单独的项,它可以高效地处理测试指定组成值是否存在的查询。

当您将多个值存储在单个列中时,GIN 索引最有用。

BRIN 索引

BRIN 表示块范围索引(Block Range Indexes),存储有关存放在一个表的连续物理块范围上的值摘要信息。BRIN 是 PostgreSQL 9.5 版本新增的索引类型。与 B-tree 索引相比,BRIN 更小,维护成本更低。

BRIN 允许在非常大的表上使用索引,这在以前使用没有水平分区的 B-tree 是不切实际的。

BRIN 常用于具有线性排序顺序的列,例如,销售订单表的创建日期列。

GiST 索引

GiST 索引不是单独一种索引类型,而是一种架构,可以在这种架构上实现很多不同的索引策略。

GiST 代表广义搜索树。GiST 索引允许构建通用的树结构。GiST 索引可用于索引几何数据类型和全文搜索。

SP-GiST 索引

SP-GiST 代表空间分区的 GiST。SP-GiST 支持分区搜索树,有助于开发各种不同的非平衡数据结构。

SP-GiST 索引最适用于具有自然聚类元素但也不是均衡树的数据,例如 GIS、多媒体、电话路由和 IP 路由。

结论

本文介绍了各种 PostgreSQL 索引类型,包括 B-tree、Hash、BRIN、GiST 和 SP-GiST。