Postgresql SP-Gist索引

最新推荐文章于 2024-03-10 16:42:55 发布

魂醉

最新推荐文章于 2024-03-10 16:42:55 发布

阅读量2.2k

点赞数

分类专栏： # PostgreSQL

本文链接：https://blog.csdn.net/dazuiba008/article/details/104607674/

版权

PostgreSQL 专栏收录该内容

206 篇文章 59 订阅

订阅专栏

SP-Gist

SP-Gist和Gist类似，
«SP»是space partitioning（空间划分）。这里的空间通常就是我们通常所说的空间，例如，二维平面。
SP-GiST适用于空间被递归划分为非相交区域的结构。此类包括四叉树，k维树（k-D树）和基数树。

结构介绍

SP-GiST的思想是将值域拆分为非重叠的子域，每个子域又可以拆分。像这样的分区会生成不平衡的树（不像B树和常规的GiST）。

不相交的特性简化了插入和搜索过程方面的决策。另一方面，通常，树是低分支的。例如，四叉树有四个子节点（与B树不同，后者的节点数为数百个），并且深度更大。像这样的树很适合RAM中的工作，但是索引存储在磁盘上，因此，为了减少I / O操，必须将节点塞到页面中，而有效地做到这一点并不容易。此外，由于分支深度的差异，在索引中找不同值所花费的时间可能会有所不同。

这种访问方法与GiST一样，可以处理低级任务（同时进行访问和锁定，日志记录和纯搜索算法），并提供专门的简化接口，以增加对新数据类型和新分区算法的支持。

SP-GiST树的内部节点存储子节点的引用；可以为每个引用定义标签。此外，内部节点存储称为prefix的值。实际上，该值不是必须的前缀；它被视为满足所有子节点的专有谓词。

SP-GiST的叶节点包含索引类型的值和对表行（TID）的引用。索引数据本身（搜索关键字）可以作为值使用，但不是必须的：可以存储缩短的值。

此外，叶节点可以分组为列表。因此，内部节点不仅可以引用一个值，而且可以引用整个列表。

请注意，叶子节点的前缀，标签和值具有各自独立的数据类型。

与GiST中相同，定义搜索的主要函数是一致性函数。该函数为树节点调用，并返回一组子节点，其值与搜索谓词“一致”（通常，以“索引字段运算符表达式”的形式）。对于叶节点，一致性函数确定该节点中的索引值是否满足搜索谓词。

搜索从根节点开始。一致性功能允许找出有意义的子节点。对找到的每个节点重复该算法。搜索是深度优先的。

在物理层面，从I/O操作的角度来看，索引节点被打包到页面中以使节点高效工作。请注意，一个页面可以包含内部节点或者叶子节点，但不能同时包含两者。

下面分别用四叉树，K-D树，基数树举例说明其搜索原理

四叉树

四叉树用于索引平面中的点。核心思想是相对于中心点将区域递归地分为四个部分（也叫象限）。这样索引树的分支深度可以变化，并取决于象限中点的密度。

正如图中所示的样子，示例是通过示例数据库。
在这里插入图片描述
首先，我们把一副平面图分割为4个象限。

然后，再继续分割每个象限

然后一直分割到最终需要的分区

接下来我们提供一个详细而简单的示例。查看这个示例中是如何分区的：
在这里插入图片描述
第一个图有象限的编号。下面的图展示了了这种情况下的索引结构。每个内部节点最多有四个子节点。如图所示，每个节点都可以用象限编号标记。但是在执行过程中并没有标签，因为存储四个固定数组更为方便，而且其中一些引用可以为空，比如编号为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)’ 这个查询语句是如何查找的。
在这里插入图片描述

首先从根节点开始，并使用一致性函数选择要下降到的子节点。对于运算符> ^，此函数将点（2,7）与中心节点（4,4）的进行比较，并选择可能包含所寻找点的象限，在这种情况下，y轴7>4,会选择会选择第一和第四象限。

在第一象限相对应的节点中，我们再次使用一致性函数确定子节点。中心点是（6,6），我们再次需要从第一和第四象限寻找。

在这里插入图片描述
叶节点（8,6）和（7,8）的列表对应于第一象限，其中只有点（7,8）满足查询条件。第四象限为空。

在内部节点（4,4）中，第四象限的引用也为空，这样就完成了搜索。

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索引的内部结构。可以看到有2个函数。

demo=# \df *spgist*
                           List of functions
 Schema |     Name     | Result data type | Argument data types | Type 
--------+--------------+------------------+---------------------+------
 public | spgist_print | SETOF record     | text                | func
 public | spgist_stat  | text             | text                | func
demo=# create index airports_coordinates_quad_idx on airports_ml using spgist(coordinates);

可以获取索引的统计信息

demo=# select * from spgist_stat('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维树

对于平面中的点，我们还有另一种划分空间的方法。
让我们通过被索引的第一个点画一条水平线。它将平面分为两部分：上部和下部。要索引的第二点分别属于这两个部分。然后，让我们画一条垂直线，将这两个部分再分为两部分：右和左。我们再次通过下一个点画一条水平线，并通过下一个点再画一条垂直线，依此类推。

以这种方式构建的树的所有内部节点将只有两个子节点。这两个引用中的每一个都可以指向层次结构中下一个内部节点或叶节点列表。

这种方法可以很容易地推广到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 tree)

我们还可以使用SP-GiST为字符串实现基数树。基数树的想法是要索引的字符串没有完全存储在叶节点中，而是通过将存储在该节点上方的节点中的值连接到根来获得的。
假设，我们需要索引以下站点：«postgrespro.ru», «postgrespro.com», «postgresql.org», «planet.postgresql.org».

create table sites(url text);

insert into sites values ('postgrespro.ru'),('postgrespro.com'),('postgresql.org'),('planet.postgresql.org');

create index on sites using spgist(url);

树结构如下：
在这里插入图片描述

树的内部节点存储所有子节点共有的前缀。例如，在«stgres»的子节点中，值以«p»+«o»+«stgres»开头。
与四叉树不同，指向子节点的每个指针都另外用一个字符标记（更确切地说，用两个字节标记，但这并不那么重要）。
«text_ops»运算符类支持类似B树的运算符：«equal», «greater», «less»:

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’，可能使用以下索引执行

set enable_seqscan = off;
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”（索引条件）的值，然后从结果中选择匹配值（过滤器）。

首先，通过一致性函数确定我们需要下降到哪个子节点。有两个选项：«p»+«l»（无需下降）和«p»+«o»+«stgres»（继续下降）。
对于«stgres»节点，再次需要调用一致性函数以检查«postgres»+«p»+«ro.» （继续下降）和«postgres»+«q»（无需下降）。
对于«ro.»节点及其所有子叶节点，一致性函数将响应“yes”，因此索引方法将返回两个值：“ postgrespro.com”和“ postgrespro.ru”。最后在过滤阶段将选择一个匹配值postgrespro.ru。

在这里插入图片描述

内部结构

select * from spgist_print('sites_url_idx') as t(
  tid tid,
  allthesame bool,
  n int,
  level int,
  tid_ptr tid,
  prefix text,         -- prefix type
  node_label smallint, -- label type
  leaf_value text      -- leaf node type
)
order by tid, n;

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的区别在于，不可以根据索引重新排序。

以下为列相关的属性：

        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不提供用于搜索最近邻居的距离运算符。
SP-GiST可用于仅索引扫描，至少可以用于以上讨论的操作符。如我们所见，在某些情况下，索引值明确的存储在叶节点中，而在另一些情况下，索引值在树下降过程中部分被重建。

NULLs

为了以上示例更加简单化，到目前为止，我们还没有提到NULL。从上面列的属性可以看出，是支持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)

魂醉

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
Postgresql SP-Gist索引

SP-GistSP-Gist和Gist类似，«SP»是space partitioning（空间划分）。这里的空间通常就是我们通常所说的空间，例如，二维平面。SP-GiST适用于空间被递归划分为非相交区域的结构。此类包括四叉树，k维树（k-D树）和基数树。结构介绍SP-GiST的思想是将值域拆分为非重叠的子域，每个子域又可以拆分。像这样的分区会生成不平衡的树（不像B树和常规的GiST）。...
复制链接

扫一扫