-for 8.1+ postgresql
+Gevel contrib module provides several functions useful for analyzing GiST and GIN index.
-select gist_tree(INDEXNAME);
-select gist_tree(INDEXNAME,MAXLEVEL);
-select gist_stat(INDEXNAME);
+[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 !
-example from gmake installcheck on rtree_gist:
-select * from gist_print('pix') as t(level int, valid bool, a box);
+Authors
-# select * from gist_print('pix') as t(level int, valid bool, a box) where level < 2;
- level | valid | a
+ * Oleg Bartunov <oleg@sai.msu.su>, Moscow, Moscow University, Russia
+ * Teodor Sigaev <teodor@sigaev.ru>, Moscow, Moscow University, Russia
+
+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 | (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+
projects / gevel.git / blobdiff