PostgreSQL中的索引(六) --(SP-GiST)
2020-08-11 21:23 abce 阅读(1634) 评论(0) 编辑 收藏 举报我们已经讨论过PostgreSQL索引引擎，访问方法的接口，以及三种方法:hash index, B-tree和GiST。在本文中，我们将描述SP-GiST。
SP-GiST
首先，简单介绍一下这个名字。«GiST»部分暗示了同GiST访问方法的一些相似性。相似性确实存在:两者都是广义搜索树，为构建各种访问方法提供了框架。
«SP»代表空间分区。这里的空间通常就是我们所说的空间，例如，一个二维平面。但我们会发现，任何搜索空间，实际上都是任意值域。
SP-GiST适用于可以递归地将空间分割为不相交区域的结构。这个类包括四叉树、k维树(k-D树)和基数树(radix trees)
Structure
因此，SP-GiST访问方法的思想是将值域(value domain)分割为不重叠的子域，每个子域依次也可以分割。这样的划分导致了树的不平衡(不像b树和常规的GiST)。
不相交的特性简化了在插入和搜索时的决策。另一方面，作为规则，树是低分枝的。例如，四叉树的一个节点通常有四个子节点(与b树不同，b树的节点有数百个)，而且深度更大。像这样的树很适合在RAM中工作，但索引存储在磁盘上，因此，为了减少I/O操作的数量，必须将节点打包到页中，而高效地做到这一点并不容易。此外，由于分支深度的不同，在索引中找到不同值所需的时间也会不同。
这种访问方法与GiST的方式相同，它处理低级别任务(同时访问和锁定、日志记录和纯搜索算法)，并提供专门的简化接口，以支持添加对新数据类型和新分区算法的支持。
SP-GiST树的内部节点存储对子节点的引用;可以为每个引用定义一个标签。 此外，一个内部节点可以存储一个称为前缀的值。实际上，这个值不是必须的前缀;它可以看作是满足所有子节点的任意谓词。
SP-GiST的叶子节点包含索引类型的值和对表行(TID)的引用。被索引的数据本身(搜索键)可以用作值，但不是强制性的:可以存储一个缩短的值。
此外，叶子节点可以分组到列表中。因此，内部节点不仅可以引用一个值，还可以引用整个列表。
请注意，叶节点中的前缀、标签和值具有各自独立的数据类型。
与GiST相同，定义搜索的主要函数是一致性函数。对树节点调用该函数，并返回一组子节点，其值«是一致的»与搜索谓词(通常以“indexed-field operator expression”的形式)。对于叶节点，一致性函数确定该节点中的索引值是否满足搜索谓词。
搜索从根节点开始。一致性函数找出访问哪些子节点是有意义的。算法对每个找到的节点重复执行。搜索是深度优先的。
在物理层，索引节点被打包到页(page)中，以便从I/O操作的角度有效地使用节点。请注意，一个页面可以包含内部节点或叶节点，但不能同时包含这两种节点。
四叉树示例
四叉树用于索引平面上的点。一个想法是递归地将区域分割成相对于中心点的四个部分(象限)。这种树中分支的深度可以变化，并取决于适当象限中点的密度。
这是由openflights.org站点的机场扩展的演示数据库的示例，如图所示。顺便说一下，最近我们发布了一个新版本的数据库，其中我们用«point»类型的字段替换了经度和纬度。
首先，我们分成四个象限。
然后我们把每个象限分开
以此类推，直到我们得到最终的划分。
让我们来看在与gist相关的文章中已经考虑过的一个简单示例的更多细节。看看这种情况下的分区是什么样子的:
象限编号如图1所示。为了明确起见，让我们将子节点从左到右完全按照相同的顺序排列。在这种情况下，可能的索引结构如下图所示。每个内部节点最多引用4个子节点。每个引用都可以用象限号标记，如图所示。但是在实现中没有标签，因为存储一个包含四个引用的固定数组更方便，其中一些引用可以是空的。
位于边界上的点与数值较小的象限有关。
postgres=# create table points(p point); postgres=# insert into points(p) values (point '(1,1)'), (point '(3,2)'), (point '(6,3)'), (point '(5,5)'), (point '(7,8)'), (point '(8,6)'); postgres=# create index points_quad_idx on points using spgist(p);
在本例中，默认使用«quad_point_ops»操作符类，它包含以下操作符:
postgres=# select amop.amopopr::regoperator, amop.amopstrategy
from pg_opclass opc, pg_opfamily opf, pg_am am, pg_amop amop
where opc.opcname = 'quad_point_ops'
and opf.oid = opc.opcfamily
and am.oid = opf.opfmethod
and amop.amopfamily = opc.opcfamily
and am.amname = 'spgist'
and amop.amoplefttype = opc.opcintype;
amopopr | amopstrategy
-----------------+--------------
<<(point,point) | 1 strictly left
>>(point,point) | 5 strictly right
~=(point,point) | 6 coincides
<^(point,point) | 10 strictly below
>^(point,point) | 11 strictly above
<@(point,box) | 8 contained in rectangle
(6 rows)
例如，让我们看看查询如何从执行select * from points where p >^ point '(2,7)'(查找给定点之上的所有点)。
postgres=# set enable_seqscan = off;
postgres=# explain (costs off) select * from points where p >^ point '(2,7)';
QUERY PLAN
------------------------------------------------
Index Only Scan using points_quad_idx on points
Index Cond: (p >^ '(2,7)'::point)
(2 rows)
我们可以使用前面提到的“gevel”扩展来探究SP-GiST索引的内部结构。坏消息是，由于一个bug，这个扩展在现代版本的PostgreSQL中工作不正确。好消息是我们计划用«gevel»的功能来增强«pageinspect»(讨论)。这个错误已经在«pageinspect»中得到修复。
再一次，坏消息是补丁没有任何进展。
demo=# create index airports_coordinates_quad_idx on airports_ml using spgist(coordinates);
首先，我们可以得到一些索引的统计数据:
demo=# select * from spgist_stats('airports_coordinates_quad_idx');
spgist_stats
----------------------------------
totalPages: 33 +
deletedPages: 0 +
innerPages: 3 +
leafPages: 30 +
emptyPages: 2 +
usedSpace: 201.53 kbytes+
usedInnerSpace: 2.17 kbytes +
usedLeafSpace: 199.36 kbytes+
freeSpace: 61.44 kbytes +
fillRatio: 76.64% +
leafTuples: 5993 +
innerTuples: 37 +
innerAllTheSame: 0 +
leafPlaceholders: 725 +
innerPlaceholders: 0 +
leafRedirects: 0 +
innerRedirects: 0
(1 row)
第二，我们可以输出索引树本身:
demo=# select tid, n, level, tid_ptr, prefix, leaf_value
from spgist_print('airports_coordinates_quad_idx') as t(
tid tid,
allthesame bool,
n int,
level int,
tid_ptr tid,
prefix point, -- prefix type
node_label int, -- label type (unused here)
leaf_value point -- list value type
)
order by tid, n;
tid | n | level | tid_ptr | prefix | leaf_value
---------+---+-------+---------+------------------+------------------
(1,1) | 0 | 1 | (5,3) | (-10.220,53.588) |
(1,1) | 1 | 1 | (5,2) | (-10.220,53.588) |
(1,1) | 2 | 1 | (5,1) | (-10.220,53.588) |
(1,1) | 3 | 1 | (5,14) | (-10.220,53.588) |
(3,68) | | 3 | | | (86.107,55.270)
(3,70) | | 3 | | | (129.771,62.093)
(3,85) | | 4 | | | (57.684,-20.430)
(3,122) | | 4 | | | (107.438,51.808)
(3,154) | | 3 | | | (-51.678,64.191)
(5,1) | 0 | 2 | (24,27) | (-88.680,48.638) |
(5,1) | 1 | 2 | (5,7) | (-88.680,48.638) |
...
但是请记住，«spgist_print»并不是输出所有的叶子值，而是只输出列表中的第一个叶子值，因此显示的是索引的结构，而不是它的全部内容。
k-dimensional(K维)树示例
对于平面上相同的点，我们也可以提出另一种划分空间的方法。
让我们通过索引的第一个点画一条水平线。它把平面分成上下两部分。要索引的第二个点属于这些部分之一。通过这一点，让我们画一条垂线，它把这部分分成两部分:右和左。我们再画一条水平线穿过下一个点，再画一条垂直线穿过下一个点，以此类推。
以这种方式构建的树的所有内部节点将只有两个子节点。这两个引用中的每一个都可以指向层次结构中的下一个内部节点，或者指向叶节点列表。
该方法易于推广到k维空间，因此在文献中也称其为k维(k-D树)。
以机场为例说明方法:
首先我们分成上下两部分。
然后我们把每一部分分成左右两部分
以此类推，直到我们得到最终的划分。
要像这样使用分区，我们需要在创建索引时显式地指定操作符类«kd_point_ops»。
postgres=# create index points_kd_idx on points using spgist(p kd_point_ops);
原理
在浏览树结构的时候，我们需要考虑到在这种情况下，前缀只是一个坐标而不是一个点:
demo=# select tid, n, level, tid_ptr, prefix, leaf_value
from spgist_print('airports_coordinates_kd_idx') as t(
tid tid,
allthesame bool,
n int,
level int,
tid_ptr tid,
prefix float, -- prefix type
node_label int, -- label type (unused here)
leaf_value point -- list node type
)
order by tid, n;
tid | n | level | tid_ptr | prefix | leaf_value
---------+---+-------+---------+------------+------------------
(1,1) | 0 | 1 | (5,1) | 53.740 |
(1,1) | 1 | 1 | (5,4) | 53.740 |
(3,113) | | 6 | | | (-7.277,62.064)
(3,114) | | 6 | | | (-85.033,73.006)
(5,1) | 0 | 2 | (5,12) | -65.449 |
(5,1) | 1 | 2 | (5,2) | -65.449 |
(5,2) | 0 | 3 | (5,6) | 35.624 |
(5,2) | 1 | 3 | (5,3) | 35.624 |
...
radix树示例
我们还可以使用SP-GiST实现字符串的基数树。 基数树的思想是，要索引的字符串并不完全存储在叶节点中，而是通过将上面节点中存储的值连接到根节点来获得。
假设我们需要索引站点的url:«postgrespro.ru»、«postgrespro.com»、«postgresql.org»和«planet.postgresql.org»。
postgres=# create table sites(url text);
postgres=# insert into sites values ('postgrespro.ru'),('postgrespro.com'),('postgresql.org'),('planet.postgresql.org');
postgres=# create index on sites using spgist(url);
树的样子如下:
树存储的内部节点使用所有子节点共有的前缀。例如，在«stgres»的子节点中，值以«p»+«o»+«stgres»开始。
与四叉树不同的是，每个指向子节点的指针都另外用一个字符标记(更确切地说，用两个字节，但这不是很重要)。
«text_ops»操作符类支持类似b树的操作符:«equal»、«greater»和«less»:
postgres=# select amop.amopopr::regoperator, amop.amopstrategy
from pg_opclass opc, pg_opfamily opf, pg_am am, pg_amop amop
where opc.opcname = 'text_ops'
and opf.oid = opc.opcfamily
and am.oid = opf.opfmethod
and amop.amopfamily = opc.opcfamily
and am.amname = 'spgist'
and amop.amoplefttype = opc.opcintype;
amopopr | amopstrategy
-----------------+--------------
~<~(text,text) | 1
~<=~(text,text) | 2
=(text,text) | 3
~>=~(text,text) | 4
~>~(text,text) | 5
<(text,text) | 11
<=(text,text) | 12
>=(text,text) | 14
>(text,text) | 15
(9 rows)
使用波浪号的操作符的区别在于它们操作的是字节而不是字符。
有时，以基数树的形式表示可能会比b树更紧凑，因为值没有被完全存储，而是在树中往下时根据需要重新构建。
考虑一个查询:select * from sites where url like 'postgresp%ru'。可以使用索引执行:
postgres=# explain (costs off) select * from sites where url like 'postgresp%ru';
QUERY PLAN
------------------------------------------------------------------------------
Index Only Scan using sites_url_idx on sites
Index Cond: ((url ~>=~ 'postgresp'::text) AND (url ~<~ 'postgresq'::text))
Filter: (url ~~ 'postgresp%ru'::text)
(3 rows)
实际上，索引用于查找大于或等于«postgresp»但小于«postgresq»的值(索引Cond)，然后从结果中选择匹配的值(过滤器)。
首先，一致性函数必须决定我们需要下行到«p»根的哪个子节点。 有两个选项可供选择:«p»+«l»(不需要向下，即使不深入也很清楚)和«p»+«o»+«stgres»(继续向下)。
对于«stgres»节点，需要再次调用一致性函数来检查«postgres»+«p»+«ro。 »(继续向下)和«postgres»+«q»(不需要向下)。
«ro.»节点及其所有子叶节点，一致性函数将响应«yes»，因此索引方法将返回两个值:«postgrespro.com»和«postgrespro.ru»。在过滤阶段将从它们中选择一个匹配值。
原理
让我们看看SP-GiST访问方法的属性(查询已经在前面提供):
amname | name | pg_indexam_has_property --------+---------------+------------------------- spgist | can_order | f spgist | can_unique | f spgist | can_multi_col | f spgist | can_exclude | t
SP-GiST索引不能用于排序和支持惟一约束。此外，像这样的索引不能在多个列上创建(与GiST不同)。但是允许使用这样的索引来支持排除约束。
以下是索引层可用的属性:
name | pg_index_has_property ---------------+----------------------- clusterable | f index_scan | t bitmap_scan | t backward_scan | f
这里与GiST的区别是clusterable不支持。
最后是列层的属性:
name | pg_index_column_has_property --------------------+------------------------------ asc | f desc | f nulls_first | f nulls_last | f orderable | f distance_orderable | f returnable | t search_array | f search_nulls | t
不支持排序，这是可预测的。到目前为止，SP-GiST中还没有用于搜索最近邻居的距离运算符。最有可能的是，这个特性将来会得到支持。
即将发布的由Nikita Glukhov发布的PostgreSQL 12补丁将支持它。
SP-GiST可用于index-only扫描，至少用于讨论过的操作符类。正如我们所看到的，在某些情况下，索引值显式存储在叶节点中，而在其他情况下，在树下降过程中，将部分地重新构建这些值。
NULLs
为了不使问题复杂化，我们到目前为止还没有提到nulls。从索引属性可以清楚地看出，支持null:
postgres=# explain (costs off)
select * from sites where url is null;
QUERY PLAN
----------------------------------------------
Index Only Scan using sites_url_idx on sites
Index Cond: (url IS NULL)
(2 rows)
但是，NULL对于spgist来说是陌生的。来自«spgist»操作符类的所有操作符必须是严格的:当操作符的任何参数为空时，都必须返回NULL。方法本身确保了这一点:null只是没有传递给操作符。
但是要使用仅索引扫描的访问方法，无论如何都必须将空值存储在索引中。它们被存储在一个单独的树中，有自己的根。
其他数据类型
除了点和用于字符串的基数树，其他基于SP-GiST的方法也在PostgreSQL中实现:
·«box_ops»操作符类为矩形提供了一个四叉树。 每个矩形由四维空间中的一个点表示，因此象限的数目等于16。 当矩形有很多交点时，这样的索引可以在性能上击败GiST:在GiST中不可能画出边界来将相交的对象从另一个分离，而点则没有这样的问题(甚至四维)。 ·«range_ops»操作符类为intervals提供了一个四叉树。 区间用二维点表示，下边界为横坐标，上边界为纵坐标。
