select * from gist_print('pix') as t(level int, valid bool, a box) where level =1;
June 19, 2020 ยท View on GitHub
Gevel contrib module provides several functions useful for analyzing GiST and GIN index.
[Online version] of this document (http://www.sai.msu.su/~megera/oddmuse/index.cgi/Gevel)
Caution: This module was designed for advanced users of GIN, GiST and SP-GiST indices !
Authors
* Oleg Bartunov <oleg@sai.msu.su>, Moscow, Moscow University, Russia
* Teodor Sigaev <teodor@sigaev.ru>, Moscow, Moscow University, Russia
* Daria Lepikhova <d.lepikhova@postgrespro.ru>, Postgres Pro
License
Stable version, included into PostgreSQL distribution, released under BSD license. Development version, available from this site, released under the GNU General Public License, version 2 (June 1991)
Install gevel module (requires 8.1+ version): % cd PGSQLSRC/contrib % tar xzvf gevel.tar.gz % make % make install % make installcheck % psql regression < gevel.sql
* gist_stat(INDEXNAME) - show some statistics about GiST tree
regression=# 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]) - show GiST tree up to MAXLEVEL
regression=# select gist_tree('pix',0); 0(l:0) blk: 0 numTuple: 29 free: 6888b(15.63%)
Designation (from left to right):
o 0 - page number
o (l:0) - tree level
o blk: 0 - block number
o numTuple: 29 - the number of tuples
o free: 6888b - free space in bytes
o (15.63%) - occupied space in percents
* gist_tree(INDEXNAME) - show full GiST tree
regression=# 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) - prints objects stored in GiST tree,
works only if objects in index have textual representation
(type_out functions should be implemented for given object type).
It's known to work with R-tree GiST based index.
Note, in example below, objects are of type box.
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)
* spgist_stat(INDEXNAME) - show some statistics about SP-GiST tree
SELECT spgist_stat('spgist_idx');
spgist_stat
totalPages: 21 + deletedPages: 0 + innerPages: 3 + leafPages: 18 + emptyPages: 1 + usedSpace: 121.27 kbytes+ freeSpace: 46.07 kbytes + fillRatio: 72.47% + leafTuples: 3669 + innerTuples: 20 + innerAllTheSame: 0 + leafPlaceholders: 569 + innerPlaceholders: 0 + leafRedirects: 0 + innerRedirects: 0
* spgist_print(INDEXNAME) - prints objects stored in GiST tree,
works only if objects in index have textual representation
(type_out functions should be implemented for given object type).
Note 1. in example below we used quad_point_ops which uses point
for leaf and prefix value, but doesn't use node_label at all.
Use type 'int' as dummy type for prefix or/and node_label.
Note 2
quad_point_ops: prefix point, node_label int, leaf_value point
kd_point_ops: prefix float, node_label int, leaf_value point
text_ops: prefix text, node_label char, leaf_value text
SELECT * FROM spgist_print('spgist_idx') as t
(
tid tid,
allthesame bool,
node_n int,
level int,
tid_pointer tid,
prefix point,
node_label int,
leaf_value point
) where level = 1;
tid | allthesame | node_n | level | tid_pointer | prefix | node_label | leaf_value -------+------------+--------+-------+-------------+-------------------------------------+------------+------------ (1,1) | f | 0 | 1 | (5,4) | (24530.2070484581,23595.7092511013) | | (1,1) | f | 1 | 1 | (5,3) | (24530.2070484581,23595.7092511013) | | (1,1) | f | 2 | 1 | (5,2) | (24530.2070484581,23595.7092511013) | | (1,1) | f | 3 | 1 | (5,1) | (24530.2070484581,23595.7092511013) | |
- gin_stat(INDEXNAME) prints estimated counts for each indexed values Note: since 8.4 gin_stat function has gin_stat(INDEXNAME, COLNUMBER) prototype, single-argument function will return result for a first column of index
SELECT * FROM gin_stat('gin_idx') as t(value int, nrow int) where nrow > 250;
value | nrow -------+------ 31 | 254 47 | 251 52 | 257 59 | 259 (4 rows)
- bigint gin_count_estimate(INDEXNAME, TSQUERY) outputs number of indexed rows matched query. It doesn't touch heap at all.
select gin_count_estimate('qq', 'star');
gin_count_estimate
790
(1 row)
- text gin_statpage(INDEXNAME) Prints various stat about index internals.
select gin_statpage('gin_idx');
gin_statpage
totalPages: 32 + dataPages: 1 + dataInnerPages: 0 + dataLeafPages: 1 + dataInnerFreeSpace: 0 + dataLeafFreeSpace: 4454 + dataInnerTuplesCount: 0 + dataLeafIptrsCount: 3600 + entryPages: 31 + entryInnerPages: 21 + entryLeafPages: 10 + entryInnerFreeSpace: 15160+ entryLeafFreeSpace: 32788+ entryInnerTuplesCount: 7810 + entryLeafTuplesCount: 305 + entryPostingSize: 42122+ entryPostingCount: 96759+ entryAttrSize: 64924+
- btree_stat(INDEXNAME) - show some statistics about btree index
SELECT btree_stat('btree_idx');
btree_stat
Number of levels: 2 + Number of pages: 75 + Number of leaf pages: 74 + Number of tuples: 11047 + Number of invalid tuples: 0 + Number of leaf tuples: 10973 + Total size of tuples: 547824 bytes+ Total size of leaf tuples: 543948 bytes+ Total size of index: 614400 bytes+
(1 row)
- btree_tree(INDEXNAME[, MAXLEVEL]) - show btree elements from root up to MAXLEVEL
SELECT btree_tree('btree_idx');
btree_tree
lvl: 0, blk: 3, numTuples: 74 + lvl: 1, blk: 1, numTuples: 139 + lvl: 1, blk: 2, numTuples: 139 + lvl: 1, blk: 4, numTuples: 139 + lvl: 1, blk: 5, numTuples: 136 + lvl: 1, blk: 6, numTuples: 141 + lvl: 1, blk: 7, numTuples: 139 + lvl: 1, blk: 8, numTuples: 140 + lvl: 1, blk: 9, numTuples: 136 + lvl: 1, blk: 10, numTuples: 140+ lvl: 1, blk: 11, numTuples: 138+ lvl: 1, blk: 12, numTuples: 143+ lvl: 1, blk: 13, numTuples: 137+ lvl: 1, blk: 14, numTuples: 138+ lvl: 1, blk: 15, numTuples: 140+ lvl: 1, blk: 16, numTuples: 141+ lvl: 1, blk: 17, numTuples: 138+ lvl: 1, blk: 18, numTuples: 141+ lvl: 1, blk: 19, numTuples: 141+ lvl: 1, blk: 20, numTuples: 138+ lvl: 1, blk: 21, numTuples: 141+ lvl: 1, blk: 22, numTuples: 141+ lvl: 1, blk: 23, numTuples: 141+ lvl: 1, blk: 24, numTuples: 138+ lvl: 1, blk: 25, numTuples: 140+ lvl: 1, blk: 26, numTuples: 142+ lvl: 1, blk: 27, numTuples: 141+ lvl: 1, blk: 28, numTuples: 142+ lvl: 1, blk: 29, numTuples: 140+ lvl: 1, blk: 30, numTuples: 141+ lvl: 1, blk: 31, numTuples: 140+ lvl: 1, blk: 32, numTuples: 142+ lvl: 1, blk: 33, numTuples: 143+ lvl: 1, blk: 34, numTuples: 141+ lvl: 1, blk: 35, numTuples: 140+ lvl: 1, blk: 36, numTuples: 142+ lvl: 1, blk: 37, numTuples: 142+ lvl: 1, blk: 38, numTuples: 141+ lvl: 1, blk: 39, numTuples: 143+ lvl: 1, blk: 40, numTuples: 142+ lvl: 1, blk: 41, numTuples: 143+ lvl: 1, blk: 42, numTuples: 143+ lvl: 1, blk: 43, numTuples: 142+ lvl: 1, blk: 44, numTuples: 143+ lvl: 1, blk: 45, numTuples: 142+ lvl: 1, blk: 46, numTuples: 142+ lvl: 1, blk: 47, numTuples: 141+ lvl: 1, blk: 48, numTuples: 144+ lvl: 1, blk: 49, numTuples: 141+ lvl: 1, blk: 50, numTuples: 142+ lvl: 1, blk: 51, numTuples: 143+ lvl: 1, blk: 52, numTuples: 142+ lvl: 1, blk: 53, numTuples: 143+ lvl: 1, blk: 54, numTuples: 141+ lvl: 1, blk: 55, numTuples: 142+ lvl: 1, blk: 56, numTuples: 141+ lvl: 1, blk: 57, numTuples: 156+ lvl: 1, blk: 58, numTuples: 159+ lvl: 1, blk: 59, numTuples: 160+ lvl: 1, blk: 60, numTuples: 161+ lvl: 1, blk: 61, numTuples: 163+ lvl: 1, blk: 62, numTuples: 163+ lvl: 1, blk: 63, numTuples: 164+ lvl: 1, blk: 64, numTuples: 165+ lvl: 1, blk: 65, numTuples: 165+ lvl: 1, blk: 66, numTuples: 166+ lvl: 1, blk: 67, numTuples: 166+ lvl: 1, blk: 68, numTuples: 167+ lvl: 1, blk: 69, numTuples: 167+ lvl: 1, blk: 70, numTuples: 167+ lvl: 1, blk: 71, numTuples: 167+ lvl: 1, blk: 72, numTuples: 167+ lvl: 1, blk: 73, numTuples: 167+ lvl: 1, blk: 74, numTuples: 167+ lvl: 1, blk: 75, numTuples: 274+
(1 row)
test1=# SELECT btree_tree('btree_idx', 0); btree_tree
lvl: 0, coff: 0, blk: 3, numTuples: +
(1 row)
- btree_print() - print objects stored in btree works only if objects in index have textual representation (type_out functions should be implemented for given object type).
SELECT * FROM btree_print('btree_idx') as t(level int, val bool, a int[]) where level = 1;
level | val | a
-------+-----+-------------------------
1 | t |
1 | t | {1,1001}
1 | t | {3,52,31,62,98}
1 | t | {5,3,25}
1 | t | {6,70,15}
1 | t | {8,1}
1 | t | {9,96}
1 | t | {11,37,55}
1 | t | {12,96,72,23,83,56}
1 | t | {14,30,132,144,174}
1 | t | {15,87}
1 | t | {17,36,97,77}
1 | t | {18,171,283}
1 | t | {20,66,81}
1 | t | {22,31,38,77,21}
1 | t | {24,2,96}
1 | t | {25,218,241,241}
1 | t | {27,87,61,54,41}
1 | t | {29,53,109,128,129,195}
1 | t | {30,183,215}
1 | t | {32,41,4}
1 | t | {34,44,47,81,124,135}
1 | t | {35,1001}
1 | t | {37,81,85}
1 | t | {39,74,47,71,63,80}
1 | t | {41,60,71}
1 | t | {43,41,12,44,75,91}
1 | t | {45,26,61,15}
1 | t | {47,22,65}
1 | t | {49,16,63,56}
1 | t | {50,98,71,10}
1 | t | {52,53,81}
1 | t | {54,46,31,87,13,8}
1 | t | {55,1001}
1 | t | {57,99,66,93}
1 | t | {59,103,180,196}
1 | t | {61,63,59,62}
1 | t | {63,66}
1 | t | {65,30,39,82,89,34}
1 | t | {67,25}
1 | t | {69,8,75}
1 | t | {71,23,89}
1 | t | {73,24,81,58,22}
1 | t | {75,54,93,39,18}
1 | t | {77,31,77}
1 | t | {79,59}
1 | t | {81,61,86,96,47,67}
1 | t | {83,79,46,37,99}
1 | t | {85,72}
1 | t | {87,93,19}
1 | t | {89,161}
1 | t | {91,1001}
1 | t | {94,8}
1 | t | {96,33,86,28,19}
1 | t | {98,53,58,58,61}
1 | t | {100,80,23,24,17}
1 | t | {107,1001}
1 | t | {115,1001}
1 | t | {124,1001}
1 | t | {133,1001}
1 | t | {143,1001}
1 | t | {154,1000}
1 | t | {164,1001}
1 | t | {175,1001}
1 | t | {186,1001}
1 | t | {198,1001}
1 | t | {210,1001}
1 | t | {222,1001}
1 | t | {235,1001}
1 | t | {248,266}
1 | t | {260,1001}
1 | t | {273,1001}
1 | t | {285,1001}
1 | t | {298,1001}
(74 rows)
- brin_stat(INDEXNAME) - show some statistics about brin index
SELECT brin_stat('brin_idx');
brin_stat
Number of revmap pages: 1 + Number of empty revmap pages: 0 + Number of regular pages: 1 + Number of tuples: 1 + Used space 56 bytes + Free space 8104 bytes+
(1 row)
- brin_print() - print values for pages stored in brin index
SELECT brin_print('brin_idx')
brin_print
Start block: 0; end block: 48; offset: 86, free: 3292+
(1 row)