Postgresql杂谈 11—深入学习SP-Gist索引

       本文，我们深入学习下Posgresql中的SP-Gist索引。SP-Gist是Space-Partition Gist（空间分区Gist索引）的简写。和Gist索引一样，它也是一个索引框架，但是相比较于Gist，它优化了索引算法，提高了索引的性能。在此框架下，可以实现如下三种索引：

• quad-tree（四叉树）
• k-d tree（K维树）
• radix tree（基数树）

       同时，Postgresql也实现了以下几种类型的SP-Gist索引的操作类，我们可以在这些类型上直接建立SP-Gist索引。

操作类名称	数据类型	索引操作符
kd_point_ops	point	<< <@ <^ >> >^ ~=
quad_point_ops	point	<< <@ <^ >> >^ ~=
range_ops	range类型	&& &< &> -|- << <@ = >> @>
text_ops	text	< <= => >= ~<=~ ~<~ ~>=~ ~>~

       接下来，笔者分别针对SP-Gist的三种索引，通过示例程序进行介绍。

• quad-tree（四叉树）

       四叉树的原理是将位于索引平面上的点，划分成四个象限，然后采用递归的方式在每个小象限里继续划分，每个小象限都划分成四个象限，如以下示例。地图上每个小蓝点表示point类型，整张图被划分成了四个象限。

       接下来，采用递归的方式继续划分：

       得到最终的划分图：

        一个quad-tree类型的SP-Gist索引结构如下所示：

（1）每个矩形中的圆角矩形都表示一个point，每个point通过一致性函数的计算可以得到下一个point。

（2）每个箭头上的数字表示划分的象限，箭头根部表示中心点。

       接下来，以执行select * from points where p >^ point '(2,7)'(查找给定点之上的所有点)为例，介绍下索引查找的过程：

（1）使用运算符>^将点(2,7)与节点(4,4)的中心点进行比较，并选择可能包含所寻点的象限，在本例中找到第一象限和第四象限。

（2）因为第四节点为空，所以在第一象限中查找，再次和(6,6)中心点比较，再次使用一致性函数找到第一象限和第四象限。

（3）以(6,6)为中心点的第四象限也是空的，所以继续在第一象限比较。(8,6)和(7,8)中，只有(7,8)满足查询条件

       下图，描述了整个查找过程：

       下面，我们以上述的数据为例，在数据库中创建相关的数据表，来执行下该select命令。首先，创建相应的数据表并插入数据：

stock_analysis_data=# create table points(p point);
CREATE TABLE
stock_analysis_data=# insert into points values ('(4,4)'::point), ('(6,6)'::point), ('(8,6)'::point), ('(7,8)'::point), ('(5,5)'::point), ('(6,3)'::point), ('(1,1)'::point), ('(3,2)'::point);
INSERT 0 8

       建立SP-Gist索引：

stock_analysis_data=# create index on points using spgist(p); 
CREATE INDEX

       使用explain查看上述查询语句执行过程：

stock_analysis_data=# explain (analyze,verbose,timing,costs,buffers) select * from points where p >^ point '(2,7)';
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using points_p_idx on public.points  (cost=0.13..8.15 rows=1 width=16) (actual time=0.041..0.042 rows=1 loops=1)
   Output: p
   Index Cond: (points.p >^ '(2,7)'::point)
   Heap Fetches: 1
   Buffers: shared hit=2
 Planning Time: 0.063 ms
 Execution Time: 0.072 ms
(7 rows)

• k-dimensional(K维)树

       K维树是另外一种划分空间的方法，不同于四叉树，以中心点划分四个象限。K维树通过索引的第一个点画一条水平线。这样，整个图就划分成了上下两个部分，如下图所示：

       然后，在上下两个部分中，分别通过索引的点再画一条垂直线，这样整个图就被划分成了四部分：

       以此类推，再把每部分画水平线分成上下两部分，再在每部分中画垂直线，每部分又分成了左右两部分。最终的划分效果如下图：

        下面，我们在points表上建立K维树索引（需要先删除之前建立的四叉树索引，需要显式的使用kd_point_ops），然后再演示使用K维树索引查询的方法：

stock_analysis_data=# create index on points using spgist(p kd_point_ops);
CREATE INDEX
stock_analysis_data=# explain (analyze,verbose,timing,costs,buffers) select * from points where p >^ point '(2,7)';
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using points_p_idx on public.points  (cost=0.13..8.15 rows=1 width=16) (actual time=0.022..0.023 rows=1 loops=1)
   Output: p
   Index Cond: (points.p >^ '(2,7)'::point)
   Heap Fetches: 1
   Buffers: shared hit=2
 Planning Time: 0.187 ms
 Execution Time: 0.061 ms
(7 rows)

• radix树

       SP-Gist最后一种索引结构是radix树（基类树）。基类树通常用于text类型的索引，它的思想是要索引的字符串不完全存储在叶节点中，而是通过将上面节点中存储的值链接到跟节点来获取。

       假设我们需要索引站点的url:«postgrespro.ru»、«postgrespro.com»、«postgresql.org»和«planet.postgresql.org»。树的结构类似如下：

（1）树内部的节点使用所有子节点共有的前缀，例如，在«stgres»的子节点中，值以«p»+«o»+«stgres»开始。

（2）每个子节点都包含一个指向子节点的指针，使用字符标记。

       基类树索引，一个很常见的用法就是字符串的模糊查找，下面，用代码演示下基类树索引的使用，执行一个查询：select * from sites where url like 'postgresp%ru'。首先，还是创建表和索引：

stock_analysis_data=# create table sites(url text);
CREATE TABLE
stock_analysis_data=# insert into sites values ('postgrespro.ru'),('postgrespro.com'),('postgresql.org'),('planet.postgresql.org');
INSERT 0 4
stock_analysis_data=# create index on sites using spgist(url);
CREATE INDEX

       执行查询：

stock_analysis_data=# explain (analyze,verbose,costs,buffers,timing)   select * from sites where url like 'postgresp%ru';
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using sites_url_idx on public.sites  (cost=0.13..8.15 rows=1 width=32) (actual time=0.032..0.034 rows=1 loops=1)
   Output: url
   Index Cond: ((sites.url ~>=~ 'postgresp'::text) AND (sites.url ~<~ 'postgresq'::text))
   Filter: (sites.url ~~ 'postgresp%ru'::text)
   Rows Removed by Filter: 1
   Heap Fetches: 2
   Buffers: shared hit=2
 Planning Time: 0.152 ms
 Execution Time: 0.075 ms
(9 rows)

       索引用于查找大于或等于«postgresp»但小于«postgresq»的值(索引Cond)，然后从结果中选择匹配的值(过滤器)。首先，一致性函数必须决定我们需要下行到«p»根的哪个子节点。 有两个选项可供选择:«p»+«l»(不需要向下，即使不深入也很清楚)和«p»+«o»+«stgres»(继续向下)。

       对于«stgres»节点，需要再次调用一致性函数来检查«postgres»+«p»+«ro。 »(继续向下)和«postgres»+«q»(不需要向下)。«ro.»节点及其所有子叶节点，一致性函数将响应«yes»，因此索引方法将返回两个值:«postgrespro.com»和«postgrespro.ru»。在过滤阶段将从它们中选择一个匹配值。

• 总结

综合上文，针对SP-Gist索引的使用，总结如下：

（1）SP-Gist索引优化了Gist索引的算法，优化了索引性能，对于诸如point这种支持SP-Gist索引的类型应该优先考虑使用SP-Gist索引。

（2）SP-Gist索引包含四叉树、K维树、基数树三种类型，其中针对point类型默认的是四叉树。

（3）建立在text类型之上的SP-Gist索引默认是基数树，使用like进行模糊查询时会走这个索引，可以提高模糊查询的性能。