Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eM0jF-00088E-Pv for pgsql-hackers@arkaria.postgresql.org; Tue, 05 Dec 2017 00:04:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eM0jF-0003QK-7Q for pgsql-hackers@arkaria.postgresql.org; Tue, 05 Dec 2017 00:04:53 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eM0jE-0003Q8-Dq for pgsql-hackers@lists.postgresql.org; Tue, 05 Dec 2017 00:04:53 +0000 Received: from mail.postgrespro.ru ([93.174.131.138]) by makus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1eM0j9-0001Gy-Ai for pgsql-hackers@postgresql.org; Tue, 05 Dec 2017 00:04:51 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id 6EA3921C1D2A; Tue, 5 Dec 2017 03:04:44 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru Received: from [10.100.236.30] (unknown [195.82.154.27]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id E568921C0902; Tue, 5 Dec 2017 03:04:43 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1512432284; bh=4p4XhyPdKEFB8EGoIX2tXEMP8Fm7C+hlMFktD9CqUXA=; h=Subject:To:Cc:References:From:Date:In-Reply-To; b=kMW7an7fZBltQK9AYaBtOwERkd/LtQTpHAP7Q8fz4Z81Chfu4cGdaxcos5Jk+Mpfk 1KRV4WnkqO+lRG905qVNFmwCZxoXrCeVvU2TCVDOLdvF8vpu83oOuIdeVJ82IqAWPi WHprxDQPdJtW84gb28qugOO8GiOQZMUWw1OG6fBg= Subject: Re: [HACKERS] compress method for spgist - 2 To: Michael Paquier Cc: Alexander Korotkov , =?UTF-8?Q?Darafei_Kom=d1=8fpa_Praliaskouski?= , Tom Lane , pgsql-hackers , Heikki Linnakangas , Fedor Sigaev References: <54907069.1030506@sigaev.ru> <20170920190059.1354.29457.pgcf@coridan.postgresql.org> <31999.1505938053@sss.pgh.pa.us> <60e048d3-aa29-8289-5438-3b0c4c1d8d84@postgrespro.ru> <1499c9d0-075a-3014-d2aa-ba59121b3728@postgrespro.ru> From: Nikita Glukhov Organization: Postgres Professional Message-ID: Date: Tue, 5 Dec 2017 03:02:32 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.5.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/mixed; boundary="------------69D59167C9034A603E796F0F" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------69D59167C9034A603E796F0F Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit On 30.11.2017 05:31, Michael Paquier wrote: > I can see as well that the patches posted at the beginning of the > thread got reviews but that those did not get answered. The set of > patches also have conflicts with HEAD so they need a rebase. For those > reasons I am marking this entry as returned with feedback. Rebased patches are attached. -- Nikita Glukhov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company --------------69D59167C9034A603E796F0F Content-Type: text/x-patch; name="0001-spgist-compress-method-8.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0001-spgist-compress-method-8.patch" From 1c251ff129f8e9f33d14df547d97ba549b109648 Mon Sep 17 00:00:00 2001 From: Nikita Glukhov Date: Tue, 5 Dec 2017 02:38:50 +0300 Subject: [PATCH 1/2] spgist-compress-method-8 --- doc/src/sgml/spgist.sgml | 54 ++++++++++++++++++++++++++------- src/backend/access/spgist/spgdoinsert.c | 44 +++++++++++++++++++-------- src/backend/access/spgist/spgutils.c | 23 ++++++++++++-- src/backend/access/spgist/spgvalidate.c | 24 ++++++++++++++- src/include/access/spgist.h | 5 ++- src/include/access/spgist_private.h | 8 +++-- 6 files changed, 127 insertions(+), 31 deletions(-) diff --git a/doc/src/sgml/spgist.sgml b/doc/src/sgml/spgist.sgml index 139c8ed..55c1b06 100644 --- a/doc/src/sgml/spgist.sgml +++ b/doc/src/sgml/spgist.sgml @@ -240,20 +240,21 @@ There are five user-defined methods that an index operator class for - SP-GiST must provide. All five follow the convention - of accepting two internal arguments, the first of which is a - pointer to a C struct containing input values for the support method, - while the second argument is a pointer to a C struct where output values - must be placed. Four of the methods just return void, since - all their results appear in the output struct; but + SP-GiST must provide and one optional. All five mandatory + methos follow the convention of accepting two internal arguments, + the first of which is a pointer to a C struct containing input values for + the support method, while the second argument is a pointer to a C struct + where output values must be placed. Four of the methods just return + void, since all their results appear in the output struct; but leaf_consistent additionally returns a boolean result. The methods must not modify any fields of their input structs. In all cases, the output struct is initialized to zeroes before calling the - user-defined method. + user-defined method. Optional method compress accepts + datum to be indexed and returns values which actually will be indexed. - The five user-defined methods are: + The five mandatory user-defined methods are: @@ -283,6 +284,7 @@ typedef struct spgConfigOut { Oid prefixType; /* Data type of inner-tuple prefixes */ Oid labelType; /* Data type of inner-tuple node labels */ + Oid leafType; /* Data type of leaf */ bool canReturnData; /* Opclass can reconstruct original data */ bool longValuesOK; /* Opclass can cope with values > 1 page */ } spgConfigOut; @@ -303,7 +305,15 @@ typedef struct spgConfigOut longValuesOK should be set true only when the attType is of variable length and the operator class is capable of segmenting long values by repeated suffixing - (see ). + (see ). leafType + usually has the same value as attType but if + it's different then optional method compress + should be provided. Method compress is responsible + for transformation from attType to + leafType. In this case all other function + should accept leafType values. Note: both + consistent functions will get scankeys + unchanged, without compress transformation. @@ -624,7 +634,8 @@ typedef struct spgInnerConsistentOut reconstructedValue is the value reconstructed for the parent tuple; it is (Datum) 0 at the root level or if the inner_consistent function did not provide a value at the - parent level. + parent level. reconstructedValue should be always a + spgConfigOut.leafType type. traversalValue is a pointer to any traverse data passed down from the previous call of inner_consistent on the parent index tuple, or NULL at the root level. @@ -730,7 +741,8 @@ typedef struct spgLeafConsistentOut reconstructedValue is the value reconstructed for the parent tuple; it is (Datum) 0 at the root level or if the inner_consistent function did not provide a value at the - parent level. + parent level. reconstructedValue should be always a + spgConfigOut.leafType type. traversalValue is a pointer to any traverse data passed down from the previous call of inner_consistent on the parent index tuple, or NULL at the root level. @@ -757,6 +769,26 @@ typedef struct spgLeafConsistentOut + + The optional user-defined method is: + + + + + Datum compress(Datum in) + + + Converts the data item into a format suitable for physical storage in + an index page. It accepts + spgConfigIn.attType + value and return + spgConfigOut.leafType + value. Output value should not be toasted. + + + + + All the SP-GiST support methods are normally called in a short-lived memory context; that is, CurrentMemoryContext will be reset diff --git a/src/backend/access/spgist/spgdoinsert.c b/src/backend/access/spgist/spgdoinsert.c index a5f4c40..edf86f1 100644 --- a/src/backend/access/spgist/spgdoinsert.c +++ b/src/backend/access/spgist/spgdoinsert.c @@ -1899,21 +1899,41 @@ spgdoinsert(Relation index, SpGistState *state, FmgrInfo *procinfo = NULL; /* - * Look up FmgrInfo of the user-defined choose function once, to save - * cycles in the loop below. + * Prepare the leaf datum to insert. + * + * If there is an optional "compress" method, call it to form the leaf + * datum from the input datum. Otherwise we will store the input datum as + * is. (We have to detoast it, though. We assume the "compress" method to + * return an untoasted value.) */ if (!isnull) - procinfo = index_getprocinfo(index, 1, SPGIST_CHOOSE_PROC); + { + if (OidIsValid(index_getprocid(index, 1, SPGIST_COMPRESS_PROC))) + { + procinfo = index_getprocinfo(index, 1, SPGIST_COMPRESS_PROC); + leafDatum = FunctionCall1Coll(procinfo, + index->rd_indcollation[0], + datum); + } + else + { + Assert(state->attLeafType.type == state->attType.type); + + if (state->attType.attlen == -1) + leafDatum = PointerGetDatum(PG_DETOAST_DATUM(datum)); + else + leafDatum = datum; + } + } + else + leafDatum = (Datum) 0; /* - * Since we don't use index_form_tuple in this AM, we have to make sure - * value to be inserted is not toasted; FormIndexDatum doesn't guarantee - * that. + * Look up FmgrInfo of the user-defined choose function once, to save + * cycles in the loop below. */ - if (!isnull && state->attType.attlen == -1) - datum = PointerGetDatum(PG_DETOAST_DATUM(datum)); - - leafDatum = datum; + if (!isnull) + procinfo = index_getprocinfo(index, 1, SPGIST_CHOOSE_PROC); /* * Compute space needed for a leaf tuple containing the given datum. @@ -1923,7 +1943,7 @@ spgdoinsert(Relation index, SpGistState *state, */ if (!isnull) leafSize = SGLTHDRSZ + sizeof(ItemIdData) + - SpGistGetTypeSize(&state->attType, leafDatum); + SpGistGetTypeSize(&state->attLeafType, leafDatum); else leafSize = SGDTSIZE + sizeof(ItemIdData); @@ -2138,7 +2158,7 @@ spgdoinsert(Relation index, SpGistState *state, { leafDatum = out.result.matchNode.restDatum; leafSize = SGLTHDRSZ + sizeof(ItemIdData) + - SpGistGetTypeSize(&state->attType, leafDatum); + SpGistGetTypeSize(&state->attLeafType, leafDatum); } /* diff --git a/src/backend/access/spgist/spgutils.c b/src/backend/access/spgist/spgutils.c index bd5301f..668e3c4 100644 --- a/src/backend/access/spgist/spgutils.c +++ b/src/backend/access/spgist/spgutils.c @@ -124,7 +124,23 @@ spgGetCache(Relation index) PointerGetDatum(&cache->config)); /* Get the information we need about each relevant datatype */ - fillTypeDesc(&cache->attType, atttype); + if (OidIsValid(cache->config.leafType) && + cache->config.leafType != atttype) + { + if (!OidIsValid(index_getprocid(index, 1, SPGIST_COMPRESS_PROC))) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("compress method must not defined when leaf type is different from input type"))); + + fillTypeDesc(&cache->attType, atttype); + fillTypeDesc(&cache->attLeafType, cache->config.leafType); + } + else + { + fillTypeDesc(&cache->attType, atttype); + cache->attLeafType = cache->attType; + } + fillTypeDesc(&cache->attPrefixType, cache->config.prefixType); fillTypeDesc(&cache->attLabelType, cache->config.labelType); @@ -164,6 +180,7 @@ initSpGistState(SpGistState *state, Relation index) state->config = cache->config; state->attType = cache->attType; + state->attLeafType = cache->attLeafType; state->attPrefixType = cache->attPrefixType; state->attLabelType = cache->attLabelType; @@ -618,7 +635,7 @@ spgFormLeafTuple(SpGistState *state, ItemPointer heapPtr, /* compute space needed (note result is already maxaligned) */ size = SGLTHDRSZ; if (!isnull) - size += SpGistGetTypeSize(&state->attType, datum); + size += SpGistGetTypeSize(&state->attLeafType, datum); /* * Ensure that we can replace the tuple with a dead tuple later. This @@ -634,7 +651,7 @@ spgFormLeafTuple(SpGistState *state, ItemPointer heapPtr, tup->nextOffset = InvalidOffsetNumber; tup->heapPtr = *heapPtr; if (!isnull) - memcpyDatum(SGLTDATAPTR(tup), &state->attType, datum); + memcpyDatum(SGLTDATAPTR(tup), &state->attLeafType, datum); return tup; } diff --git a/src/backend/access/spgist/spgvalidate.c b/src/backend/access/spgist/spgvalidate.c index 157cf2a..514da47 100644 --- a/src/backend/access/spgist/spgvalidate.c +++ b/src/backend/access/spgist/spgvalidate.c @@ -52,6 +52,10 @@ spgvalidate(Oid opclassoid) OpFamilyOpFuncGroup *opclassgroup; int i; ListCell *lc; + spgConfigIn configIn; + spgConfigOut configOut; + Oid configOutLefttype = InvalidOid; + Oid configOutRighttype = InvalidOid; /* Fetch opclass information */ classtup = SearchSysCache1(CLAOID, ObjectIdGetDatum(opclassoid)); @@ -100,6 +104,15 @@ spgvalidate(Oid opclassoid) switch (procform->amprocnum) { case SPGIST_CONFIG_PROC: + ok = check_amproc_signature(procform->amproc, VOIDOID, true, + 2, 2, INTERNALOID, INTERNALOID); + configIn.attType = procform->amproclefttype; + OidFunctionCall2(procform->amproc, + PointerGetDatum(&configIn), + PointerGetDatum(&configOut)); + configOutLefttype = procform->amproclefttype; + configOutRighttype = procform->amprocrighttype; + break; case SPGIST_CHOOSE_PROC: case SPGIST_PICKSPLIT_PROC: case SPGIST_INNER_CONSISTENT_PROC: @@ -110,6 +123,15 @@ spgvalidate(Oid opclassoid) ok = check_amproc_signature(procform->amproc, BOOLOID, true, 2, 2, INTERNALOID, INTERNALOID); break; + case SPGIST_COMPRESS_PROC: + if (configOutLefttype != procform->amproclefttype || + configOutRighttype != procform->amprocrighttype) + ok = false; + else + ok = check_amproc_signature(procform->amproc, + configOut.leafType, true, + 1, 1, procform->amproclefttype); + break; default: ereport(INFO, (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), @@ -212,7 +234,7 @@ spgvalidate(Oid opclassoid) if (thisgroup->lefttype != thisgroup->righttype) continue; - for (i = 1; i <= SPGISTNProc; i++) + for (i = 1; i <= SPGISTNRequiredProc; i++) { if ((thisgroup->functionset & (((uint64) 1) << i)) != 0) continue; /* got it */ diff --git a/src/include/access/spgist.h b/src/include/access/spgist.h index d1bc396..a477278 100644 --- a/src/include/access/spgist.h +++ b/src/include/access/spgist.h @@ -30,7 +30,9 @@ #define SPGIST_PICKSPLIT_PROC 3 #define SPGIST_INNER_CONSISTENT_PROC 4 #define SPGIST_LEAF_CONSISTENT_PROC 5 -#define SPGISTNProc 5 +#define SPGIST_COMPRESS_PROC 6 +#define SPGISTNRequiredProc 5 +#define SPGISTNProc 6 /* * Argument structs for spg_config method @@ -44,6 +46,7 @@ typedef struct spgConfigOut { Oid prefixType; /* Data type of inner-tuple prefixes */ Oid labelType; /* Data type of inner-tuple node labels */ + Oid leafType; /* Data type of leaf (type of SPGIST_COMPRESS_PROC output) */ bool canReturnData; /* Opclass can reconstruct original data */ bool longValuesOK; /* Opclass can cope with values > 1 page */ } spgConfigOut; diff --git a/src/include/access/spgist_private.h b/src/include/access/spgist_private.h index 1c4b321..69dc2ba 100644 --- a/src/include/access/spgist_private.h +++ b/src/include/access/spgist_private.h @@ -119,7 +119,8 @@ typedef struct SpGistState { spgConfigOut config; /* filled in by opclass config method */ - SpGistTypeDesc attType; /* type of input data and leaf values */ + SpGistTypeDesc attType; /* type of values to be indexed/restored */ + SpGistTypeDesc attLeafType; /* type of leaf values */ SpGistTypeDesc attPrefixType; /* type of inner-tuple prefix values */ SpGistTypeDesc attLabelType; /* type of node label values */ @@ -178,7 +179,8 @@ typedef struct SpGistCache { spgConfigOut config; /* filled in by opclass config method */ - SpGistTypeDesc attType; /* type of input data and leaf values */ + SpGistTypeDesc attType; /* type of values to be indexed/restored */ + SpGistTypeDesc attLeafType; /* type of leaf values */ SpGistTypeDesc attPrefixType; /* type of inner-tuple prefix values */ SpGistTypeDesc attLabelType; /* type of node label values */ @@ -300,7 +302,7 @@ typedef SpGistLeafTupleData *SpGistLeafTuple; #define SGLTHDRSZ MAXALIGN(sizeof(SpGistLeafTupleData)) #define SGLTDATAPTR(x) (((char *) (x)) + SGLTHDRSZ) -#define SGLTDATUM(x, s) ((s)->attType.attbyval ? \ +#define SGLTDATUM(x, s) ((s)->attLeafType.attbyval ? \ *(Datum *) SGLTDATAPTR(x) : \ PointerGetDatum(SGLTDATAPTR(x))) -- 2.7.4 --------------69D59167C9034A603E796F0F Content-Type: text/x-patch; name="0002-spgist-polygon-8.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0002-spgist-polygon-8.patch" From f50670601756ca5ea495179627bb3166959b6481 Mon Sep 17 00:00:00 2001 From: Nikita Glukhov Date: Tue, 5 Dec 2017 02:50:09 +0300 Subject: [PATCH 2/2] spgist-polygon-8 --- doc/src/sgml/spgist.sgml | 18 +++ src/backend/utils/adt/geo_ops.c | 3 +- src/backend/utils/adt/geo_spgist.c | 92 ++++++++++++- src/include/catalog/pg_amop.h | 16 +++ src/include/catalog/pg_amproc.h | 6 + src/include/catalog/pg_opclass.h | 1 + src/include/catalog/pg_opfamily.h | 1 + src/include/catalog/pg_proc.h | 5 + src/include/utils/geo_decls.h | 3 +- src/test/regress/expected/polygon.out | 238 ++++++++++++++++++++++++++++++++++ src/test/regress/sql/polygon.sql | 93 +++++++++++++ 11 files changed, 469 insertions(+), 7 deletions(-) diff --git a/doc/src/sgml/spgist.sgml b/doc/src/sgml/spgist.sgml index 55c1b06..4f48d2b 100644 --- a/doc/src/sgml/spgist.sgml +++ b/doc/src/sgml/spgist.sgml @@ -131,6 +131,24 @@ + poly_ops + polygon + + << + &< + && + &> + >> + ~= + @> + <@ + &<| + <<| + |>> + |&> + + + text_ops text diff --git a/src/backend/utils/adt/geo_ops.c b/src/backend/utils/adt/geo_ops.c index 9dbe5db..f00ea54 100644 --- a/src/backend/utils/adt/geo_ops.c +++ b/src/backend/utils/adt/geo_ops.c @@ -41,7 +41,6 @@ enum path_delim static int point_inside(Point *p, int npts, Point *plist); static int lseg_crossing(double x, double y, double px, double py); static BOX *box_construct(double x1, double x2, double y1, double y2); -static BOX *box_copy(BOX *box); static BOX *box_fill(BOX *result, double x1, double x2, double y1, double y2); static bool box_ov(BOX *box1, BOX *box2); static double box_ht(BOX *box); @@ -482,7 +481,7 @@ box_fill(BOX *result, double x1, double x2, double y1, double y2) /* box_copy - copy a box */ -static BOX * +BOX * box_copy(BOX *box) { BOX *result = (BOX *) palloc(sizeof(BOX)); diff --git a/src/backend/utils/adt/geo_spgist.c b/src/backend/utils/adt/geo_spgist.c index f6334ba..a105436 100644 --- a/src/backend/utils/adt/geo_spgist.c +++ b/src/backend/utils/adt/geo_spgist.c @@ -391,7 +391,7 @@ spg_box_quad_choose(PG_FUNCTION_ARGS) spgChooseIn *in = (spgChooseIn *) PG_GETARG_POINTER(0); spgChooseOut *out = (spgChooseOut *) PG_GETARG_POINTER(1); BOX *centroid = DatumGetBoxP(in->prefixDatum), - *box = DatumGetBoxP(in->datum); + *box = DatumGetBoxP(in->leafDatum); out->resultType = spgMatchNode; out->result.matchNode.restDatum = BoxPGetDatum(box); @@ -474,6 +474,51 @@ spg_box_quad_picksplit(PG_FUNCTION_ARGS) } /* + * Check if result of consistent method based on bounding box is exact. + */ +static bool +is_bounding_box_test_exact(StrategyNumber strategy) +{ + switch (strategy) + { + case RTLeftStrategyNumber: + case RTOverLeftStrategyNumber: + case RTOverRightStrategyNumber: + case RTRightStrategyNumber: + case RTOverBelowStrategyNumber: + case RTBelowStrategyNumber: + case RTAboveStrategyNumber: + case RTOverAboveStrategyNumber: + return true; + + default: + return false; + } +} + +/* + * Get bounding box for ScanKey. + */ +static BOX * +spg_box_quad_get_scankey_bbox(ScanKey sk, bool *recheck) +{ + switch (sk->sk_subtype) + { + case BOXOID: + return DatumGetBoxP(sk->sk_argument); + + case POLYGONOID: + if (recheck && !is_bounding_box_test_exact(sk->sk_strategy)) + *recheck = true; + return &DatumGetPolygonP(sk->sk_argument)->boundbox; + + default: + elog(ERROR, "unrecognized scankey subtype: %d", sk->sk_subtype); + return NULL; + } +} + +/* * SP-GiST inner consistent function */ Datum @@ -515,7 +560,11 @@ spg_box_quad_inner_consistent(PG_FUNCTION_ARGS) centroid = getRangeBox(DatumGetBoxP(in->prefixDatum)); queries = (RangeBox **) palloc(in->nkeys * sizeof(RangeBox *)); for (i = 0; i < in->nkeys; i++) - queries[i] = getRangeBox(DatumGetBoxP(in->scankeys[i].sk_argument)); + { + BOX *box = spg_box_quad_get_scankey_bbox(&in->scankeys[i], NULL); + + queries[i] = getRangeBox(box); + } /* Allocate enough memory for nodes */ out->nNodes = 0; @@ -637,8 +686,10 @@ spg_box_quad_leaf_consistent(PG_FUNCTION_ARGS) /* Perform the required comparison(s) */ for (i = 0; i < in->nkeys; i++) { - StrategyNumber strategy = in->scankeys[i].sk_strategy; - Datum query = in->scankeys[i].sk_argument; + StrategyNumber strategy = in->scankeys[i].sk_strategy; + BOX *box = spg_box_quad_get_scankey_bbox(&in->scankeys[i], + &out->recheck); + Datum query = BoxPGetDatum(box); switch (strategy) { @@ -713,3 +764,36 @@ spg_box_quad_leaf_consistent(PG_FUNCTION_ARGS) PG_RETURN_BOOL(flag); } + + +/* + * SP-GiST config function for 2-D types that are lossy represented by their + * bounding boxes + */ +Datum +spg_bbox_quad_config(PG_FUNCTION_ARGS) +{ + spgConfigOut *cfg = (spgConfigOut *) PG_GETARG_POINTER(1); + + cfg->prefixType = BOXOID; /* A type represented by its bounding box */ + cfg->labelType = VOIDOID; /* We don't need node labels. */ + cfg->leafType = BOXOID; + cfg->canReturnData = false; + cfg->longValuesOK = false; + + PG_RETURN_VOID(); +} + +/* + * SP-GiST compress function for polygons + */ +Datum +spg_poly_quad_compress(PG_FUNCTION_ARGS) +{ + POLYGON *polygon = PG_GETARG_POLYGON_P(0); + BOX *box; + + box = box_copy(&polygon->boundbox); + + PG_RETURN_BOX_P(box); +} diff --git a/src/include/catalog/pg_amop.h b/src/include/catalog/pg_amop.h index f850be4..d877079 100644 --- a/src/include/catalog/pg_amop.h +++ b/src/include/catalog/pg_amop.h @@ -858,6 +858,22 @@ DATA(insert ( 5000 603 603 11 s 2573 4000 0 )); DATA(insert ( 5000 603 603 12 s 2572 4000 0 )); /* + * SP-GiST poly_ops (supports polygons) + */ +DATA(insert ( 5008 604 604 1 s 485 4000 0 )); +DATA(insert ( 5008 604 604 2 s 486 4000 0 )); +DATA(insert ( 5008 604 604 3 s 492 4000 0 )); +DATA(insert ( 5008 604 604 4 s 487 4000 0 )); +DATA(insert ( 5008 604 604 5 s 488 4000 0 )); +DATA(insert ( 5008 604 604 6 s 491 4000 0 )); +DATA(insert ( 5008 604 604 7 s 490 4000 0 )); +DATA(insert ( 5008 604 604 8 s 489 4000 0 )); +DATA(insert ( 5008 604 604 9 s 2575 4000 0 )); +DATA(insert ( 5008 604 604 10 s 2574 4000 0 )); +DATA(insert ( 5008 604 604 11 s 2577 4000 0 )); +DATA(insert ( 5008 604 604 12 s 2576 4000 0 )); + +/* * GiST inet_ops */ DATA(insert ( 3550 869 869 3 s 3552 783 0 )); diff --git a/src/include/catalog/pg_amproc.h b/src/include/catalog/pg_amproc.h index 1c95846..8b0c26b 100644 --- a/src/include/catalog/pg_amproc.h +++ b/src/include/catalog/pg_amproc.h @@ -334,6 +334,12 @@ DATA(insert ( 5000 603 603 2 5013 )); DATA(insert ( 5000 603 603 3 5014 )); DATA(insert ( 5000 603 603 4 5015 )); DATA(insert ( 5000 603 603 5 5016 )); +DATA(insert ( 5008 604 604 1 5009 )); +DATA(insert ( 5008 604 604 2 5013 )); +DATA(insert ( 5008 604 604 3 5014 )); +DATA(insert ( 5008 604 604 4 5015 )); +DATA(insert ( 5008 604 604 5 5016 )); +DATA(insert ( 5008 604 604 6 5011 )); /* BRIN opclasses */ /* minmax bytea */ diff --git a/src/include/catalog/pg_opclass.h b/src/include/catalog/pg_opclass.h index 28dbc74..6aabc72 100644 --- a/src/include/catalog/pg_opclass.h +++ b/src/include/catalog/pg_opclass.h @@ -205,6 +205,7 @@ DATA(insert ( 4000 box_ops PGNSP PGUID 5000 603 t 0 )); DATA(insert ( 4000 quad_point_ops PGNSP PGUID 4015 600 t 0 )); DATA(insert ( 4000 kd_point_ops PGNSP PGUID 4016 600 f 0 )); DATA(insert ( 4000 text_ops PGNSP PGUID 4017 25 t 0 )); +DATA(insert ( 4000 poly_ops PGNSP PGUID 5008 604 t 603 )); DATA(insert ( 403 jsonb_ops PGNSP PGUID 4033 3802 t 0 )); DATA(insert ( 405 jsonb_ops PGNSP PGUID 4034 3802 t 0 )); DATA(insert ( 2742 jsonb_ops PGNSP PGUID 4036 3802 t 25 )); diff --git a/src/include/catalog/pg_opfamily.h b/src/include/catalog/pg_opfamily.h index 0d0ba7c..838812b 100644 --- a/src/include/catalog/pg_opfamily.h +++ b/src/include/catalog/pg_opfamily.h @@ -186,5 +186,6 @@ DATA(insert OID = 4103 ( 3580 range_inclusion_ops PGNSP PGUID )); DATA(insert OID = 4082 ( 3580 pg_lsn_minmax_ops PGNSP PGUID )); DATA(insert OID = 4104 ( 3580 box_inclusion_ops PGNSP PGUID )); DATA(insert OID = 5000 ( 4000 box_ops PGNSP PGUID )); +DATA(insert OID = 5008 ( 4000 poly_ops PGNSP PGUID )); #endif /* PG_OPFAMILY_H */ diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index c969375..a87c2f2 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -5335,6 +5335,11 @@ DESCR("SP-GiST support for quad tree over box"); DATA(insert OID = 5016 ( spg_box_quad_leaf_consistent PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "2281 2281" _null_ _null_ _null_ _null_ _null_ spg_box_quad_leaf_consistent _null_ _null_ _null_ )); DESCR("SP-GiST support for quad tree over box"); +DATA(insert OID = 5009 ( spg_bbox_quad_config PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 2278 "2281 2281" _null_ _null_ _null_ _null_ _null_ spg_bbox_quad_config _null_ _null_ _null_ )); +DESCR("SP-GiST support for quad tree over 2-D types represented by their bounding boxes"); +DATA(insert OID = 5011 ( spg_poly_quad_compress PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 603 "604" _null_ _null_ _null_ _null_ _null_ spg_poly_quad_compress _null_ _null_ _null_ )); +DESCR("SP-GiST support for quad tree over polygons"); + /* replication slots */ DATA(insert OID = 3779 ( pg_create_physical_replication_slot PGNSP PGUID 12 1 0 0 0 f f f f t f v u 3 0 2249 "19 16 16" "{19,16,16,19,3220}" "{i,i,i,o,o}" "{slot_name,immediately_reserve,temporary,slot_name,lsn}" _null_ _null_ pg_create_physical_replication_slot _null_ _null_ _null_ )); DESCR("create a physical replication slot"); diff --git a/src/include/utils/geo_decls.h b/src/include/utils/geo_decls.h index 44c6381..c89e6c3 100644 --- a/src/include/utils/geo_decls.h +++ b/src/include/utils/geo_decls.h @@ -178,9 +178,10 @@ typedef struct * in geo_ops.c */ -/* private point routines */ +/* private routines */ extern double point_dt(Point *pt1, Point *pt2); extern double point_sl(Point *pt1, Point *pt2); extern double pg_hypot(double x, double y); +extern BOX *box_copy(BOX *box); #endif /* GEO_DECLS_H */ diff --git a/src/test/regress/expected/polygon.out b/src/test/regress/expected/polygon.out index 2361274..a9e7752 100644 --- a/src/test/regress/expected/polygon.out +++ b/src/test/regress/expected/polygon.out @@ -227,3 +227,241 @@ SELECT '(0,0)'::point <-> '((0,0),(1,2),(2,1))'::polygon as on_corner, 0 | 0 | 0 | 1.4142135623731 | 3.2 (1 row) +-- +-- Test the SP-GiST index +-- +CREATE TEMPORARY TABLE quad_poly_tbl (id int, p polygon); +INSERT INTO quad_poly_tbl + SELECT (x - 1) * 100 + y, polygon(circle(point(x * 10, y * 10), 1 + (x + y) % 10)) + FROM generate_series(1, 100) x, + generate_series(1, 100) y; +INSERT INTO quad_poly_tbl + SELECT i, polygon '((200, 300),(210, 310),(230, 290))' + FROM generate_series(10001, 11000) AS i; +INSERT INTO quad_poly_tbl + VALUES + (11001, NULL), + (11002, NULL), + (11003, NULL); +CREATE INDEX quad_poly_tbl_idx ON quad_poly_tbl USING spgist(p); +-- get reference results for ORDER BY distance from seq scan +SET enable_seqscan = ON; +SET enable_indexscan = OFF; +SET enable_bitmapscan = OFF; +CREATE TEMP TABLE quad_poly_tbl_ord_seq1 AS +SELECT rank() OVER (ORDER BY p <-> point '123,456') n, p <-> point '123,456' dist, id +FROM quad_poly_tbl; +CREATE TEMP TABLE quad_poly_tbl_ord_seq2 AS +SELECT rank() OVER (ORDER BY p <-> point '123,456') n, p <-> point '123,456' dist, id +FROM quad_poly_tbl WHERE p <@ polygon '((300,300),(400,600),(600,500),(700,200))'; +-- check results results from index scan +SET enable_seqscan = OFF; +SET enable_indexscan = OFF; +SET enable_bitmapscan = ON; +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_poly_tbl WHERE p << polygon '((300,300),(400,600),(600,500),(700,200))'; + QUERY PLAN +--------------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on quad_poly_tbl + Recheck Cond: (p << '((300,300),(400,600),(600,500),(700,200))'::polygon) + -> Bitmap Index Scan on quad_poly_tbl_idx + Index Cond: (p << '((300,300),(400,600),(600,500),(700,200))'::polygon) +(5 rows) + +SELECT count(*) FROM quad_poly_tbl WHERE p << polygon '((300,300),(400,600),(600,500),(700,200))'; + count +------- + 3890 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_poly_tbl WHERE p &< polygon '((300,300),(400,600),(600,500),(700,200))'; + QUERY PLAN +--------------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on quad_poly_tbl + Recheck Cond: (p &< '((300,300),(400,600),(600,500),(700,200))'::polygon) + -> Bitmap Index Scan on quad_poly_tbl_idx + Index Cond: (p &< '((300,300),(400,600),(600,500),(700,200))'::polygon) +(5 rows) + +SELECT count(*) FROM quad_poly_tbl WHERE p &< polygon '((300,300),(400,600),(600,500),(700,200))'; + count +------- + 7900 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_poly_tbl WHERE p && polygon '((300,300),(400,600),(600,500),(700,200))'; + QUERY PLAN +--------------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on quad_poly_tbl + Recheck Cond: (p && '((300,300),(400,600),(600,500),(700,200))'::polygon) + -> Bitmap Index Scan on quad_poly_tbl_idx + Index Cond: (p && '((300,300),(400,600),(600,500),(700,200))'::polygon) +(5 rows) + +SELECT count(*) FROM quad_poly_tbl WHERE p && polygon '((300,300),(400,600),(600,500),(700,200))'; + count +------- + 977 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_poly_tbl WHERE p &> polygon '((300,300),(400,600),(600,500),(700,200))'; + QUERY PLAN +--------------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on quad_poly_tbl + Recheck Cond: (p &> '((300,300),(400,600),(600,500),(700,200))'::polygon) + -> Bitmap Index Scan on quad_poly_tbl_idx + Index Cond: (p &> '((300,300),(400,600),(600,500),(700,200))'::polygon) +(5 rows) + +SELECT count(*) FROM quad_poly_tbl WHERE p &> polygon '((300,300),(400,600),(600,500),(700,200))'; + count +------- + 7000 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_poly_tbl WHERE p >> polygon '((300,300),(400,600),(600,500),(700,200))'; + QUERY PLAN +--------------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on quad_poly_tbl + Recheck Cond: (p >> '((300,300),(400,600),(600,500),(700,200))'::polygon) + -> Bitmap Index Scan on quad_poly_tbl_idx + Index Cond: (p >> '((300,300),(400,600),(600,500),(700,200))'::polygon) +(5 rows) + +SELECT count(*) FROM quad_poly_tbl WHERE p >> polygon '((300,300),(400,600),(600,500),(700,200))'; + count +------- + 2990 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_poly_tbl WHERE p <<| polygon '((300,300),(400,600),(600,500),(700,200))'; + QUERY PLAN +---------------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on quad_poly_tbl + Recheck Cond: (p <<| '((300,300),(400,600),(600,500),(700,200))'::polygon) + -> Bitmap Index Scan on quad_poly_tbl_idx + Index Cond: (p <<| '((300,300),(400,600),(600,500),(700,200))'::polygon) +(5 rows) + +SELECT count(*) FROM quad_poly_tbl WHERE p <<| polygon '((300,300),(400,600),(600,500),(700,200))'; + count +------- + 1890 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_poly_tbl WHERE p &<| polygon '((300,300),(400,600),(600,500),(700,200))'; + QUERY PLAN +---------------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on quad_poly_tbl + Recheck Cond: (p &<| '((300,300),(400,600),(600,500),(700,200))'::polygon) + -> Bitmap Index Scan on quad_poly_tbl_idx + Index Cond: (p &<| '((300,300),(400,600),(600,500),(700,200))'::polygon) +(5 rows) + +SELECT count(*) FROM quad_poly_tbl WHERE p &<| polygon '((300,300),(400,600),(600,500),(700,200))'; + count +------- + 6900 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_poly_tbl WHERE p |&> polygon '((300,300),(400,600),(600,500),(700,200))'; + QUERY PLAN +---------------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on quad_poly_tbl + Recheck Cond: (p |&> '((300,300),(400,600),(600,500),(700,200))'::polygon) + -> Bitmap Index Scan on quad_poly_tbl_idx + Index Cond: (p |&> '((300,300),(400,600),(600,500),(700,200))'::polygon) +(5 rows) + +SELECT count(*) FROM quad_poly_tbl WHERE p |&> polygon '((300,300),(400,600),(600,500),(700,200))'; + count +------- + 9000 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_poly_tbl WHERE p |>> polygon '((300,300),(400,600),(600,500),(700,200))'; + QUERY PLAN +---------------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on quad_poly_tbl + Recheck Cond: (p |>> '((300,300),(400,600),(600,500),(700,200))'::polygon) + -> Bitmap Index Scan on quad_poly_tbl_idx + Index Cond: (p |>> '((300,300),(400,600),(600,500),(700,200))'::polygon) +(5 rows) + +SELECT count(*) FROM quad_poly_tbl WHERE p |>> polygon '((300,300),(400,600),(600,500),(700,200))'; + count +------- + 3990 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_poly_tbl WHERE p <@ polygon '((300,300),(400,600),(600,500),(700,200))'; + QUERY PLAN +--------------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on quad_poly_tbl + Recheck Cond: (p <@ '((300,300),(400,600),(600,500),(700,200))'::polygon) + -> Bitmap Index Scan on quad_poly_tbl_idx + Index Cond: (p <@ '((300,300),(400,600),(600,500),(700,200))'::polygon) +(5 rows) + +SELECT count(*) FROM quad_poly_tbl WHERE p <@ polygon '((300,300),(400,600),(600,500),(700,200))'; + count +------- + 831 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_poly_tbl WHERE p @> polygon '((340,550),(343,552),(341,553))'; + QUERY PLAN +----------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on quad_poly_tbl + Recheck Cond: (p @> '((340,550),(343,552),(341,553))'::polygon) + -> Bitmap Index Scan on quad_poly_tbl_idx + Index Cond: (p @> '((340,550),(343,552),(341,553))'::polygon) +(5 rows) + +SELECT count(*) FROM quad_poly_tbl WHERE p @> polygon '((340,550),(343,552),(341,553))'; + count +------- + 1 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_poly_tbl WHERE p ~= polygon '((200, 300),(210, 310),(230, 290))'; + QUERY PLAN +----------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on quad_poly_tbl + Recheck Cond: (p ~= '((200,300),(210,310),(230,290))'::polygon) + -> Bitmap Index Scan on quad_poly_tbl_idx + Index Cond: (p ~= '((200,300),(210,310),(230,290))'::polygon) +(5 rows) + +SELECT count(*) FROM quad_poly_tbl WHERE p ~= polygon '((200, 300),(210, 310),(230, 290))'; + count +------- + 1000 +(1 row) + +RESET enable_seqscan; +RESET enable_indexscan; +RESET enable_bitmapscan; diff --git a/src/test/regress/sql/polygon.sql b/src/test/regress/sql/polygon.sql index 7ac8079..c58277b 100644 --- a/src/test/regress/sql/polygon.sql +++ b/src/test/regress/sql/polygon.sql @@ -116,3 +116,96 @@ SELECT '(0,0)'::point <-> '((0,0),(1,2),(2,1))'::polygon as on_corner, '(2,2)'::point <-> '((0,0),(1,4),(3,1))'::polygon as inside, '(3,3)'::point <-> '((0,2),(2,0),(2,2))'::polygon as near_corner, '(4,4)'::point <-> '((0,0),(0,3),(4,0))'::polygon as near_segment; + +-- +-- Test the SP-GiST index +-- + +CREATE TEMPORARY TABLE quad_poly_tbl (id int, p polygon); + +INSERT INTO quad_poly_tbl + SELECT (x - 1) * 100 + y, polygon(circle(point(x * 10, y * 10), 1 + (x + y) % 10)) + FROM generate_series(1, 100) x, + generate_series(1, 100) y; + +INSERT INTO quad_poly_tbl + SELECT i, polygon '((200, 300),(210, 310),(230, 290))' + FROM generate_series(10001, 11000) AS i; + +INSERT INTO quad_poly_tbl + VALUES + (11001, NULL), + (11002, NULL), + (11003, NULL); + +CREATE INDEX quad_poly_tbl_idx ON quad_poly_tbl USING spgist(p); + +-- get reference results for ORDER BY distance from seq scan +SET enable_seqscan = ON; +SET enable_indexscan = OFF; +SET enable_bitmapscan = OFF; + +CREATE TEMP TABLE quad_poly_tbl_ord_seq1 AS +SELECT rank() OVER (ORDER BY p <-> point '123,456') n, p <-> point '123,456' dist, id +FROM quad_poly_tbl; + +CREATE TEMP TABLE quad_poly_tbl_ord_seq2 AS +SELECT rank() OVER (ORDER BY p <-> point '123,456') n, p <-> point '123,456' dist, id +FROM quad_poly_tbl WHERE p <@ polygon '((300,300),(400,600),(600,500),(700,200))'; + +-- check results results from index scan +SET enable_seqscan = OFF; +SET enable_indexscan = OFF; +SET enable_bitmapscan = ON; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_poly_tbl WHERE p << polygon '((300,300),(400,600),(600,500),(700,200))'; +SELECT count(*) FROM quad_poly_tbl WHERE p << polygon '((300,300),(400,600),(600,500),(700,200))'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_poly_tbl WHERE p &< polygon '((300,300),(400,600),(600,500),(700,200))'; +SELECT count(*) FROM quad_poly_tbl WHERE p &< polygon '((300,300),(400,600),(600,500),(700,200))'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_poly_tbl WHERE p && polygon '((300,300),(400,600),(600,500),(700,200))'; +SELECT count(*) FROM quad_poly_tbl WHERE p && polygon '((300,300),(400,600),(600,500),(700,200))'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_poly_tbl WHERE p &> polygon '((300,300),(400,600),(600,500),(700,200))'; +SELECT count(*) FROM quad_poly_tbl WHERE p &> polygon '((300,300),(400,600),(600,500),(700,200))'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_poly_tbl WHERE p >> polygon '((300,300),(400,600),(600,500),(700,200))'; +SELECT count(*) FROM quad_poly_tbl WHERE p >> polygon '((300,300),(400,600),(600,500),(700,200))'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_poly_tbl WHERE p <<| polygon '((300,300),(400,600),(600,500),(700,200))'; +SELECT count(*) FROM quad_poly_tbl WHERE p <<| polygon '((300,300),(400,600),(600,500),(700,200))'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_poly_tbl WHERE p &<| polygon '((300,300),(400,600),(600,500),(700,200))'; +SELECT count(*) FROM quad_poly_tbl WHERE p &<| polygon '((300,300),(400,600),(600,500),(700,200))'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_poly_tbl WHERE p |&> polygon '((300,300),(400,600),(600,500),(700,200))'; +SELECT count(*) FROM quad_poly_tbl WHERE p |&> polygon '((300,300),(400,600),(600,500),(700,200))'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_poly_tbl WHERE p |>> polygon '((300,300),(400,600),(600,500),(700,200))'; +SELECT count(*) FROM quad_poly_tbl WHERE p |>> polygon '((300,300),(400,600),(600,500),(700,200))'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_poly_tbl WHERE p <@ polygon '((300,300),(400,600),(600,500),(700,200))'; +SELECT count(*) FROM quad_poly_tbl WHERE p <@ polygon '((300,300),(400,600),(600,500),(700,200))'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_poly_tbl WHERE p @> polygon '((340,550),(343,552),(341,553))'; +SELECT count(*) FROM quad_poly_tbl WHERE p @> polygon '((340,550),(343,552),(341,553))'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_poly_tbl WHERE p ~= polygon '((200, 300),(210, 310),(230, 290))'; +SELECT count(*) FROM quad_poly_tbl WHERE p ~= polygon '((200, 300),(210, 310),(230, 290))'; + +RESET enable_seqscan; +RESET enable_indexscan; +RESET enable_bitmapscan; -- 2.7.4 --------------69D59167C9034A603E796F0F--