【Postgresql】索引类型（btree、hash、GIST、GIN）
【Postgresql】索引类型（btree、hash、GIST、GIN）
引言
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
操作符 | 描述 | 例子 |
|---|---|---|
~ | 匹配正则表达式，大小写相关 | 'thomas' ~ '.*thomas.*' |
~* | 匹配正则表达式，大小写无关 | 'thomas' ~* '.*Thomas.*' |
!~ | 不匹配正则表达式，大小写相关 | 'thomas' !~ '.*Thomas.*' |
!~* | 不匹配正则表达式，大小写无关 | 'thomas' !~* '.*vadim.*' |
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