Index-only scans for multicolumn GIST
Started by Anastasia Lubennikovaover 11 years ago5 messages
Hi, hackers!
There are new results of my work on GSoC project "Index-only scans for
GIST".
Previous post is here:
http://postgresql.1045698.n5.nabble.com/Index-only-scans-for-GIST-td5804892.html
Repository is
https://github.com/lubennikovaav/postgres/tree/indexonlygist2
Patch is in attachments.
It includes indexonlyscan for multicolumn GiST. It works correctly -
results are the same with another scan plans.
Fetch() method is realized for box and circle opclasses
Improvement for circle opclass is not such distinct as for box opclass,
because of recheck.
I remember that all "elog" and other bad comments must be fixed before this
patch can be committed.
Any comments are welcome
--
Best regards,
Lubennikova Anastasia
Attachments:
indexonlygist_2.1.patchapplication/octet-stream; name=indexonlygist_2.1.patchDownload
*** a/src/backend/access/gist/gist.c
--- b/src/backend/access/gist/gist.c
***************
*** 1379,1384 **** initGISTstate(Relation index)
--- 1379,1395 ----
else
giststate->distanceFn[i].fn_oid = InvalidOid;
+ /* opclasses are not required to provide a Fetch method */
+ //elog(NOTICE, "Debug. gist.c amsupport = %d", index->rd_am->amsupport);
+ if (OidIsValid(index_getprocid(index, i + 1, GIST_FETCH_PROC))) {
+ //elog(NOTICE, "Debug. gist.c OidIsValid. i=%d fetch. rd_support(%d) = %d", i, GIST_FETCH_PROC, //index_getprocid(index, i + 1, GIST_FETCH_PROC));
+ fmgr_info_copy(&(giststate->fetchFn[i]),
+ index_getprocinfo(index, i + 1, GIST_FETCH_PROC),
+ scanCxt);
+ }
+ else
+ giststate->fetchFn[i].fn_oid = InvalidOid;
+
/*
* If the index column has a specified collation, we should honor that
* while doing comparisons. However, we may have a collatable storage
***************
*** 1401,1406 **** initGISTstate(Relation index)
--- 1412,1433 ----
return giststate;
}
+ Datum
+ gistcanreturn(PG_FUNCTION_ARGS) {
+ Relation index = (Relation) PG_GETARG_POINTER(0);
+ int i = PG_GETARG_INT32(1);
+
+ if (OidIsValid(index_getprocid(index, i+1, GIST_FETCH_PROC))) {
+ PG_RETURN_BOOL(true);
+ }
+ else {
+ //elog(NOTICE, " Debug. missing support function %d of index \"%s\"",
+ // GIST_FETCH_PROC,
+ // RelationGetRelationName(index));
+ PG_RETURN_BOOL(false);
+ }
+ }
+
void
freeGISTstate(GISTSTATE *giststate)
{
*** a/src/backend/access/gist/gistget.c
--- b/src/backend/access/gist/gistget.c
***************
*** 240,245 **** gistScanPage(IndexScanDesc scan, GISTSearchItem *pageItem, double *myDistances,
--- 240,249 ----
GISTScanOpaque so = (GISTScanOpaque) scan->opaque;
Buffer buffer;
Page page;
+ GISTSTATE *giststate = so->giststate;
+ Relation r = scan->indexRelation;
+ bool isnull[INDEX_MAX_KEYS];
+ GISTSearchHeapItem *tmpListItem;
GISTPageOpaque opaque;
OffsetNumber maxoff;
OffsetNumber i;
***************
*** 288,297 **** gistScanPage(IndexScanDesc scan, GISTSearchItem *pageItem, double *myDistances,
(void) rb_insert(so->queue, (RBNode *) tmpItem, &isNew);
MemoryContextSwitchTo(oldcxt);
}
! so->nPageData = so->curPageData = 0;
/*
* check all tuples on page
--- 292,303 ----
(void) rb_insert(so->queue, (RBNode *) tmpItem, &isNew);
+ /* Create new GISTSearchHeapItem to insert into pageData*/
+
MemoryContextSwitchTo(oldcxt);
}
! so->curPageData = NULL;
/*
* check all tuples on page
***************
*** 329,340 **** gistScanPage(IndexScanDesc scan, GISTSearchItem *pageItem, double *myDistances,
}
else if (scan->numberOfOrderBys == 0 && GistPageIsLeaf(page))
{
! /*
! * Non-ordered scan, so report heap tuples in so->pageData[]
! */
! so->pageData[so->nPageData].heapPtr = it->t_tid;
! so->pageData[so->nPageData].recheck = recheck;
! so->nPageData++;
}
else
{
--- 335,356 ----
}
else if (scan->numberOfOrderBys == 0 && GistPageIsLeaf(page))
{
! /* Non-oredered scan, so tuples report in so->pageData */
! oldcxt = MemoryContextSwitchTo(so->queueCxt);
! /* form tmpListItem and fill it with data to add into so->pageData */
! tmpListItem = palloc(sizeof(GISTSearchHeapItem));
! tmpListItem->heapPtr = it->t_tid;
! tmpListItem->recheck = recheck;
! if (scan->xs_want_itup)
! tmpListItem->ftup = gistFetchTuple(giststate, r, it, isnull);
!
! so->pageData = lappend(so->pageData, tmpListItem);
!
! /* If it's first call of lappend() we should set so->curPageData not NULL*/
! if(so->curPageData == NULL)
! so->curPageData = list_head(so->pageData);
!
! MemoryContextSwitchTo(oldcxt);
}
else
{
***************
*** 357,362 **** gistScanPage(IndexScanDesc scan, GISTSearchItem *pageItem, double *myDistances,
--- 373,380 ----
item->blkno = InvalidBlockNumber;
item->data.heap.heapPtr = it->t_tid;
item->data.heap.recheck = recheck;
+ if (scan->xs_want_itup)
+ item->data.heap.ftup = gistFetchTuple(giststate, r, it, isnull);
}
else
{
***************
*** 451,456 **** getNextNearest(IndexScanDesc scan)
--- 469,476 ----
/* found a heap item at currently minimal distance */
scan->xs_ctup.t_self = item->data.heap.heapPtr;
scan->xs_recheck = item->data.heap.recheck;
+ if(scan->xs_want_itup)
+ scan->xs_itup = item->data.heap.ftup;
res = true;
}
else
***************
*** 492,498 **** gistgettuple(PG_FUNCTION_ARGS)
so->firstCall = false;
so->curTreeItem = NULL;
! so->curPageData = so->nPageData = 0;
fakeItem.blkno = GIST_ROOT_BLKNO;
memset(&fakeItem.data.parentlsn, 0, sizeof(GistNSN));
--- 512,519 ----
so->firstCall = false;
so->curTreeItem = NULL;
!
! so->curPageData = NULL;
fakeItem.blkno = GIST_ROOT_BLKNO;
memset(&fakeItem.data.parentlsn, 0, sizeof(GistNSN));
***************
*** 509,521 **** gistgettuple(PG_FUNCTION_ARGS)
/* Fetch tuples index-page-at-a-time */
for (;;)
{
! if (so->curPageData < so->nPageData)
! {
! /* continuing to return tuples from a leaf page */
! scan->xs_ctup.t_self = so->pageData[so->curPageData].heapPtr;
! scan->xs_recheck = so->pageData[so->curPageData].recheck;
! so->curPageData++;
! PG_RETURN_BOOL(true);
}
/* find and process the next index page */
--- 530,550 ----
/* Fetch tuples index-page-at-a-time */
for (;;)
{
! if(so->curPageData!=NULL) {
! GISTSearchHeapItem *tmp = (GISTSearchHeapItem *)lfirst(so->curPageData);
! scan->xs_ctup.t_self = tmp->heapPtr;
! scan->xs_recheck = tmp->recheck;
! if(scan->xs_want_itup)
! scan->xs_itup = tmp->ftup;
!
! ListCell *tmpPageData = so->curPageData;
! /* go to next ListCell */
! so->curPageData = lnext(so->curPageData);
! /* Delete ListCell that we have already read.
! * It's always head of so->pageData
! */
! so->pageData = list_delete_cell(so->pageData, tmpPageData, NULL);
! PG_RETURN_BOOL(TRUE);
}
/* find and process the next index page */
***************
*** 537,543 **** gistgettuple(PG_FUNCTION_ARGS)
gistScanPage(scan, item, so->curTreeItem->distances, NULL, NULL);
pfree(item);
! } while (so->nPageData == 0);
}
}
}
--- 566,572 ----
gistScanPage(scan, item, so->curTreeItem->distances, NULL, NULL);
pfree(item);
! } while (list_length(so->pageData)==0);
}
}
}
***************
*** 561,567 **** gistgetbitmap(PG_FUNCTION_ARGS)
/* Begin the scan by processing the root page */
so->curTreeItem = NULL;
! so->curPageData = so->nPageData = 0;
fakeItem.blkno = GIST_ROOT_BLKNO;
memset(&fakeItem.data.parentlsn, 0, sizeof(GistNSN));
--- 590,597 ----
/* Begin the scan by processing the root page */
so->curTreeItem = NULL;
! so->curPageData = NULL;
! //so->curPageData = so->nPageData = 0;
fakeItem.blkno = GIST_ROOT_BLKNO;
memset(&fakeItem.data.parentlsn, 0, sizeof(GistNSN));
*** a/src/backend/access/gist/gistproc.c
--- b/src/backend/access/gist/gistproc.c
***************
*** 152,157 **** gist_box_decompress(PG_FUNCTION_ARGS)
--- 152,168 ----
}
/*
+ * GiST Fetch method for boxes
+ * do not do anything --- we just use the stored box as is.
+ */
+ Datum
+ gist_box_fetch(PG_FUNCTION_ARGS)
+ {
+ //elog(NOTICE, "Debug. gistproc.c we are in gist_box_fetch");
+ PG_RETURN_POINTER(PG_GETARG_POINTER(0));
+ }
+
+ /*
* The GiST Penalty method for boxes (also used for points)
*
* As in the R-tree paper, we use change in area as our penalty metric
***************
*** 1142,1147 **** gist_circle_compress(PG_FUNCTION_ARGS)
--- 1153,1192 ----
}
/*
+ * GiST Fetch method for circle
+ * get circle coordinates from it's bounding box coordinates
+ */
+ Datum
+ gist_circle_fetch(PG_FUNCTION_ARGS)
+ {
+ GISTENTRY *entry = (GISTENTRY *) PG_GETARG_POINTER(0);
+ GISTENTRY *retval;
+ retval = palloc(sizeof(GISTENTRY));
+ if (DatumGetCircleP(entry->key) != NULL)
+ {
+ BOX *in = DatumGetBoxP(entry->key);
+ CIRCLE *r;
+
+ r = (CIRCLE *) palloc(sizeof(CIRCLE));
+ r->radius = (in->high.x - in->low.x)/2;
+ r->center.x = (in->high.x + in->low.x)/2;
+ r->center.y = (in->high.y + in->low.y)/2;
+ gistentryinit(*retval, PointerGetDatum(r),
+ entry->rel, entry->page,
+ entry->offset, FALSE);
+
+ }
+ else
+ {
+ gistentryinit(*retval, (Datum) 0,
+ entry->rel, entry->page,
+ entry->offset, FALSE);
+ }
+ PG_RETURN_POINTER(retval);
+ }
+
+
+ /*
* The GiST Consistent method for circles
*/
Datum
*** a/src/backend/access/gist/gistscan.c
--- b/src/backend/access/gist/gistscan.c
***************
*** 133,138 **** gistbeginscan(PG_FUNCTION_ARGS)
--- 133,146 ----
scan->opaque = so;
+ /* All fields required for index-only scans are null until gistrescan.
+ * However, we set up scan->xs_itupdesc whether we'll need it or not,
+ * since that's cheap.
+ */
+ so->pageData = NULL;
+ so->curPageData = NULL;
+ scan->xs_itupdesc = RelationGetDescr(r);
+
MemoryContextSwitchTo(oldCxt);
PG_RETURN_POINTER(scan);
***************
*** 194,204 **** gistrescan(PG_FUNCTION_ARGS)
GISTSearchTreeItemAllocator,
GISTSearchTreeItemDeleter,
scan);
MemoryContextSwitchTo(oldCxt);
- so->curTreeItem = NULL;
so->firstCall = true;
!
/* Update scan key, if a new one is given */
if (key && scan->numberOfKeys > 0)
{
--- 202,212 ----
GISTSearchTreeItemAllocator,
GISTSearchTreeItemDeleter,
scan);
+
MemoryContextSwitchTo(oldCxt);
so->firstCall = true;
!
/* Update scan key, if a new one is given */
if (key && scan->numberOfKeys > 0)
{
*** a/src/backend/access/gist/gistutil.c
--- b/src/backend/access/gist/gistutil.c
***************
*** 618,623 **** gistFormTuple(GISTSTATE *giststate, Relation r,
--- 618,682 ----
return res;
}
+
+ /*
+ * initialize a GiST entry with fetched value in key field
+ */
+ void
+ gistfentryinit(GISTSTATE *giststate, int nkey,
+ GISTENTRY *e, Datum k, Relation r,
+ Page pg, OffsetNumber o, bool l, bool isNull)
+ {
+ if (!isNull)
+ {
+ GISTENTRY *fep;
+
+ gistentryinit(*e, k, r, pg, o, l);
+ fep = (GISTENTRY *)
+ DatumGetPointer(FunctionCall1Coll(&giststate->fetchFn[nkey],
+ giststate->supportCollation[nkey],
+ PointerGetDatum(e)));
+ //elog(NOTICE, "Debug. gistfentryinit()");
+ /* fecthFn returns the given pointer */
+ if (fep != e)
+ gistentryinit(*e, fep->key, fep->rel, fep->page, fep->offset,
+ fep->leafkey);
+ }
+ else
+ gistentryinit(*e, (Datum) 0, r, pg, o, l);
+ }
+
+ /*
+ * Fetch all keys in tuple.
+ * returns new IndexTuple that contains GISTENTRY with fetched data in key field
+ */
+ IndexTuple
+ gistFetchTuple(GISTSTATE *giststate, Relation r, IndexTuple tuple, bool isnull[])
+ {
+ GISTENTRY fentry[INDEX_MAX_KEYS];
+ Datum fetchatt[INDEX_MAX_KEYS];
+ int i;
+ IndexTuple res;
+
+ for (i = 0; i < r->rd_att->natts; i++) {
+
+ Datum datum = index_getattr(tuple, i + 1, giststate->tupdesc, &isnull[i]);
+
+ gistfentryinit(giststate, i, &fentry[i],
+ datum, r, NULL, (OffsetNumber) 0,
+ FALSE, FALSE);
+ fetchatt[i] = fentry[i].key;
+ }
+ res = index_form_tuple(giststate->tupdesc, fetchatt, isnull);
+
+ /*
+ * The offset number on tuples on internal pages is unused. For historical
+ * reasons, it is set 0xffff.
+ */
+ ItemPointerSetOffsetNumber(&(res->t_tid), 0xffff);
+ return res;
+ }
+
float
gistpenalty(GISTSTATE *giststate, int attno,
GISTENTRY *orig, bool isNullOrig,
*** a/src/backend/access/index/indexam.c
--- b/src/backend/access/index/indexam.c
***************
*** 722,732 **** index_vacuum_cleanup(IndexVacuumInfo *info,
}
/* ----------------
! * index_can_return - does index support index-only scans?
* ----------------
*/
bool
! index_can_return(Relation indexRelation)
{
FmgrInfo *procedure;
--- 722,732 ----
}
/* ----------------
! * index_can_return - does index column support index-only scans?
* ----------------
*/
bool
! index_can_return(Relation indexRelation, int attno)
{
FmgrInfo *procedure;
***************
*** 738,745 **** index_can_return(Relation indexRelation)
GET_REL_PROCEDURE(amcanreturn);
! return DatumGetBool(FunctionCall1(procedure,
! PointerGetDatum(indexRelation)));
}
/* ----------------
--- 738,746 ----
GET_REL_PROCEDURE(amcanreturn);
! return DatumGetBool(FunctionCall2(procedure,
! PointerGetDatum(indexRelation),
! Int32GetDatum(attno)));
}
/* ----------------
*** a/src/backend/optimizer/path/indxpath.c
--- b/src/backend/optimizer/path/indxpath.c
***************
*** 1746,1759 **** check_index_only(RelOptInfo *rel, IndexOptInfo *index)
bool result;
Bitmapset *attrs_used = NULL;
Bitmapset *index_attrs = NULL;
ListCell *lc;
int i;
/* Index-only scans must be enabled, and index must be capable of them */
if (!enable_indexonlyscan)
return false;
- if (!index->canreturn)
- return false;
/*
* Check that all needed attributes of the relation are available from the
--- 1746,1758 ----
bool result;
Bitmapset *attrs_used = NULL;
Bitmapset *index_attrs = NULL;
+ Bitmapset *index_only_attrs = NULL;
ListCell *lc;
int i;
/* Index-only scans must be enabled, and index must be capable of them */
if (!enable_indexonlyscan)
return false;
/*
* Check that all needed attributes of the relation are available from the
***************
*** 1798,1810 **** check_index_only(RelOptInfo *rel, IndexOptInfo *index)
index_attrs =
bms_add_member(index_attrs,
attno - FirstLowInvalidHeapAttributeNumber);
}
/* Do we have all the necessary attributes? */
! result = bms_is_subset(attrs_used, index_attrs);
bms_free(attrs_used);
bms_free(index_attrs);
return result;
}
--- 1797,1815 ----
index_attrs =
bms_add_member(index_attrs,
attno - FirstLowInvalidHeapAttributeNumber);
+
+ if (index->canreturn[i])
+ index_only_attrs = bms_add_member(index_only_attrs,
+ attno - FirstLowInvalidHeapAttributeNumber);
}
/* Do we have all the necessary attributes? */
! result = ((bms_is_subset(attrs_used, index_attrs))&&
! (bms_is_subset(attrs_used, index_only_attrs)));
bms_free(attrs_used);
bms_free(index_attrs);
+ bms_free(index_only_attrs);
return result;
}
*** a/src/backend/optimizer/util/plancat.c
--- b/src/backend/optimizer/util/plancat.c
***************
*** 207,212 **** get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
--- 207,213 ----
info->indexcollations = (Oid *) palloc(sizeof(Oid) * ncolumns);
info->opfamily = (Oid *) palloc(sizeof(Oid) * ncolumns);
info->opcintype = (Oid *) palloc(sizeof(Oid) * ncolumns);
+ info->canreturn = (bool *) palloc(sizeof(bool) * ncolumns);
for (i = 0; i < ncolumns; i++)
{
***************
*** 214,224 **** get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
info->indexcollations[i] = indexRelation->rd_indcollation[i];
info->opfamily[i] = indexRelation->rd_opfamily[i];
info->opcintype[i] = indexRelation->rd_opcintype[i];
}
info->relam = indexRelation->rd_rel->relam;
info->amcostestimate = indexRelation->rd_am->amcostestimate;
- info->canreturn = index_can_return(indexRelation);
info->amcanorderbyop = indexRelation->rd_am->amcanorderbyop;
info->amoptionalkey = indexRelation->rd_am->amoptionalkey;
info->amsearcharray = indexRelation->rd_am->amsearcharray;
--- 215,225 ----
info->indexcollations[i] = indexRelation->rd_indcollation[i];
info->opfamily[i] = indexRelation->rd_opfamily[i];
info->opcintype[i] = indexRelation->rd_opcintype[i];
+ info->canreturn[i] = index_can_return(indexRelation, i);
}
info->relam = indexRelation->rd_rel->relam;
info->amcostestimate = indexRelation->rd_am->amcostestimate;
info->amcanorderbyop = indexRelation->rd_am->amcanorderbyop;
info->amoptionalkey = indexRelation->rd_am->amoptionalkey;
info->amsearcharray = indexRelation->rd_am->amsearcharray;
*** a/src/include/access/genam.h
--- b/src/include/access/genam.h
***************
*** 156,162 **** extern IndexBulkDeleteResult *index_bulk_delete(IndexVacuumInfo *info,
void *callback_state);
extern IndexBulkDeleteResult *index_vacuum_cleanup(IndexVacuumInfo *info,
IndexBulkDeleteResult *stats);
! extern bool index_can_return(Relation indexRelation);
extern RegProcedure index_getprocid(Relation irel, AttrNumber attnum,
uint16 procnum);
extern FmgrInfo *index_getprocinfo(Relation irel, AttrNumber attnum,
--- 156,162 ----
void *callback_state);
extern IndexBulkDeleteResult *index_vacuum_cleanup(IndexVacuumInfo *info,
IndexBulkDeleteResult *stats);
! extern bool index_can_return(Relation indexRelation, int attno);
extern RegProcedure index_getprocid(Relation irel, AttrNumber attnum,
uint16 procnum);
extern FmgrInfo *index_getprocinfo(Relation irel, AttrNumber attnum,
*** a/src/include/access/gist.h
--- b/src/include/access/gist.h
***************
*** 33,39 ****
#define GIST_PICKSPLIT_PROC 6
#define GIST_EQUAL_PROC 7
#define GIST_DISTANCE_PROC 8
! #define GISTNProcs 8
/*
* strategy numbers for GiST opclasses that want to implement the old
--- 33,40 ----
#define GIST_PICKSPLIT_PROC 6
#define GIST_EQUAL_PROC 7
#define GIST_DISTANCE_PROC 8
! #define GIST_FETCH_PROC 9
! #define GISTNProcs 9
/*
* strategy numbers for GiST opclasses that want to implement the old
*** a/src/include/access/gist_private.h
--- b/src/include/access/gist_private.h
***************
*** 86,91 **** typedef struct GISTSTATE
--- 86,92 ----
FmgrInfo picksplitFn[INDEX_MAX_KEYS];
FmgrInfo equalFn[INDEX_MAX_KEYS];
FmgrInfo distanceFn[INDEX_MAX_KEYS];
+ FmgrInfo fetchFn[INDEX_MAX_KEYS];
/* Collations to pass to the support functions */
Oid supportCollation[INDEX_MAX_KEYS];
***************
*** 117,122 **** typedef struct GISTSearchHeapItem
--- 118,124 ----
{
ItemPointerData heapPtr;
bool recheck; /* T if quals must be rechecked */
+ IndexTuple ftup; /* Tuple contains datum fetched from key for index-only scans. ftup = fetched tuple*/
} GISTSearchHeapItem;
/* Unvisited item, either index page or heap tuple */
***************
*** 167,175 **** typedef struct GISTScanOpaqueData
double *distances; /* output area for gistindex_keytest */
/* In a non-ordered search, returnable heap items are stored here: */
! GISTSearchHeapItem pageData[BLCKSZ / sizeof(IndexTupleData)];
! OffsetNumber nPageData; /* number of valid items in array */
! OffsetNumber curPageData; /* next item to return */
} GISTScanOpaqueData;
typedef GISTScanOpaqueData *GISTScanOpaque;
--- 169,177 ----
double *distances; /* output area for gistindex_keytest */
/* In a non-ordered search, returnable heap items are stored here: */
! List *pageData;
! ListCell *curPageData;
!
} GISTScanOpaqueData;
typedef GISTScanOpaqueData *GISTScanOpaque;
***************
*** 423,428 **** typedef struct GiSTOptions
--- 425,431 ----
/* gist.c */
extern Datum gistbuildempty(PG_FUNCTION_ARGS);
extern Datum gistinsert(PG_FUNCTION_ARGS);
+ extern Datum gistcanreturn(PG_FUNCTION_ARGS);
extern MemoryContext createTempGistContext(void);
extern GISTSTATE *initGISTstate(Relation index);
extern void freeGISTstate(GISTSTATE *giststate);
***************
*** 522,527 **** extern bool gistKeyIsEQ(GISTSTATE *giststate, int attno, Datum a, Datum b);
--- 525,536 ----
extern void gistDeCompressAtt(GISTSTATE *giststate, Relation r, IndexTuple tuple, Page p,
OffsetNumber o, GISTENTRY *attdata, bool *isnull);
+ extern void gistfentryinit(GISTSTATE *giststate, int nkey,
+ GISTENTRY *e, Datum k, Relation r,
+ Page pg, OffsetNumber o, bool l, bool isNull);
+
+ extern IndexTuple gistFetchTuple(GISTSTATE *giststate, Relation r, IndexTuple tuple, bool *isnull);
+
extern void gistMakeUnionKey(GISTSTATE *giststate, int attno,
GISTENTRY *entry1, bool isnull1,
GISTENTRY *entry2, bool isnull2,
*** a/src/include/catalog/pg_am.h
--- b/src/include/catalog/pg_am.h
***************
*** 123,129 **** DESCR("b-tree index access method");
DATA(insert OID = 405 ( hash 1 1 f f t f f f f f f f f 23 hashinsert hashbeginscan hashgettuple hashgetbitmap hashrescan hashendscan hashmarkpos hashrestrpos hashbuild hashbuildempty hashbulkdelete hashvacuumcleanup - hashcostestimate hashoptions ));
DESCR("hash index access method");
#define HASH_AM_OID 405
! DATA(insert OID = 783 ( gist 0 8 f t f f t t f t t t f 0 gistinsert gistbeginscan gistgettuple gistgetbitmap gistrescan gistendscan gistmarkpos gistrestrpos gistbuild gistbuildempty gistbulkdelete gistvacuumcleanup - gistcostestimate gistoptions ));
DESCR("GiST index access method");
#define GIST_AM_OID 783
DATA(insert OID = 2742 ( gin 0 6 f f f f t t f f t f f 0 gininsert ginbeginscan - gingetbitmap ginrescan ginendscan ginmarkpos ginrestrpos ginbuild ginbuildempty ginbulkdelete ginvacuumcleanup - gincostestimate ginoptions ));
--- 123,129 ----
DATA(insert OID = 405 ( hash 1 1 f f t f f f f f f f f 23 hashinsert hashbeginscan hashgettuple hashgetbitmap hashrescan hashendscan hashmarkpos hashrestrpos hashbuild hashbuildempty hashbulkdelete hashvacuumcleanup - hashcostestimate hashoptions ));
DESCR("hash index access method");
#define HASH_AM_OID 405
! DATA(insert OID = 783 ( gist 0 9 f t f f t t f t t t f 0 gistinsert gistbeginscan gistgettuple gistgetbitmap gistrescan gistendscan gistmarkpos gistrestrpos gistbuild gistbuildempty gistbulkdelete gistvacuumcleanup gistcanreturn gistcostestimate gistoptions ));
DESCR("GiST index access method");
#define GIST_AM_OID 783
DATA(insert OID = 2742 ( gin 0 6 f f f f t t f f t f f 0 gininsert ginbeginscan - gingetbitmap ginrescan ginendscan ginmarkpos ginrestrpos ginbuild ginbuildempty ginbulkdelete ginvacuumcleanup - gincostestimate ginoptions ));
*** a/src/include/catalog/pg_amproc.h
--- b/src/include/catalog/pg_amproc.h
***************
*** 195,200 **** DATA(insert ( 2593 603 603 4 2580 ));
--- 195,201 ----
DATA(insert ( 2593 603 603 5 2581 ));
DATA(insert ( 2593 603 603 6 2582 ));
DATA(insert ( 2593 603 603 7 2584 ));
+ DATA(insert ( 2593 603 603 9 3252 ));
DATA(insert ( 2594 604 604 1 2585 ));
DATA(insert ( 2594 604 604 2 2583 ));
DATA(insert ( 2594 604 604 3 2586 ));
***************
*** 209,214 **** DATA(insert ( 2595 718 718 4 2580 ));
--- 210,216 ----
DATA(insert ( 2595 718 718 5 2581 ));
DATA(insert ( 2595 718 718 6 2582 ));
DATA(insert ( 2595 718 718 7 2584 ));
+ DATA(insert ( 2595 718 718 9 3253 ));
DATA(insert ( 3655 3614 3614 1 3654 ));
DATA(insert ( 3655 3614 3614 2 3651 ));
DATA(insert ( 3655 3614 3614 3 3648 ));
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 558,564 **** DATA(insert OID = 332 ( btbulkdelete PGNSP PGUID 12 1 0 0 0 f f f f t f v 4
DESCR("btree(internal)");
DATA(insert OID = 972 ( btvacuumcleanup PGNSP PGUID 12 1 0 0 0 f f f f t f v 2 0 2281 "2281 2281" _null_ _null_ _null_ _null_ btvacuumcleanup _null_ _null_ _null_ ));
DESCR("btree(internal)");
! DATA(insert OID = 276 ( btcanreturn PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 16 "2281" _null_ _null_ _null_ _null_ btcanreturn _null_ _null_ _null_ ));
DESCR("btree(internal)");
DATA(insert OID = 1268 ( btcostestimate PGNSP PGUID 12 1 0 0 0 f f f f t f v 7 0 2278 "2281 2281 2281 2281 2281 2281 2281" _null_ _null_ _null_ _null_ btcostestimate _null_ _null_ _null_ ));
DESCR("btree(internal)");
--- 558,564 ----
DESCR("btree(internal)");
DATA(insert OID = 972 ( btvacuumcleanup PGNSP PGUID 12 1 0 0 0 f f f f t f v 2 0 2281 "2281 2281" _null_ _null_ _null_ _null_ btvacuumcleanup _null_ _null_ _null_ ));
DESCR("btree(internal)");
! DATA(insert OID = 276 ( btcanreturn PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 16 "2281 2281" _null_ _null_ _null_ _null_ btcanreturn _null_ _null_ _null_ ));
DESCR("btree(internal)");
DATA(insert OID = 1268 ( btcostestimate PGNSP PGUID 12 1 0 0 0 f f f f t f v 7 0 2278 "2281 2281 2281 2281 2281 2281 2281" _null_ _null_ _null_ _null_ btcostestimate _null_ _null_ _null_ ));
DESCR("btree(internal)");
***************
*** 941,946 **** DATA(insert OID = 776 ( gistbulkdelete PGNSP PGUID 12 1 0 0 0 f f f f t f v
--- 941,949 ----
DESCR("gist(internal)");
DATA(insert OID = 2561 ( gistvacuumcleanup PGNSP PGUID 12 1 0 0 0 f f f f t f v 2 0 2281 "2281 2281" _null_ _null_ _null_ _null_ gistvacuumcleanup _null_ _null_ _null_ ));
DESCR("gist(internal)");
+ //Debug
+ DATA(insert OID = 3251 ( gistcanreturn PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 16 "2281 2281" _null_ _null_ _null_ _null_ gistcanreturn _null_ _null_ _null_ ));
+ DESCR("gist(internal)");
DATA(insert OID = 772 ( gistcostestimate PGNSP PGUID 12 1 0 0 0 f f f f t f v 7 0 2278 "2281 2281 2281 2281 2281 2281 2281" _null_ _null_ _null_ _null_ gistcostestimate _null_ _null_ _null_ ));
DESCR("gist(internal)");
DATA(insert OID = 2787 ( gistoptions PGNSP PGUID 12 1 0 0 0 f f f f t f s 2 0 17 "1009 16" _null_ _null_ _null_ _null_ gistoptions _null_ _null_ _null_ ));
***************
*** 3996,4001 **** DATA(insert OID = 2579 ( gist_box_compress PGNSP PGUID 12 1 0 0 0 f f f f t f
--- 3999,4007 ----
DESCR("GiST support");
DATA(insert OID = 2580 ( gist_box_decompress PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 2281 "2281" _null_ _null_ _null_ _null_ gist_box_decompress _null_ _null_ _null_ ));
DESCR("GiST support");
+ //Debug. gist_box_fetch
+ DATA(insert OID = 3252 ( gist_box_fetch PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 2281 "2281" _null_ _null_ _null_ _null_ gist_box_fetch _null_ _null_ _null_ ));
+ DESCR("GiST support");
DATA(insert OID = 2581 ( gist_box_penalty PGNSP PGUID 12 1 0 0 0 f f f f t f i 3 0 2281 "2281 2281 2281" _null_ _null_ _null_ _null_ gist_box_penalty _null_ _null_ _null_ ));
DESCR("GiST support");
DATA(insert OID = 2582 ( gist_box_picksplit PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 2281 "2281 2281" _null_ _null_ _null_ _null_ gist_box_picksplit _null_ _null_ _null_ ));
***************
*** 4012,4017 **** DATA(insert OID = 2591 ( gist_circle_consistent PGNSP PGUID 12 1 0 0 0 f f f f
--- 4018,4025 ----
DESCR("GiST support");
DATA(insert OID = 2592 ( gist_circle_compress PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 2281 "2281" _null_ _null_ _null_ _null_ gist_circle_compress _null_ _null_ _null_ ));
DESCR("GiST support");
+ DATA(insert OID = 3253 ( gist_circle_fetch PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 2281 "2281" _null_ _null_ _null_ _null_ gist_circle_fetch _null_ _null_ _null_ ));
+ DESCR("GiST support");
DATA(insert OID = 1030 ( gist_point_compress PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 2281 "2281" _null_ _null_ _null_ _null_ gist_point_compress _null_ _null_ _null_ ));
DESCR("GiST support");
DATA(insert OID = 2179 ( gist_point_consistent PGNSP PGUID 12 1 0 0 0 f f f f t f i 5 0 16 "2281 600 23 26 2281" _null_ _null_ _null_ _null_ gist_point_consistent _null_ _null_ _null_ ));
***************
*** 4905,4911 **** DATA(insert OID = 4011 ( spgbulkdelete PGNSP PGUID 12 1 0 0 0 f f f f t f v
DESCR("spgist(internal)");
DATA(insert OID = 4012 ( spgvacuumcleanup PGNSP PGUID 12 1 0 0 0 f f f f t f v 2 0 2281 "2281 2281" _null_ _null_ _null_ _null_ spgvacuumcleanup _null_ _null_ _null_ ));
DESCR("spgist(internal)");
! DATA(insert OID = 4032 ( spgcanreturn PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 16 "2281" _null_ _null_ _null_ _null_ spgcanreturn _null_ _null_ _null_ ));
DESCR("spgist(internal)");
DATA(insert OID = 4013 ( spgcostestimate PGNSP PGUID 12 1 0 0 0 f f f f t f v 7 0 2278 "2281 2281 2281 2281 2281 2281 2281" _null_ _null_ _null_ _null_ spgcostestimate _null_ _null_ _null_ ));
DESCR("spgist(internal)");
--- 4913,4919 ----
DESCR("spgist(internal)");
DATA(insert OID = 4012 ( spgvacuumcleanup PGNSP PGUID 12 1 0 0 0 f f f f t f v 2 0 2281 "2281 2281" _null_ _null_ _null_ _null_ spgvacuumcleanup _null_ _null_ _null_ ));
DESCR("spgist(internal)");
! DATA(insert OID = 4032 ( spgcanreturn PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 16 "2281 2281" _null_ _null_ _null_ _null_ spgcanreturn _null_ _null_ _null_ ));
DESCR("spgist(internal)");
DATA(insert OID = 4013 ( spgcostestimate PGNSP PGUID 12 1 0 0 0 f f f f t f v 7 0 2278 "2281 2281 2281 2281 2281 2281 2281" _null_ _null_ _null_ _null_ spgcostestimate _null_ _null_ _null_ ));
DESCR("spgist(internal)");
*** a/src/include/nodes/relation.h
--- b/src/include/nodes/relation.h
***************
*** 526,532 **** typedef struct IndexOptInfo
bool unique; /* true if a unique index */
bool immediate; /* is uniqueness enforced immediately? */
bool hypothetical; /* true if index doesn't really exist */
! bool canreturn; /* can index return IndexTuples? */
bool amcanorderbyop; /* does AM support order by operator result? */
bool amoptionalkey; /* can query omit key for the first column? */
bool amsearcharray; /* can AM handle ScalarArrayOpExpr quals? */
--- 526,532 ----
bool unique; /* true if a unique index */
bool immediate; /* is uniqueness enforced immediately? */
bool hypothetical; /* true if index doesn't really exist */
! bool *canreturn; /* can index return IndexTuples? */
bool amcanorderbyop; /* does AM support order by operator result? */
bool amoptionalkey; /* can query omit key for the first column? */
bool amsearcharray; /* can AM handle ScalarArrayOpExpr quals? */
*** a/src/include/utils/geo_decls.h
--- b/src/include/utils/geo_decls.h
***************
*** 273,278 **** extern Datum box_out(PG_FUNCTION_ARGS);
--- 273,279 ----
extern Datum box_recv(PG_FUNCTION_ARGS);
extern Datum box_send(PG_FUNCTION_ARGS);
extern Datum box_same(PG_FUNCTION_ARGS);
+ extern Datum gist_box_fetch(PG_FUNCTION_ARGS); //Debug. gist_box_fetch
extern Datum box_overlap(PG_FUNCTION_ARGS);
extern Datum box_left(PG_FUNCTION_ARGS);
extern Datum box_overleft(PG_FUNCTION_ARGS);
***************
*** 367,372 **** extern Datum circle_out(PG_FUNCTION_ARGS);
--- 368,374 ----
extern Datum circle_recv(PG_FUNCTION_ARGS);
extern Datum circle_send(PG_FUNCTION_ARGS);
extern Datum circle_same(PG_FUNCTION_ARGS);
+ extern Datum gist_circle_fetch(PG_FUNCTION_ARGS);
extern Datum circle_overlap(PG_FUNCTION_ARGS);
extern Datum circle_overleft(PG_FUNCTION_ARGS);
extern Datum circle_left(PG_FUNCTION_ARGS);
Re: Index-only scans for multicolumn GIST
On 07/21/2014 10:47 PM, Anastasia Lubennikova wrote:
Hi, hackers!
There are new results of my work on GSoC project "Index-only scans for
GIST".
Previous post is here:
http://postgresql.1045698.n5.nabble.com/Index-only-scans-for-GIST-td5804892.htmlRepository is
https://github.com/lubennikovaav/postgres/tree/indexonlygist2
Patch is in attachments.
It includes indexonlyscan for multicolumn GiST. It works correctly -
results are the same with another scan plans.
Fetch() method is realized for box and circle opclasses
Improvement for circle opclass is not such distinct as for box opclass,
because of recheck.
For a circle, the GiST index stores a bounding box of the circle. The
new fetch function reverses that, calculating the radius and center of
the circle from the bounding box.
Those conversions lose some precision due to rounding. Are we okay with
that? Floating point math is always subject to rounding errors, but
there's a good argument to be made that it's not acceptable to get a
different value back when the user didn't explicitly invoke any math
functions.
As an example:
create table circle_tbl (c circle);
create index circle_tbl_idx on circle_tbl using gist (c);
insert into circle_tbl values ('1.23456789012345,1.23456789012345,1e300');
postgres=# set enable_seqscan=off; set enable_bitmapscan=off; set
enable_indexonlyscan=on;
SET
SET
SET
postgres=# select * from circle_tbl ;
c
----------------
<(0,0),1e+300>
(1 row)
postgres=# set enable_indexonlyscan=off;
SET
postgres=# select * from circle_tbl ;
c
----------------------------------------------
<(1.23456789012345,1.23456789012345),1e+300>
(1 row)
- Heikki
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: Index-only scans for multicolumn GIST
Heikki Linnakangas <hlinnakangas@vmware.com> writes:
For a circle, the GiST index stores a bounding box of the circle. The
new fetch function reverses that, calculating the radius and center of
the circle from the bounding box.
Those conversions lose some precision due to rounding. Are we okay with
that?
That seems like a nonstarter :-(. Index-only scans don't have a license
to return approximations. If we drop the behavior for circles, how much
functionality do we have left?
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: Index-only scans for multicolumn GIST
On Tue, Jul 22, 2014 at 2:55 AM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:
On 07/21/2014 10:47 PM, Anastasia Lubennikova wrote:
Hi, hackers!
There are new results of my work on GSoC project "Index-only scans for
GIST".
Previous post is here:http://postgresql.1045698.n5.nabble.com/Index-only-scans-for-GIST-td5804892.html
Repository is
https://github.com/lubennikovaav/postgres/tree/indexonlygist2
Patch is in attachments.
It includes indexonlyscan for multicolumn GiST. It works correctly -
results are the same with another scan plans.
Fetch() method is realized for box and circle opclasses
Improvement for circle opclass is not such distinct as for box opclass,
because of recheck.For a circle, the GiST index stores a bounding box of the circle. The new
fetch function reverses that, calculating the radius and center of the
circle from the bounding box.Those conversions lose some precision due to rounding. Are we okay with
that? Floating point math is always subject to rounding errors, but there's
a good argument to be made that it's not acceptable to get a different value
back when the user didn't explicitly invoke any math functions.As an example:
create table circle_tbl (c circle);
create index circle_tbl_idx on circle_tbl using gist (c);
insert into circle_tbl values ('1.23456789012345,1.23456789012345,1e300');postgres=# set enable_seqscan=off; set enable_bitmapscan=off; set
enable_indexonlyscan=on;
SET
SET
SET
postgres=# select * from circle_tbl ;
c
----------------
<(0,0),1e+300>
(1 row)postgres=# set enable_indexonlyscan=off;
SET
postgres=# select * from circle_tbl ;
c
----------------------------------------------
<(1.23456789012345,1.23456789012345),1e+300>
(1 row)
I really don't think it's ever OK for a query to produce different
answers depending on which plan is chosen.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: Index-only scans for multicolumn GIST
That seems like a nonstarter :-(. Index-only scans don't have a license
to return approximations. If we drop the behavior for circles, how much
functionality do we have left?
It should work with exact operator classes, box_ops, point_ops,
range_ops, inet_ops.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers