From 83cb441a9cfdc546c7667509e486d3e53f13d529 Mon Sep 17 00:00:00 2001 From: Teodor Sigaev Date: Fri, 19 Jun 2020 16:30:39 +0300 Subject: [PATCH] v12, brin, btree --- Makefile | 28 +- README.gevel | 210 ++++++++++++ expected/gevel.out.12 | 526 ++++++++++++++++++++++++++++ expected/gevel_brin.out | 26 ++ expected/gevel_btree.out | 185 ++++++++++ gevel.brin.sql | 16 + gevel.btree.sql | 28 ++ gevel.c | 720 ++++++++++++++++++++++++++++++++++++++- gevel.control | 5 + gevel.sql | 64 ++++ sql/gevel.sql | 4 +- sql/gevel_brin.sql | 14 + sql/gevel_btree.sql | 18 + 13 files changed, 1820 insertions(+), 24 deletions(-) create mode 100644 expected/gevel.out.12 create mode 100644 expected/gevel_brin.out create mode 100644 expected/gevel_btree.out create mode 100644 gevel.brin.sql create mode 100644 gevel.btree.sql create mode 100644 gevel.control create mode 100644 gevel.sql create mode 100644 sql/gevel_brin.sql create mode 100644 sql/gevel_btree.sql diff --git a/Makefile b/Makefile index e15bc01..0d44520 100644 --- a/Makefile +++ b/Makefile @@ -1,31 +1,35 @@ -subdir = contrib/gevel -top_builddir = ../.. +# contrib/gevel/Makefile -MODULES = gevel -DATA_built = gevel.sql -DOCS = README.gevel +MODULE_big = gevel +OBJS = gevel.o REGRESS = gevel - +DATA = gevel.sql +DOCS = README.gevel EXTRA_CLEAN = pg_version.txt expected/gevel.out +all installcheck: pg_version.txt + ifdef USE_PGXS PG_CONFIG = pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) include $(PGXS) else subdir = contrib/gevel -top_builddir = ../.. -include $(top_builddir)/src/Makefile.global include $(top_srcdir)/contrib/contrib-global.mk endif -all installcheck: pg_version.txt +VERSION = $(MAJORVERSION) +ifeq ($(VERSION),12) + REGRESS += gevel_btree gevel_brin + DATA += gevel.btree.sql gevel.brin.sql +endif + pg_version.txt: echo PG_MAJORVERSION | $(CPP) -undef -x c -w -P $(CPPFLAGS) -include pg_config.h -o - - | grep -v '^$$' | sed -e 's/"//g' > $@ - if [ -f expected/gevel.out.`cat pg_version.txt` ] ; \ + if [ -f expected/gevel.out.$(VERSION)`cat pg_version.txt` ] ; \ then \ - cp expected/gevel.out.`cat pg_version.txt` expected/gevel.out ; \ + cp expected/gevel.out.$(VERSION)`cat pg_version.txt` expected/gevel.out ; \ else \ - cp expected/gevel.out.st expected/gevel.out ; \ + cp expected/gevel.out.$(VERSION) expected/gevel.out ; \ fi diff --git a/README.gevel b/README.gevel index b09529e..74917d5 100644 --- a/README.gevel +++ b/README.gevel @@ -221,3 +221,213 @@ regression=# select gist_tree('pix'); 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) + diff --git a/expected/gevel.out.12 b/expected/gevel.out.12 new file mode 100644 index 0000000..c37c6e6 --- /dev/null +++ b/expected/gevel.out.12 @@ -0,0 +1,526 @@ +SET client_min_messages = warning; +\set ECHO none +RESET client_min_messages; +CREATE TABLE gevelt ( t box ); +\copy gevelt from 'data/rect.data' +SELECT center(t) AS p INTO gevelp FROM gevelt; +CREATE INDEX gist_idx ON gevelt USING gist ( t ); +CREATE INDEX spgist_idx ON gevelp USING spgist ( p ); +CREATE INDEX kdspgist_idx ON gevelp USING spgist ( p kd_point_ops); +--GiST +SELECT gist_stat('gist_idx'); + gist_stat +----------------------------------------- + Number of levels: 2 + + Number of pages: 29 + + Number of leaf pages: 28 + + Number of tuples: 3406 + + Number of invalid tuples: 0 + + Number of leaf tuples: 3378 + + Total size of tuples: 143516 bytes+ + Total size of leaf tuples: 142296 bytes+ + Total size of index: 237568 bytes+ + +(1 row) + +SELECT gist_tree('gist_idx'); + gist_tree +----------------------------------------------------------------------------------------------- + 0(l:0) blk: 0 numTuple: 28 free: 6940b(14.95%) rightlink:4294967295 (InvalidBlockNumber) + + 1(l:1) blk: 2 numTuple: 278 free: 2588b(68.28%) rightlink:4294967295 (InvalidBlockNumber)+ + 2(l:1) blk: 11 numTuple: 146 free: 1724b(78.87%) rightlink:2 (OK) + + 3(l:1) blk: 14 numTuple: 163 free: 976b(88.04%) rightlink:6 (OK) + + 4(l:1) blk: 15 numTuple: 135 free: 2208b(72.94%) rightlink:11 (OK) + + 5(l:1) blk: 7 numTuple: 99 free: 3792b(53.53%) rightlink:16 (OK) + + 6(l:1) blk: 17 numTuple: 89 free: 4232b(48.14%) rightlink:4 (OK) + + 7(l:1) blk: 10 numTuple: 156 free: 1284b(84.26%) rightlink:18 (OK) + + 8(l:1) blk: 18 numTuple: 123 free: 2736b(66.47%) rightlink:3 (OK) + + 9(l:1) blk: 13 numTuple: 130 free: 2428b(70.25%) rightlink:19 (OK) + + 10(l:1) blk: 19 numTuple: 122 free: 2780b(65.93%) rightlink:7 (OK) + + 11(l:1) blk: 1 numTuple: 92 free: 4100b(49.75%) rightlink:20 (OK) + + 12(l:1) blk: 20 numTuple: 166 free: 844b(89.66%) rightlink:13 (OK) + + 13(l:1) blk: 8 numTuple: 128 free: 2516b(69.17%) rightlink:21 (OK) + + 14(l:1) blk: 21 numTuple: 112 free: 3220b(60.54%) rightlink:10 (OK) + + 15(l:1) blk: 6 numTuple: 85 free: 4408b(45.98%) rightlink:22 (OK) + + 16(l:1) blk: 22 numTuple: 147 free: 1680b(79.41%) rightlink:9 (OK) + + 17(l:1) blk: 4 numTuple: 92 free: 4100b(49.75%) rightlink:23 (OK) + + 18(l:1) blk: 23 numTuple: 126 free: 2604b(68.09%) rightlink:8 (OK) + + 19(l:1) blk: 9 numTuple: 124 free: 2692b(67.01%) rightlink:24 (OK) + + 20(l:1) blk: 24 numTuple: 80 free: 4628b(43.28%) rightlink:15 (OK) + + 21(l:1) blk: 12 numTuple: 70 free: 5068b(37.89%) rightlink:25 (OK) + + 22(l:1) blk: 25 numTuple: 156 free: 1284b(84.26%) rightlink:17 (OK) + + 23(l:1) blk: 16 numTuple: 89 free: 4232b(48.14%) rightlink:26 (OK) + + 24(l:1) blk: 26 numTuple: 108 free: 3396b(58.38%) rightlink:5 (OK) + + 25(l:1) blk: 3 numTuple: 57 free: 5640b(30.88%) rightlink:27 (OK) + + 26(l:1) blk: 27 numTuple: 122 free: 2780b(65.93%) rightlink:14 (OK) + + 27(l:1) blk: 5 numTuple: 61 free: 5464b(33.04%) rightlink:28 (OK) + + 28(l:1) blk: 28 numTuple: 122 free: 2780b(65.93%) rightlink:12 (OK) + + +(1 row) + +SELECT * FROM gist_print('gist_idx') as t(level int, valid bool, a box) where level=1; + level | valid | a +-------+-------+----------------------------- + 1 | t | + 1 | t | (50027,50073),(32453,42694) + 1 | t | (49985,15380),(42047,43) + 1 | t | (49999,42533),(43290,27088) + 1 | t | (6986,30859),(107,19466) + 1 | t | (32544,31262),(21146,25161) + 1 | t | (32394,42395),(20513,31557) + 1 | t | (32261,50040),(20397,42779) + 1 | t | (20715,10161),(10407,51) + 1 | t | (20594,19151),(10869,10535) + 1 | t | (10146,7495),(138,81) + 1 | t | (10104,18999),(15,7761) + 1 | t | (20400,41715),(9175,31485) + 1 | t | (20327,49975),(9216,42130) + 1 | t | (39187,26879),(32770,15690) + 1 | t | (49944,26858),(39348,15728) + 1 | t | (8924,39250),(3,31083) + 1 | t | (8993,50012),(64,39781) + 1 | t | (42941,36244),(32606,27088) + 1 | t | (43056,42360),(32661,36457) + 1 | t | (32512,16364),(20947,11843) + 1 | t | (32705,25105),(20838,16626) + 1 | t | (13397,31188),(7334,19288) + 1 | t | (20856,31294),(13642,19263) + 1 | t | (41916,5154),(32914,357) + 1 | t | (41835,15495),(32828,5360) + 1 | t | (24423,11473),(20828,181) + 1 | t | (32715,11509),(24675,6) +(28 rows) + +--SPGiST +SELECT spgist_stat('spgist_idx'); + spgist_stat +---------------------------------- + totalPages: 21 + + deletedPages: 0 + + innerPages: 2 + + leafPages: 19 + + emptyPages: 0 + + usedSpace: 126.70 kbytes+ + usedInnerSpace: 1.17 kbytes + + usedLeafSpace: 125.53 kbytes+ + freeSpace: 40.64 kbytes + + fillRatio: 75.71% + + leafTuples: 3947 + + innerTuples: 20 + + innerAllTheSame: 0 + + leafPlaceholders: 569 + + innerPlaceholders: 0 + + leafRedirects: 0 + + innerRedirects: 0 +(1 row) + +SELECT * FROM spgist_print('kdspgist_idx') as t(tid tid, allthesame bool, node_n int, level int, tid_pointer tid, prefix float8, node_label int, leaf_value point); + tid | allthesame | node_n | level | tid_pointer | prefix | node_label | leaf_value +----------+------------+--------+-------+-------------+---------+------------+------------------- + (1,1) | f | 0 | 1 | | 23030 | | + (1,1) | f | 1 | 1 | | 23030 | | + (5,2) | f | 0 | 2 | | 21664 | | + (5,2) | f | 1 | 2 | | 21664 | | + (5,3) | f | 0 | 3 | | 37159.5 | | + (5,3) | f | 1 | 3 | | 37159.5 | | + (5,12) | f | 0 | 4 | | 36357.5 | | + (5,12) | f | 1 | 4 | | 36357.5 | | + (14,226) | | | 5 | | | | (43240,47256.5) + (5,13) | f | 0 | 5 | | 43507 | | + (5,13) | f | 1 | 5 | | 43507 | | + (5,25) | f | 0 | 6 | | 28862 | | + (5,25) | f | 1 | 6 | | 28862 | | + (22,115) | | | 7 | | | | (30902,44532) + (22,116) | | | 7 | | | | (23048,47416) + (3,112) | | | 6 | | | | (35218.5,42124) + (5,5) | f | 0 | 4 | | 33416 | | + (5,5) | f | 1 | 4 | | 33416 | | + (5,22) | f | 0 | 5 | | 30991 | | + (5,22) | f | 1 | 5 | | 30991 | | + (5,23) | f | 0 | 6 | | 41820 | | + (5,23) | f | 1 | 6 | | 41820 | | + (9,114) | | | 7 | | | | (44732,32182) + (9,112) | | | 7 | | | | (35580,33526.5) + (20,225) | | | 6 | | | | (47724.5,27185.5) + (5,11) | f | 0 | 5 | | 29986.5 | | + (5,11) | f | 1 | 5 | | 29986.5 | | + (13,100) | | | 6 | | | | (24069,30850.5) + (13,101) | | | 6 | | | | (29539,25566) + (5,6) | f | 0 | 3 | | 36774 | | + (5,6) | f | 1 | 3 | | 36774 | | + (5,19) | f | 0 | 4 | | 10075.5 | | + (5,19) | f | 1 | 4 | | 10075.5 | | + (18,225) | | | 5 | | | | (20920.5,49105.5) + (5,20) | f | 0 | 5 | | 44171.5 | | + (5,20) | f | 1 | 5 | | 44171.5 | | + (10,113) | | | 6 | | | | (93,46797) + (10,110) | | | 6 | | | | (28.5,38640.5) + (5,7) | f | 0 | 4 | | 9517 | | + (5,7) | f | 1 | 4 | | 9517 | | + (5,21) | f | 0 | 5 | | 28907.5 | | + (5,21) | f | 1 | 5 | | 28907.5 | | + (19,112) | | | 6 | | | | (11916.5,31668) + (19,115) | | | 6 | | | | (20622.5,27462.5) + (7,113) | | | 5 | | | | (9296,35157) + (5,1) | f | 0 | 2 | | 26938 | | + (5,1) | f | 1 | 2 | | 26938 | | + (5,4) | f | 0 | 3 | | 9532 | | + (5,4) | f | 1 | 3 | | 9532 | | + (5,14) | f | 0 | 4 | | 38603 | | + (5,14) | f | 1 | 4 | | 38603 | | + (15,222) | | | 5 | | | | (41926.5,17934.5) + (5,15) | f | 0 | 5 | | 16345 | | + (5,15) | f | 1 | 5 | | 16345 | | + (8,112) | | | 6 | | | | (32425,20702.5) + (8,113) | | | 6 | | | | (29134,15559.5) + (5,10) | f | 0 | 4 | | 38800.5 | | + (5,10) | f | 1 | 4 | | 38800.5 | | + (5,24) | f | 0 | 5 | | 4752 | | + (5,24) | f | 1 | 5 | | 4752 | | + (21,107) | | | 6 | | | | (49822.5,7097.5) + (21,108) | | | 6 | | | | (40315.5,1689.5) + (12,94) | | | 5 | | | | (30295.5,5090) + (5,8) | f | 0 | 3 | | 11733.5 | | + (5,8) | f | 1 | 3 | | 11733.5 | | + (5,9) | f | 0 | 4 | | 11993 | | + (5,9) | f | 1 | 4 | | 11993 | | + (5,16) | f | 0 | 5 | | 17591 | | + (5,16) | f | 1 | 5 | | 17591 | | + (16,127) | | | 6 | | | | (18352.5,19366) + (16,123) | | | 6 | | | | (24795,14921) + (6,114) | | | 5 | | | | (6706,16676) + (5,17) | f | 0 | 4 | | 13329.5 | | + (5,17) | f | 1 | 4 | | 13329.5 | | + (17,226) | | | 5 | | | | (23690,10214.5) + (5,18) | f | 0 | 5 | | 6375 | | + (5,18) | f | 1 | 5 | | 6375 | | + (11,109) | | | 6 | | | | (5501.5,9916) + (11,113) | | | 6 | | | | (1072.5,4752) +(79 rows) + +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 | | (24530.20704845815,23595.70925110132) | | + (1,1) | f | 1 | 1 | | (24530.20704845815,23595.70925110132) | | + (1,1) | f | 2 | 1 | | (24530.20704845815,23595.70925110132) | | + (1,1) | f | 3 | 1 | | (24530.20704845815,23595.70925110132) | | +(4 rows) + +--GIN +CREATE TABLE test__int( a int[] ); +\copy test__int from 'data/test__int.data' +CREATE INDEX gin_idx ON test__int USING gin ( a ); +INSERT INTO test__int ( SELECT ARRAY[t] || '{1000}'::_int4 FROM generate_series (1,300) as t ); +INSERT INTO test__int ( SELECT ARRAY[t] || '{1001}'::_int4 FROM generate_series (1,300) as t, generate_series(1,12) ); +VACUUM ANALYZE test__int; +SELECT * FROM gin_stat('gin_idx') as t(value int, nrow int); + value | nrow +-------+------ + 0 | 38 + 1 | 257 + 2 | 244 + 3 | 222 + 4 | 228 + 5 | 227 + 6 | 243 + 7 | 238 + 8 | 236 + 9 | 224 + 10 | 236 + 11 | 248 + 12 | 224 + 13 | 235 + 14 | 237 + 15 | 253 + 16 | 234 + 17 | 245 + 18 | 246 + 19 | 234 + 20 | 236 + 21 | 213 + 22 | 235 + 23 | 222 + 24 | 197 + 25 | 253 + 26 | 218 + 27 | 239 + 28 | 198 + 29 | 244 + 30 | 247 + 31 | 267 + 32 | 234 + 33 | 209 + 34 | 231 + 35 | 218 + 36 | 242 + 37 | 232 + 38 | 221 + 39 | 232 + 40 | 241 + 41 | 239 + 42 | 221 + 43 | 211 + 44 | 231 + 45 | 213 + 46 | 236 + 47 | 264 + 48 | 221 + 49 | 235 + 50 | 219 + 51 | 250 + 52 | 270 + 53 | 222 + 54 | 242 + 55 | 237 + 56 | 237 + 57 | 251 + 58 | 231 + 59 | 272 + 60 | 219 + 61 | 233 + 62 | 235 + 63 | 260 + 64 | 252 + 65 | 221 + 66 | 228 + 67 | 253 + 68 | 196 + 69 | 232 + 70 | 232 + 71 | 223 + 72 | 247 + 73 | 214 + 74 | 232 + 75 | 229 + 76 | 233 + 77 | 221 + 78 | 227 + 79 | 233 + 80 | 216 + 81 | 244 + 82 | 223 + 83 | 254 + 84 | 227 + 85 | 247 + 86 | 255 + 87 | 239 + 88 | 258 + 89 | 249 + 90 | 244 + 91 | 226 + 92 | 225 + 93 | 230 + 94 | 218 + 95 | 232 + 96 | 239 + 97 | 226 + 98 | 209 + 99 | 211 + 100 | 216 + 101 | 49 + 102 | 47 + 103 | 59 + 104 | 55 + 105 | 48 + 106 | 49 + 107 | 49 + 108 | 51 + 109 | 47 + 110 | 51 + 111 | 45 + 112 | 46 + 113 | 48 + 114 | 38 + 115 | 39 + 116 | 43 + 117 | 44 + 118 | 46 + 119 | 45 + 120 | 52 + 121 | 41 + 122 | 64 + 123 | 50 + 124 | 41 + 125 | 55 + 126 | 41 + 127 | 50 + 128 | 54 + 129 | 43 + 130 | 44 + 131 | 50 + 132 | 57 + 133 | 40 + 134 | 41 + 135 | 44 + 136 | 58 + 137 | 48 + 138 | 44 + 139 | 50 + 140 | 49 + 141 | 48 + 142 | 43 + 143 | 36 + 144 | 43 + 145 | 49 + 146 | 46 + 147 | 40 + 148 | 52 + 149 | 46 + 150 | 49 + 151 | 42 + 152 | 58 + 153 | 49 + 154 | 51 + 155 | 49 + 156 | 45 + 157 | 53 + 158 | 59 + 159 | 38 + 160 | 53 + 161 | 48 + 162 | 41 + 163 | 40 + 164 | 52 + 165 | 50 + 166 | 40 + 167 | 48 + 168 | 45 + 169 | 49 + 170 | 50 + 171 | 45 + 172 | 46 + 173 | 47 + 174 | 41 + 175 | 51 + 176 | 50 + 177 | 41 + 178 | 42 + 179 | 48 + 180 | 47 + 181 | 49 + 182 | 40 + 183 | 46 + 184 | 52 + 185 | 36 + 186 | 45 + 187 | 41 + 188 | 47 + 189 | 49 + 190 | 46 + 191 | 45 + 192 | 39 + 193 | 50 + 194 | 39 + 195 | 54 + 196 | 43 + 197 | 50 + 198 | 46 + 199 | 42 + 200 | 31 + 201 | 28 + 202 | 28 + 203 | 28 + 204 | 30 + 205 | 22 + 206 | 25 + 207 | 27 + 208 | 32 + 209 | 22 + 210 | 21 + 211 | 31 + 212 | 22 + 213 | 37 + 214 | 19 + 215 | 25 + 216 | 27 + 217 | 26 + 218 | 24 + 219 | 25 + 220 | 26 + 221 | 21 + 222 | 23 + 223 | 20 + 224 | 26 + 225 | 25 + 226 | 25 + 227 | 23 + 228 | 30 + 229 | 30 + 230 | 24 + 231 | 22 + 232 | 27 + 233 | 27 + 234 | 21 + 235 | 27 + 236 | 24 + 237 | 24 + 238 | 29 + 239 | 32 + 240 | 31 + 241 | 24 + 242 | 36 + 243 | 21 + 244 | 29 + 245 | 22 + 246 | 23 + 247 | 21 + 248 | 26 + 249 | 29 + 250 | 24 + 251 | 29 + 252 | 25 + 253 | 28 + 254 | 25 + 255 | 19 + 256 | 23 + 257 | 20 + 258 | 24 + 259 | 31 + 260 | 29 + 261 | 20 + 262 | 25 + 263 | 23 + 264 | 25 + 265 | 23 + 266 | 31 + 267 | 22 + 268 | 26 + 269 | 33 + 270 | 25 + 271 | 27 + 272 | 29 + 273 | 29 + 274 | 26 + 275 | 28 + 276 | 30 + 277 | 28 + 278 | 29 + 279 | 22 + 280 | 29 + 281 | 23 + 282 | 30 + 283 | 29 + 284 | 23 + 285 | 37 + 286 | 27 + 287 | 31 + 288 | 28 + 289 | 26 + 290 | 25 + 291 | 29 + 292 | 22 + 293 | 26 + 294 | 29 + 295 | 24 + 296 | 27 + 297 | 30 + 298 | 29 + 299 | 26 + 300 | 13 + 1000 | 300 + 1001 | 3600 + | 9 + | 244 +(305 rows) + diff --git a/expected/gevel_brin.out b/expected/gevel_brin.out new file mode 100644 index 0000000..8ddab61 --- /dev/null +++ b/expected/gevel_brin.out @@ -0,0 +1,26 @@ +SET client_min_messages = warning; +\set ECHO none +RESET client_min_messages; +CREATE TABLE gevelb ( t box ); +\copy gevelb from 'data/rect.data' +--BRIN +CREATE INDEX brin_idx ON gevelb USING brin ( t ); +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) + +SELECT brin_print('brin_idx'); + brin_print +-------------------------------------------------------- + Start block: 0; end block: 24; offset: 113, free: 1704+ + +(1 row) + diff --git a/expected/gevel_btree.out b/expected/gevel_btree.out new file mode 100644 index 0000000..e15a4f6 --- /dev/null +++ b/expected/gevel_btree.out @@ -0,0 +1,185 @@ +SET client_min_messages = warning; +\set ECHO none +RESET client_min_messages; +CREATE TABLE test__val( a int[] ); +\copy test__val from 'data/test__int.data' +INSERT INTO test__val ( SELECT ARRAY[t] || '{1000}'::_int4 FROM generate_series (1,300) as t ); +INSERT INTO test__val ( SELECT ARRAY[t] || '{1001}'::_int4 FROM generate_series (1,300) as t, generate_series(1,12) ); +VACUUM ANALYZE test__val; +--Btree +CREATE INDEX btree_idx ON test__val USING btree ( a ); +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: 138624 bytes+ + Total size of leaf tuples: 140204 bytes+ + Total size of index: 614400 bytes+ + +(1 row) + +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) + +SELECT * FROM btree_print('btree_idx') as t(level int, valid bool, a int[]) where level=1; + level | valid | 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) + diff --git a/gevel.brin.sql b/gevel.brin.sql new file mode 100644 index 0000000..fc4f8df --- /dev/null +++ b/gevel.brin.sql @@ -0,0 +1,16 @@ +SET search_path = public; +BEGIN; + +create or replace function brin_stat(text) + returns text + as '$libdir/gevel' + language C + strict; + +create or replace function brin_print(text) + returns text + as '$libdir/gevel' + language C + strict; + +END; diff --git a/gevel.btree.sql b/gevel.btree.sql new file mode 100644 index 0000000..bd75336 --- /dev/null +++ b/gevel.btree.sql @@ -0,0 +1,28 @@ +SET search_path = public; +BEGIN; + +create or replace function btree_stat(text) + returns text + as '$libdir/gevel' + language C + strict; + +create or replace function btree_print(text) + returns setof record + as '$libdir/gevel' + language C + strict; + +create or replace function btree_tree(text) + returns text + as '$libdir/gevel' + language C + strict; + +create or replace function btree_tree(text, int) + returns text + as '$libdir/gevel' + language C + strict; + +END; diff --git a/gevel.c b/gevel.c index ff5e5c4..330126a 100644 --- a/gevel.c +++ b/gevel.c @@ -31,7 +31,6 @@ #include #include #endif -#include #include "utils/builtins.h" #include "utils/lsyscache.h" #include "utils/datum.h" @@ -41,6 +40,13 @@ #include #include #include +#if PG_VERSION_NUM >= 120000 +#include +#include +#include +#include +#include +#endif #define PAGESIZE (BLCKSZ - MAXALIGN(sizeof(PageHeaderData) + sizeof(ItemIdData))) @@ -107,6 +113,27 @@ gin_index_open(RangeVar *relvar) { #define gin_index_close(r) index_close((r), AccessShareLock) +#if PG_VERSION_NUM >= 120000 +static Relation +btree_index_open(RangeVar *relvar) { + Oid relOid = RangeVarGetRelid(relvar, NoLock, false); + return checkOpenedRelation( + index_open(relOid, AccessExclusiveLock), BTREE_AM_OID); +} + +#define btree_index_close(r) index_close((r), AccessExclusiveLock) + +static Relation +brin_index_open(RangeVar *relvar) +{ + Oid relOid = RangeVarGetRelid(relvar, NoLock, false); + return checkOpenedRelation( + index_open(relOid, AccessExclusiveLock), BRIN_AM_OID); +} + +#define brin_index_close(r) index_close((r), AccessExclusiveLock) +#endif + #else /* <8.2 */ static Relation @@ -137,6 +164,20 @@ gin_index_close(Relation rel) { index_close(rel); } +static Relation +btree_index_open(RangeVar *relvar) { + Relation rel = index_openrv(relvar); + + LockRelation(rel, AccessExclusiveLock); + return checkOpenedRelation(rel, BTREE_AM_OID); +} + +static void +btree_index_close(Relation rel) { + UnlockRelation(rel, AccessExclusiveLock); + index_close(rel); +} + #endif #if PG_VERSION_NUM >= 80300 @@ -432,7 +473,11 @@ setup_firstcall(FuncCallContext *funcctx, text *name) { st->index = gist_index_open(st->relvar); funcctx->user_fctx = (void*)st; - tupdesc = CreateTemplateTupleDesc(st->index->rd_att->natts+2, false); +#if PG_VERSION_NUM >= 120000 + tupdesc = CreateTemplateTupleDesc(st->index->rd_att->natts+2); +#else + tupdesc = CreateTemplateTupleDesc(3 /* types */ + 1 /* level */ + 1 /* nlabel */ + 2 /* tids */ + 1, false); +#endif TupleDescInitEntry(tupdesc, 1, "level", INT4OID, -1, 0); TupleDescInitEntry(tupdesc, 2, "valid", BOOLOID, -1, 0); for (i = 0; i < st->index->rd_att->natts; i++) { @@ -450,8 +495,11 @@ setup_firstcall(FuncCallContext *funcctx, text *name) { st->dvalues = (Datum *) palloc((tupdesc->natts+2) * sizeof(Datum)); st->nulls = palloc((tupdesc->natts+2) * sizeof(*st->nulls)); - funcctx->slot = TupleDescGetSlot(tupdesc); +#if PG_VERSION_NUM >= 120000 funcctx->attinmeta = TupleDescGetAttInMetadata(tupdesc); +#else + funcctx->slot = TupleDescGetSlot(tupdesc); +#endif MemoryContextSwitchTo(oldcontext); pfree(relname); @@ -513,8 +561,10 @@ gist_print(PG_FUNCTION_ARGS) { st->nulls[0] = ISNOTNULL; st->dvalues[1] = BoolGetDatum( (!GistPageIsLeaf(st->item->page) && GistTupleIsInvalid(ituple)) ? false : true ); st->nulls[1] = ISNOTNULL; - for(i=2; iattinmeta->tupdesc->natts; i++) { - if ( !GistPageIsLeaf(st->item->page) && GistTupleIsInvalid(ituple) ) { + for(i=2; iattinmeta->tupdesc->natts; i++) + { + if ( !GistPageIsLeaf(st->item->page) && GistTupleIsInvalid(ituple) ) + { st->dvalues[i] = (Datum)0; st->nulls[i] = ISNULL; } else { @@ -524,7 +574,11 @@ gist_print(PG_FUNCTION_ARGS) { } htuple = heap_formtuple(funcctx->attinmeta->tupdesc, st->dvalues, st->nulls); +#if PG_VERSION_NUM >= 120000 + result = HeapTupleGetDatum(htuple); +#else result = TupleGetDatum(funcctx->slot, htuple); +#endif st->item->offset = OffsetNumberNext(st->item->offset); if ( !GistPageIsLeaf(st->item->page) ) openGPPage(funcctx, ItemPointerGetBlockNumber(&(ituple->t_tid)) ); @@ -620,7 +674,7 @@ refindPosition(GinStatState *st) itup = (IndexTuple) PageGetItem(page, PageGetItemId(page, st->offset)); #if PG_VERSION_NUM >= 90100 - datum = gintuple_get_key(&st->ginstate, itup, &category); + datum = gintuple_get_key(&st->ginstate, itup, &category); cmp = ginCompareAttEntries(&st->ginstate, st->attnum + 1, st->curval, st->category, gintuple_get_attrnum(&st->ginstate, itup), datum, category); @@ -678,7 +732,11 @@ gin_setup_firstcall(FuncCallContext *funcctx, text *name, int attnum) { funcctx->user_fctx = (void*)st; +#if PG_VERSION_NUM >= 120000 + tupdesc = CreateTemplateTupleDesc(2); +#else tupdesc = CreateTemplateTupleDesc(2, false); +#endif TupleDescInitEntry(tupdesc, 1, "value", TS_GET_TYPEVAL(st, st->attnum, atttypid), TS_GET_TYPEVAL(st, st->attnum, atttypmod), @@ -687,8 +745,11 @@ gin_setup_firstcall(FuncCallContext *funcctx, text *name, int attnum) { memset( st->nulls, ISNOTNULL, 2*sizeof(*st->nulls) ); - funcctx->slot = TupleDescGetSlot(tupdesc); +#if PG_VERSION_NUM >= 120000 funcctx->attinmeta = TupleDescGetAttInMetadata(tupdesc); +#else + funcctx->slot = TupleDescGetSlot(tupdesc); +#endif MemoryContextSwitchTo(oldcontext); pfree(relname); @@ -839,8 +900,12 @@ gin_stat(PG_FUNCTION_ARGS) { processTuple( funcctx, st, ituple ); htuple = heap_formtuple(funcctx->attinmeta->tupdesc, st->dvalues, st->nulls); +#if PG_VERSION_NUM >= 120000 + result = HeapTupleGetDatum(htuple); +#else result = TupleGetDatum(funcctx->slot, htuple); - +#endif + SRF_RETURN_NEXT(funcctx, result); } @@ -1148,7 +1213,11 @@ spgist_print(PG_FUNCTION_ARGS) prst->index = index; initSpGistState(&prst->state, index); +#if PG_VERSION_NUM >= 120000 + tupdesc = CreateTemplateTupleDesc(3 /* types */ + 1 /* level */ + 1 /* nlabel */ + 2 /* tids */ + 1); +#else tupdesc = CreateTemplateTupleDesc(3 /* types */ + 1 /* level */ + 1 /* nlabel */ + 2 /* tids */ + 1, false); +#endif TupleDescInitEntry(tupdesc, 1, "tid", TIDOID, -1, 0); TupleDescInitEntry(tupdesc, 2, "allthesame", BOOLOID, -1, 0); TupleDescInitEntry(tupdesc, 3, "node", INT4OID, -1, 0); @@ -1161,8 +1230,11 @@ spgist_print(PG_FUNCTION_ARGS) TupleDescInitEntry(tupdesc, 8, "leaf", (prst->state.attType.type == VOIDOID) ? INT4OID : prst->state.attType.type, -1, 0); - funcctx->slot = TupleDescGetSlot(tupdesc); +#if PG_VERSION_NUM >= 120000 funcctx->attinmeta = TupleDescGetAttInMetadata(tupdesc); +#else + funcctx->slot = TupleDescGetSlot(tupdesc); +#endif funcctx->user_fctx = (void*)prst; @@ -1295,13 +1367,16 @@ next: } while(0); htuple = heap_formtuple(funcctx->attinmeta->tupdesc, prst->dvalues, prst->nulls); +#if PG_VERSION_NUM >= 120000 + result = HeapTupleGetDatum(htuple); +#else result = TupleGetDatum(funcctx->slot, htuple); +#endif SRF_RETURN_NEXT(funcctx, result); #endif } - PG_FUNCTION_INFO_V1(gin_statpage); Datum gin_statpage(PG_FUNCTION_ARGS); Datum @@ -1498,3 +1573,628 @@ gin_statpage(PG_FUNCTION_ARGS) #endif } +#if PG_VERSION_NUM >= 120000 +typedef enum {stat, print} TreeCond; +typedef struct +{ + IdxInfo idxInfo; + IdxStat idxStat; +}BtreeIdxInfo; + +/* + * Depth-first search for btree + * using for statistic data collection + * and printing index values by level + */ +static void +btree_deep_search(Relation rel, int level, + BlockNumber blk, BtreeIdxInfo *btreeIdxInfo, TreeCond cond) +{ + Page page; + IndexTuple itup; + ItemId iid; + OffsetNumber i, + maxoff; + BlockNumber cblk; + BTPageOpaque opaque; + Buffer buffer = _bt_getbuf(rel, blk, BT_READ); + + page = (Page) BufferGetPage(buffer); + opaque = (BTPageOpaque) PageGetSpecialPointer(page); + maxoff = PageGetMaxOffsetNumber(page); + + switch (cond) + { + case stat: + { + btreeIdxInfo->idxStat.numpages++; + btreeIdxInfo->idxStat.tuplesize+=BTMaxItemSize(page)-PageGetFreeSpace(page); + btreeIdxInfo->idxStat.totalsize+=BLCKSZ; + btreeIdxInfo->idxStat.numtuple+=maxoff; + + if (level > btreeIdxInfo->idxStat.level) + btreeIdxInfo->idxStat.level = level; + + if (P_ISLEAF(opaque)) + { + btreeIdxInfo->idxStat.numleafpages++; + btreeIdxInfo->idxStat.leaftuplesize+=BTMaxItemSize(page)- + PageGetFreeSpace(page); + btreeIdxInfo->idxStat.numleaftuple+=maxoff; + } + break; + } + case print: + { + while ( (btreeIdxInfo->idxInfo.ptr-((char*)btreeIdxInfo->idxInfo.txt)) + + level*4 + 128 >= btreeIdxInfo->idxInfo.len ) + { + int dist=btreeIdxInfo->idxInfo.ptr-((char*)btreeIdxInfo->idxInfo.txt); + btreeIdxInfo->idxInfo.len *= 2; + btreeIdxInfo->idxInfo.txt=(text*)repalloc(btreeIdxInfo->idxInfo.txt, + btreeIdxInfo->idxInfo.len); + btreeIdxInfo->idxInfo.ptr = ((char*)btreeIdxInfo->idxInfo.txt)+dist; + } + + sprintf(btreeIdxInfo->idxInfo.ptr, "lvl: %d, blk: %d, numTuples: %d\n", + level, + blk, + (int)maxoff); + + btreeIdxInfo->idxInfo.ptr=strchr(btreeIdxInfo->idxInfo.ptr,'\0'); + break; + } + } + + if (!P_ISLEAF(opaque) && ((level < btreeIdxInfo->idxInfo.maxlevel) + ||(btreeIdxInfo->idxInfo.maxlevel<0))) + { + for (i = P_FIRSTDATAKEY(opaque); i <= maxoff; i = OffsetNumberNext(i)) + { + iid = PageGetItemId(page, i); + + if (!ItemIdIsValid(iid)) + btreeIdxInfo->idxStat.numinvalidtuple++; + + itup = (IndexTuple) PageGetItem(page, iid); + cblk = BTreeInnerTupleGetDownLink(itup); + + btree_deep_search(rel, level + 1, cblk, btreeIdxInfo, cond); + } + } + UnlockReleaseBuffer(buffer); +} + +/* + * Print some statistic about btree index + * This function shows information for live pages only + * and do not shows information about deleting pages + * + * SELECT btree_stat(INDEXNAME); + */ +PG_FUNCTION_INFO_V1(btree_stat); +Datum btree_stat(PG_FUNCTION_ARGS); +Datum +btree_stat(PG_FUNCTION_ARGS) +{ + text *name=PG_GETARG_TEXT_PP(0); + RangeVar *relvar; + Relation index; + List *relname_list; + BtreeIdxInfo btreeIdxInfo; + + Buffer metabuf; + Page metapg; + BTMetaPageData *metad; + BlockNumber rootBlk; + + text *out=(text*)palloc(1024); + char *ptr=((char*)out)+VARHDRSZ; + relname_list = textToQualifiedNameList(name); + relvar = makeRangeVarFromNameList(relname_list); + index = btree_index_open(relvar); + + memset(&btreeIdxInfo.idxStat, 0, sizeof(IdxStat)); + + /* Start dts from root */ + metabuf = _bt_getbuf(index, BTREE_METAPAGE, BT_READ); + metapg = BufferGetPage(metabuf); + metad = BTPageGetMeta(metapg); + rootBlk = metad->btm_root; + UnlockReleaseBuffer(metabuf); + + btree_deep_search(index, 0, rootBlk, &btreeIdxInfo,stat); + + btree_index_close(index); + + sprintf(ptr, + "Number of levels: %d\n" + "Number of pages: %d\n" + "Number of leaf pages: %d\n" + "Number of tuples: %d\n" + "Number of invalid tuples: %d\n" + "Number of leaf tuples: %d\n" + "Total size of tuples: "INT64_FORMAT" bytes\n" + "Total size of leaf tuples: "INT64_FORMAT" bytes\n" + "Total size of index: "INT64_FORMAT" bytes\n", + btreeIdxInfo.idxStat.level+1, + btreeIdxInfo.idxStat.numpages, + btreeIdxInfo.idxStat.numleafpages, + btreeIdxInfo.idxStat.numtuple, + btreeIdxInfo.idxStat.numinvalidtuple, + btreeIdxInfo.idxStat.numleaftuple, + btreeIdxInfo.idxStat.tuplesize, + btreeIdxInfo.idxStat.leaftuplesize, + btreeIdxInfo.idxStat.totalsize); + + ptr=strchr(ptr,'\0'); + + SET_VARSIZE(out, ptr-((char*)out)); + PG_RETURN_POINTER(out); +} + +typedef struct BtPItem +{ + Buffer buffer; + Page page; + OffsetNumber offset; + int level; + struct BtPItem *next; +} BtPItem; + +typedef struct +{ + List *relname_list; + RangeVar *relvar; + Relation index; + Datum *dvalues; + bool *nulls; + BtPItem *item; +} BtTypeStorage; + +/* + * Open page in btree + * Returns nitem as a pointer to stack for btree levels stored. + * We process tuples from buffer in the top of nitem. + * After the complete processing of a top buffer + * we return to buffer on one level upper and go to next btree leaf. + */ +static BtPItem* +openBtPPage( FuncCallContext *funcctx, BlockNumber blk ) +{ + BtPItem *nitem; + MemoryContext oldcontext; + + Relation index = ( (BtTypeStorage*)(funcctx->user_fctx) )->index; + BTPageOpaque opaque; + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + nitem = (BtPItem*)palloc( sizeof(BtPItem) ); + memset(nitem,0,sizeof(BtPItem)); + + nitem->buffer = _bt_getbuf(index, blk, BT_READ); + Assert(BufferIsValid(nitem->buffer)); + nitem->page = (Page) BufferGetPage(nitem->buffer); + opaque = (BTPageOpaque)PageGetSpecialPointer(nitem->page); + nitem->offset=P_FIRSTDATAKEY(opaque); + nitem->next = ( (BtTypeStorage*)(funcctx->user_fctx) )->item; + nitem->level = ( nitem->next ) ? nitem->next->level+1 : 1; + ( (BtTypeStorage*)(funcctx->user_fctx) )->item = nitem; + + MemoryContextSwitchTo(oldcontext); + + return nitem; +} + +static BtPItem* +closeBtPPage( FuncCallContext *funcctx ) +{ + BtPItem *oitem = ( (BtTypeStorage*)(funcctx->user_fctx) )->item; + + ( (BtTypeStorage*)(funcctx->user_fctx) )->item = oitem->next; + + UnlockReleaseBuffer(oitem->buffer); + pfree( oitem ); + return ( (BtTypeStorage*)(funcctx->user_fctx) )->item; +} + +static void +btree_close_call( FuncCallContext *funcctx ) +{ + BtTypeStorage *st = (BtTypeStorage*)(funcctx->user_fctx); + + while(st->item && closeBtPPage(funcctx)); + + pfree(st->dvalues); + pfree(st->nulls); + + btree_index_close(st->index); +} + +/* + * Settings for first call of btree_print + * Sets the current memory context + */ +static void +btree_setup_firstcall(FuncCallContext *funcctx, text *name) +{ + MemoryContext oldcontext; + BtTypeStorage *st; + TupleDesc tupdesc; + char attname[NAMEDATALEN]; + int i; + BlockNumber blk; + Buffer buffer; + + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + st=(BtTypeStorage*)palloc( sizeof(BtTypeStorage) ); + memset(st,0,sizeof(BtTypeStorage)); + st->relname_list = textToQualifiedNameList(name); + st->relvar = makeRangeVarFromNameList(st->relname_list); + st->index = btree_index_open(st->relvar); + st->item = NULL; + funcctx->user_fctx = (void*)st; + + tupdesc = CreateTemplateTupleDesc(st->index->rd_att->natts+2); + TupleDescInitEntry(tupdesc, 1, "level", INT4OID, -1, 0); + TupleDescInitEntry(tupdesc, 2, "valid", BOOLOID, -1, 0); + for (i = 0; i < st->index->rd_att->natts; i++) + { + sprintf(attname, "z%d", i+2); + TupleDescInitEntry( + tupdesc, + i+3, + attname, + TS_GET_TYPEVAL(st, i, atttypid), + TS_GET_TYPEVAL(st, i, atttypmod), + TS_GET_TYPEVAL(st, i, attndims) ); + BlessTupleDesc(tupdesc); + } + BlessTupleDesc(tupdesc); + + + st->dvalues = (Datum *) palloc((tupdesc->natts+2) * sizeof(Datum)); + st->nulls = palloc((tupdesc->natts+2) * sizeof(*st->nulls)); + + funcctx->attinmeta = TupleDescGetAttInMetadata(tupdesc); + + buffer = _bt_gettrueroot(st->index); + blk = BufferGetBlockNumber(buffer); + UnlockReleaseBuffer(buffer); + + MemoryContextSwitchTo(oldcontext); + + st->item=openBtPPage(funcctx, blk); +} + +/* + * Show index elements for btree from root to MAXLEVEL + * SELECT btree_tree(INDEXNAME[, MAXLEVEL]); + */ +PG_FUNCTION_INFO_V1(btree_tree); +Datum btree_tree(PG_FUNCTION_ARGS); +Datum +btree_tree(PG_FUNCTION_ARGS) +{ + text *name=PG_GETARG_TEXT_PP(0); + RangeVar *relvar; + Relation index; + List *relname_list; + BtreeIdxInfo btreeIdxInfo; + Buffer metabuf; + Page metapg; + BTMetaPageData *metad; + BlockNumber rootBlk; + + /* + * If we use MAXLEVEL is not used in SELECT btree_tree(INDEXNAME), + * info.maxlevel set to -1 + * If MAXLEVEL is used in btree_tree call, set info.maxlevel = MAXLEVEL + */ + btreeIdxInfo.idxInfo.maxlevel = ( PG_NARGS() > 1 ) ? PG_GETARG_INT32(1) : -1; + btreeIdxInfo.idxInfo.len=1024; + btreeIdxInfo.idxInfo.txt=(text*)palloc( btreeIdxInfo.idxInfo.len ); + btreeIdxInfo.idxInfo.ptr=((char*)btreeIdxInfo.idxInfo.txt)+VARHDRSZ; + + relname_list = textToQualifiedNameList(name); + relvar = makeRangeVarFromNameList(relname_list); + index = btree_index_open(relvar); + + /* Start dts from root block */ + metabuf = _bt_getbuf(index, BTREE_METAPAGE, BT_READ); + metapg = BufferGetPage(metabuf); + metad = BTPageGetMeta(metapg); + rootBlk = metad->btm_root; + UnlockReleaseBuffer(metabuf); + + btree_deep_search(index, 0, rootBlk, &btreeIdxInfo, print); + + btree_index_close(index); + + btreeIdxInfo.idxInfo.ptr=strchr(btreeIdxInfo.idxInfo.ptr,'\0'); + + SET_VARSIZE(btreeIdxInfo.idxInfo.txt, + btreeIdxInfo.idxInfo.ptr-((char*)btreeIdxInfo.idxInfo.txt)); + PG_RETURN_POINTER(btreeIdxInfo.idxInfo.txt); +} + +/* + * Print objects stored in btree tuples + * works only if objects in index have textual representation + * select * from btree_print(INDEXNAME) + * as t(level int, valid bool, a box) where level =1; + */ +PG_FUNCTION_INFO_V1(btree_print); +Datum btree_print(PG_FUNCTION_ARGS); +Datum +btree_print(PG_FUNCTION_ARGS) +{ + FuncCallContext *funcctx; + BtTypeStorage *st; + ItemId iid; + IndexTuple ituple; + HeapTuple htuple; + int i; + bool isnull; + BTPageOpaque opaque; + Datum result=(Datum)0; + + if (SRF_IS_FIRSTCALL()) + { + text *name=PG_GETARG_TEXT_PP(0); + funcctx = SRF_FIRSTCALL_INIT(); + btree_setup_firstcall(funcctx, name); + PG_FREE_IF_COPY(name,0); + } + + funcctx = SRF_PERCALL_SETUP(); + st = (BtTypeStorage*)(funcctx->user_fctx); + + /* + * Looking through the stack of btree pages. + * If the item == NULL, then the search is over. + * For each btree page we look through all the tuples. + * If the offset is larger than the btree page size, the search is over. + */ + + if ( !st->item ) + { + btree_close_call(funcctx); + SRF_RETURN_DONE(funcctx); + } + + /* View all tuples on the page */ + while( st->item->offset > PageGetMaxOffsetNumber( st->item->page ) ) + { + if ( ! closeBtPPage(funcctx) ) { + btree_close_call(funcctx); + SRF_RETURN_DONE(funcctx); + } + } + + iid = PageGetItemId( st->item->page, st->item->offset ); + ituple = (IndexTuple) PageGetItem(st->item->page, iid); + + st->dvalues[0] = Int32GetDatum( st->item->level ); + st->nulls[0] = ISNOTNULL; + opaque = (BTPageOpaque)PageGetSpecialPointer(st->item->page); + st->dvalues[1] = BoolGetDatum(P_ISLEAF(opaque) && !ItemPointerIsValid(&(ituple->t_tid)) ? false : true); + st->nulls[1] = ISNOTNULL; + for(i=2; iattinmeta->tupdesc->natts; i++) + { + if (!P_ISLEAF(opaque) && !ItemPointerIsValid(&(ituple->t_tid))) + { + st->dvalues[i] = (Datum)0; + st->nulls[i] = ISNULL; + } + else + { + st->dvalues[i] = index_getattr(ituple, i-1, st->index->rd_att, &isnull); + st->nulls[i] = ( isnull ) ? ISNULL : ISNOTNULL; + } + } + + htuple = heap_formtuple(funcctx->attinmeta->tupdesc, st->dvalues, st->nulls); + result = TupleGetDatum(funcctx->slot, htuple); + result = HeapTupleGetDatum(htuple); + st->item->offset = OffsetNumberNext(st->item->offset); + + if (!P_ISLEAF(opaque)) + { + BlockNumber blk = BTreeInnerTupleGetDownLink(ituple); + openBtPPage(funcctx, blk ); + } + + SRF_RETURN_NEXT(funcctx, result); +} + +/* + * Print some statistic about brin index + * SELECT brin_stat(INDEXNAME); + */ +PG_FUNCTION_INFO_V1(brin_stat); +Datum brin_stat(PG_FUNCTION_ARGS); +Datum +brin_stat(PG_FUNCTION_ARGS) +{ + text *name=PG_GETARG_TEXT_PP(0); + RangeVar *relvar; + Relation index; + List *relname_list; + + BlockNumber startBlk; + BlockNumber heapNumBlocks; + BlockNumber pagesPerRange; + BrinRevmap *revmap; + Relation heapRel; + + Buffer buf; + + uint32 numRevmapPages=0; + uint32 numTuples = 0; + uint32 numEmptyPages = 0; + uint32 numRegularPages = 0; + uint64 freeSpace = 0; + uint64 usedSpace = 0; + + text *out=(text*)palloc(1024); + char *ptr=((char*)out)+VARHDRSZ; + + relname_list = textToQualifiedNameList(name); + relvar = makeRangeVarFromNameList(relname_list); + index = brin_index_open(relvar); + + revmap = brinRevmapInitialize(index, &pagesPerRange, NULL); + heapRel = table_open(IndexGetRelation(RelationGetRelid(index), false), + AccessShareLock); + /* Determine range of pages to process */ + heapNumBlocks = RelationGetNumberOfBlocks(heapRel); // total pages + + table_close(heapRel, AccessShareLock); + + /* The index is up to date, no update required */ + startBlk = 0; + + buf = InvalidBuffer; + /* Scan the revmap */ + for (; startBlk < heapNumBlocks; startBlk += pagesPerRange) + { + BrinTuple *tup; + OffsetNumber off; + Page page; + + numRevmapPages++; + /* Get on-disk tuple */ + tup = brinGetTupleForHeapBlock(revmap, startBlk, &buf, &off, NULL, + BUFFER_LOCK_SHARE, NULL); + + if (tup) + { + BrinDesc* bdesc; + + numTuples++; + page = BufferGetPage(buf); + + if (BRIN_IS_REGULAR_PAGE(page)) + numRegularPages++; + freeSpace += PageGetFreeSpace(page); + bdesc = brin_build_desc(index); + usedSpace += MAXALIGN(sizeof(BrinMemTuple) + + sizeof(BrinValues) * bdesc->bd_tupdesc->natts); + } + else + /* If the revmap page points to void */ + numEmptyPages++; + + UnlockReleaseBuffer(buf); + } + + brinRevmapTerminate(revmap); + + sprintf(ptr, + "Number of revmap pages: %d\n" + "Number of empty revmap pages: %d\n" + "Number of regular pages: %d\n" + "Number of tuples: %d\n" + "Used space "INT64_FORMAT" bytes\n" + "Free space "INT64_FORMAT" bytes\n", + numRevmapPages, + numEmptyPages, + numRegularPages, + numTuples, + usedSpace, + freeSpace + ); + + ptr=strchr(ptr,'\0'); + + brin_index_close(index); + SET_VARSIZE(out, ptr-((char*)out)); + PG_RETURN_POINTER(out); +} + +/* + * Print numbers of heap blocks from revmap + * and numbers of end blocks from ranges + */ +PG_FUNCTION_INFO_V1(brin_print); +Datum brin_print(PG_FUNCTION_ARGS); +Datum +brin_print(PG_FUNCTION_ARGS) +{ + text *name=PG_GETARG_TEXT_PP(0); + RangeVar *relvar; + Relation index; + List *relname_list; + + BlockNumber heapBlk; + BlockNumber numBlocks; + BlockNumber pagesPerRange; + BrinRevmap *revmap; + Relation heapRel; + int len = 1024; + + text *out=(text*)palloc(len); + char *ptr=((char*)out)+VARHDRSZ; + + relname_list = textToQualifiedNameList(name); + relvar = makeRangeVarFromNameList(relname_list); + index = brin_index_open(relvar); + + heapRel = table_open(IndexGetRelation(RelationGetRelid(index), false), + AccessShareLock); + + /* Determine range of pages to process */ + numBlocks = RelationGetNumberOfBlocks(heapRel); + table_close(heapRel, AccessShareLock); + revmap = brinRevmapInitialize(index, &pagesPerRange, NULL); + + for(heapBlk = 0; heapBlk < numBlocks; heapBlk += pagesPerRange) + { + BlockNumber rangeEndBlk; + BlockNumber rangeBlk; + + while ( (ptr-((char*)out)) + heapBlk*4 + 128 >= len ) + { + int dist=ptr-((char*)out); + len *= 2; + out=(text*)repalloc(out, len); + ptr = ((char*)out)+dist; + Assert(0); + } + + /* Compute rage end */ + if (heapBlk + pagesPerRange > numBlocks) + rangeEndBlk = Min(RelationGetNumberOfBlocks(heapRel) - heapBlk, + pagesPerRange); + else + /* Easy case: range is known to be complete */ + rangeEndBlk = pagesPerRange; + sprintf(ptr, "Start block: %d; end block: %d \n", heapBlk, rangeEndBlk); + + for (rangeBlk = 0; rangeBlk < rangeEndBlk; rangeBlk++) + { + Page page; + /* Read buffer for open table */ + Buffer buf = ReadBuffer(heapRel, rangeBlk); + page = BufferGetPage(buf); + sprintf(ptr, "Start block: %d; end block: %d; offset: %d, free: %d\n", + heapBlk, + rangeEndBlk, + (int) PageGetMaxOffsetNumber(page), + (int) PageGetFreeSpace(page)); + + ReleaseBuffer(buf); + } + + ptr=strchr(ptr,'\0'); + } + + brinRevmapTerminate(revmap); + + brin_index_close(index); + ptr=strchr(ptr,'\0'); + SET_VARSIZE(out, ptr-((char*)out)); + PG_RETURN_POINTER(out); +} +#endif diff --git a/gevel.control b/gevel.control new file mode 100644 index 0000000..d8c60f1 --- /dev/null +++ b/gevel.control @@ -0,0 +1,5 @@ +# gevel extension +comment = 'gevel' +default_version = '1.1' +module_pathname = '$libdir/gevel' +relocatable = false diff --git a/gevel.sql b/gevel.sql new file mode 100644 index 0000000..6884c85 --- /dev/null +++ b/gevel.sql @@ -0,0 +1,64 @@ +SET search_path = public; +BEGIN; + +create or replace function gist_tree(text) + returns text + as '$libdir/gevel' + language C + strict; + +create or replace function gist_tree(text,int4) + returns text + as '$libdir/gevel' + language C + strict; + +create or replace function gist_stat(text) + returns text + as '$libdir/gevel' + language C + strict; + +create or replace function gist_print(text) + returns setof record + as '$libdir/gevel' + language C + strict; + +create or replace function gin_stat(text) + returns setof record + as '$libdir/gevel' + language C + strict; + +create or replace function gin_stat(text, int) + returns setof record + as '$libdir/gevel' + language C + strict; + +create or replace function gin_statpage(text) + returns text + as '$libdir/gevel' + language C + strict; + +create or replace function gin_count_estimate(text, tsquery) + returns bigint + as '$libdir/gevel' + language C + strict; + +create or replace function spgist_stat(text) + returns text + as '$libdir/gevel' + language C + strict; + +create or replace function spgist_print(text) + returns setof record + as '$libdir/gevel' + language C + strict; + +END; diff --git a/sql/gevel.sql b/sql/gevel.sql index 2a5d653..b9262ff 100644 --- a/sql/gevel.sql +++ b/sql/gevel.sql @@ -24,7 +24,7 @@ SELECT spgist_stat('spgist_idx'); SELECT * FROM spgist_print('kdspgist_idx') as t(tid tid, allthesame bool, node_n int, level int, tid_pointer tid, prefix float8, node_label int, leaf_value point); 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; ---GIN +--GIN CREATE TABLE test__int( a int[] ); \copy test__int from 'data/test__int.data' @@ -32,5 +32,5 @@ CREATE INDEX gin_idx ON test__int USING gin ( a ); INSERT INTO test__int ( SELECT ARRAY[t] || '{1000}'::_int4 FROM generate_series (1,300) as t ); INSERT INTO test__int ( SELECT ARRAY[t] || '{1001}'::_int4 FROM generate_series (1,300) as t, generate_series(1,12) ); -VACUUM ANALYZE test__int; +VACUUM ANALYZE test__int; SELECT * FROM gin_stat('gin_idx') as t(value int, nrow int); diff --git a/sql/gevel_brin.sql b/sql/gevel_brin.sql new file mode 100644 index 0000000..2382378 --- /dev/null +++ b/sql/gevel_brin.sql @@ -0,0 +1,14 @@ +SET client_min_messages = warning; +\set ECHO none +\i gevel.brin.sql +\set ECHO all +RESET client_min_messages; + +CREATE TABLE gevelb ( t box ); +\copy gevelb from 'data/rect.data' + +--BRIN +CREATE INDEX brin_idx ON gevelb USING brin ( t ); + +SELECT brin_stat('brin_idx'); +SELECT brin_print('brin_idx'); diff --git a/sql/gevel_btree.sql b/sql/gevel_btree.sql new file mode 100644 index 0000000..c05a71e --- /dev/null +++ b/sql/gevel_btree.sql @@ -0,0 +1,18 @@ +SET client_min_messages = warning; +\set ECHO none +\i gevel.btree.sql +\set ECHO all +RESET client_min_messages; + +CREATE TABLE test__val( a int[] ); +\copy test__val from 'data/test__int.data' +INSERT INTO test__val ( SELECT ARRAY[t] || '{1000}'::_int4 FROM generate_series (1,300) as t ); +INSERT INTO test__val ( SELECT ARRAY[t] || '{1001}'::_int4 FROM generate_series (1,300) as t, generate_series(1,12) ); +VACUUM ANALYZE test__val; + +--Btree +CREATE INDEX btree_idx ON test__val USING btree ( a ); + +SELECT btree_stat('btree_idx'); +SELECT btree_tree('btree_idx'); +SELECT * FROM btree_print('btree_idx') as t(level int, valid bool, a int[]) where level=1; -- 2.37.3