关于postgresql:Postgresql索引类型btreehashGISTGIN

引言

Postgresql 存在许多特定的索引查问类型，和大部分的 Btree 为基础架构的关系型数据库一样，在创立索引缺省的时候会把 btree 作为默认值。

本节简略介绍 Postgresql 的索引类型，尽管大部分业务常见常见能够用 btree 搞定，然而某些状况下其余非凡的索引能够有事倍功半的成果。

官网文档

PostgreSQL: Documentation: 14: 11.2. Index Types

pgsql 索引类型

• B-tree
• Hash
• GiST
• GIN
• BRIN

B-tree 索引

B-tree 适宜解决那些可能按顺序存储的数据之上的等于（或范畴）查问。范畴查问蕴含上面的内容：

<
<=
=
>=
>

在进行下面这些操作符的运算时候，Postgresql 优化器会优先选择 Btree 索引，除了下面操作符以外还有 BETWEEN 和 IN 也能够应用索引，另外索引列上的IS NULL 或IS NOT NULL条件也能够用于 B 树索引。

然而理论尝试发现 IS NULL 或IS NOT NULL条件的查问很多时候是走不了索引了。

依据官网的定义，仅当模式是一个常量，并且锚定在字符串结尾的时候，优化器才会把 B-tree 索引用于模式匹配操作符 LIKE 和 ~。这个说法比拟云里雾里，实际上是暗示开发者尽量避开正则或者含糊查问操作。

模式匹配

比方：col LIKE 'foo%' 或 col ~ '^foo'，这些操作能够认为是能够动用索引的，然而留神 col LIKE '%bar' 这样的操作就不能够应用正则，因为简直所有数据库都不反对后缀索引，这和 Btree 自身的数据结构无关。

这种时候如果要存储可能须要思考 逆序存储 + 索引 的形式来触发扫描索引操作。

B-tree 索引能够用于 ILIKE 和 ~*，然而和前文说的一样，仅当模式 以非字母字符 (不受大小写影响的字符) 结尾才能够应用索引。

B 树索引能够用来检索排序后的数据，这并不总是比简略的扫描和排序快，这一点须要留神。

留神：
$：示意匹配字串的结尾。
^：示意匹配输出字符串的开始地位。
 等效于 LIKE，而 \~\~ 对应 ILIKE，! 和 !\~\~ 操作符 别离代表 NOT LIKE 和 NOT ILIKE

Hash 索引

Hash 索引 只能解决简略的等于比拟查问操作，并且留神退出哈希索引必然是在惟一值的列，否则索引容易生效。

留神官网并不举荐应用哈希索引。测试表明，PostgreSQL 的 Hash 索引的性能不比 B-tree 索引强，而 Hash 索引的尺寸和制作工夫更差。hash 索引因为不记录 WAL 日志，所以咱们可能须要用 REINDEX 重建 Hash 索引，这会消耗大量零碎开销。

哈希索引的创立 SQL 如下：

CREATE INDEX index_name 
ON table_name USING HASH (indexed_column);

GiST 索引(Generalized Search Tree – 通用的搜寻树)

GiST 的意思是通用的搜寻树(Generalized Search Tree)。外部是均衡树的拜访形式，GiST 索引通常能够用来代替其余索引，比方 Btree。

Gist 索引的创立形式如下：

CREATE INDEX gist_idx_test ON GIST_IDX USING gist(circle_dim);

Gist 容许自定义类型，自定义数据类型是 Gist 索引的强项。

然而 GiST 索引不是独自一种索引类型，而更像是一种架构，在这个架构上能够扩大出其余的索引，因为 Gist 索引反对多种索引策略，PostgresSql 提供了多个二维几何数据类型的 GiST 运算符类，这些运算符类反对应用以下运算符的索引查问：

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

对于这些操作符号的含意能够看上面的官网文档，文档次要介绍了一些三角函数，多边形，坐标运算等等。集体数学渣就不过多开展了。

PostgreSQL: Documentation: 14: 9.11. Geometric Functions and Operators

GiST 索引还可能优化“近邻”搜寻，例如上面这样的搜寻：

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

SP-Gist 索引

SP-GiST 是指空间分区的 GiST。SP-GiST 反对分区搜寻树，便于开发各种不同的非均衡数据结构。

Sp-Gist 索引的创立形式如下：

CREATE INDEX spgist_idx ON spgist_table USING SPGiST (phone_no);

在官网介绍中介绍 Sp-Gist 的外围如下：

 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).

SP-GiST 容许实现各种不同的基于磁盘的非均衡数据结构，例如 四叉树、k-d 树和基数树（tries）。Postgresql 反对二维点位的运算符，这些运算符类反对应用以下运算符的索引查问：

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

与 GiST 一样，SP-GiST 反对“相邻”搜寻。对于反对间隔排序的 SP-GiST 运算符类，在官网文档的 PostgreSQL: Documentation: 14: 66.2. Built-in Operator Classes 中列出了相应的运算符。

SP-GiST 索引对那些具备天然聚类元素，同时也不是等量均衡树的数据最有用，例如，GIS、多媒体、电话路由、IP 路由等。

GIN 索引(Generalized Inverted Index- 基因倒排索引)

基因倒排索引(Generalized Inverted Index) 存储了一个 Key/posting list 的构造，Key 是惟一键。posting list 外面存储了 Key 呈现的行。

GIN 索引的创立形式如下：

CREATE INDEX GIN_idx1 ON student USING GIN (to_tsvector('english', stud_name));

GIN 索引显然是给搜寻优化做筹备的，GIN 索引是反转索引，它能够解决蕴含多个键的值(比方数组)。Gin 和下面提到 Gist 索引一样反对自定义索引策略。

PostgreSQL 的规范公布中蕴含用于一维数组的 GIN 操作符类，它反对上面的操作：

<@

@>

=

&&

GIN 应用技巧

留神 GIN 索引的插入是十分慢的，个别倡议保护 GIN 索引是和图数据库一样先删除后从新建设。另外 GIN 有一个可配置的后果集大小软下限配置参数 gin_fuzzy_search_limit，这个参数能够避免因为海量搜寻匹配进行大量的磁盘读写。

缺省值 0 示意没有限度。如果设置了非零值，那么返回的后果就是从残缺后果集中随机抉择的一部分

局限性

GIN 的局限性是不能进行残缺索引扫描，因为对应多个 key，所以堆指针会屡次扫描回退，如果索引返回 0 个 Key 也会报错，空白查问也有可能匹配局部或者齐全无奈匹配内容。

GIN 用于应用等值匹配比拟适合，对于简单的场景查问后果比拟难管制，集体认为实用于轻量级的代替 ES 这样的中间件进行简略搜寻的场景能够实用。

BRIN 索引（Block Range Indexes）

BRIN 索引（Block Range INdexes 的缩写）存储了对于存储在一个表的间断物理块范畴内的值的 摘要 ，也就是援用数据对应于每个块范畴的列中数值的 最小值 和最大值。

BRIN 索引的创立形式如下：

CREATE INDEX brin_idx ON test_idx USING BRIN(phone);

像 GiST、SP-GiST 和 GIN 一样，BRIN 能够反对许多不同的索引策略，而且依据索引策略的不同，BRIN 索引所能应用的特定运算符也不同。

比方 Brin 索引反对上面的运算符：

<   <=   =   >=   >

在官网文档的 https://www.postgresql.org/docs/current/brin-builtin-opclasses.html#BRIN-BUILTIN-OPCLASSES-TABLE 中有更多操作符的介绍。

小结

从全文列出的索引当中，能够看到用的比拟多的根本是 Btree、GIN、Brin 这几个索引，而对于简单的倒排索引以及 Hash 索引都有着比拟非凡的业务才有可能用到，而 Hash 索引仅仅实用于等值查问这样繁多的场景。

参考资料

• (1 条音讯) 15.pgsql 索引类型（btree、hash、GIST、GIN）\_Lei_Da_Gou 的博客 -CSDN 博客_pgsql 索引类型
• PostgreSQL: Documentation: 15: 11.2. Index Types