Postgresql安装gevel插件

最新推荐文章于 2023-08-15 14:59:24 发布

魂醉

最新推荐文章于 2023-08-15 14:59:24 发布

阅读量698

点赞数

分类专栏： # PostgreSQL

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

版权

PostgreSQL 专栏收录该内容

206 篇文章 63 订阅

订阅专栏

概念介绍

gevel插件可以查看Gist,GIN,SP-Gist索引的内部结构信息。PG自身不提供此功能，所以需要安装插件。在之前的索引介绍都使用过该插件查看索引的信息。

安装

历史版本可以此链接下载http://www.sai.msu.su/~megera/postgres/gist/gevel/

克隆gevel仓库

git clone git://sigaev.ru/gevel

或者解压tar包

tar xzvf gevel.tar.gz

这里我的pg是11.5，把gevel目录移动到pg的源码contrib目录下，然后编译安装

mv gevel /home/postgres/postgresql-11.5/contrib

make
make install
make installcheck

编译后，会生成gevel.so文件，把该文件复制到pg的lib库目录下

pg_config --pkglibdir
/opt/pgsql11.5/lib
cp  gevel.so /opt/pgsql11.5/lib

执行生成gevel.sql脚本文件，给hank库创建相关函数后就可以使用了

psql hank < gevel.sql
SET
BEGIN
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
COMMIT

举例介绍一下如何使用：

假设有两张表和两个索引

# \pset tuples_only 1
Showing only tuples.
#  \d
          List of relations
 Schema |  Name   | Type  |  Owner   
--------+---------+-------+----------
 public | boxtmp  | table | postgres
 public | polytmp | table | postgres
(2 rows)
# \di
             List of relations
 Schema | Name | Type  |  Owner   |  Table  
--------+------+-------+----------+---------
 public | bix  | index | postgres | boxtmp
 public | pix  | index | postgres | polytmp

通过gist_stat(INDEXNAME) 查看Gist索引的统计信息

select gist_stat('pix');
 Number of levels:          2
Number of pages:           30
Number of leaf pages:      29
Number of tuples:          3129
Number of leaf tuples:     3100
Total size of tuples:      137676 bytes
Total size of leaf tuples: 136400 bytes
Total size of index:       245760 bytes

通过gist_tree(INDEXNAME,MAXLEVEL)查看Gist索引树的最大层

select gist_tree('pix',0);
 0(l:0) blk: 0 numTuple: 29 free: 6888b(15.63%)

从左到右的参数解释
0 - page号
(l:0) - 树级别
blk: 0 - 块号
numTuple: 29 - 行号
free: 6888b - 可用空间，以byte为单位
(15.63%) - 占用空间的百分比

通过gist_tree(INDEXNAME)查看完整的Gist树结构

select gist_tree('pix');
 0(l:0) blk: 0 numTuple: 29 free: 6888b(15.63%)
    1(l:1) blk: 13 numTuple: 180 free: 244b(97.01%)
    2(l:1) blk: 11 numTuple: 175 free: 464b(94.32%)
    3(l:1) blk: 2 numTuple: 101 free: 3720b(54.43%)
    4(l:1) blk: 17 numTuple: 111 free: 3280b(59.82%)
    5(l:1) blk: 18 numTuple: 101 free: 3720b(54.43%)
    6(l:1) blk: 10 numTuple: 98 free: 3852b(52.82%)
    7(l:1) blk: 19 numTuple: 111 free: 3280b(59.82%)
    8(l:1) blk: 9 numTuple: 97 free: 3896b(52.28%)
    9(l:1) blk: 20 numTuple: 104 free: 3588b(56.05%)
    10(l:1) blk: 14 numTuple: 96 free: 3940b(51.74%)
    11(l:1) blk: 21 numTuple: 106 free: 3500b(57.13%)
    12(l:1) blk: 7 numTuple: 103 free: 3632b(55.51%)
    13(l:1) blk: 1 numTuple: 101 free: 3720b(54.43%)
    14(l:1) blk: 16 numTuple: 97 free: 3896b(52.28%)
    15(l:1) blk: 24 numTuple: 103 free: 3632b(55.51%)
    16(l:1) blk: 4 numTuple: 98 free: 3852b(52.82%)
    17(l:1) blk: 25 numTuple: 98 free: 3852b(52.82%)
    18(l:1) blk: 3 numTuple: 97 free: 3896b(52.28%)
    19(l:1) blk: 26 numTuple: 96 free: 3940b(51.74%)
    20(l:1) blk: 6 numTuple: 103 free: 3632b(55.51%)
    21(l:1) blk: 8 numTuple: 162 free: 1036b(87.31%)
    22(l:1) blk: 23 numTuple: 94 free: 4028b(50.66%)
    23(l:1) blk: 12 numTuple: 82 free: 4556b(44.19%)
    24(l:1) blk: 27 numTuple: 105 free: 3544b(56.59%)
    25(l:1) blk: 5 numTuple: 90 free: 4204b(48.51%)
    26(l:1) blk: 28 numTuple: 100 free: 3764b(53.90%)
    27(l:1) blk: 22 numTuple: 101 free: 3720b(54.43%)
    28(l:1) blk: 15 numTuple: 95 free: 3984b(51.20%)
    29(l:1) blk: 29 numTuple: 95 free: 3984b(51.20%)

通过Gist_print（INDEXNAME）-打印存储在GiST中的具体的索引行数据。

# \di 
             List of relations
 Schema | Name | Type  |  Owner   |  Table  
--------+------+-------+----------+---------
 public | bix  | index | postgres | boxtmp
 public | pix  | index | postgres | polytmp
(2 rows)
# \d pix
Index "public.pix"
 Column | Type 
--------+------
 p      | box
gist, for table "public.polytmp"

# select * from gist_print('pix') as t(level int, valid bool, a box) where level =1;
 level | valid |              a
-------+-------+-----------------------------
     1 | t     | (37357,50073),(34242,357)
     1 | t     | (43499,49770),(40358,43)
     1 | t     | (31193,24679),(25047,12410)
     1 | t     | (31018,12142),(25083,6)
     1 | t     | (49944,25174),(43471,12802)
     1 | t     | (12577,49757),(6302,37534)
     1 | t     | (12528,37333),(6171,24861)
     1 | t     | (50027,49751),(46817,25462)
     1 | t     | (46870,49912),(43664,25722)
     1 | t     | (24855,25574),(12447,19263)
     1 | t     | (25054,19126),(12403,12796)
     1 | t     | (32737,49923),(31178,1038)  
     1 | t     | (3184,24465),(15,81)
     1 | t     | (24951,49983),(12740,44000)
     1 | t     | (24919,43956),(12617,37901)
     1 | t     | (40387,49852),(37338,25217)
     1 | t     | (40325,24963),(37375,491)  
     1 | t     | (24919,12698),(12654,6518)
     1 | t     | (25002,6338),(12350,51)   
     1 | t     | (49985,12554),(43447,222)  
     1 | t     | (25003,37769),(12552,25573)
     1 | t     | (34270,49382),(32763,594)  
     1 | t     | (6205,50012),(3,37527)
     1 | t     | (6163,37358),(120,25034)  
     1 | t     | (12343,24542),(9295,294)
     1 | t     | (9308,24151),(6234,620)
     1 | t     | (6230,24629),(3169,108)
     1 | t     | (31179,50040),(28113,25556)
     1 | t     | (28048,49694),(25000,25000)
(29 rows)

参考：http://www.sai.msu.su/~megera/wiki/Gevel