1 Gevel contrib module provides several functions useful for analyzing GiST and GIN index.
3 [Online version] of this document (http://www.sai.msu.su/~megera/oddmuse/index.cgi/Gevel)
5 Caution: This module was designed for advanced users of GIN, GiST and SP-GiST indices !
9 * Oleg Bartunov <oleg@sai.msu.su>, Moscow, Moscow University, Russia
10 * Teodor Sigaev <teodor@sigaev.ru>, Moscow, Moscow University, Russia
14 Stable version, included into PostgreSQL distribution, released under
15 BSD license. Development version, available from this site, released under
16 the GNU General Public License, version 2 (June 1991)
18 Install gevel module (requires 8.1+ version):
20 % tar xzvf gevel.tar.gz
24 % psql regression < gevel.sql
26 * gist_stat(INDEXNAME) - show some statistics about GiST tree
28 regression=# select gist_stat('pix');
31 Number of leaf pages: 29
32 Number of tuples: 3129
33 Number of leaf tuples: 3100
34 Total size of tuples: 137676 bytes
35 Total size of leaf tuples: 136400 bytes
36 Total size of index: 245760 bytes
38 * gist_tree(INDEXNAME[,MAXLEVEL]) - show GiST tree up to MAXLEVEL
40 regression=# select gist_tree('pix',0);
41 0(l:0) blk: 0 numTuple: 29 free: 6888b(15.63%)
43 Designation (from left to right):
47 o blk: 0 - block number
48 o numTuple: 29 - the number of tuples
49 o free: 6888b - free space in bytes
50 o (15.63%) - occupied space in percents
52 * gist_tree(INDEXNAME) - show full GiST tree
54 regression=# select gist_tree('pix');
55 0(l:0) blk: 0 numTuple: 29 free: 6888b(15.63%)
56 1(l:1) blk: 13 numTuple: 180 free: 244b(97.01%)
57 2(l:1) blk: 11 numTuple: 175 free: 464b(94.32%)
58 3(l:1) blk: 2 numTuple: 101 free: 3720b(54.43%)
59 4(l:1) blk: 17 numTuple: 111 free: 3280b(59.82%)
60 5(l:1) blk: 18 numTuple: 101 free: 3720b(54.43%)
61 6(l:1) blk: 10 numTuple: 98 free: 3852b(52.82%)
62 7(l:1) blk: 19 numTuple: 111 free: 3280b(59.82%)
63 8(l:1) blk: 9 numTuple: 97 free: 3896b(52.28%)
64 9(l:1) blk: 20 numTuple: 104 free: 3588b(56.05%)
65 10(l:1) blk: 14 numTuple: 96 free: 3940b(51.74%)
66 11(l:1) blk: 21 numTuple: 106 free: 3500b(57.13%)
67 12(l:1) blk: 7 numTuple: 103 free: 3632b(55.51%)
68 13(l:1) blk: 1 numTuple: 101 free: 3720b(54.43%)
69 14(l:1) blk: 16 numTuple: 97 free: 3896b(52.28%)
70 15(l:1) blk: 24 numTuple: 103 free: 3632b(55.51%)
71 16(l:1) blk: 4 numTuple: 98 free: 3852b(52.82%)
72 17(l:1) blk: 25 numTuple: 98 free: 3852b(52.82%)
73 18(l:1) blk: 3 numTuple: 97 free: 3896b(52.28%)
74 19(l:1) blk: 26 numTuple: 96 free: 3940b(51.74%)
75 20(l:1) blk: 6 numTuple: 103 free: 3632b(55.51%)
76 21(l:1) blk: 8 numTuple: 162 free: 1036b(87.31%)
77 22(l:1) blk: 23 numTuple: 94 free: 4028b(50.66%)
78 23(l:1) blk: 12 numTuple: 82 free: 4556b(44.19%)
79 24(l:1) blk: 27 numTuple: 105 free: 3544b(56.59%)
80 25(l:1) blk: 5 numTuple: 90 free: 4204b(48.51%)
81 26(l:1) blk: 28 numTuple: 100 free: 3764b(53.90%)
82 27(l:1) blk: 22 numTuple: 101 free: 3720b(54.43%)
83 28(l:1) blk: 15 numTuple: 95 free: 3984b(51.20%)
84 29(l:1) blk: 29 numTuple: 95 free: 3984b(51.20%)
86 * gist_print(INDEXNAME) - prints objects stored in GiST tree,
87 works only if objects in index have textual representation
88 (type_out functions should be implemented for given object type).
89 It's known to work with R-tree GiST based index.
90 Note, in example below, objects are of type box.
92 # select * from gist_print('pix') as t(level int, valid bool, a box) where level =1;
94 -------+-------+-----------------------------
95 1 | t | (37357,50073),(34242,357)
96 1 | t | (43499,49770),(40358,43)
97 1 | t | (31193,24679),(25047,12410)
98 1 | t | (31018,12142),(25083,6)
99 1 | t | (49944,25174),(43471,12802)
100 1 | t | (12577,49757),(6302,37534)
101 1 | t | (12528,37333),(6171,24861)
102 1 | t | (50027,49751),(46817,25462)
103 1 | t | (46870,49912),(43664,25722)
104 1 | t | (24855,25574),(12447,19263)
105 1 | t | (25054,19126),(12403,12796)
106 1 | t | (32737,49923),(31178,1038)
107 1 | t | (3184,24465),(15,81)
108 1 | t | (24951,49983),(12740,44000)
109 1 | t | (24919,43956),(12617,37901)
110 1 | t | (40387,49852),(37338,25217)
111 1 | t | (40325,24963),(37375,491)
112 1 | t | (24919,12698),(12654,6518)
113 1 | t | (25002,6338),(12350,51)
114 1 | t | (49985,12554),(43447,222)
115 1 | t | (25003,37769),(12552,25573)
116 1 | t | (34270,49382),(32763,594)
117 1 | t | (6205,50012),(3,37527)
118 1 | t | (6163,37358),(120,25034)
119 1 | t | (12343,24542),(9295,294)
120 1 | t | (9308,24151),(6234,620)
121 1 | t | (6230,24629),(3169,108)
122 1 | t | (31179,50040),(28113,25556)
123 1 | t | (28048,49694),(25000,25000)
126 * spgist_stat(INDEXNAME) - show some statistics about SP-GiST tree
128 # SELECT spgist_stat('spgist_idx');
130 ----------------------------------
136 usedSpace: 121.27 kbytes+
137 freeSpace: 46.07 kbytes +
142 leafPlaceholders: 569 +
143 innerPlaceholders: 0 +
147 * spgist_print(INDEXNAME) - prints objects stored in GiST tree,
148 works only if objects in index have textual representation
149 (type_out functions should be implemented for given object type).
150 Note 1. in example below we used quad_point_ops which uses point
151 for leaf and prefix value, but doesn't use node_label at all.
152 Use type 'int' as dummy type for prefix or/and node_label.
154 quad_point_ops: prefix point, node_label int, leaf_value point
155 kd_point_ops: prefix float, node_label int, leaf_value point
156 text_ops: prefix text, node_label char, leaf_value text
158 # SELECT * FROM spgist_print('spgist_idx') as t
168 tid | node_n | level | tid_pointer | prefix | node_label | leaf_value
169 -------+--------+-------+-------------+-------------------------------------+------------+------------
170 (1,1) | 0 | 1 | (5,4) | (24530.2070484581,23595.7092511013) | |
171 (1,1) | 1 | 1 | (5,3) | (24530.2070484581,23595.7092511013) | |
172 (1,1) | 2 | 1 | (5,2) | (24530.2070484581,23595.7092511013) | |
173 (1,1) | 3 | 1 | (5,1) | (24530.2070484581,23595.7092511013) | |
175 * gin_stat(INDEXNAME) prints estimated counts for each indexed values
176 Note: since 8.4 gin_stat function has gin_stat(INDEXNAME, COLNUMBER)
177 prototype, single-argument function will return result for a first
180 # SELECT * FROM gin_stat('gin_idx') as t(value int, nrow int) where nrow > 250;
189 * bigint gin_count_estimate(INDEXNAME, TSQUERY) outputs number of indexed
190 rows matched query. It doesn't touch heap at all.
192 # select gin_count_estimate('qq', 'star');
198 * text gin_statpage(INDEXNAME)
199 Prints various stat about index internals.
201 # select gin_statpage('gin_idx');
203 ------------------------------
208 dataInnerFreeSpace: 0 +
209 dataLeafFreeSpace: 4454 +
210 dataInnerTuplesCount: 0 +
211 dataLeafIptrsCount: 3600 +
213 entryInnerPages: 21 +
215 entryInnerFreeSpace: 15160+
216 entryLeafFreeSpace: 32788+
217 entryInnerTuplesCount: 7810 +
218 entryLeafTuplesCount: 305 +
219 entryPostingSize: 42122+
220 entryPostingCount: 96759+
221 entryAttrSize: 64924+