From 8b7af6b1376dd6b60df600c479466ad0f25b5843 Mon Sep 17 00:00:00 2001 From: teodor Date: Tue, 27 Dec 2011 12:41:24 +0000 Subject: [PATCH] spgist_stat --- expected/gevel.out.9.2 | 24 +++++++ gevel.c | 144 ++++++++++++++++++++++++++++++++++++++++- gevel.sql.in | 6 ++ sql/gevel.sql | 7 ++ 4 files changed, 179 insertions(+), 2 deletions(-) diff --git a/expected/gevel.out.9.2 b/expected/gevel.out.9.2 index 26cb1d1..915c789 100644 --- a/expected/gevel.out.9.2 +++ b/expected/gevel.out.9.2 @@ -3,7 +3,9 @@ SET client_min_messages = warning; 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 ); --GiST SELECT gist_stat('gist_idx'); gist_stat @@ -88,6 +90,28 @@ SELECT * FROM gist_print('gist_idx') as t(level int, valid bool, a box) where le 1 | t | (32511,31225),(25000,19942) (28 rows) +--SPGiST +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 +(1 row) + +--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 ); diff --git a/gevel.c b/gevel.c index 86fb43e..ef8f3d9 100644 --- a/gevel.c +++ b/gevel.c @@ -8,6 +8,10 @@ #include "access/gist.h" #include "access/gist_private.h" #include "access/gistscan.h" +#if PG_VERSION_NUM >= 90200 +#include "access/spgist_private.h" +#include "access/spgist.h" +#endif #include "access/heapam.h" #include "catalog/index.h" #include "miscadmin.h" @@ -58,7 +62,7 @@ PG_MODULE_MAGIC; static Relation gist_index_open(RangeVar *relvar) { -#if PG_VERSION_NUM < 90100 +#if PG_VERSION_NUM <= 90100 Oid relOid = RangeVarGetRelid(relvar, false); #else Oid relOid = RangeVarGetRelid(relvar, NoLock, false); @@ -71,7 +75,7 @@ gist_index_open(RangeVar *relvar) { static Relation gin_index_open(RangeVar *relvar) { -#if PG_VERSION_NUM < 90100 +#if PG_VERSION_NUM <= 90100 Oid relOid = RangeVarGetRelid(relvar, false); #else Oid relOid = RangeVarGetRelid(relvar, NoLock, false); @@ -835,3 +839,139 @@ gin_count_estimate(PG_FUNCTION_ARGS) { PG_RETURN_INT64(0); } #endif + +PG_FUNCTION_INFO_V1(spgist_stat); +Datum spgist_stat(PG_FUNCTION_ARGS); +Datum +spgist_stat(PG_FUNCTION_ARGS) +{ +#if PG_VERSION_NUM < 90200 + elog(NOTICE, "Function is not working under PgSQL < 9.2"); + + PG_RETURN_TEXT_P(CStringGetTextDatum("???")); +#else + text *name = PG_GETARG_TEXT_P(0); + RangeVar *relvar; + Relation index; + BlockNumber blkno; + BlockNumber totalPages = 0, + innerPages = 0, + leafPages = 0, + emptyPages = 0, + deletedPages = 0; + double usedSpace = 0.0; + char res[1024]; + int bufferSize = -1; + int64 innerTuples = 0, + leafTuples = 0, + nAllTheSame = 0, + nLeafPlaceholder = 0, + nInnerPlaceholder = 0, + nLeafRedirect = 0, + nInnerRedirect = 0; + +#define IS_INDEX(r) ((r)->rd_rel->relkind == RELKIND_INDEX) +#define IS_SPGIST(r) ((r)->rd_rel->relam == SPGIST_AM_OID) + + relvar = makeRangeVarFromNameList(textToQualifiedNameList(name)); + index = relation_openrv(relvar, AccessExclusiveLock); + + if (!IS_INDEX(index) || !IS_SPGIST(index)) + elog(ERROR, "relation \"%s\" is not an SPGiST index", + RelationGetRelationName(index)); + + totalPages = RelationGetNumberOfBlocks(index); + + for (blkno = SPGIST_HEAD_BLKNO; blkno < totalPages; blkno++) + { + Buffer buffer; + Page page; + int pageFree; + + buffer = ReadBuffer(index, blkno); + LockBuffer(buffer, BUFFER_LOCK_SHARE); + + page = BufferGetPage(buffer); + + if (PageIsNew(page) || SpGistPageIsDeleted(page)) + { + deletedPages++; + UnlockReleaseBuffer(buffer); + continue; + } + + if (SpGistPageIsLeaf(page)) + { + leafPages++; + leafTuples += PageGetMaxOffsetNumber(page); + nLeafPlaceholder += SpGistPageGetOpaque(page)->nPlaceholder; + nLeafRedirect += SpGistPageGetOpaque(page)->nRedirection; + } + else + { + int i, + max; + + innerPages++; + max = PageGetMaxOffsetNumber(page); + innerTuples += max; + nInnerPlaceholder += SpGistPageGetOpaque(page)->nPlaceholder; + nInnerRedirect += SpGistPageGetOpaque(page)->nRedirection; + for (i = FirstOffsetNumber; i <= max; i++) + { + SpGistInnerTuple it; + + it = (SpGistInnerTuple) PageGetItem(page, + PageGetItemId(page, i)); + if (it->allTheSame) + nAllTheSame++; + } + } + + if (bufferSize < 0) + bufferSize = BufferGetPageSize(buffer) + - MAXALIGN(sizeof(SpGistPageOpaqueData)) + - SizeOfPageHeaderData; + + pageFree = PageGetExactFreeSpace(page); + + usedSpace += bufferSize - pageFree; + + if (pageFree == bufferSize) + emptyPages++; + + UnlockReleaseBuffer(buffer); + } + + index_close(index, AccessExclusiveLock); + + totalPages--; /* discount metapage */ + + snprintf(res, sizeof(res), + "totalPages: %u\n" + "deletedPages: %u\n" + "innerPages: %u\n" + "leafPages: %u\n" + "emptyPages: %u\n" + "usedSpace: %.2f kbytes\n" + "freeSpace: %.2f kbytes\n" + "fillRatio: %.2f%%\n" + "leafTuples: " INT64_FORMAT "\n" + "innerTuples: " INT64_FORMAT "\n" + "innerAllTheSame: " INT64_FORMAT "\n" + "leafPlaceholders: " INT64_FORMAT "\n" + "innerPlaceholders: " INT64_FORMAT "\n" + "leafRedirects: " INT64_FORMAT "\n" + "innerRedirects: " INT64_FORMAT, + totalPages, deletedPages, innerPages, leafPages, emptyPages, + usedSpace / 1024.0, + (((double) bufferSize) * ((double) totalPages) - usedSpace) / 1024, + 100.0 * (usedSpace / (((double) bufferSize) * ((double) totalPages))), + leafTuples, innerTuples, nAllTheSame, + nLeafPlaceholder, nInnerPlaceholder, + nLeafRedirect, nInnerRedirect); + + PG_RETURN_TEXT_P(CStringGetTextDatum(res)); +#endif +} + diff --git a/gevel.sql.in b/gevel.sql.in index 48691f8..dc7784f 100644 --- a/gevel.sql.in +++ b/gevel.sql.in @@ -43,5 +43,11 @@ create or replace function gin_count_estimate(text, tsquery) language C with (isstrict); +create or replace function spgist_stat(text) + returns text + as 'MODULE_PATHNAME' + language C + with (isstrict); + END; diff --git a/sql/gevel.sql b/sql/gevel.sql index 50bf184..734bba3 100644 --- a/sql/gevel.sql +++ b/sql/gevel.sql @@ -8,13 +8,20 @@ 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 ); --GiST SELECT gist_stat('gist_idx'); SELECT gist_tree('gist_idx'); SELECT * FROM gist_print('gist_idx') as t(level int, valid bool, a box) where level=1; +--SPGiST +SELECT spgist_stat('spgist_idx'); + +--GIN CREATE TABLE test__int( a int[] ); \copy test__int from 'data/test__int.data' -- 2.37.3