[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 and GiST indices !
+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, Delta-Soft Ltd.,Russia
+ * Teodor Sigaev <teodor@sigaev.ru>, Moscow, Moscow University, Russia
License
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
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+
+