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
169 tid | allthesame | node_n | level | tid_pointer | prefix | node_label | leaf_value
170 -------+------------+--------+-------+-------------+-------------------------------------+------------+------------
171 (1,1) | f | 0 | 1 | (5,4) | (24530.2070484581,23595.7092511013) | |
172 (1,1) | f | 1 | 1 | (5,3) | (24530.2070484581,23595.7092511013) | |
173 (1,1) | f | 2 | 1 | (5,2) | (24530.2070484581,23595.7092511013) | |
174 (1,1) | f | 3 | 1 | (5,1) | (24530.2070484581,23595.7092511013) | |
176 * gin_stat(INDEXNAME) prints estimated counts for each indexed values
177 Note: since 8.4 gin_stat function has gin_stat(INDEXNAME, COLNUMBER)
178 prototype, single-argument function will return result for a first
181 # SELECT * FROM gin_stat('gin_idx') as t(value int, nrow int) where nrow > 250;
190 * bigint gin_count_estimate(INDEXNAME, TSQUERY) outputs number of indexed
191 rows matched query. It doesn't touch heap at all.
193 # select gin_count_estimate('qq', 'star');
199 * text gin_statpage(INDEXNAME)
200 Prints various stat about index internals.
202 # select gin_statpage('gin_idx');
204 ------------------------------
209 dataInnerFreeSpace: 0 +
210 dataLeafFreeSpace: 4454 +
211 dataInnerTuplesCount: 0 +
212 dataLeafIptrsCount: 3600 +
214 entryInnerPages: 21 +
216 entryInnerFreeSpace: 15160+
217 entryLeafFreeSpace: 32788+
218 entryInnerTuplesCount: 7810 +
219 entryLeafTuplesCount: 305 +
220 entryPostingSize: 42122+
221 entryPostingCount: 96759+
222 entryAttrSize: 64924+
224 * btree_stat(INDEXNAME) - show some statistics about btree index
226 # SELECT btree_stat('btree_idx');
228 -----------------------------------------
229 Number of levels: 2 +
230 Number of pages: 75 +
231 Number of leaf pages: 74 +
232 Number of tuples: 11047 +
233 Number of invalid tuples: 0 +
234 Number of leaf tuples: 10973 +
235 Total size of tuples: 547824 bytes+
236 Total size of leaf tuples: 543948 bytes+
237 Total size of index: 614400 bytes+
241 * btree_tree(INDEXNAME[, MAXLEVEL]) - show btree elements from root up to MAXLEVEL
243 # SELECT btree_tree('btree_idx');
245 ---------------------------------
246 lvl: 0, blk: 3, numTuples: 74 +
247 lvl: 1, blk: 1, numTuples: 139 +
248 lvl: 1, blk: 2, numTuples: 139 +
249 lvl: 1, blk: 4, numTuples: 139 +
250 lvl: 1, blk: 5, numTuples: 136 +
251 lvl: 1, blk: 6, numTuples: 141 +
252 lvl: 1, blk: 7, numTuples: 139 +
253 lvl: 1, blk: 8, numTuples: 140 +
254 lvl: 1, blk: 9, numTuples: 136 +
255 lvl: 1, blk: 10, numTuples: 140+
256 lvl: 1, blk: 11, numTuples: 138+
257 lvl: 1, blk: 12, numTuples: 143+
258 lvl: 1, blk: 13, numTuples: 137+
259 lvl: 1, blk: 14, numTuples: 138+
260 lvl: 1, blk: 15, numTuples: 140+
261 lvl: 1, blk: 16, numTuples: 141+
262 lvl: 1, blk: 17, numTuples: 138+
263 lvl: 1, blk: 18, numTuples: 141+
264 lvl: 1, blk: 19, numTuples: 141+
265 lvl: 1, blk: 20, numTuples: 138+
266 lvl: 1, blk: 21, numTuples: 141+
267 lvl: 1, blk: 22, numTuples: 141+
268 lvl: 1, blk: 23, numTuples: 141+
269 lvl: 1, blk: 24, numTuples: 138+
270 lvl: 1, blk: 25, numTuples: 140+
271 lvl: 1, blk: 26, numTuples: 142+
272 lvl: 1, blk: 27, numTuples: 141+
273 lvl: 1, blk: 28, numTuples: 142+
274 lvl: 1, blk: 29, numTuples: 140+
275 lvl: 1, blk: 30, numTuples: 141+
276 lvl: 1, blk: 31, numTuples: 140+
277 lvl: 1, blk: 32, numTuples: 142+
278 lvl: 1, blk: 33, numTuples: 143+
279 lvl: 1, blk: 34, numTuples: 141+
280 lvl: 1, blk: 35, numTuples: 140+
281 lvl: 1, blk: 36, numTuples: 142+
282 lvl: 1, blk: 37, numTuples: 142+
283 lvl: 1, blk: 38, numTuples: 141+
284 lvl: 1, blk: 39, numTuples: 143+
285 lvl: 1, blk: 40, numTuples: 142+
286 lvl: 1, blk: 41, numTuples: 143+
287 lvl: 1, blk: 42, numTuples: 143+
288 lvl: 1, blk: 43, numTuples: 142+
289 lvl: 1, blk: 44, numTuples: 143+
290 lvl: 1, blk: 45, numTuples: 142+
291 lvl: 1, blk: 46, numTuples: 142+
292 lvl: 1, blk: 47, numTuples: 141+
293 lvl: 1, blk: 48, numTuples: 144+
294 lvl: 1, blk: 49, numTuples: 141+
295 lvl: 1, blk: 50, numTuples: 142+
296 lvl: 1, blk: 51, numTuples: 143+
297 lvl: 1, blk: 52, numTuples: 142+
298 lvl: 1, blk: 53, numTuples: 143+
299 lvl: 1, blk: 54, numTuples: 141+
300 lvl: 1, blk: 55, numTuples: 142+
301 lvl: 1, blk: 56, numTuples: 141+
302 lvl: 1, blk: 57, numTuples: 156+
303 lvl: 1, blk: 58, numTuples: 159+
304 lvl: 1, blk: 59, numTuples: 160+
305 lvl: 1, blk: 60, numTuples: 161+
306 lvl: 1, blk: 61, numTuples: 163+
307 lvl: 1, blk: 62, numTuples: 163+
308 lvl: 1, blk: 63, numTuples: 164+
309 lvl: 1, blk: 64, numTuples: 165+
310 lvl: 1, blk: 65, numTuples: 165+
311 lvl: 1, blk: 66, numTuples: 166+
312 lvl: 1, blk: 67, numTuples: 166+
313 lvl: 1, blk: 68, numTuples: 167+
314 lvl: 1, blk: 69, numTuples: 167+
315 lvl: 1, blk: 70, numTuples: 167+
316 lvl: 1, blk: 71, numTuples: 167+
317 lvl: 1, blk: 72, numTuples: 167+
318 lvl: 1, blk: 73, numTuples: 167+
319 lvl: 1, blk: 74, numTuples: 167+
320 lvl: 1, blk: 75, numTuples: 274+
324 test1=# SELECT btree_tree('btree_idx', 0);
326 --------------------------------------
327 lvl: 0, coff: 0, blk: 3, numTuples: +
331 * btree_print() - print objects stored in btree
332 works only if objects in index have textual representation
333 (type_out functions should be implemented for given object type).
334 # SELECT * FROM btree_print('btree_idx') as t(level int, val bool, a int[]) where level = 1;
336 -------+-----+-------------------------
339 1 | t | {3,52,31,62,98}
345 1 | t | {12,96,72,23,83,56}
346 1 | t | {14,30,132,144,174}
348 1 | t | {17,36,97,77}
351 1 | t | {22,31,38,77,21}
353 1 | t | {25,218,241,241}
354 1 | t | {27,87,61,54,41}
355 1 | t | {29,53,109,128,129,195}
358 1 | t | {34,44,47,81,124,135}
361 1 | t | {39,74,47,71,63,80}
363 1 | t | {43,41,12,44,75,91}
364 1 | t | {45,26,61,15}
366 1 | t | {49,16,63,56}
367 1 | t | {50,98,71,10}
369 1 | t | {54,46,31,87,13,8}
371 1 | t | {57,99,66,93}
372 1 | t | {59,103,180,196}
373 1 | t | {61,63,59,62}
375 1 | t | {65,30,39,82,89,34}
379 1 | t | {73,24,81,58,22}
380 1 | t | {75,54,93,39,18}
383 1 | t | {81,61,86,96,47,67}
384 1 | t | {83,79,46,37,99}
390 1 | t | {96,33,86,28,19}
391 1 | t | {98,53,58,58,61}
392 1 | t | {100,80,23,24,17}
413 * brin_stat(INDEXNAME) - show some statistics about brin index
414 # SELECT brin_stat('brin_idx');
416 ------------------------------------
417 Number of revmap pages: 1 +
418 Number of empty revmap pages: 0 +
419 Number of regular pages: 1 +
420 Number of tuples: 1 +
421 Used space 56 bytes +
422 Free space 8104 bytes+
426 * brin_print() - print values for pages stored in brin index
427 # SELECT brin_print('brin_idx')
429 -------------------------------------------------------
430 Start block: 0; end block: 48; offset: 86, free: 3292+