PostgreSQL 10 索引 - 理解索引类型

很多时候，b-tree就足够了，它是基于排序的，可以处理<、<=、=、>=和>。但是，比如在处理矩形的时候，应该怎么排序呢，根据面积，还是周长？
所以，需要更多的索引类型。PostgreSQL 10.0支持如下索引类型：

postgres=# SELECT * FROM pg_am;
 amname |  amhandler  | amtype 
--------+-------------+--------
 btree  | bthandler   | i
 hash   | hashhandler | i
 gist   | gisthandler | i
 gin    | ginhandler  | i
 spgist | spghandler  | i
 brin   | brinhandler | i
(6 行记录)

Hash索引

Hash索引已经存在了好多年。其思想是hash输入值并保存它，将来搜索的时候使用。PostgreSQL 10之前，不建议使用，因为对它们，PostgreSQL没有WAL支持。PostgreSQL 10开始，Hash索引全部有WAL日志，可以复制（replication），并且是100% crash安全的。
一般来说，Hash索引比b-tree索引占空间。假设你要为400万个整数值建索引，btree索引大约需要90MB空间，而hash索引需要125MB空间。

GiST索引

GiST（Generalized Search Tree）索引是非常重要的类型，可以用来实现R-tree，甚至可能是b-tree（不推荐）。
GiST可用于：

• Range类型
• Geometric索引（比如PostGIS扩展）
• 模糊（Fuzzy）搜索

GiST是如何工作的

对很多人来说，GiST还是个黑盒子。因此，需要了解一下GiST内部是如何工作的。

看这棵树。R1和R2在顶端，是包含其他矩形的边界框。而R3、R4和R5在R1，R8、R9和R10在R3内，等等。所以，GiST索引是分层组织的，它的一些操作（比如重叠、在左边、在右边等等），是b-tree无法支持的。GiST树的布局是几何索引的理想选择。

GiST的扩展

当然，也支持自定义运算符类别。支持下列策略（Strategy）：

运算	描述	策略
Strictly left of	左参数严格地位于右参数的左边	1
Does not extend to right of	左参数不会延伸到右参数的右边	2
Overlaps	重叠	3
Does not extend to left of	左参数不会延伸到右参数的左边	4
Strictly right of	左参数严格地位于右参数的右边	5
Same	相同	6
Contains	包含	7
Contained by	被包含	8
Does not extend above	不会延伸到高于	9
Strictly below	严格低于	10
Strictly above	严格高于	11
Does not extend below	不会延伸到低于	12

如果你想为GiST写运算符类别，必须提供一系列支持函数。

函数	描述	支持函数号
consistent	key是否满足查询修饰语（qualifier），在内部，策略被查找和检查	1
union	计算key集合的联合。对于数值，简单地计算上限、下限或者范围	2
compress	key或者value的压缩	3
decompress	解压	4
penalty	计算在插入期间，插入到树的成本。成本决定新的条目是否被加进树（这是性能的关键）	5
picksplit	决定一个页中的条目要被移动到哪儿。一些还在旧页，一些会到新增加的页	6
equal	判断相等，和b-tree中的类似	7
distance	计算key和查询value之间的距离，可选的，需要支持KNN搜索	8
fetch	决定压缩的key的原始表达。需要处理index only scans	9

GIN

GIN（Generalized inverted）是索引文本的好办法。加入你想索引100万文本文档。某些关键字可能出现了几百万次。如果是普通的b-tree，该key会被保存几百万次。而GIN不是这样的。每个key只保存一次，并分配给文档列表。key组织成一个标准的b-tree。每个条目会有一个文档列表的指针，指向列表中有相同key的全部条目。一个GIN索引是很小的。

扩展GIN

运算	描述	策略
Overlap	重叠	1
Contains	包含	2
Is contained by	被包含	3
Equal	相等	4

函数	描述	支持函数号
compare	比较两个key，返回-1、0或1	1
extractValue	从要被索引的值中抽取key。一个值可以有很多key。比如，一个文本可以包含很多字	2
extractQuery	从查询条件抽取key	3
consistent	检查值是否匹配查询条件	4
comparePartial	比较来自查询的部分key和来自索引的key返回-1、0或1	5
triConsistent	决定值是否匹配查询条件（三元变体），如果consistent函数存在，它是可选的	6

SP-GiST

SP-GiST（Space partitioned GiST）为了在内存中使用，因为如果保存到磁盘，需要很高的磁盘命中率。SP-GiST可以被用来实现四叉树、k-d树和radix trees。
提供了下列策略：

运算	描述	策略
Strictly left of	左参数严格地位于右参数的左边	1
Strictly right of	左参数严格地位于右参数的右边	5
Same	相同	6
Contained by	被包含	8
Strictly below	严格低于	10
Strictly above	严格高于	11

要实现自定义运算符类别，需要实现：

函数	描述	支持函数号
config	提供相关信息	1
choose	如何在内部元组中插入新值	2
picksplit	如何划分值的集合	3
inner_consistent	决定一个查询需要搜索哪个子分区	4
leaf_consistent	key是否满足查询修饰语	5

BRIN

BRIN（Block range indexes）非常实用。前面讨论的索引都需要很多磁盘空间。即使GIN索引，每个条目也需要索引的指针，如果有1000万条数据，就需要1000万个索引指针。BRIN索引主要解决空间问题。一个BRIN索引不会为每个元组保存索引条目，但是会保存128个块（默认）数据（1MB）的最小和最大值。因此，索引很小但是扫描索引会返回比我们所要求的更多的数据。PostgreSQL不得不过滤掉多余的行。
看下面的例子，BRIN索引有多小：

postgres=# CREATE INDEX idx_brin ON t_test USING brin(id);
CREATE INDEX
Time: 1058.603 ms (00:01.059)
postgres=# \di+ idx_brin
                           关联列表
 架构模式 |   名称   | 类型 |  拥有者  | 数据表 | 大小  | 描述 
----------+----------+------+----------+--------+-------+------
 public   | idx_brin | 索引 | postgres | t_test | 48 kB | 
(1 行记录)

可以看到，该BRIN索引只是标准b-tree的1/2000。如果数据是排好序的，BRIN就很高效。

扩展BRIN索引

BRIN支持的策略和b-tree相同，因此需要相同的运算符（小于、小于等于、等于、大于等于、大于）。
BRIN需要的支持函数是

函数	描述	支持函数号
opcInfo	关于索引的列的内部信息	1
add_value	在已经存在的摘要（summary）元组中加新条目	2
consistent	检查值是否匹配条件	3
union	计算两个摘要条目的联合（最小/最大值）	4

添加其他索引

PostgreSQL 9.6开始，可以很容易地增加新的索引类型。

postgres=# \h CREATE ACCESS METHOD
命令：       CREATE ACCESS METHOD
描述：       定义新的访问方法
语法：
CREATE ACCESS METHOD 名称
    TYPE access_method_type
    HANDLER handler_function(处理_函数)

比如，可以实现一个bloom过滤器。这是一个概率数据结构，有时候会返回太多行。因此，可以用bloom过滤来实现数据的预过滤。bloom过滤器定义在几列上。基于输入值计算bitmask，用它和你的查询做比较。bloom过滤器可以索引任何数量的列，缺点是必须读取整个bloom过滤器。当然，和底层数据相比，bloom过滤器还是挺小的。
要使用bloom过滤器，只要打开扩展就可以，它是PostgreSQL的contrib包的一部分：

postgres=# CREATE EXTENSION bloom;
CREATE EXTENSION

我们看个例子

postgres=# CREATE TABLE t_bloom (x1 int, x2 int, x3 int, x4 int, x5 int, x6 int, x7 int);
CREATE TABLE
时间：9.989 ms
postgres=# insert into t_bloom select i, i+1, i-1, i+2, i-2, i+3, i-3 from (select trunc(1000*(random())) i from generate_series(1,1000)) t;
INSERT 0 1000
时间：50.984 ms

然后增加索引：

postgres=# CREATE INDEX idx_bloom ON t_bloom USING bloom(x1, x2, x3, x4, x5, x6, x7);
CREATE INDEX
时间：46.588 ms

查看执行计划

postgres=# explain SELECT * FROM t_bloom WHERE x5 = 9 AND x3 = 7;
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Bitmap Heap Scan on t_bloom  (cost=22.00..26.02 rows=1 width=28)
   Recheck Cond: ((x3 = 7) AND (x5 = 9))
   ->  Bitmap Index Scan on idx_bloom  (cost=0.00..22.00 rows=1 width=0)
         Index Cond: ((x3 = 7) AND (x5 = 9))
(4 行记录)