【Postgresql】索引类型（btree、hash、GIST、GIN）

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

本节简单介绍Postgresql的索引类型，虽然大部分业务常见常见可以用btree搞定，但是某些情况下其他特殊的索引可以有事半功倍的效果。

PostgreSQL: Documentation: 14: 11.2. Index Types

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 索引只能处理简单的等于比较查询操作，并且注意加入哈希索引必然是在唯一值的列，否则索引容易失效。

注意官方并不推荐使用哈希索引。测试表明，PostgreSQL 的 Hash 索引的性能不比 B-tree 索引强，而 Hash 索引的尺寸和制作时间更差。hash索引因为不记录WAL日志，所以我们可能需要用 REINDEX 重建 Hash 索引，这会耗费大量系统开销。

哈希索引的创建SQL如下：

GiST 的意思是通用的搜索树(Generalized Search Tree)。内部是平衡树的访问方式，GiST索引通常可以用来替代其他索引，比如Btree。

Gist索引的创建方式如下：

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

但是GiST 索引不是单独一种索引类型，而更像是一种架构，在这个架构上可以扩展出其他的索引，因为Gist索引支持多种索引策略，PostgresSql提供了多个二维几何数据类型的 GiST 运算符类，这些运算符类支持使用以下运算符的索引查询：

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

从全文列出的索引当中，可以看到用的比较多的基本是Btree、GIN、Brin 这几个索引，而对于复杂的倒排索引以及Hash索引都有着比较特殊的业务才有可能用到，而Hash索引仅仅适用于等值查询这样单一的场景。