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,
+ node_n int,
+ level int,
+ tid_pointer tid,
+ prefix point,
+ node_label int,
+ leaf_value point
+ ) where level = 1;
+ tid | node_n | level | tid_pointer | prefix | node_label | leaf_value
+-------+--------+-------+-------------+-------------------------------------+------------+------------
+ (1,1) | 0 | 1 | (5,4) | (24530.2070484581,23595.7092511013) | |
+ (1,1) | 1 | 1 | (5,3) | (24530.2070484581,23595.7092511013) | |
+ (1,1) | 2 | 1 | (5,2) | (24530.2070484581,23595.7092511013) | |
+ (1,1) | 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
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
innerRedirects: 0
(1 row)
+SELECT * FROM spgist_print('kdspgist_idx') as t(tid tid, node_n int, level int, tid_pointer tid, prefix float8, node_label int, leaf_value point);
+ tid | node_n | level | tid_pointer | prefix | node_label | leaf_value
+----------+--------+-------+-------------+---------+------------+-------------------
+ (1,1) | 0 | 1 | (5,1) | 23030 | |
+ (1,1) | 1 | 1 | (5,2) | 23030 | |
+ (5,2) | 0 | 2 | (5,6) | 21664 | |
+ (5,2) | 1 | 2 | (5,3) | 21664 | |
+ (5,3) | 0 | 3 | (5,5) | 37159.5 | |
+ (5,3) | 1 | 3 | (5,12) | 37159.5 | |
+ (5,12) | 0 | 4 | (5,13) | 36357.5 | |
+ (5,12) | 1 | 4 | (14,226) | 36357.5 | |
+ (14,226) | | 5 | | | | (43240,47256.5)
+ (5,13) | 0 | 5 | (2,112) | 43507 | |
+ (5,13) | 1 | 5 | (5,25) | 43507 | |
+ (5,25) | 0 | 6 | (22,116) | 28862 | |
+ (5,25) | 1 | 6 | (22,115) | 28862 | |
+ (22,115) | | 7 | | | | (30902,44532)
+ (22,116) | | 7 | | | | (23048,47416)
+ (2,112) | | 6 | | | | (35218.5,42124)
+ (5,5) | 0 | 4 | (5,11) | 33416 | |
+ (5,5) | 1 | 4 | (5,22) | 33416 | |
+ (5,22) | 0 | 5 | (20,225) | 30991 | |
+ (5,22) | 1 | 5 | (5,23) | 30991 | |
+ (5,23) | 0 | 6 | (9,112) | 41820 | |
+ (5,23) | 1 | 6 | (9,114) | 41820 | |
+ (9,114) | | 7 | | | | (44732,32182)
+ (9,112) | | 7 | | | | (35580,33526.5)
+ (20,225) | | 6 | | | | (47724.5,27185.5)
+ (5,11) | 0 | 5 | (13,101) | 29986.5 | |
+ (5,11) | 1 | 5 | (13,100) | 29986.5 | |
+ (13,100) | | 6 | | | | (24069,30850.5)
+ (13,101) | | 6 | | | | (29539,25566)
+ (5,6) | 0 | 3 | (5,7) | 36774 | |
+ (5,6) | 1 | 3 | (5,19) | 36774 | |
+ (5,19) | 0 | 4 | (5,20) | 10075.5 | |
+ (5,19) | 1 | 4 | (18,225) | 10075.5 | |
+ (18,225) | | 5 | | | | (20920.5,49105.5)
+ (5,20) | 0 | 5 | (10,110) | 44171.5 | |
+ (5,20) | 1 | 5 | (10,113) | 44171.5 | |
+ (10,113) | | 6 | | | | (93,46797)
+ (10,110) | | 6 | | | | (28.5,38640.5)
+ (5,7) | 0 | 4 | (7,113) | 9517 | |
+ (5,7) | 1 | 4 | (5,21) | 9517 | |
+ (5,21) | 0 | 5 | (19,115) | 28907.5 | |
+ (5,21) | 1 | 5 | (19,112) | 28907.5 | |
+ (19,112) | | 6 | | | | (11916.5,31668)
+ (19,115) | | 6 | | | | (20622.5,27462.5)
+ (7,113) | | 5 | | | | (9296,35157)
+ (5,1) | 0 | 2 | (5,8) | 26938 | |
+ (5,1) | 1 | 2 | (5,4) | 26938 | |
+ (5,4) | 0 | 3 | (5,10) | 9532 | |
+ (5,4) | 1 | 3 | (5,14) | 9532 | |
+ (5,14) | 0 | 4 | (5,15) | 38603 | |
+ (5,14) | 1 | 4 | (15,222) | 38603 | |
+ (15,222) | | 5 | | | | (41926.5,17934.5)
+ (5,15) | 0 | 5 | (8,113) | 16345 | |
+ (5,15) | 1 | 5 | (8,112) | 16345 | |
+ (8,112) | | 6 | | | | (32425,20702.5)
+ (8,113) | | 6 | | | | (29134,15559.5)
+ (5,10) | 0 | 4 | (12,94) | 38800.5 | |
+ (5,10) | 1 | 4 | (5,24) | 38800.5 | |
+ (5,24) | 0 | 5 | (21,108) | 4752 | |
+ (5,24) | 1 | 5 | (21,107) | 4752 | |
+ (21,107) | | 6 | | | | (49822.5,7097.5)
+ (21,108) | | 6 | | | | (40315.5,1689.5)
+ (12,94) | | 5 | | | | (30295.5,5090)
+ (5,8) | 0 | 3 | (5,17) | 11733.5 | |
+ (5,8) | 1 | 3 | (5,9) | 11733.5 | |
+ (5,9) | 0 | 4 | (6,114) | 11993 | |
+ (5,9) | 1 | 4 | (5,16) | 11993 | |
+ (5,16) | 0 | 5 | (16,123) | 17591 | |
+ (5,16) | 1 | 5 | (16,127) | 17591 | |
+ (16,127) | | 6 | | | | (18352.5,19366)
+ (16,123) | | 6 | | | | (24795,14921)
+ (6,114) | | 5 | | | | (6706,16676)
+ (5,17) | 0 | 4 | (5,18) | 13329.5 | |
+ (5,17) | 1 | 4 | (17,226) | 13329.5 | |
+ (17,226) | | 5 | | | | (23690,10214.5)
+ (5,18) | 0 | 5 | (11,113) | 6375 | |
+ (5,18) | 1 | 5 | (11,109) | 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, node_n int, level int, tid_pointer tid, prefix point, node_label int, leaf_value point) WHERE level = 1;
+ tid | node_n | level | tid_pointer | prefix | node_label | leaf_value
+-------+--------+-------+-------------+-------------------------------------+------------+------------
+ (1,1) | 0 | 1 | (5,4) | (24530.2070484581,23595.7092511013) | |
+ (1,1) | 1 | 1 | (5,3) | (24530.2070484581,23595.7092511013) | |
+ (1,1) | 2 | 1 | (5,2) | (24530.2070484581,23595.7092511013) | |
+ (1,1) | 3 | 1 | (5,1) | (24530.2070484581,23595.7092511013) | |
+(4 rows)
+
--GIN
CREATE TABLE test__int( a int[] );
\copy test__int from 'data/test__int.data'
#if PG_VERSION_NUM >= 90200
#include "access/spgist_private.h"
#include "access/spgist.h"
+#include "utils/datum.h"
#endif
#include "access/heapam.h"
#include "catalog/index.h"
#endif
}
+#if PG_VERSION_NUM >= 90200
+
+typedef struct SPGistPrintStackElem {
+ ItemPointerData iptr;
+ int16 nlabel;
+ int level;
+} SPGistPrintStackElem;
+
+typedef struct SPGistPrint {
+ SpGistState state;
+ Relation index;
+ Datum dvalues[7 /* see CreateTemplateTupleDesc call */];
+ char nulls[7 /* see CreateTemplateTupleDesc call */];
+ List *stack;
+} SPGistPrint;
+
+static void
+pushSPGistPrint(FuncCallContext *funcctx, SPGistPrint *prst, ItemPointer ip, int level) {
+ MemoryContext oldcontext;
+ SPGistPrintStackElem *e;
+
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ e = palloc(sizeof(*e));
+ e->iptr = *ip;
+ e->nlabel = 0;
+ e->level = level;
+ prst->stack = lcons(e, prst->stack);
+
+ MemoryContextSwitchTo(oldcontext);
+}
+
+static void
+close_spgist_print(SPGistPrint *prst) {
+ index_close(prst->index, AccessExclusiveLock);
+}
+#endif
+
+PG_FUNCTION_INFO_V1(spgist_print);
+Datum spgist_print(PG_FUNCTION_ARGS);
+Datum
+spgist_print(PG_FUNCTION_ARGS)
+{
+#if PG_VERSION_NUM < 90200
+ elog(NOTICE, "Function is not working under PgSQL < 9.2");
+
+ PG_RETURN_TEXT_P(CStringGetTextDatum("???"));
+#else
+ FuncCallContext *funcctx;
+ SPGistPrint *prst;
+ SPGistPrintStackElem *s;
+ HeapTuple htuple;
+ Datum result;
+ MemoryContext oldcontext;
+
+ if (SRF_IS_FIRSTCALL()) {
+ text *name=PG_GETARG_TEXT_P(0);
+ RangeVar *relvar;
+ Relation index;
+ ItemPointerData ipd;
+ TupleDesc tupdesc;
+
+ funcctx = SRF_FIRSTCALL_INIT();
+ 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));
+
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ prst = palloc(sizeof(*prst));
+
+ prst->index = index;
+ initSpGistState(&prst->state, index);
+
+ tupdesc = CreateTemplateTupleDesc(3 /* types */ + 1 /* level */ + 1 /* nlabel */ + 2 /* tids */, false);
+ TupleDescInitEntry(tupdesc, 1, "tid", TIDOID, -1, 0);
+ TupleDescInitEntry(tupdesc, 2, "node", INT4OID, -1, 0);
+ TupleDescInitEntry(tupdesc, 3, "level", INT4OID, -1, 0);
+ TupleDescInitEntry(tupdesc, 4, "tid_pointer", TIDOID, -1, 0);
+ TupleDescInitEntry(tupdesc, 5, "prefix",
+ (prst->state.attPrefixType.type == VOIDOID) ? INT4OID : prst->state.attPrefixType.type, -1, 0);
+ TupleDescInitEntry(tupdesc, 6, "label",
+ (prst->state.attLabelType.type == VOIDOID) ? INT4OID : prst->state.attLabelType.type, -1, 0);
+ TupleDescInitEntry(tupdesc, 7, "leaf",
+ (prst->state.attType.type == VOIDOID) ? INT4OID : prst->state.attType.type, -1, 0);
+
+ funcctx->slot = TupleDescGetSlot(tupdesc);
+ funcctx->attinmeta = TupleDescGetAttInMetadata(tupdesc);
+
+ funcctx->user_fctx = (void*)prst;
+
+ MemoryContextSwitchTo(oldcontext);
+
+ ItemPointerSet(&ipd, SPGIST_HEAD_BLKNO, FirstOffsetNumber);
+ prst->stack = NIL;
+ pushSPGistPrint(funcctx, prst, &ipd, 1);
+
+ PG_FREE_IF_COPY(name,0);
+ }
+
+ funcctx = SRF_PERCALL_SETUP();
+ prst = (SPGistPrint*)(funcctx->user_fctx);
+
+next:
+ for(;;) {
+ if ( prst->stack == NIL ) {
+ close_spgist_print(prst);
+ SRF_RETURN_DONE(funcctx);
+ }
+
+ CHECK_FOR_INTERRUPTS();
+
+ s = (SPGistPrintStackElem*)linitial(prst->stack);
+ prst->stack = list_delete_first(prst->stack);
+
+ if (ItemPointerIsValid(&s->iptr))
+ break;
+ free(s);
+ }
+
+ do {
+ Buffer buffer;
+ Page page;
+ SpGistDeadTuple dtuple;
+ ItemPointer tid;
+
+ buffer = ReadBuffer(prst->index, ItemPointerGetBlockNumber(&s->iptr));
+ LockBuffer(buffer, BUFFER_LOCK_SHARE);
+
+ page = BufferGetPage(buffer);
+ if (ItemPointerGetOffsetNumber(&s->iptr) > PageGetMaxOffsetNumber(page)) {
+ UnlockReleaseBuffer(buffer);
+ pfree(s);
+ goto next;
+ }
+
+ dtuple = (SpGistDeadTuple)PageGetItem(page, PageGetItemId(page, ItemPointerGetOffsetNumber(&s->iptr)));
+
+ if (dtuple->tupstate != SPGIST_LIVE) {
+ UnlockReleaseBuffer(buffer);
+ pfree(s);
+ goto next;
+ }
+
+ if (SpGistPageIsLeaf(page)) {
+ SpGistLeafTuple leafTuple = (SpGistLeafTuple)dtuple;
+
+ tid = palloc(sizeof(ItemPointerData));
+ *tid = s->iptr;
+ prst->dvalues[0] = PointerGetDatum(tid);
+ prst->nulls[0] = ' ';
+ prst->nulls[1] = 'n';
+ prst->dvalues[2] = s->level;
+ prst->nulls[2] = ' ';
+ prst->nulls[3] = 'n';
+ prst->nulls[4] = 'n';
+ prst->nulls[5] = 'n';
+ prst->dvalues[6] = datumCopy(SGLTDATUM(leafTuple, &prst->state),
+ prst->state.attType.attbyval, prst->state.attType.attlen);
+ prst->nulls[6] = ' ';
+ } else {
+ SpGistInnerTuple innerTuple = (SpGistInnerTuple)dtuple;
+ int i;
+ SpGistNodeTuple node;
+
+ SGITITERATE(innerTuple, i, node) {
+ if (ItemPointerIsValid(&node->t_tid)) {
+ if (i >= s->nlabel)
+ break;
+ }
+ }
+
+ if (i >= innerTuple->nNodes) {
+ UnlockReleaseBuffer(buffer);
+ pfree(s);
+ goto next;
+ }
+
+ tid = palloc(sizeof(ItemPointerData));
+ *tid = s->iptr;
+ prst->dvalues[0] = PointerGetDatum(tid);
+ prst->nulls[0] = ' ';
+ prst->dvalues[1] = Int32GetDatum(s->nlabel);
+ prst->nulls[1] = ' ';
+ prst->dvalues[2] = s->level;
+ prst->nulls[2] = ' ';
+ tid = palloc(sizeof(ItemPointerData));
+ *tid = node->t_tid;
+ prst->dvalues[3] = PointerGetDatum(tid);
+ prst->nulls[3] = ' ';
+ if (prst->state.attPrefixType.attbyval != VOIDOID && innerTuple->prefixSize > 0) {
+ prst->dvalues[4] = datumCopy(SGITDATUM(innerTuple, &prst->state),
+ prst->state.attPrefixType.attbyval, prst->state.attPrefixType.attlen);
+ prst->nulls[4] = ' ';
+ } else
+ prst->nulls[4] = 'n';
+ if (prst->state.attLabelType.attbyval != VOIDOID && !IndexTupleHasNulls(node)) {
+ prst->dvalues[5] = datumCopy(SGNTDATUM(node, &prst->state),
+ prst->state.attLabelType.attbyval, prst->state.attLabelType.attlen);
+ prst->nulls[5] = ' ';
+ } else
+ prst->nulls[5] = 'n';
+ prst->nulls[6] = 'n';
+
+ pushSPGistPrint(funcctx, prst, &node->t_tid, s->level + 1);
+ s->nlabel = i + 1;
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+ prst->stack = lcons(s, prst->stack);
+ MemoryContextSwitchTo(oldcontext);
+ s = NULL;
+ }
+
+ UnlockReleaseBuffer(buffer);
+ if (s) pfree(s);
+ } while(0);
+
+ htuple = heap_formtuple(funcctx->attinmeta->tupdesc, prst->dvalues, prst->nulls);
+ result = TupleGetDatum(funcctx->slot, htuple);
+
+ SRF_RETURN_NEXT(funcctx, result);
+#endif
+}
+
language C
with (isstrict);
+create or replace function spgist_print(text)
+ returns setof record
+ as 'MODULE_PATHNAME'
+ language C
+ with (isstrict);
+
END;
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');
--SPGiST
SELECT spgist_stat('spgist_idx');
+SELECT * FROM spgist_print('kdspgist_idx') as t(tid tid, 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, node_n int, level int, tid_pointer tid, prefix point, node_label int, leaf_value point) WHERE level = 1;
--GIN
CREATE TABLE test__int( a int[] );
projects / gevel.git / commitdiff