X-Git-Url: http://www.sigaev.ru/git/gitweb.cgi?p=gevel.git;a=blobdiff_plain;f=README.gevel;h=afac50d7e27a14501d9c92997f9cc6b26b5801a1;hp=4d7c2a57f9379a9ec6899a7f9df9bf9c87421b68;hb=HEAD;hpb=a61c2088be6a6f32e3ce6829e860bf2a431d5e05 diff --git a/README.gevel b/README.gevel index 4d7c2a5..afac50d 100644 --- a/README.gevel +++ b/README.gevel @@ -2,12 +2,13 @@ Gevel contrib module provides several functions useful for analyzing GiST and GI [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 , Moscow, Moscow University, Russia - * Teodor Sigaev , Moscow, Delta-Soft Ltd.,Russia + * Teodor Sigaev , Moscow, Moscow University, Russia + * Daria Lepikhova , Postgres Pro License @@ -123,6 +124,56 @@ regression=# select gist_tree('pix'); 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 @@ -146,3 +197,238 @@ regression=# select gist_tree('pix'); 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) +