public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v9 2/2] New command ALTER TABLE ALL IN TABLESPACE SET LOGGED/UNLOGGED 12+ messages / 5 participants [nested] [flat]
* [PATCH v9 2/2] New command ALTER TABLE ALL IN TABLESPACE SET LOGGED/UNLOGGED @ 2020-11-11 14:21 Kyotaro Horiguchi <[email protected]> 0 siblings, 0 replies; 12+ messages in thread From: Kyotaro Horiguchi @ 2020-11-11 14:21 UTC (permalink / raw) To ease invoking ALTER TABLE SET LOGGED/UNLOGGED, this command changes relation persistence of all tables in the specified tablespace. --- src/backend/commands/tablecmds.c | 140 +++++++++++++++++++++++++++++++ src/backend/nodes/copyfuncs.c | 16 ++++ src/backend/nodes/equalfuncs.c | 15 ++++ src/backend/parser/gram.y | 20 +++++ src/backend/tcop/utility.c | 11 +++ src/include/commands/tablecmds.h | 2 + src/include/nodes/nodes.h | 1 + src/include/nodes/parsenodes.h | 9 ++ 8 files changed, 214 insertions(+) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 0d9c801535..7c18ed9e75 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -14499,6 +14499,146 @@ AlterTableMoveAll(AlterTableMoveAllStmt *stmt) return new_tablespaceoid; } +/* + * Alter Table ALL ... SET LOGGED/UNLOGGED + * + * Allows a user to change persistence of all objects in a given tablespace in + * the current database. Objects can be chosen based on the owner of the + * object also, to allow users to change persistene only their objects. The + * main permissions handling is done by the lower-level change persistence + * function. + * + * All to-be-modified objects are locked first. If NOWAIT is specified and the + * lock can't be acquired then we ereport(ERROR). + */ +void +AlterTableSetLoggedAll(AlterTableSetLoggedAllStmt *stmt) +{ + List *relations = NIL; + ListCell *l; + ScanKeyData key[1]; + Relation rel; + TableScanDesc scan; + HeapTuple tuple; + Oid tablespaceoid; + List *role_oids = roleSpecsToIds(NIL); + + /* Ensure we were not asked to change something we can't */ + if (stmt->objtype != OBJECT_TABLE) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("only tables can be specified"))); + + /* Get the tablespace OID */ + tablespaceoid = get_tablespace_oid(stmt->tablespacename, false); + + /* + * Now that the checks are done, check if we should set either to + * InvalidOid because it is our database's default tablespace. + */ + if (tablespaceoid == MyDatabaseTableSpace) + tablespaceoid = InvalidOid; + + /* + * Walk the list of objects in the tablespace to pick up them. This will + * only find objects in our database, of course. + */ + ScanKeyInit(&key[0], + Anum_pg_class_reltablespace, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(tablespaceoid)); + + rel = table_open(RelationRelationId, AccessShareLock); + scan = table_beginscan_catalog(rel, 1, key); + while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL) + { + Form_pg_class relForm = (Form_pg_class) GETSTRUCT(tuple); + Oid relOid = relForm->oid; + + /* + * Do not pick-up objects in pg_catalog as part of this, if an admin + * really wishes to do so, they can issue the individual ALTER + * commands directly. + * + * Also, explicitly avoid any shared tables, temp tables, or TOAST + * (TOAST will be changed with the main table). + */ + if (IsCatalogNamespace(relForm->relnamespace) || + relForm->relisshared || + isAnyTempNamespace(relForm->relnamespace) || + IsToastNamespace(relForm->relnamespace)) + continue; + + /* Only pick up the object type requested */ + if (relForm->relkind != RELKIND_RELATION) + continue; + + /* Check if we are only picking-up objects owned by certain roles */ + if (role_oids != NIL && !list_member_oid(role_oids, relForm->relowner)) + continue; + + /* + * Handle permissions-checking here since we are locking the tables + * and also to avoid doing a bunch of work only to fail part-way. Note + * that permissions will also be checked by AlterTableInternal(). + * + * Caller must be considered an owner on the table of which we're going + * to change persistence. + */ + if (!pg_class_ownercheck(relOid, GetUserId())) + aclcheck_error(ACLCHECK_NOT_OWNER, get_relkind_objtype(get_rel_relkind(relOid)), + NameStr(relForm->relname)); + + if (stmt->nowait && + !ConditionalLockRelationOid(relOid, AccessExclusiveLock)) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_IN_USE), + errmsg("aborting because lock on relation \"%s.%s\" is not available", + get_namespace_name(relForm->relnamespace), + NameStr(relForm->relname)))); + else + LockRelationOid(relOid, AccessExclusiveLock); + + /* + * Add to our list of objects of which we're going to change + * persistence. + */ + relations = lappend_oid(relations, relOid); + } + + table_endscan(scan); + table_close(rel, AccessShareLock); + + if (relations == NIL) + ereport(NOTICE, + (errcode(ERRCODE_NO_DATA_FOUND), + errmsg("no matching relations in tablespace \"%s\" found", + tablespaceoid == InvalidOid ? "(database default)" : + get_tablespace_name(tablespaceoid)))); + + /* + * Everything is locked, loop through and change persistence of all of the + * relations. + */ + foreach(l, relations) + { + List *cmds = NIL; + AlterTableCmd *cmd = makeNode(AlterTableCmd); + + if (stmt->logged) + cmd->subtype = AT_SetLogged; + else + cmd->subtype = AT_SetUnLogged; + + cmds = lappend(cmds, cmd); + + EventTriggerAlterTableStart((Node *) stmt); + /* OID is set by AlterTableInternal */ + AlterTableInternal(lfirst_oid(l), cmds, false); + EventTriggerAlterTableEnd(); + } +} + static void index_copy_data(Relation rel, RelFileNode newrnode) { diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index df0b747883..55e38cfe3f 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -4269,6 +4269,19 @@ _copyAlterTableMoveAllStmt(const AlterTableMoveAllStmt *from) return newnode; } +static AlterTableSetLoggedAllStmt * +_copyAlterTableSetLoggedAllStmt(const AlterTableSetLoggedAllStmt *from) +{ + AlterTableSetLoggedAllStmt *newnode = makeNode(AlterTableSetLoggedAllStmt); + + COPY_STRING_FIELD(tablespacename); + COPY_SCALAR_FIELD(objtype); + COPY_SCALAR_FIELD(logged); + COPY_SCALAR_FIELD(nowait); + + return newnode; +} + static CreateExtensionStmt * _copyCreateExtensionStmt(const CreateExtensionStmt *from) { @@ -5622,6 +5635,9 @@ copyObjectImpl(const void *from) case T_AlterTableMoveAllStmt: retval = _copyAlterTableMoveAllStmt(from); break; + case T_AlterTableSetLoggedAllStmt: + retval = _copyAlterTableSetLoggedAllStmt(from); + break; case T_CreateExtensionStmt: retval = _copyCreateExtensionStmt(from); break; diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index cb7ddd463c..a19b7874d7 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -1916,6 +1916,18 @@ _equalAlterTableMoveAllStmt(const AlterTableMoveAllStmt *a, return true; } +static bool +_equalAlterTableSetLoggedAllStmt(const AlterTableSetLoggedAllStmt *a, + const AlterTableSetLoggedAllStmt *b) +{ + COMPARE_STRING_FIELD(tablespacename); + COMPARE_SCALAR_FIELD(objtype); + COMPARE_SCALAR_FIELD(logged); + COMPARE_SCALAR_FIELD(nowait); + + return true; +} + static bool _equalCreateExtensionStmt(const CreateExtensionStmt *a, const CreateExtensionStmt *b) { @@ -3625,6 +3637,9 @@ equal(const void *a, const void *b) case T_AlterTableMoveAllStmt: retval = _equalAlterTableMoveAllStmt(a, b); break; + case T_AlterTableSetLoggedAllStmt: + retval = _equalAlterTableSetLoggedAllStmt(a, b); + break; case T_CreateExtensionStmt: retval = _equalCreateExtensionStmt(a, b); break; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 3d4dd43e47..9823d57a54 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -1984,6 +1984,26 @@ AlterTableStmt: n->nowait = $13; $$ = (Node *)n; } + | ALTER TABLE ALL IN_P TABLESPACE name SET LOGGED opt_nowait + { + AlterTableSetLoggedAllStmt *n = + makeNode(AlterTableSetLoggedAllStmt); + n->tablespacename = $6; + n->objtype = OBJECT_TABLE; + n->logged = true; + n->nowait = $9; + $$ = (Node *)n; + } + | ALTER TABLE ALL IN_P TABLESPACE name SET UNLOGGED opt_nowait + { + AlterTableSetLoggedAllStmt *n = + makeNode(AlterTableSetLoggedAllStmt); + n->tablespacename = $6; + n->objtype = OBJECT_TABLE; + n->logged = false; + n->nowait = $9; + $$ = (Node *)n; + } | ALTER INDEX qualified_name alter_table_cmds { AlterTableStmt *n = makeNode(AlterTableStmt); diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 1fbc387d47..1483f9a475 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -162,6 +162,7 @@ ClassifyUtilityCommandAsReadOnly(Node *parsetree) case T_AlterTSConfigurationStmt: case T_AlterTSDictionaryStmt: case T_AlterTableMoveAllStmt: + case T_AlterTableSetLoggedAllStmt: case T_AlterTableSpaceOptionsStmt: case T_AlterTableStmt: case T_AlterTypeStmt: @@ -1747,6 +1748,12 @@ ProcessUtilitySlow(ParseState *pstate, commandCollected = true; break; + case T_AlterTableSetLoggedAllStmt: + AlterTableSetLoggedAll((AlterTableSetLoggedAllStmt *) parsetree); + /* commands are stashed in AlterTableSetLoggedAll */ + commandCollected = true; + break; + case T_DropStmt: ExecDropStmt((DropStmt *) parsetree, isTopLevel); /* no commands stashed for DROP */ @@ -2669,6 +2676,10 @@ CreateCommandTag(Node *parsetree) tag = AlterObjectTypeCommandTag(((AlterTableMoveAllStmt *) parsetree)->objtype); break; + case T_AlterTableSetLoggedAllStmt: + tag = AlterObjectTypeCommandTag(((AlterTableSetLoggedAllStmt *) parsetree)->objtype); + break; + case T_AlterTableStmt: tag = AlterObjectTypeCommandTag(((AlterTableStmt *) parsetree)->objtype); break; diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h index 336549cc5f..714077ff4c 100644 --- a/src/include/commands/tablecmds.h +++ b/src/include/commands/tablecmds.h @@ -42,6 +42,8 @@ extern void AlterTableInternal(Oid relid, List *cmds, bool recurse); extern Oid AlterTableMoveAll(AlterTableMoveAllStmt *stmt); +extern void AlterTableSetLoggedAll(AlterTableSetLoggedAllStmt *stmt); + extern ObjectAddress AlterTableNamespace(AlterObjectSchemaStmt *stmt, Oid *oldschema); diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index 7c657c1241..8860b2e548 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -428,6 +428,7 @@ typedef enum NodeTag T_AlterCollationStmt, T_CallStmt, T_AlterStatsStmt, + T_AlterTableSetLoggedAllStmt, /* * TAGS FOR PARSE TREE NODES (parsenodes.h) diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 4c5a8a39bf..c3e1bc66d1 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2350,6 +2350,15 @@ typedef struct AlterTableMoveAllStmt bool nowait; } AlterTableMoveAllStmt; +typedef struct AlterTableSetLoggedAllStmt +{ + NodeTag type; + char *tablespacename; + ObjectType objtype; /* Object type to move */ + bool logged; + bool nowait; +} AlterTableSetLoggedAllStmt; + /* ---------------------- * Create/Alter Extension Statements * ---------------------- -- 2.27.0 ----Next_Part(Mon_Dec_20_16_53_20_2021_853)---- ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PATCH] Add sortsupport for range types and btree_gist @ 2024-01-10 00:00 jian he <[email protected]> 0 siblings, 2 replies; 12+ messages in thread From: jian he @ 2024-01-10 00:00 UTC (permalink / raw) To: Bernd Helmle <[email protected]>; [email protected]; +Cc: [email protected] On Thu, Dec 1, 2022 at 1:27 AM Bernd Helmle <[email protected]> wrote: > > Hi, > > No deep code review yet, but CF is approaching its end and i didn't > have time to look at this earlier :/ > > Below are some things i've tested so far. > > Am Mittwoch, dem 15.06.2022 um 12:45 +0200 schrieb Christoph Heiss: > > > > Testing was done using following setup, with about 50 million rows: > > > > CREATE EXTENSION btree_gist; > > CREATE TABLE t (id uuid, block_range int4range); > > CREATE INDEX ON before USING GIST (id, block_range); > > COPY t FROM '..' DELIMITER ',' CSV HEADER; > > > > using > > > > SELECT * FROM t WHERE id = '..' AND block_range && '..' > > > > as test query, using a unpatched instance and one with the patch > > applied. > > > > Some stats for fetching 10,000 random rows using the query above, > > 100 iterations to get good averages. > > > > Here are my results with repeating this: > > HEAD: > -- token index (buffering=auto) > CREATE INDEX Time: 700213,110 ms (11:40,213) > > HEAD patched: > > -- token index (buffering=auto) > CREATE INDEX Time: 136229,400 ms (02:16,229) > > So index creation speed on the test set (table filled with the tokens > and then creating the index afterwards) gets a lot of speedup with this > patch and default buffering strategy. > > > The benchmarking was done on a unpatched instance compiled using the > > > exact same options as with the patch applied. > > > [ Results are noted in a unpatched -> patched fashion. ] > > > > > > First set of results are after the initial CREATE TABLE, CREATE > > INDEX > > > and a COPY to the table, thereby incrementally building the index. > > > > > > Shared Hit Blocks (average): 110.97 -> 78.58 > > > Shared Read Blocks (average): 58.90 -> 47.42 > > > Execution Time (average): 1.10 -> 0.83 ms > > > I/O Read Time (average): 0.19 -> 0.15 ms > > I've changed this a little and did the following: > > CREATE EXTENSION btree_gist; > CREATE TABLE t (id uuid, block_range int4range); > COPY t FROM '..' DELIMITER ',' CSV HEADER; > CREATE INDEX ON before USING GIST (id, block_range); > > So creating the index _after_ having loaded the tokens. > My configuration was: > > shared_buffers = 4G > max_wal_size = 6G > effective_cache_size = 4g # (default, index fits) > maintenance_work_mem = 1G > > > Here are my numbers from the attached benchmark script > > HEAD -> HEAD patched: > > Shared Hit Blocks (avg) : 76.81 -> 9.17 > Shared Read Blocks (avg): 0.43 -> 0.11 > Execution Time (avg) : 0.40 -> 0.05 > IO Read Time (avg) : 0.001 -> 0.0007 > > So with these settings i see an improvement with the provided test set. > Since this patches adds sortsupport for all other existing opclasses, i > thought to give it a try with another test set. What i did was to adapt > the benchmark script (see attached) to use the "pgbench_accounts" table > which i changed to instead using the primary key to have a btree_gist > index on column "aid". > > I let pgbench fill its tables with scale = 1000, dropped the primary > key, create the btree_gist on "aid" with default buffering strategy: > > pgbench -s 1000 -i bernd > > ALTER TABLE pgbench_accounts DROP CONSTRAINT pgbench_accounts_pkey ; > CREATE INDEX ON pgbench_accounts USING gist(aid); > > Ran the benchmark script bench-gist-pgbench_accounts.py: > \d pgbench_accounts Table "public.pgbench_accounts" Column | Type | Collation | Nullable | Default ----------+---------------+-----------+----------+--------- aid | integer | | not null | bid | integer | | | abalance | integer | | | filler | character(84) | | | Indexes: "pgbench_accounts_pkey" PRIMARY KEY, btree (aid) you do `CREATE INDEX ON pgbench_accounts USING gist(aid);` but the original patch didn't change contrib/btree_gist/btree_int4.c So I doubt your benchmark is related to the original patch. or maybe I missed something. also per doc: ` sortsupport Returns a comparator function to sort data in a way that preserves locality. It is used by CREATE INDEX and REINDEX commands. The quality of the created index depends on how well the sort order determined by the comparator function preserves locality of the inputs. ` from the doc, add sortsupport function will only influence index build time? +/* + * GiST sortsupport comparator for ranges. + * + * Operates solely on the lower bounds of the ranges, comparing them using + * range_cmp_bounds(). + * Empty ranges are sorted before non-empty ones. + */ +static int +range_gist_cmp(Datum a, Datum b, SortSupport ssup) +{ + RangeType *range_a = DatumGetRangeTypeP(a); + RangeType *range_b = DatumGetRangeTypeP(b); + TypeCacheEntry *typcache = ssup->ssup_extra; + RangeBound lower1, + lower2; + RangeBound upper1, + upper2; + bool empty1, + empty2; + int result; + + if (typcache == NULL) { + Assert(RangeTypeGetOid(range_a) == RangeTypeGetOid(range_b)); + typcache = lookup_type_cache(RangeTypeGetOid(range_a), TYPECACHE_RANGE_INFO); + + /* + * Cache the range info between calls to avoid having to call + * lookup_type_cache() for each comparison. + */ + ssup->ssup_extra = typcache; + } + + range_deserialize(typcache, range_a, &lower1, &upper1, &empty1); + range_deserialize(typcache, range_b, &lower2, &upper2, &empty2); + + /* For b-tree use, empty ranges sort before all else */ + if (empty1 && empty2) + result = 0; + else if (empty1) + result = -1; + else if (empty2) + result = 1; + else + result = range_cmp_bounds(typcache, &lower1, &lower2); + + if ((Datum) range_a != a) + pfree(range_a); + + if ((Datum) range_b != b) + pfree(range_b); + + return result; +} per https://www.postgresql.org/docs/current/gist-extensibility.html QUOTE: All the GiST support methods are normally called in short-lived memory contexts; that is, CurrentMemoryContext will get reset after each tuple is processed. It is therefore not very important to worry about pfree'ing everything you palloc. However, in some cases it's useful for a support method to ENDOF_QUOTE so removing the following part should be OK. + if ((Datum) range_a != a) + pfree(range_a); + + if ((Datum) range_b != b) + pfree(range_b); comparison solely on the lower bounds looks strange to me. if lower bound is the same, then compare upper bound, so the range_gist_cmp function is consistent with function range_compare. so following change: + else + result = range_cmp_bounds(typcache, &lower1, &lower2); to ` else { result = range_cmp_bounds(typcache, &lower1, &lower2); if (result == 0) result = range_cmp_bounds(typcache, &upper1, &upper2); } ` does contrib/btree_gist/btree_gist--1.7--1.8.sql function be declared as strict ? (I am not sure) other than that, the whole patch looks good. ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PATCH] Add sortsupport for range types and btree_gist @ 2024-01-10 14:18 jian he <[email protected]> parent: jian he <[email protected]> 1 sibling, 3 replies; 12+ messages in thread From: jian he @ 2024-01-10 14:18 UTC (permalink / raw) To: Bernd Helmle <[email protected]>; +Cc: [email protected] On Wed, Jan 10, 2024 at 8:00 AM jian he <[email protected]> wrote: > > ` > from the doc, add sortsupport function will only influence index build time? > > +/* > + * GiST sortsupport comparator for ranges. > + * > + * Operates solely on the lower bounds of the ranges, comparing them using > + * range_cmp_bounds(). > + * Empty ranges are sorted before non-empty ones. > + */ > +static int > +range_gist_cmp(Datum a, Datum b, SortSupport ssup) > +{ > + RangeType *range_a = DatumGetRangeTypeP(a); > + RangeType *range_b = DatumGetRangeTypeP(b); > + TypeCacheEntry *typcache = ssup->ssup_extra; > + RangeBound lower1, > + lower2; > + RangeBound upper1, > + upper2; > + bool empty1, > + empty2; > + int result; > + > + if (typcache == NULL) { > + Assert(RangeTypeGetOid(range_a) == RangeTypeGetOid(range_b)); > + typcache = lookup_type_cache(RangeTypeGetOid(range_a), TYPECACHE_RANGE_INFO); > + > + /* > + * Cache the range info between calls to avoid having to call > + * lookup_type_cache() for each comparison. > + */ > + ssup->ssup_extra = typcache; > + } > + > + range_deserialize(typcache, range_a, &lower1, &upper1, &empty1); > + range_deserialize(typcache, range_b, &lower2, &upper2, &empty2); > + > + /* For b-tree use, empty ranges sort before all else */ > + if (empty1 && empty2) > + result = 0; > + else if (empty1) > + result = -1; > + else if (empty2) > + result = 1; > + else > + result = range_cmp_bounds(typcache, &lower1, &lower2); > + > + if ((Datum) range_a != a) > + pfree(range_a); > + > + if ((Datum) range_b != b) > + pfree(range_b); > + > + return result; > +} > > per https://www.postgresql.org/docs/current/gist-extensibility.html > QUOTE: > All the GiST support methods are normally called in short-lived memory > contexts; that is, CurrentMemoryContext will get reset after each > tuple is processed. It is therefore not very important to worry about > pfree'ing everything you palloc. However, in some cases it's useful > for a support method to > ENDOF_QUOTE > > so removing the following part should be OK. > + if ((Datum) range_a != a) > + pfree(range_a); > + > + if ((Datum) range_b != b) > + pfree(range_b); > > comparison solely on the lower bounds looks strange to me. > if lower bound is the same, then compare upper bound, so the > range_gist_cmp function is consistent with function range_compare. > so following change: > > + else > + result = range_cmp_bounds(typcache, &lower1, &lower2); > to > ` > else > { > result = range_cmp_bounds(typcache, &lower1, &lower2); > if (result == 0) > result = range_cmp_bounds(typcache, &upper1, &upper2); > } > ` > > does contrib/btree_gist/btree_gist--1.7--1.8.sql function be declared > as strict ? (I am not sure) > other than that, the whole patch looks good. the original author email address ([email protected]) Address not found. so I don't include it. I split the original author's patch into 2. 1. Add GiST sortsupport function for all the btree-gist module data types except anyrange data type (which actually does not in this module) 2. Add GiST sortsupport function for anyrange data type. What changed compared to the original patch: 1. The original patch missed some operator class for all the data types in btree-gist modules. So I added them. now add sortsupport function for all the following data types in btree-gist: int2,int4,int8,float4,float8,numeric timestamp with time zone, timestamp without time zone, time with time zone, time without time zone, date interval, oid, money, char varchar, text, bytea, bit, varbit macaddr, macaddr8, inet, cidr, uuid, bool, enum 2. range_gist_cmp: the gist range sortsupport function, it looks like range_cmp, but the range typcache is cached, so we don't need to repeatedly call lookup_type_cache. refactor: As mentioned above, if the range lower bound is the same then compare the upper bound. I aslo refactored the comment. what I am confused: In fmgr.h /* * Support for cleaning up detoasted copies of inputs. This must only * be used for pass-by-ref datatypes, and normally would only be used * for toastable types. If the given pointer is different from the * original argument, assume it's a palloc'd detoasted copy, and pfree it. * NOTE: most functions on toastable types do not have to worry about this, * but we currently require that support functions for indexes not leak * memory. */ #define PG_FREE_IF_COPY(ptr,n) \ do { \ if ((Pointer) (ptr) != PG_GETARG_POINTER(n)) \ pfree(ptr); \ } while (0) but the doc (https://www.postgresql.org/docs/current/gist-extensibility.html) says: All the GiST support methods are normally called in short-lived memory contexts; that is, CurrentMemoryContext will get reset after each tuple is processed. It is therefore not very important to worry about pfree'ing everything you palloc. ENDOF_QUOTE so I am not sure in range_gist_cmp, we need the following: ` if ((Datum) range_a != a) pfree(range_a); if ((Datum) range_b != b) pfree(range_b); ` Attachments: [text/x-patch] v5-0002-Add-GIST-sortsupport-function-for-range_ops.patch (4.6K, ../../CACJufxEVYNCQJEnRr6jpdrNSb9hH6YkavxrLabrRuCWoeTDHjQ@mail.gmail.com/2-v5-0002-Add-GIST-sortsupport-function-for-range_ops.patch) download | inline diff: From fb85f80ff416f902fde3d80654dfb61b4479f6ec Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Wed, 10 Jan 2024 15:10:29 +0800 Subject: [PATCH v5 2/2] Add GIST sortsupport function for range_ops It can facilitate fast GIST index build for range data type. --- src/backend/utils/adt/rangetypes_gist.c | 70 +++++++++++++++++++++++++ src/include/catalog/pg_amproc.dat | 3 ++ src/include/catalog/pg_proc.dat | 3 ++ 3 files changed, 76 insertions(+) diff --git a/src/backend/utils/adt/rangetypes_gist.c b/src/backend/utils/adt/rangetypes_gist.c index cb28e985..cb230f39 100644 --- a/src/backend/utils/adt/rangetypes_gist.c +++ b/src/backend/utils/adt/rangetypes_gist.c @@ -21,6 +21,7 @@ #include "utils/fmgrprotos.h" #include "utils/multirangetypes.h" #include "utils/rangetypes.h" +#include "utils/sortsupport.h" /* * Range class properties used to segregate different classes of ranges in @@ -177,6 +178,7 @@ static void range_gist_double_sorting_split(TypeCacheEntry *typcache, static void range_gist_consider_split(ConsiderSplitContext *context, RangeBound *right_lower, int min_left_count, RangeBound *left_upper, int max_left_count); +static int range_gist_cmp(Datum a, Datum b, SortSupport ssup); static int get_gist_range_class(RangeType *range); static int single_bound_cmp(const void *a, const void *b, void *arg); static int interval_cmp_lower(const void *a, const void *b, void *arg); @@ -773,6 +775,20 @@ range_gist_picksplit(PG_FUNCTION_ARGS) PG_RETURN_POINTER(v); } +/* + * Sort support routine for fast GiST index build by sorting. + */ +Datum +range_gist_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = range_gist_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} + /* equality comparator for GiST */ Datum range_gist_same(PG_FUNCTION_ARGS) @@ -1693,6 +1709,60 @@ range_gist_consider_split(ConsiderSplitContext *context, } } +/* + * GiST sortsupport comparator for ranges. + * similar to range_cmp, but range typcache is cached. + */ +static int +range_gist_cmp(Datum a, Datum b, SortSupport ssup) +{ + RangeType *range_a = DatumGetRangeTypeP(a); + RangeType *range_b = DatumGetRangeTypeP(b); + TypeCacheEntry *typcache = ssup->ssup_extra; + RangeBound lower1, + lower2; + RangeBound upper1, + upper2; + bool empty1, + empty2; + int result; + + if (typcache == NULL) + { + Assert(RangeTypeGetOid(range_a) == RangeTypeGetOid(range_b)); + typcache = lookup_type_cache(RangeTypeGetOid(range_a), TYPECACHE_RANGE_INFO); + + /* + * Cache the range info between calls to avoid having to call + * lookup_type_cache() for each comparison. + */ + ssup->ssup_extra = typcache; + } + + range_deserialize(typcache, range_a, &lower1, &upper1, &empty1); + range_deserialize(typcache, range_b, &lower2, &upper2, &empty2); + + if (empty1 && empty2) + result = 0; + else if (empty1) + result = -1; + else if (empty2) + result = 1; + else + { + result = range_cmp_bounds(typcache, &lower1, &lower2); + if (result == 0) + result = range_cmp_bounds(typcache, &upper1, &upper2); + } + + if ((Datum) range_a != a) + pfree(range_a); + if ((Datum) range_b != b) + pfree(range_b); + + return result; +} + /* * Find class number for range. * diff --git a/src/include/catalog/pg_amproc.dat b/src/include/catalog/pg_amproc.dat index f639c3a6..71d4bba1 100644 --- a/src/include/catalog/pg_amproc.dat +++ b/src/include/catalog/pg_amproc.dat @@ -598,6 +598,9 @@ { amprocfamily => 'gist/range_ops', amproclefttype => 'anyrange', amprocrighttype => 'anyrange', amprocnum => '7', amproc => 'range_gist_same' }, +{ amprocfamily => 'gist/range_ops', amproclefttype => 'anyrange', + amprocrighttype => 'anyrange', amprocnum => '11', + amproc => 'range_gist_sortsupport' }, { amprocfamily => 'gist/network_ops', amproclefttype => 'inet', amprocrighttype => 'inet', amprocnum => '1', amproc => 'inet_gist_consistent' }, diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 79793927..8b8aa5af 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -10577,6 +10577,9 @@ { oid => '3881', descr => 'GiST support', proname => 'range_gist_same', prorettype => 'internal', proargtypes => 'anyrange anyrange internal', prosrc => 'range_gist_same' }, +{ oid => '8849', descr => 'GiST support', + proname => 'range_gist_sortsupport', prorettype => 'void', + proargtypes => 'internal', prosrc => 'range_gist_sortsupport' }, { oid => '6154', descr => 'GiST support', proname => 'multirange_gist_consistent', prorettype => 'bool', proargtypes => 'internal anymultirange int2 oid internal', -- 2.34.1 [text/x-patch] v5-0001-Add-GIST-sortsupport-function-for-all-the-btree-g.patch (18.1K, ../../CACJufxEVYNCQJEnRr6jpdrNSb9hH6YkavxrLabrRuCWoeTDHjQ@mail.gmail.com/3-v5-0001-Add-GIST-sortsupport-function-for-all-the-btree-g.patch) download | inline diff: From c3f396e1490892ead6f32b4f0eac8a25cb877deb Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Wed, 10 Jan 2024 13:26:34 +0800 Subject: [PATCH v5 1/2] Add GIST sortsupport function for all the btree-gist module data types Using GIST penalty and picksplit functions doing index build can be very slower, because of random IO. GIST sortsupport function can make GIST index build very fast. While build the index, with the sortsupport function, we can the first sort the data, then build the index. Add support for the following GiST data types: int2 int4 int8 float4 float8 numeric timestamp with time zone timestamp without time zone time with time zone time without time zone date interval oid money char varchar text bytea bit varbit macaddr macaddr8 inet cidr, uuid bool enum --- contrib/btree_gist/Makefile | 3 +- contrib/btree_gist/btree_bit.c | 36 ++++++ contrib/btree_gist/btree_bool.c | 22 ++++ contrib/btree_gist/btree_cash.c | 22 ++++ contrib/btree_gist/btree_enum.c | 26 ++++ contrib/btree_gist/btree_gist--1.7--1.8.sql | 132 ++++++++++++++++++++ contrib/btree_gist/btree_gist.control | 2 +- contrib/btree_gist/btree_inet.c | 19 +++ contrib/btree_gist/btree_interval.c | 19 +++ contrib/btree_gist/btree_macaddr8.c | 19 +++ contrib/btree_gist/btree_time.c | 38 ++++++ contrib/btree_gist/meson.build | 1 + 12 files changed, 337 insertions(+), 2 deletions(-) create mode 100644 contrib/btree_gist/btree_gist--1.7--1.8.sql diff --git a/contrib/btree_gist/Makefile b/contrib/btree_gist/Makefile index 073dcc74..965f0559 100644 --- a/contrib/btree_gist/Makefile +++ b/contrib/btree_gist/Makefile @@ -33,7 +33,8 @@ EXTENSION = btree_gist DATA = btree_gist--1.0--1.1.sql \ btree_gist--1.1--1.2.sql btree_gist--1.2.sql btree_gist--1.2--1.3.sql \ btree_gist--1.3--1.4.sql btree_gist--1.4--1.5.sql \ - btree_gist--1.5--1.6.sql btree_gist--1.6--1.7.sql + btree_gist--1.5--1.6.sql btree_gist--1.6--1.7.sql \ + btree_gist--1.7--1.8.sql PGFILEDESC = "btree_gist - B-tree equivalent GiST operator classes" REGRESS = init int2 int4 int8 float4 float8 cash oid timestamp timestamptz \ diff --git a/contrib/btree_gist/btree_bit.c b/contrib/btree_gist/btree_bit.c index 6790f22b..bbd28165 100644 --- a/contrib/btree_gist/btree_bit.c +++ b/contrib/btree_gist/btree_bit.c @@ -7,6 +7,7 @@ #include "btree_utils_var.h" #include "utils/builtins.h" #include "utils/bytea.h" +#include "utils/sortsupport.h" #include "utils/varbit.h" @@ -19,10 +20,23 @@ PG_FUNCTION_INFO_V1(gbt_bit_picksplit); PG_FUNCTION_INFO_V1(gbt_bit_consistent); PG_FUNCTION_INFO_V1(gbt_bit_penalty); PG_FUNCTION_INFO_V1(gbt_bit_same); +PG_FUNCTION_INFO_V1(gbt_bit_sortsupport); +PG_FUNCTION_INFO_V1(gbt_varbit_sortsupport); /* define for comparison */ +static int +bit_fast_cmp(Datum x, Datum y, SortSupport ssup) +{ + return DatumGetInt32(DirectFunctionCall2(byteacmp, x, y)); +} + +static int +varbit_fast_cmp(Datum x, Datum y, SortSupport ssup) +{ + return DatumGetInt32(DirectFunctionCall2(bitcmp, x, y)); +} static bool gbt_bitgt(const void *a, const void *b, Oid collation, FmgrInfo *flinfo) { @@ -208,3 +222,25 @@ gbt_bit_penalty(PG_FUNCTION_ARGS) PG_RETURN_POINTER(gbt_var_penalty(result, o, n, PG_GET_COLLATION(), &tinfo, fcinfo->flinfo)); } + +Datum +gbt_bit_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = bit_fast_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} + +Datum +gbt_varbit_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = varbit_fast_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} \ No newline at end of file diff --git a/contrib/btree_gist/btree_bool.c b/contrib/btree_gist/btree_bool.c index 8b2af129..3a9f230e 100644 --- a/contrib/btree_gist/btree_bool.c +++ b/contrib/btree_gist/btree_bool.c @@ -6,6 +6,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" #include "common/int.h" +#include "utils/sortsupport.h" typedef struct boolkey { @@ -23,6 +24,16 @@ PG_FUNCTION_INFO_V1(gbt_bool_picksplit); PG_FUNCTION_INFO_V1(gbt_bool_consistent); PG_FUNCTION_INFO_V1(gbt_bool_penalty); PG_FUNCTION_INFO_V1(gbt_bool_same); +PG_FUNCTION_INFO_V1(gbt_bool_sortsupport); + +static int +bool_fast_cmp(Datum x, Datum y, SortSupport ssup) +{ + bool arg1 = DatumGetBool(x); + bool arg2 = DatumGetBool(y); + + return arg1 - arg2; +} static bool gbt_boolgt(const void *a, const void *b, FmgrInfo *flinfo) @@ -167,3 +178,14 @@ gbt_bool_same(PG_FUNCTION_ARGS) *result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo); PG_RETURN_POINTER(result); } + +Datum +gbt_bool_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = bool_fast_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} diff --git a/contrib/btree_gist/btree_cash.c b/contrib/btree_gist/btree_cash.c index 546b948e..b9c336e2 100644 --- a/contrib/btree_gist/btree_cash.c +++ b/contrib/btree_gist/btree_cash.c @@ -7,6 +7,7 @@ #include "btree_utils_num.h" #include "common/int.h" #include "utils/cash.h" +#include "utils/sortsupport.h" typedef struct { @@ -25,6 +26,16 @@ PG_FUNCTION_INFO_V1(gbt_cash_consistent); PG_FUNCTION_INFO_V1(gbt_cash_distance); PG_FUNCTION_INFO_V1(gbt_cash_penalty); PG_FUNCTION_INFO_V1(gbt_cash_same); +PG_FUNCTION_INFO_V1(gbt_cash_sortsupport); + +static int +cash_fast_cmp(Datum x, Datum y, SortSupport ssup) +{ + bool arg1 = DatumGetCash(x); + bool arg2 = DatumGetCash(y); + + return arg1 - arg2; +} static bool gbt_cashgt(const void *a, const void *b, FmgrInfo *flinfo) @@ -215,3 +226,14 @@ gbt_cash_same(PG_FUNCTION_ARGS) *result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo); PG_RETURN_POINTER(result); } + +Datum +gbt_cash_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = cash_fast_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} diff --git a/contrib/btree_gist/btree_enum.c b/contrib/btree_gist/btree_enum.c index d4dc38a3..33b9a342 100644 --- a/contrib/btree_gist/btree_enum.c +++ b/contrib/btree_gist/btree_enum.c @@ -7,6 +7,7 @@ #include "btree_utils_num.h" #include "fmgr.h" #include "utils/builtins.h" +#include "utils/sortsupport.h" /* enums are really Oids, so we just use the same structure */ @@ -26,8 +27,16 @@ PG_FUNCTION_INFO_V1(gbt_enum_picksplit); PG_FUNCTION_INFO_V1(gbt_enum_consistent); PG_FUNCTION_INFO_V1(gbt_enum_penalty); PG_FUNCTION_INFO_V1(gbt_enum_same); +PG_FUNCTION_INFO_V1(gbt_enum_sortsupport); +static int +enum_fast_cmp(Datum x, Datum y, SortSupport ssup) +{ + return DatumGetInt32(CallerFInfoFunctionCall2(enum_cmp, ssup->ssup_extra, + InvalidOid, x, y)); +} + static bool gbt_enumgt(const void *a, const void *b, FmgrInfo *flinfo) { @@ -183,3 +192,20 @@ gbt_enum_same(PG_FUNCTION_ARGS) *result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo); PG_RETURN_POINTER(result); } + +Datum +gbt_enum_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = enum_fast_cmp; + + /* + * Since enum_fast_cmp() also uses enum_cmp() like the rest of the + * comparsion functions, it also needs to pass on flinfo when calling + * it. Thus save it in ->ssup_extra and later retrieve it in enum_fast_cmp(). + */ + ssup->ssup_extra = fcinfo->flinfo; + + PG_RETURN_VOID(); +} diff --git a/contrib/btree_gist/btree_gist--1.7--1.8.sql b/contrib/btree_gist/btree_gist--1.7--1.8.sql new file mode 100644 index 00000000..bd71d498 --- /dev/null +++ b/contrib/btree_gist/btree_gist--1.7--1.8.sql @@ -0,0 +1,132 @@ +/* contrib/btree_gist/btree_gist--1.7--1.8.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "ALTER EXTENSION btree_gist UPDATE TO '1.8'" to load this file. \quit + +CREATE FUNCTION gbt_bit_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_varbit_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_bool_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_cash_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_enum_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_inet_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_intv_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_macad8_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_time_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_timetz_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +ALTER OPERATOR FAMILY gist_bit_ops USING gist ADD + FUNCTION 11 (varbit, varbit) gbt_bit_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_vbit_ops USING gist ADD + FUNCTION 11 (bit, bit) gbt_varbit_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_bool_ops USING gist ADD + FUNCTION 11 (bool, bool) gbt_bool_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_bytea_ops USING gist ADD + FUNCTION 11 (bytea, bytea) bytea_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_cash_ops USING gist ADD + FUNCTION 11 (money, money) gbt_cash_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_date_ops USING gist ADD + FUNCTION 11 (date, date) date_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_enum_ops USING gist ADD + FUNCTION 11 (anyenum, anyenum) gbt_enum_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_float4_ops USING gist ADD + FUNCTION 11 (float4, float4) btfloat4sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_float8_ops USING gist ADD + FUNCTION 11 (float8, float8) btfloat8sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_inet_ops USING gist ADD + FUNCTION 11 (inet, inet) gbt_inet_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_cidr_ops USING gist ADD + FUNCTION 11 (inet, inet) gbt_inet_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_int2_ops USING gist ADD + FUNCTION 11 (int2, int2) btint2sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_int4_ops USING gist ADD + FUNCTION 11 (int4, int4) btint4sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_int8_ops USING gist ADD + FUNCTION 11 (int8, int8) btint8sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_interval_ops USING gist ADD + FUNCTION 11 (interval, interval) gbt_intv_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_macaddr_ops USING gist ADD + FUNCTION 11 (macaddr, macaddr) macaddr_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_macaddr8_ops USING gist ADD + FUNCTION 11 (macaddr8, macaddr8) gbt_macad8_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_numeric_ops USING gist ADD + FUNCTION 11 (numeric, numeric) numeric_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_oid_ops USING gist ADD + FUNCTION 11 (oid, oid) btoidsortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_text_ops USING gist ADD + FUNCTION 11 (text, text) bttextsortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_bpchar_ops USING gist ADD + FUNCTION 11 (bpchar, bpchar) bpchar_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_time_ops USING gist ADD + FUNCTION 11 (time, time) gbt_time_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_timestamp_ops USING gist ADD + FUNCTION 11 (timestamp, timestamp) timestamp_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_timestamptz_ops USING gist ADD + FUNCTION 11 (timestamptz, timestamptz) timestamp_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_timetz_ops USING gist ADD + FUNCTION 11 (timetz, timetz) gbt_timetz_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_uuid_ops USING gist ADD + FUNCTION 11 (uuid, uuid) uuid_sortsupport (internal) ; diff --git a/contrib/btree_gist/btree_gist.control b/contrib/btree_gist/btree_gist.control index fa9171a8..abf66538 100644 --- a/contrib/btree_gist/btree_gist.control +++ b/contrib/btree_gist/btree_gist.control @@ -1,6 +1,6 @@ # btree_gist extension comment = 'support for indexing common datatypes in GiST' -default_version = '1.7' +default_version = '1.8' module_pathname = '$libdir/btree_gist' relocatable = true trusted = true diff --git a/contrib/btree_gist/btree_inet.c b/contrib/btree_gist/btree_inet.c index 2fb952dc..1f519cfd 100644 --- a/contrib/btree_gist/btree_inet.c +++ b/contrib/btree_gist/btree_inet.c @@ -8,6 +8,7 @@ #include "catalog/pg_type.h" #include "utils/builtins.h" #include "utils/inet.h" +#include "utils/sortsupport.h" typedef struct inetkey { @@ -24,8 +25,15 @@ PG_FUNCTION_INFO_V1(gbt_inet_picksplit); PG_FUNCTION_INFO_V1(gbt_inet_consistent); PG_FUNCTION_INFO_V1(gbt_inet_penalty); PG_FUNCTION_INFO_V1(gbt_inet_same); +PG_FUNCTION_INFO_V1(gbt_inet_sortsupport); +static int +inet_fast_cmp(Datum x, Datum y, SortSupport ssup) +{ + return DatumGetInt32(DirectFunctionCall2(network_cmp, x, y)); +} + static bool gbt_inetgt(const void *a, const void *b, FmgrInfo *flinfo) { @@ -185,3 +193,14 @@ gbt_inet_same(PG_FUNCTION_ARGS) *result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo); PG_RETURN_POINTER(result); } + +Datum +gbt_inet_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = inet_fast_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} diff --git a/contrib/btree_gist/btree_interval.c b/contrib/btree_gist/btree_interval.c index b0afdf02..45c0c7a4 100644 --- a/contrib/btree_gist/btree_interval.c +++ b/contrib/btree_gist/btree_interval.c @@ -6,6 +6,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" #include "utils/builtins.h" +#include "utils/sortsupport.h" #include "utils/timestamp.h" typedef struct @@ -27,8 +28,15 @@ PG_FUNCTION_INFO_V1(gbt_intv_consistent); PG_FUNCTION_INFO_V1(gbt_intv_distance); PG_FUNCTION_INFO_V1(gbt_intv_penalty); PG_FUNCTION_INFO_V1(gbt_intv_same); +PG_FUNCTION_INFO_V1(gbt_intv_sortsupport); +static int +intv_fast_cmp(Datum x, Datum y, SortSupport ssup) +{ + return DatumGetInt32(DirectFunctionCall2(interval_cmp, x, y)); +} + static bool gbt_intvgt(const void *a, const void *b, FmgrInfo *flinfo) { @@ -295,3 +303,14 @@ gbt_intv_same(PG_FUNCTION_ARGS) *result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo); PG_RETURN_POINTER(result); } + +Datum +gbt_intv_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = intv_fast_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} diff --git a/contrib/btree_gist/btree_macaddr8.c b/contrib/btree_gist/btree_macaddr8.c index 796cc4ef..034e34bf 100644 --- a/contrib/btree_gist/btree_macaddr8.c +++ b/contrib/btree_gist/btree_macaddr8.c @@ -7,6 +7,7 @@ #include "btree_utils_num.h" #include "utils/builtins.h" #include "utils/inet.h" +#include "utils/sortsupport.h" typedef struct { @@ -25,8 +26,15 @@ PG_FUNCTION_INFO_V1(gbt_macad8_picksplit); PG_FUNCTION_INFO_V1(gbt_macad8_consistent); PG_FUNCTION_INFO_V1(gbt_macad8_penalty); PG_FUNCTION_INFO_V1(gbt_macad8_same); +PG_FUNCTION_INFO_V1(gbt_macad8_sortsupport); +static int +macaddr8_fast_cmp(Datum x, Datum y, SortSupport ssup) +{ + return DatumGetInt32(DirectFunctionCall2(macaddr8_cmp, x, y)); +} + static bool gbt_macad8gt(const void *a, const void *b, FmgrInfo *flinfo) { @@ -194,3 +202,14 @@ gbt_macad8_same(PG_FUNCTION_ARGS) *result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo); PG_RETURN_POINTER(result); } + +Datum +gbt_macad8_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = macaddr8_fast_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} diff --git a/contrib/btree_gist/btree_time.c b/contrib/btree_gist/btree_time.c index d89401c0..2b3249d7 100644 --- a/contrib/btree_gist/btree_time.c +++ b/contrib/btree_gist/btree_time.c @@ -7,6 +7,7 @@ #include "btree_utils_num.h" #include "utils/builtins.h" #include "utils/date.h" +#include "utils/sortsupport.h" #include "utils/timestamp.h" typedef struct @@ -28,6 +29,8 @@ PG_FUNCTION_INFO_V1(gbt_time_distance); PG_FUNCTION_INFO_V1(gbt_timetz_consistent); PG_FUNCTION_INFO_V1(gbt_time_penalty); PG_FUNCTION_INFO_V1(gbt_time_same); +PG_FUNCTION_INFO_V1(gbt_time_sortsupport); +PG_FUNCTION_INFO_V1(gbt_timetz_sortsupport); #ifdef USE_FLOAT8_BYVAL @@ -37,6 +40,19 @@ PG_FUNCTION_INFO_V1(gbt_time_same); #endif +static int +time_fast_cmp(Datum x, Datum y, SortSupport ssup) +{ + return DatumGetInt32(DirectFunctionCall2(time_cmp, x, y)); +} + +static int +timetz_fast_cmp(Datum x, Datum y, SortSupport ssup) +{ + return DatumGetInt32(DirectFunctionCall2(timetz_cmp, x, y)); +} + + static bool gbt_timegt(const void *a, const void *b, FmgrInfo *flinfo) { @@ -332,3 +348,25 @@ gbt_time_same(PG_FUNCTION_ARGS) *result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo); PG_RETURN_POINTER(result); } + +Datum +gbt_time_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = time_fast_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} + +Datum +gbt_timetz_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = timetz_fast_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} \ No newline at end of file diff --git a/contrib/btree_gist/meson.build b/contrib/btree_gist/meson.build index c88a6ac8..4b148ede 100644 --- a/contrib/btree_gist/meson.build +++ b/contrib/btree_gist/meson.build @@ -50,6 +50,7 @@ install_data( 'btree_gist--1.4--1.5.sql', 'btree_gist--1.5--1.6.sql', 'btree_gist--1.6--1.7.sql', + 'btree_gist--1.7--1.8.sql', kwargs: contrib_data_args, ) -- 2.34.1 ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PATCH] Add sortsupport for range types and btree_gist @ 2024-01-10 15:13 Andrey M. Borodin <[email protected]> parent: jian he <[email protected]> 2 siblings, 1 reply; 12+ messages in thread From: Andrey M. Borodin @ 2024-01-10 15:13 UTC (permalink / raw) To: jian he <[email protected]>; +Cc: Bernd Helmle <[email protected]>; [email protected] > On 10 Jan 2024, at 19:18, jian he <[email protected]> wrote: > > what I am confused: > In fmgr.h > > /* > * Support for cleaning up detoasted copies of inputs. This must only > * be used for pass-by-ref datatypes, and normally would only be used > * for toastable types. If the given pointer is different from the > * original argument, assume it's a palloc'd detoasted copy, and pfree it. > * NOTE: most functions on toastable types do not have to worry about this, > * but we currently require that support functions for indexes not leak > * memory. > */ > #define PG_FREE_IF_COPY(ptr,n) \ > do { \ > if ((Pointer) (ptr) != PG_GETARG_POINTER(n)) \ > pfree(ptr); \ > } while (0) > > but the doc (https://www.postgresql.org/docs/current/gist-extensibility.html) > says: > All the GiST support methods are normally called in short-lived memory > contexts; that is, CurrentMemoryContext will get reset after each > tuple is processed. It is therefore not very important to worry about > pfree'ing everything you palloc. > ENDOF_QUOTE > > so I am not sure in range_gist_cmp, we need the following: > ` > if ((Datum) range_a != a) > pfree(range_a); > if ((Datum) range_b != b) > pfree(range_b); > ` I think GiST sortsupport comments are more relevant, so there's no need for this pfree()s. Also, please check other thread, maybe you will find some useful code there [0,1]. It was committed[2] once, but reverted. Please make sure that corrections made there are taken into account in your patch. Thanks for working on this! Best regards, Andrey Borodin. [0] https://commitfest.postgresql.org/31/2824/ [1] https://www.postgresql.org/message-id/flat/285041639646332%40sas1-bf93f9015d57.qloud-c.yandex.net#0e... [2] https://github.com/postgres/postgres/commit/9f984ba6d23dc6eecebf479ab1d3f2e550a4e9be ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PATCH] Add sortsupport for range types and btree_gist @ 2024-01-10 16:35 Bernd Helmle <[email protected]> parent: jian he <[email protected]> 1 sibling, 0 replies; 12+ messages in thread From: Bernd Helmle @ 2024-01-10 16:35 UTC (permalink / raw) To: jian he <[email protected]>; [email protected]; +Cc: [email protected]; Christoph Berg <[email protected]> Am Mittwoch, dem 10.01.2024 um 08:00 +0800 schrieb jian he: > you do `CREATE INDEX ON pgbench_accounts USING gist(aid);` > but the original patch didn't change contrib/btree_gist/btree_int4.c > So I doubt your benchmark is related to the original patch. > or maybe I missed something. > The patch originally does this: +ALTER OPERATOR FAMILY gist_int4_ops USING gist ADD + FUNCTION 11 (int4, int4) btint4sortsupport (internal) ; This adds sortsupport function to int4 as well. We reuse existing btint4sortsupport() function, so no need to change btree_int4.c. > also per doc: > ` > sortsupport > Returns a comparator function to sort data in a way that preserves > locality. It is used by CREATE INDEX and REINDEX commands. The > quality > of the created index depends on how well the sort order determined by > the comparator function preserves locality of the inputs. > ` > from the doc, add sortsupport function will only influence index > build time? > Thats the point of this patch. Though it influences the index quality in a way which seems to cause the measured performance regression upthread. > > per https://www.postgresql.org/docs/current/gist-extensibility.html > QUOTE: > All the GiST support methods are normally called in short-lived > memory > contexts; that is, CurrentMemoryContext will get reset after each > tuple is processed. It is therefore not very important to worry about > pfree'ing everything you palloc. However, in some cases it's useful > for a support method to > ENDOF_QUOTE > > so removing the following part should be OK. > + if ((Datum) range_a != a) > + pfree(range_a); > + > + if ((Datum) range_b != b) > + pfree(range_b); > Probably, i think we get a different range objects in case of detoasting in this case. > comparison solely on the lower bounds looks strange to me. > if lower bound is the same, then compare upper bound, so the > range_gist_cmp function is consistent with function range_compare. > so following change: > > + else > + result = range_cmp_bounds(typcache, &lower1, &lower2); > to > ` > else > { > result = range_cmp_bounds(typcache, &lower1, &lower2); > if (result == 0) > result = range_cmp_bounds(typcache, &upper1, &upper2); > } > ` > > does contrib/btree_gist/btree_gist--1.7--1.8.sql function be declared > as strict ? (I am not sure) > other than that, the whole patch looks good. > > That's something surely to consider. ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PATCH] Add sortsupport for range types and btree_gist @ 2024-01-10 17:04 Bernd Helmle <[email protected]> parent: Andrey M. Borodin <[email protected]> 0 siblings, 0 replies; 12+ messages in thread From: Bernd Helmle @ 2024-01-10 17:04 UTC (permalink / raw) To: Andrey M. Borodin <[email protected]>; jian he <[email protected]>; +Cc: [email protected] Am Mittwoch, dem 10.01.2024 um 20:13 +0500 schrieb Andrey M. Borodin: > I think GiST sortsupport comments are more relevant, so there's no > need for this pfree()s. > > Also, please check other thread, maybe you will find some useful code > there [0,1]. It was committed[2] once, but reverted. Please make sure > that corrections made there are taken into account in your patch. > At least, i believe we have the same problem described here (many thanks Andrey for the links, i wasn't aware about this discussion): https://www.postgresql.org/message-id/98b34b51-a6db-acc4-1bcf-a29caf69bbc7%40iki.fi > Thanks for working on this! Absolutely. This patch needs input ... ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PATCH] Add sortsupport for range types and btree_gist @ 2024-01-26 13:01 vignesh C <[email protected]> parent: jian he <[email protected]> 2 siblings, 1 reply; 12+ messages in thread From: vignesh C @ 2024-01-26 13:01 UTC (permalink / raw) To: jian he <[email protected]>; +Cc: Bernd Helmle <[email protected]>; [email protected] On Wed, 10 Jan 2024 at 19:49, jian he <[email protected]> wrote: > > On Wed, Jan 10, 2024 at 8:00 AM jian he <[email protected]> wrote: > > > > ` > > from the doc, add sortsupport function will only influence index build time? > > > > +/* > > + * GiST sortsupport comparator for ranges. > > + * > > + * Operates solely on the lower bounds of the ranges, comparing them using > > + * range_cmp_bounds(). > > + * Empty ranges are sorted before non-empty ones. > > + */ > > +static int > > +range_gist_cmp(Datum a, Datum b, SortSupport ssup) > > +{ > > + RangeType *range_a = DatumGetRangeTypeP(a); > > + RangeType *range_b = DatumGetRangeTypeP(b); > > + TypeCacheEntry *typcache = ssup->ssup_extra; > > + RangeBound lower1, > > + lower2; > > + RangeBound upper1, > > + upper2; > > + bool empty1, > > + empty2; > > + int result; > > + > > + if (typcache == NULL) { > > + Assert(RangeTypeGetOid(range_a) == RangeTypeGetOid(range_b)); > > + typcache = lookup_type_cache(RangeTypeGetOid(range_a), TYPECACHE_RANGE_INFO); > > + > > + /* > > + * Cache the range info between calls to avoid having to call > > + * lookup_type_cache() for each comparison. > > + */ > > + ssup->ssup_extra = typcache; > > + } > > + > > + range_deserialize(typcache, range_a, &lower1, &upper1, &empty1); > > + range_deserialize(typcache, range_b, &lower2, &upper2, &empty2); > > + > > + /* For b-tree use, empty ranges sort before all else */ > > + if (empty1 && empty2) > > + result = 0; > > + else if (empty1) > > + result = -1; > > + else if (empty2) > > + result = 1; > > + else > > + result = range_cmp_bounds(typcache, &lower1, &lower2); > > + > > + if ((Datum) range_a != a) > > + pfree(range_a); > > + > > + if ((Datum) range_b != b) > > + pfree(range_b); > > + > > + return result; > > +} > > > > per https://www.postgresql.org/docs/current/gist-extensibility.html > > QUOTE: > > All the GiST support methods are normally called in short-lived memory > > contexts; that is, CurrentMemoryContext will get reset after each > > tuple is processed. It is therefore not very important to worry about > > pfree'ing everything you palloc. However, in some cases it's useful > > for a support method to > > ENDOF_QUOTE > > > > so removing the following part should be OK. > > + if ((Datum) range_a != a) > > + pfree(range_a); > > + > > + if ((Datum) range_b != b) > > + pfree(range_b); > > > > comparison solely on the lower bounds looks strange to me. > > if lower bound is the same, then compare upper bound, so the > > range_gist_cmp function is consistent with function range_compare. > > so following change: > > > > + else > > + result = range_cmp_bounds(typcache, &lower1, &lower2); > > to > > ` > > else > > { > > result = range_cmp_bounds(typcache, &lower1, &lower2); > > if (result == 0) > > result = range_cmp_bounds(typcache, &upper1, &upper2); > > } > > ` > > > > does contrib/btree_gist/btree_gist--1.7--1.8.sql function be declared > > as strict ? (I am not sure) > > other than that, the whole patch looks good. > > the original author email address ([email protected]) > Address not found. > so I don't include it. > > I split the original author's patch into 2. > 1. Add GiST sortsupport function for all the btree-gist module data > types except anyrange data type (which actually does not in this > module) > 2. Add GiST sortsupport function for anyrange data type. CFBot shows that the patch does not apply anymore as in [1]: === Applying patches on top of PostgreSQL commit ID 7014c9a4bba2d1b67d60687afb5b2091c1d07f73 === === applying patch ./v5-0001-Add-GIST-sortsupport-function-for-all-the-btree-g.patch patching file contrib/btree_gist/Makefile Hunk #1 FAILED at 33. 1 out of 1 hunk FAILED -- saving rejects to file contrib/btree_gist/Makefile.rej ... The next patch would create the file contrib/btree_gist/btree_gist--1.7--1.8.sql, which already exists! Applying it anyway. patching file contrib/btree_gist/btree_gist--1.7--1.8.sql Hunk #1 FAILED at 1. 1 out of 1 hunk FAILED -- saving rejects to file contrib/btree_gist/btree_gist--1.7--1.8.sql.rej patching file contrib/btree_gist/btree_gist.control Hunk #1 FAILED at 1. 1 out of 1 hunk FAILED -- saving rejects to file contrib/btree_gist/btree_gist.control.rej ... patching file contrib/btree_gist/meson.build Hunk #1 FAILED at 50. 1 out of 1 hunk FAILED -- saving rejects to file contrib/btree_gist/meson.build.rej Please post an updated version for the same. [1] - http://cfbot.cputube.org/patch_46_3686.log Regards, Vignesh ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PATCH] Add sortsupport for range types and btree_gist @ 2024-01-26 18:22 Bernd Helmle <[email protected]> parent: vignesh C <[email protected]> 0 siblings, 0 replies; 12+ messages in thread From: Bernd Helmle @ 2024-01-26 18:22 UTC (permalink / raw) To: vignesh C <[email protected]>; jian he <[email protected]>; +Cc: [email protected] Am Freitag, dem 26.01.2024 um 18:31 +0530 schrieb vignesh C: > CFBot shows that the patch does not apply anymore as in [1]: > === Applying patches on top of PostgreSQL commit ID I've started working on it and planning to submit a polished patch for the upcoming CF. ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PATCH] Add sortsupport for range types and btree_gist @ 2024-02-08 18:14 Bernd Helmle <[email protected]> parent: jian he <[email protected]> 2 siblings, 1 reply; 12+ messages in thread From: Bernd Helmle @ 2024-02-08 18:14 UTC (permalink / raw) To: jian he <[email protected]>; +Cc: [email protected] Am Mittwoch, dem 10.01.2024 um 22:18 +0800 schrieb jian he: > > I split the original author's patch into 2. > 1. Add GiST sortsupport function for all the btree-gist module data > types except anyrange data type (which actually does not in this > module) > 2. Add GiST sortsupport function for anyrange data type. > Please find attached a new version of this patch set with the following changes/adjustments: - Rebased to current master - Heavily reworked *_cmp() functions to properly decode GPT_VARKEY and GBT_KEY input. For some datatypes the btree comparison functions were reused and the input arguments not properly handled. This patch adds dedicated btree_gist sortsupport comparison methods for all datatypes. There was another patch from Andrey Borodin (thanks again for the hint) and a deeper review done by Heikki in [1]. I've incorporated Heikkis findings in this patch, too. [...] I've also updated the btree_gist documentation to reflect the default sorted built strategy this patch introduces now. Additionally i did some benchmarks again on this new version on the patch. Still, index build speed improvement is quite impressive on the dataset originally provided by Christoph Heiss (since its not available anymore i've uploaded it here [2] again): HEAD (Index was built with default buffering setting) --------------------- REINDEX (s) 4809 CREATE INDEX (s) 4920 btree_gist sortsupport ---------------------- REINDEX (s) 573 CREATE INDEX (s) 578 I created another pgbench based custom script to measure the single core speed of the lookup query of the bench-gist.py script. This looks like this: init.sql -------- BEGIN; DROP TABLE IF EXISTS test_dataset; CREATE TABLE test_dataset(keyid integer not null, id text not null, block_range int4range); CREATE TEMP SEQUENCE testset_seq; INSERT INTO test_dataset SELECT nextval('testset_seq'), id, block_range FROM test ORDER BY random() LIMIT 10000; CREATE UNIQUE INDEX ON test_dataset(keyid); COMMIT; bench.pgbench ------------- \set keyid random(1, 10000) SELECT id, block_range FROM test_dataset WHERE keyid = :keyid \gset SELECT id, block_range FROM test WHERE id = ':id' AND block_range && ':block_range'; Run by for in in `seq 1 3`; do psql -qXf init.pgbench && pgbench -n -r -c 1 -T 60 -f bench.pgbench; done With this i get the following (on prewarmed index and table): HEAD ------------------------------------- pgbench single core tps=248,67 btree_gist sortsupport ---------------------------- pgbench single core tps=1830,33 This is an average over 3 runs each (complete results attached). So this looks really impressive and i hope i didn't do something entirely wrong (still learning about this GiST stuff). > what I am confused: > In fmgr.h > > /* > * Support for cleaning up detoasted copies of inputs. This must > only > * be used for pass-by-ref datatypes, and normally would only be used > * for toastable types. If the given pointer is different from the > * original argument, assume it's a palloc'd detoasted copy, and > pfree it. > * NOTE: most functions on toastable types do not have to worry about > this, > * but we currently require that support functions for indexes not > leak > * memory. > */ > #define PG_FREE_IF_COPY(ptr,n) \ > do { \ > if ((Pointer) (ptr) != PG_GETARG_POINTER(n)) \ > pfree(ptr); \ > } while (0) > > but the doc > (https://www.postgresql.org/docs/current/gist-extensibility.html) > says: > All the GiST support methods are normally called in short-lived > memory > contexts; that is, CurrentMemoryContext will get reset after each > tuple is processed. It is therefore not very important to worry about > pfree'ing everything you palloc. > ENDOF_QUOTE > > so I am not sure in range_gist_cmp, we need the following: > ` > if ((Datum) range_a != a) > pfree(range_a); > if ((Datum) range_b != b) > pfree(range_b); > ` Turns out this is not true for sortsupport: the comparison function is called for each tuple during sorting, which will leak the detoasted (and probably copied datum) in the sort memory context. See the same for e.g. numeric and text, which i needed to change to pass the key values correctly to the text_cmp() or numeric_cmp() function in these cases. I've adapted the PG_FREE_IF_COPY() macro for these functions and introduced GBT_FREE_IF_COPY() in btree_utils_var.h, since the former relies on fcinfo. I'll add the patch again to the upcoming CF for another review round. [1] https://www.postgresql.org/message-id/c0846e34-8b3a-e1bf-c88e-021eb241a481%40iki.fi [2] https://drive.google.com/file/d/1CPNFGR53-FUto1zjXPMM2Yrn0GaGfGFz/view?usp=drive_link HEAD ---------------------- pgbench (17devel) transaction type: bench.pgbench scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 15192 number of failed transactions: 0 (0.000%) latency average = 3.965 ms initial connection time = 2.786 ms tps = 252.198398 (without initial connection time) statement latencies in milliseconds and failures: 0.001 0 \set keyid random(1, 10000) 0.151 0 SELECT id, block_range FROM test_dataset WHERE keyid = :keyid 3.814 0 SELECT id, block_range FROM test WHERE id = ':id' AND block_range && ':block_range'; pgbench (17devel) transaction type: bench.pgbench scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 14854 number of failed transactions: 0 (0.000%) latency average = 4.040 ms initial connection time = 2.160 ms tps = 247.550345 (without initial connection time) statement latencies in milliseconds and failures: 0.001 0 \set keyid random(1, 10000) 0.153 0 SELECT id, block_range FROM test_dataset WHERE keyid = :keyid 3.886 0 SELECT id, block_range FROM test WHERE id = ':id' AND block_range && ':block_range'; pgbench (17devel) transaction type: bench.pgbench scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 14860 number of failed transactions: 0 (0.000%) latency average = 4.039 ms initial connection time = 2.714 ms tps = 247.580707 (without initial connection time) statement latencies in milliseconds and failures: 0.001 0 \set keyid random(1, 10000) 0.151 0 SELECT id, block_range FROM test_dataset WHERE keyid = :keyid 3.887 0 SELECT id, block_range FROM test WHERE id = ':id' AND block_range && ':block_range'; btree_gist sortsupport ---------------------- pgbench (17devel) transaction type: bench.pgbench scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 108857 number of failed transactions: 0 (0.000%) latency average = 0.551 ms initial connection time = 2.559 ms tps = 1814.357057 (without initial connection time) statement latencies in milliseconds and failures: 0.000 0 \set keyid random(1, 10000) 0.143 0 SELECT id, block_range FROM test_dataset WHERE keyid = :keyid 0.408 0 SELECT id, block_range FROM test WHERE id = ':id' AND block_range && ':block_range'; pgbench (17devel) transaction type: bench.pgbench scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 109234 number of failed transactions: 0 (0.000%) latency average = 0.549 ms initial connection time = 2.141 ms tps = 1820.620739 (without initial connection time) statement latencies in milliseconds and failures: 0.000 0 \set keyid random(1, 10000) 0.142 0 SELECT id, block_range FROM test_dataset WHERE keyid = :keyid 0.406 0 SELECT id, block_range FROM test WHERE id = ':id' AND block_range && ':block_range'; pgbench (17devel) transaction type: bench.pgbench scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 111429 number of failed transactions: 0 (0.000%) latency average = 0.538 ms initial connection time = 2.397 ms tps = 1857.207357 (without initial connection time) statement latencies in milliseconds and failures: 0.000 0 \set keyid random(1, 10000) 0.140 0 SELECT id, block_range FROM test_dataset WHERE keyid = :keyid 0.397 0 SELECT id, block_range FROM test WHERE id = ':id' AND block_range && ':block_range'; Attachments: [text/plain] bench.pgbench.results.txt (4.1K, ../../[email protected]/2-bench.pgbench.results.txt) download | inline: HEAD ---------------------- pgbench (17devel) transaction type: bench.pgbench scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 15192 number of failed transactions: 0 (0.000%) latency average = 3.965 ms initial connection time = 2.786 ms tps = 252.198398 (without initial connection time) statement latencies in milliseconds and failures: 0.001 0 \set keyid random(1, 10000) 0.151 0 SELECT id, block_range FROM test_dataset WHERE keyid = :keyid 3.814 0 SELECT id, block_range FROM test WHERE id = ':id' AND block_range && ':block_range'; pgbench (17devel) transaction type: bench.pgbench scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 14854 number of failed transactions: 0 (0.000%) latency average = 4.040 ms initial connection time = 2.160 ms tps = 247.550345 (without initial connection time) statement latencies in milliseconds and failures: 0.001 0 \set keyid random(1, 10000) 0.153 0 SELECT id, block_range FROM test_dataset WHERE keyid = :keyid 3.886 0 SELECT id, block_range FROM test WHERE id = ':id' AND block_range && ':block_range'; pgbench (17devel) transaction type: bench.pgbench scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 14860 number of failed transactions: 0 (0.000%) latency average = 4.039 ms initial connection time = 2.714 ms tps = 247.580707 (without initial connection time) statement latencies in milliseconds and failures: 0.001 0 \set keyid random(1, 10000) 0.151 0 SELECT id, block_range FROM test_dataset WHERE keyid = :keyid 3.887 0 SELECT id, block_range FROM test WHERE id = ':id' AND block_range && ':block_range'; btree_gist sortsupport ---------------------- pgbench (17devel) transaction type: bench.pgbench scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 108857 number of failed transactions: 0 (0.000%) latency average = 0.551 ms initial connection time = 2.559 ms tps = 1814.357057 (without initial connection time) statement latencies in milliseconds and failures: 0.000 0 \set keyid random(1, 10000) 0.143 0 SELECT id, block_range FROM test_dataset WHERE keyid = :keyid 0.408 0 SELECT id, block_range FROM test WHERE id = ':id' AND block_range && ':block_range'; pgbench (17devel) transaction type: bench.pgbench scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 109234 number of failed transactions: 0 (0.000%) latency average = 0.549 ms initial connection time = 2.141 ms tps = 1820.620739 (without initial connection time) statement latencies in milliseconds and failures: 0.000 0 \set keyid random(1, 10000) 0.142 0 SELECT id, block_range FROM test_dataset WHERE keyid = :keyid 0.406 0 SELECT id, block_range FROM test WHERE id = ':id' AND block_range && ':block_range'; pgbench (17devel) transaction type: bench.pgbench scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 111429 number of failed transactions: 0 (0.000%) latency average = 0.538 ms initial connection time = 2.397 ms tps = 1857.207357 (without initial connection time) statement latencies in milliseconds and failures: 0.000 0 \set keyid random(1, 10000) 0.140 0 SELECT id, block_range FROM test_dataset WHERE keyid = :keyid 0.397 0 SELECT id, block_range FROM test WHERE id = ':id' AND block_range && ':block_range'; [text/x-patch] v6-Add-GIST-sortsupport-btree-gist.patch (38.3K, ../../[email protected]/3-v6-Add-GIST-sortsupport-btree-gist.patch) download | inline diff: diff --git a/contrib/btree_gist/btree_bit.c b/contrib/btree_gist/btree_bit.c index 6790f22b4b6..67ea5d9e182 100644 --- a/contrib/btree_gist/btree_bit.c +++ b/contrib/btree_gist/btree_bit.c @@ -6,7 +6,7 @@ #include "btree_gist.h" #include "btree_utils_var.h" #include "utils/builtins.h" -#include "utils/bytea.h" +#include "utils/sortsupport.h" #include "utils/varbit.h" @@ -19,10 +19,33 @@ PG_FUNCTION_INFO_V1(gbt_bit_picksplit); PG_FUNCTION_INFO_V1(gbt_bit_consistent); PG_FUNCTION_INFO_V1(gbt_bit_penalty); PG_FUNCTION_INFO_V1(gbt_bit_same); +PG_FUNCTION_INFO_V1(gbt_bit_sortsupport); +PG_FUNCTION_INFO_V1(gbt_varbit_sortsupport); /* define for comparison */ +static int +gbt_bit_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + GBT_VARKEY *key1 = PG_DETOAST_DATUM(x); + GBT_VARKEY *key2 = PG_DETOAST_DATUM(y); + + GBT_VARKEY_R arg1 = gbt_var_key_readable(key1); + GBT_VARKEY_R arg2 = gbt_var_key_readable(key2); + Datum result; + + /* lower is always equal to upper, so just compare those fields */ + result = DirectFunctionCall2(bitcmp, + PointerGetDatum(arg1.lower), + PointerGetDatum(arg2.lower)); + + GBT_FREE_IF_COPY(key1, x); + GBT_FREE_IF_COPY(key2, y); + + return DatumGetInt32(result); +} + static bool gbt_bitgt(const void *a, const void *b, Oid collation, FmgrInfo *flinfo) { @@ -208,3 +231,25 @@ gbt_bit_penalty(PG_FUNCTION_ARGS) PG_RETURN_POINTER(gbt_var_penalty(result, o, n, PG_GET_COLLATION(), &tinfo, fcinfo->flinfo)); } + +Datum +gbt_bit_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_bit_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} + +Datum +gbt_varbit_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_bit_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} \ No newline at end of file diff --git a/contrib/btree_gist/btree_bool.c b/contrib/btree_gist/btree_bool.c index 8b2af129b52..5058dba2986 100644 --- a/contrib/btree_gist/btree_bool.c +++ b/contrib/btree_gist/btree_bool.c @@ -6,6 +6,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" #include "common/int.h" +#include "utils/sortsupport.h" typedef struct boolkey { @@ -23,6 +24,16 @@ PG_FUNCTION_INFO_V1(gbt_bool_picksplit); PG_FUNCTION_INFO_V1(gbt_bool_consistent); PG_FUNCTION_INFO_V1(gbt_bool_penalty); PG_FUNCTION_INFO_V1(gbt_bool_same); +PG_FUNCTION_INFO_V1(gbt_bool_sortsupport); + +static int +gbt_bool_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + boolKEY *arg1 = (boolKEY *) DatumGetPointer(x); + boolKEY *arg2 = (boolKEY *) DatumGetPointer(y); + + return arg1->lower - arg2->lower; +} static bool gbt_boolgt(const void *a, const void *b, FmgrInfo *flinfo) @@ -167,3 +178,14 @@ gbt_bool_same(PG_FUNCTION_ARGS) *result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo); PG_RETURN_POINTER(result); } + +Datum +gbt_bool_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_bool_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} diff --git a/contrib/btree_gist/btree_bytea.c b/contrib/btree_gist/btree_bytea.c index 6b005f0157e..2047e3ddee0 100644 --- a/contrib/btree_gist/btree_bytea.c +++ b/contrib/btree_gist/btree_bytea.c @@ -6,7 +6,7 @@ #include "btree_gist.h" #include "btree_utils_var.h" #include "utils/builtins.h" -#include "utils/bytea.h" +#include "utils/sortsupport.h" /* @@ -18,7 +18,41 @@ PG_FUNCTION_INFO_V1(gbt_bytea_picksplit); PG_FUNCTION_INFO_V1(gbt_bytea_consistent); PG_FUNCTION_INFO_V1(gbt_bytea_penalty); PG_FUNCTION_INFO_V1(gbt_bytea_same); +PG_FUNCTION_INFO_V1(gbt_bytea_sortsupport); +/* sortsupport support */ + +static int +gbt_bytea_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + GBT_VARKEY *key1 = PG_DETOAST_DATUM(x); + GBT_VARKEY *key2 = PG_DETOAST_DATUM(y); + + GBT_VARKEY_R xkey = gbt_var_key_readable(key1); + GBT_VARKEY_R ykey = gbt_var_key_readable(key2); + Datum result; + + /* lower and upper are always the same, so it is enough to compare lower */ + result = DirectFunctionCall2(byteacmp, + PointerGetDatum(xkey.lower), + PointerGetDatum(ykey.lower)); + + GBT_FREE_IF_COPY(key1, x); + GBT_FREE_IF_COPY(key2, y); + + return DatumGetInt32(result); +} + +Datum +gbt_bytea_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_bytea_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} /* define for comparison */ diff --git a/contrib/btree_gist/btree_cash.c b/contrib/btree_gist/btree_cash.c index 546b948ea40..94090c9ac0d 100644 --- a/contrib/btree_gist/btree_cash.c +++ b/contrib/btree_gist/btree_cash.c @@ -7,6 +7,7 @@ #include "btree_utils_num.h" #include "common/int.h" #include "utils/cash.h" +#include "utils/sortsupport.h" typedef struct { @@ -25,6 +26,21 @@ PG_FUNCTION_INFO_V1(gbt_cash_consistent); PG_FUNCTION_INFO_V1(gbt_cash_distance); PG_FUNCTION_INFO_V1(gbt_cash_penalty); PG_FUNCTION_INFO_V1(gbt_cash_same); +PG_FUNCTION_INFO_V1(gbt_cash_sortsupport); + +extern Datum cash_cmp(PG_FUNCTION_ARGS); + +static int +gbt_cash_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + cashKEY *arg1 = (cashKEY *) DatumGetPointer(x); + cashKEY *arg2 = (cashKEY *) DatumGetPointer(y); + + /* Since lower and upper are always equal, it is enough to compare lower */ + return DatumGetInt32(DirectFunctionCall2(cash_cmp, + CashGetDatum(arg1->lower), + CashGetDatum(arg2->lower))); +} static bool gbt_cashgt(const void *a, const void *b, FmgrInfo *flinfo) @@ -215,3 +231,14 @@ gbt_cash_same(PG_FUNCTION_ARGS) *result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo); PG_RETURN_POINTER(result); } + +Datum +gbt_cash_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_cash_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} diff --git a/contrib/btree_gist/btree_date.c b/contrib/btree_gist/btree_date.c index 68a4107dbf0..c4ff15f27b4 100644 --- a/contrib/btree_gist/btree_date.c +++ b/contrib/btree_gist/btree_date.c @@ -6,6 +6,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" #include "utils/builtins.h" +#include "utils/sortsupport.h" #include "utils/date.h" typedef struct @@ -25,6 +26,30 @@ PG_FUNCTION_INFO_V1(gbt_date_consistent); PG_FUNCTION_INFO_V1(gbt_date_distance); PG_FUNCTION_INFO_V1(gbt_date_penalty); PG_FUNCTION_INFO_V1(gbt_date_same); +PG_FUNCTION_INFO_V1(gbt_date_sortsupport); + +/* sortsupport functions */ + +static int gbt_date_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + dateKEY *akey = (dateKEY *) DatumGetPointer(x); + dateKEY *bkey = (dateKEY *) DatumGetPointer(y); + + return DatumGetInt32(DirectFunctionCall2(date_cmp, + DateADTGetDatum(akey->lower), + DateADTGetDatum(bkey->lower))); +} + +Datum +gbt_date_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_date_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} static bool gbt_dategt(const void *a, const void *b, FmgrInfo *flinfo) diff --git a/contrib/btree_gist/btree_enum.c b/contrib/btree_gist/btree_enum.c index d4dc38a38e5..67395182341 100644 --- a/contrib/btree_gist/btree_enum.c +++ b/contrib/btree_gist/btree_enum.c @@ -7,6 +7,7 @@ #include "btree_utils_num.h" #include "fmgr.h" #include "utils/builtins.h" +#include "utils/sortsupport.h" /* enums are really Oids, so we just use the same structure */ @@ -26,8 +27,20 @@ PG_FUNCTION_INFO_V1(gbt_enum_picksplit); PG_FUNCTION_INFO_V1(gbt_enum_consistent); PG_FUNCTION_INFO_V1(gbt_enum_penalty); PG_FUNCTION_INFO_V1(gbt_enum_same); +PG_FUNCTION_INFO_V1(gbt_enum_sortsupport); +static int +gbt_enum_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + oidKEY *arg1 = (oidKEY *) DatumGetPointer(x); + oidKEY *arg2 = (oidKEY *) DatumGetPointer(y); + + /* Since lower and upper in oidKEY are always the same, just compare lower */ + return DatumGetInt32(CallerFInfoFunctionCall2(enum_cmp, ssup->ssup_extra, + InvalidOid, arg1->lower, arg2->lower)); +} + static bool gbt_enumgt(const void *a, const void *b, FmgrInfo *flinfo) { @@ -183,3 +196,20 @@ gbt_enum_same(PG_FUNCTION_ARGS) *result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo); PG_RETURN_POINTER(result); } + +Datum +gbt_enum_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_enum_ssup_cmp; + + /* + * Since enum_fast_cmp() also uses enum_cmp() like the rest of the + * comparsion functions, it also needs to pass on flinfo when calling + * it. Thus save it in ->ssup_extra and later retrieve it in enum_fast_cmp(). + */ + ssup->ssup_extra = fcinfo->flinfo; + + PG_RETURN_VOID(); +} diff --git a/contrib/btree_gist/btree_float4.c b/contrib/btree_gist/btree_float4.c index 84ca5eee501..b2229cdb04d 100644 --- a/contrib/btree_gist/btree_float4.c +++ b/contrib/btree_gist/btree_float4.c @@ -5,6 +5,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" +#include "utils/sortsupport.h" #include "utils/float.h" typedef struct float4key @@ -24,6 +25,33 @@ PG_FUNCTION_INFO_V1(gbt_float4_consistent); PG_FUNCTION_INFO_V1(gbt_float4_distance); PG_FUNCTION_INFO_V1(gbt_float4_penalty); PG_FUNCTION_INFO_V1(gbt_float4_same); +PG_FUNCTION_INFO_V1(gbt_float4_sortsupport); + +extern Datum btfloat4cmp(PG_FUNCTION_ARGS); + +/* sortsupport functions */ +static int +gbt_float4_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + float4KEY *arg1 = (float4KEY *) DatumGetPointer(x); + float4KEY *arg2 = (float4KEY *) DatumGetPointer(y); + + /* Since lower and upper for float4KEYs here are always equal it is okay to compare them only */ + return DatumGetInt32(DirectFunctionCall2(btfloat4cmp, + Float4GetDatum(arg1->lower), + Float4GetDatum(arg2->lower))); +} + +Datum +gbt_float4_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_float4_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} static bool gbt_float4gt(const void *a, const void *b, FmgrInfo *flinfo) diff --git a/contrib/btree_gist/btree_float8.c b/contrib/btree_gist/btree_float8.c index 081a719b006..2f312f5bd83 100644 --- a/contrib/btree_gist/btree_float8.c +++ b/contrib/btree_gist/btree_float8.c @@ -5,6 +5,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" +#include "utils/sortsupport.h" #include "utils/float.h" typedef struct float8key @@ -24,7 +25,33 @@ PG_FUNCTION_INFO_V1(gbt_float8_consistent); PG_FUNCTION_INFO_V1(gbt_float8_distance); PG_FUNCTION_INFO_V1(gbt_float8_penalty); PG_FUNCTION_INFO_V1(gbt_float8_same); +PG_FUNCTION_INFO_V1(gbt_float8_sortsupport); +extern Datum btfloat8cmp(PG_FUNCTION_ARGS); + +/* sortsupport functions */ +static int +gbt_float8_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + float8KEY *arg1 = (float8KEY *) DatumGetPointer(x); + float8KEY *arg2 = (float8KEY *) DatumGetPointer(y); + + /* upper and lower for input keys are equal here */ + return DatumGetInt32(DirectFunctionCall2(btfloat8cmp, + Float8GetDatum(arg1->lower), + Float8GetDatum(arg2->lower))); +} + +Datum +gbt_float8_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_float8_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} static bool gbt_float8gt(const void *a, const void *b, FmgrInfo *flinfo) diff --git a/contrib/btree_gist/btree_gist--1.7--1.8.sql b/contrib/btree_gist/btree_gist--1.7--1.8.sql index 307bfe574b0..657285ca7ce 100644 --- a/contrib/btree_gist/btree_gist--1.7--1.8.sql +++ b/contrib/btree_gist/btree_gist--1.7--1.8.sql @@ -85,3 +85,4 @@ ALTER OPERATOR FAMILY gist_enum_ops USING gist ADD ALTER OPERATOR FAMILY gist_bool_ops USING gist ADD FUNCTION 12 (bool, bool) gist_stratnum_btree (int2) ; + diff --git a/contrib/btree_gist/btree_gist--1.8--1.9.sql b/contrib/btree_gist/btree_gist--1.8--1.9.sql new file mode 100644 index 00000000000..c52e750ed1a --- /dev/null +++ b/contrib/btree_gist/btree_gist--1.8--1.9.sql @@ -0,0 +1,192 @@ +/* contrib/btree_gist/btree_gist--1.7--1.8.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "ALTER EXTENSION btree_gist UPDATE TO '1.9'" to load this file. \quit + +CREATE FUNCTION gbt_bit_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_varbit_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_bool_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_cash_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_enum_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_inet_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_intv_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_macad8_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_time_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_int2_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_int4_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_int8_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_bytea_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_date_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_float4_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_float8_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_macaddr_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_numeric_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_oid_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_text_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_ts_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_uuid_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +ALTER OPERATOR FAMILY gist_vbit_ops USING gist ADD + FUNCTION 11 (varbit, varbit) gbt_varbit_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_bit_ops USING gist ADD + FUNCTION 11 (bit, bit) gbt_bit_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_bool_ops USING gist ADD + FUNCTION 11 (bool, bool) gbt_bool_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_bytea_ops USING gist ADD + FUNCTION 11 (bytea, bytea) gbt_bytea_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_cash_ops USING gist ADD + FUNCTION 11 (money, money) gbt_cash_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_date_ops USING gist ADD + FUNCTION 11 (date, date) gbt_date_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_enum_ops USING gist ADD + FUNCTION 11 (anyenum, anyenum) gbt_enum_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_float4_ops USING gist ADD + FUNCTION 11 (float4, float4) gbt_float4_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_float8_ops USING gist ADD + FUNCTION 11 (float8, float8) gbt_float8_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_inet_ops USING gist ADD + FUNCTION 11 (inet, inet) gbt_inet_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_cidr_ops USING gist ADD + FUNCTION 11 (inet, inet) gbt_inet_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_int2_ops USING gist ADD + FUNCTION 11 (int2, int2) gbt_int2_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_int4_ops USING gist ADD + FUNCTION 11 (int4, int4) gbt_int4_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_int8_ops USING gist ADD + FUNCTION 11 (int8, int8) gbt_int8_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_interval_ops USING gist ADD + FUNCTION 11 (interval, interval) gbt_intv_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_macaddr_ops USING gist ADD + FUNCTION 11 (macaddr, macaddr) gbt_macad8_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_macaddr8_ops USING gist ADD + FUNCTION 11 (macaddr8, macaddr8) gbt_macad8_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_numeric_ops USING gist ADD + FUNCTION 11 (numeric, numeric) gbt_numeric_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_oid_ops USING gist ADD + FUNCTION 11 (oid, oid) gbt_oid_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_text_ops USING gist ADD + FUNCTION 11 (text, text) gbt_text_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_bpchar_ops USING gist ADD + FUNCTION 11 (bpchar, bpchar) bpchar_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_time_ops USING gist ADD + FUNCTION 11 (time, time) gbt_time_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_timestamp_ops USING gist ADD + FUNCTION 11 (timestamp, timestamp) gbt_ts_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_timestamptz_ops USING gist ADD + FUNCTION 11 (timestamptz, timestamptz) gbt_ts_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_timetz_ops USING gist ADD + FUNCTION 11 (timetz, timetz) gbt_time_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_uuid_ops USING gist ADD + FUNCTION 11 (uuid, uuid) gbt_uuid_sortsupport (internal) ; diff --git a/contrib/btree_gist/btree_gist.c b/contrib/btree_gist/btree_gist.c index 5fd4cce27d0..c8acbe76b55 100644 --- a/contrib/btree_gist/btree_gist.c +++ b/contrib/btree_gist/btree_gist.c @@ -6,6 +6,9 @@ #include "access/stratnum.h" #include "utils/builtins.h" +#include "access/gist.h" +#include "btree_gist.h" + PG_MODULE_MAGIC; PG_FUNCTION_INFO_V1(gbt_decompress); diff --git a/contrib/btree_gist/btree_gist.control b/contrib/btree_gist/btree_gist.control index abf66538f32..69d9341a0ad 100644 --- a/contrib/btree_gist/btree_gist.control +++ b/contrib/btree_gist/btree_gist.control @@ -1,6 +1,6 @@ # btree_gist extension comment = 'support for indexing common datatypes in GiST' -default_version = '1.8' +default_version = '1.9' module_pathname = '$libdir/btree_gist' relocatable = true trusted = true diff --git a/contrib/btree_gist/btree_gist.h b/contrib/btree_gist/btree_gist.h index 0db8522c8a7..245c3559281 100644 --- a/contrib/btree_gist/btree_gist.h +++ b/contrib/btree_gist/btree_gist.h @@ -9,6 +9,12 @@ #define BtreeGistNotEqualStrategyNumber 6 +typedef struct int32key +{ + int32 lower; + int32 upper; +} int32KEY; + /* indexed types */ enum gbtree_type diff --git a/contrib/btree_gist/btree_inet.c b/contrib/btree_gist/btree_inet.c index 2fb952dca83..77ef14a4f7d 100644 --- a/contrib/btree_gist/btree_inet.c +++ b/contrib/btree_gist/btree_inet.c @@ -8,6 +8,7 @@ #include "catalog/pg_type.h" #include "utils/builtins.h" #include "utils/inet.h" +#include "utils/sortsupport.h" typedef struct inetkey { @@ -24,8 +25,23 @@ PG_FUNCTION_INFO_V1(gbt_inet_picksplit); PG_FUNCTION_INFO_V1(gbt_inet_consistent); PG_FUNCTION_INFO_V1(gbt_inet_penalty); PG_FUNCTION_INFO_V1(gbt_inet_same); +PG_FUNCTION_INFO_V1(gbt_inet_sortsupport); +static int +gbt_inet_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + inetKEY *arg1 = (inetKEY *) DatumGetPointer(x); + inetKEY *arg2 = (inetKEY *) DatumGetPointer(y); + + if (arg1->lower == arg2->lower) + return 0; + else if (arg1->lower > arg2->lower) + return 1; + else + return -1; +} + static bool gbt_inetgt(const void *a, const void *b, FmgrInfo *flinfo) { @@ -185,3 +201,14 @@ gbt_inet_same(PG_FUNCTION_ARGS) *result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo); PG_RETURN_POINTER(result); } + +Datum +gbt_inet_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_inet_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} diff --git a/contrib/btree_gist/btree_int2.c b/contrib/btree_gist/btree_int2.c index fdbf156586c..b19770ff67f 100644 --- a/contrib/btree_gist/btree_int2.c +++ b/contrib/btree_gist/btree_int2.c @@ -5,6 +5,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" +#include "utils/sortsupport.h" #include "common/int.h" typedef struct int16key @@ -24,6 +25,31 @@ PG_FUNCTION_INFO_V1(gbt_int2_consistent); PG_FUNCTION_INFO_V1(gbt_int2_distance); PG_FUNCTION_INFO_V1(gbt_int2_penalty); PG_FUNCTION_INFO_V1(gbt_int2_same); +PG_FUNCTION_INFO_V1(gbt_int2_sortsupport); + +/* sortsupport functions */ +static int +gbt_int2_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + int16KEY *arg1 = (int16KEY *) DatumGetPointer(x); + int16KEY *arg2 = (int16KEY *) DatumGetPointer(y); + + if (arg1->lower < arg2->lower) + return -1; + else if (arg1->lower > arg2->lower) + return 1; + else + return 0; +} + +Datum +gbt_int2_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_int2_ssup_cmp; + PG_RETURN_VOID(); +} static bool gbt_int2gt(const void *a, const void *b, FmgrInfo *flinfo) diff --git a/contrib/btree_gist/btree_int4.c b/contrib/btree_gist/btree_int4.c index 8915fb5d087..60418511ede 100644 --- a/contrib/btree_gist/btree_int4.c +++ b/contrib/btree_gist/btree_int4.c @@ -2,16 +2,10 @@ * contrib/btree_gist/btree_int4.c */ #include "postgres.h" - +#include "common/int.h" +#include "utils/sortsupport.h" #include "btree_gist.h" #include "btree_utils_num.h" -#include "common/int.h" - -typedef struct int32key -{ - int32 lower; - int32 upper; -} int32KEY; /* ** int32 ops @@ -24,6 +18,7 @@ PG_FUNCTION_INFO_V1(gbt_int4_consistent); PG_FUNCTION_INFO_V1(gbt_int4_distance); PG_FUNCTION_INFO_V1(gbt_int4_penalty); PG_FUNCTION_INFO_V1(gbt_int4_same); +PG_FUNCTION_INFO_V1(gbt_int4_sortsupport); static bool @@ -90,6 +85,29 @@ static const gbtree_ninfo tinfo = gbt_int4_dist }; +static int +gbt_int4_ssup_cmp(Datum a, Datum b, SortSupport ssup) +{ + int32KEY *ia = (int32KEY *) DatumGetPointer(a); + int32KEY *ib = (int32KEY *) DatumGetPointer(b); + + /* int4KEY upper and lower are always the same for input keys here. */ + if (ia->lower < ib->lower) + return -1; + else if (ia->lower > ib->lower) + return 1; + else + return 0; +} + +Datum +gbt_int4_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_int4_ssup_cmp; + PG_RETURN_VOID(); +} PG_FUNCTION_INFO_V1(int4_dist); Datum diff --git a/contrib/btree_gist/btree_int8.c b/contrib/btree_gist/btree_int8.c index 7c63a5b6dc1..00d55163fc9 100644 --- a/contrib/btree_gist/btree_int8.c +++ b/contrib/btree_gist/btree_int8.c @@ -6,6 +6,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" #include "common/int.h" +#include "utils/sortsupport.h" typedef struct int64key { @@ -24,7 +25,32 @@ PG_FUNCTION_INFO_V1(gbt_int8_consistent); PG_FUNCTION_INFO_V1(gbt_int8_distance); PG_FUNCTION_INFO_V1(gbt_int8_penalty); PG_FUNCTION_INFO_V1(gbt_int8_same); +PG_FUNCTION_INFO_V1(gbt_int8_sortsupport); +/* sortsupport functions */ +static int +gbt_int8_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + int64KEY *arg1 = (int64KEY *) DatumGetPointer(x); + int64KEY *arg2 = (int64KEY *) DatumGetPointer(y); + + if (arg1->lower < arg2->lower) + return -1; + else if (arg1->lower > arg2->lower) + return 1; + else + return 0; + +} + +Datum +gbt_int8_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_int8_ssup_cmp; + PG_RETURN_VOID(); +} static bool gbt_int8gt(const void *a, const void *b, FmgrInfo *flinfo) diff --git a/contrib/btree_gist/btree_interval.c b/contrib/btree_gist/btree_interval.c index b0afdf02bb5..4da30be9e46 100644 --- a/contrib/btree_gist/btree_interval.c +++ b/contrib/btree_gist/btree_interval.c @@ -6,6 +6,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" #include "utils/builtins.h" +#include "utils/sortsupport.h" #include "utils/timestamp.h" typedef struct @@ -27,8 +28,21 @@ PG_FUNCTION_INFO_V1(gbt_intv_consistent); PG_FUNCTION_INFO_V1(gbt_intv_distance); PG_FUNCTION_INFO_V1(gbt_intv_penalty); PG_FUNCTION_INFO_V1(gbt_intv_same); +PG_FUNCTION_INFO_V1(gbt_intv_sortsupport); +static int +gbt_intv_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + intvKEY *arg1 = (intvKEY *) DatumGetPointer(x); + intvKEY *arg2 = (intvKEY *) DatumGetPointer(y); + + /* intvKEY lower and upper are always equal here, so compare just lower members is enough */ + return DatumGetInt32(DirectFunctionCall2(interval_cmp, + IntervalPGetDatum(&arg1->lower), + IntervalPGetDatum(&arg2->lower))); +} + static bool gbt_intvgt(const void *a, const void *b, FmgrInfo *flinfo) { @@ -295,3 +309,14 @@ gbt_intv_same(PG_FUNCTION_ARGS) *result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo); PG_RETURN_POINTER(result); } + +Datum +gbt_intv_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_intv_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} diff --git a/contrib/btree_gist/btree_macaddr.c b/contrib/btree_gist/btree_macaddr.c index 17290529c02..b95fd8d3409 100644 --- a/contrib/btree_gist/btree_macaddr.c +++ b/contrib/btree_gist/btree_macaddr.c @@ -7,6 +7,7 @@ #include "btree_utils_num.h" #include "utils/builtins.h" #include "utils/inet.h" +#include "utils/sortsupport.h" typedef struct { @@ -25,6 +26,34 @@ PG_FUNCTION_INFO_V1(gbt_macad_picksplit); PG_FUNCTION_INFO_V1(gbt_macad_consistent); PG_FUNCTION_INFO_V1(gbt_macad_penalty); PG_FUNCTION_INFO_V1(gbt_macad_same); +PG_FUNCTION_INFO_V1(gbt_macaddr_sortsupport); + +/* sortsupport functions */ +static int +gbt_macaddr_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + macKEY *arg1 = (macKEY *) DatumGetPointer(x); + macKEY *arg2 = (macKEY *) DatumGetPointer(y); + + /* macKEY lower and upper members are always equal here, + * so its enough to compare just lower. + */ + return DatumGetInt32(DirectFunctionCall2(macaddr_cmp, + MacaddrPGetDatum(&arg1->lower), + MacaddrPGetDatum(&arg2->lower))); + +} + +Datum +gbt_macaddr_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_macaddr_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} static bool diff --git a/contrib/btree_gist/btree_macaddr8.c b/contrib/btree_gist/btree_macaddr8.c index 796cc4efee3..c3ff7eeb0b5 100644 --- a/contrib/btree_gist/btree_macaddr8.c +++ b/contrib/btree_gist/btree_macaddr8.c @@ -7,6 +7,7 @@ #include "btree_utils_num.h" #include "utils/builtins.h" #include "utils/inet.h" +#include "utils/sortsupport.h" typedef struct { @@ -25,8 +26,20 @@ PG_FUNCTION_INFO_V1(gbt_macad8_picksplit); PG_FUNCTION_INFO_V1(gbt_macad8_consistent); PG_FUNCTION_INFO_V1(gbt_macad8_penalty); PG_FUNCTION_INFO_V1(gbt_macad8_same); +PG_FUNCTION_INFO_V1(gbt_macad8_sortsupport); +static int +gbt_macaddr8_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + mac8KEY *arg1 = (mac8KEY *) DatumGetPointer(x); + mac8KEY *arg2 = (mac8KEY *) DatumGetPointer(y); + + return DatumGetInt32(DirectFunctionCall2(macaddr8_cmp, + Macaddr8PGetDatum(&arg1->lower), + Macaddr8PGetDatum(&arg2->lower))); +} + static bool gbt_macad8gt(const void *a, const void *b, FmgrInfo *flinfo) { @@ -194,3 +207,14 @@ gbt_macad8_same(PG_FUNCTION_ARGS) *result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo); PG_RETURN_POINTER(result); } + +Datum +gbt_macad8_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_macaddr8_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} diff --git a/contrib/btree_gist/btree_numeric.c b/contrib/btree_gist/btree_numeric.c index 35e466cdd94..227712645da 100644 --- a/contrib/btree_gist/btree_numeric.c +++ b/contrib/btree_gist/btree_numeric.c @@ -11,6 +11,7 @@ #include "utils/builtins.h" #include "utils/numeric.h" #include "utils/rel.h" +#include "utils/sortsupport.h" /* ** Bytea ops @@ -21,7 +22,39 @@ PG_FUNCTION_INFO_V1(gbt_numeric_picksplit); PG_FUNCTION_INFO_V1(gbt_numeric_consistent); PG_FUNCTION_INFO_V1(gbt_numeric_penalty); PG_FUNCTION_INFO_V1(gbt_numeric_same); +PG_FUNCTION_INFO_V1(gbt_numeric_sortsupport); +/* Sortsupport functions */ +static int +gbt_numeric_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + GBT_VARKEY *key1 = PG_DETOAST_DATUM(x); + GBT_VARKEY *key2 = PG_DETOAST_DATUM(y); + + GBT_VARKEY_R arg1 = gbt_var_key_readable(key1); + GBT_VARKEY_R arg2 = gbt_var_key_readable(key2); + Datum result; + + result = DirectFunctionCall2(numeric_cmp, + PointerGetDatum(arg1.lower), + PointerGetDatum(arg2.lower)); + + GBT_FREE_IF_COPY(key1, x); + GBT_FREE_IF_COPY(key2, y); + + return DatumGetInt32(result); +} + +Datum +gbt_numeric_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_numeric_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} /* define for comparison */ diff --git a/contrib/btree_gist/btree_oid.c b/contrib/btree_gist/btree_oid.c index 3cc7d4245d4..4ce4d3ff87e 100644 --- a/contrib/btree_gist/btree_oid.c +++ b/contrib/btree_gist/btree_oid.c @@ -5,6 +5,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" +#include "utils/sortsupport.h" typedef struct { @@ -23,7 +24,34 @@ PG_FUNCTION_INFO_V1(gbt_oid_consistent); PG_FUNCTION_INFO_V1(gbt_oid_distance); PG_FUNCTION_INFO_V1(gbt_oid_penalty); PG_FUNCTION_INFO_V1(gbt_oid_same); +PG_FUNCTION_INFO_V1(gbt_oid_sortsupport); +/* Sortsupport functions */ +static int +gbt_oid_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + oidKEY *arg1 = (oidKEY *) DatumGetPointer(x); + oidKEY *arg2 = (oidKEY *) DatumGetPointer(y); + + /* upper and lower fields are equal for each oidKEY, so just compare lower */ + if (arg1->lower > arg2->lower) + return 1; + else if (arg1->lower < arg2->lower) + return -1; + else + return 0; +} + +Datum +gbt_oid_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_oid_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} static bool gbt_oidgt(const void *a, const void *b, FmgrInfo *flinfo) diff --git a/contrib/btree_gist/btree_text.c b/contrib/btree_gist/btree_text.c index be0eac7975b..530648cb629 100644 --- a/contrib/btree_gist/btree_text.c +++ b/contrib/btree_gist/btree_text.c @@ -6,6 +6,7 @@ #include "btree_gist.h" #include "btree_utils_var.h" #include "utils/builtins.h" +#include "utils/sortsupport.h" /* ** Text ops @@ -18,7 +19,40 @@ PG_FUNCTION_INFO_V1(gbt_text_consistent); PG_FUNCTION_INFO_V1(gbt_bpchar_consistent); PG_FUNCTION_INFO_V1(gbt_text_penalty); PG_FUNCTION_INFO_V1(gbt_text_same); +PG_FUNCTION_INFO_V1(gbt_text_sortsupport); +/* Sortsupport functions */ +static int +gbt_text_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + GBT_VARKEY *key1 = PG_DETOAST_DATUM(x); + GBT_VARKEY *key2 = PG_DETOAST_DATUM(y); + + GBT_VARKEY_R arg1 = gbt_var_key_readable(key1); + GBT_VARKEY_R arg2 = gbt_var_key_readable(key2); + Datum result; + + result = DirectFunctionCall2Coll(bttextcmp, + ssup->ssup_collation, + PointerGetDatum(arg1.lower), + PointerGetDatum(arg2.lower)); + + GBT_FREE_IF_COPY(key1, x); + GBT_FREE_IF_COPY(key2, y); + + return DatumGetInt32(result); +} + +Datum +gbt_text_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_text_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} /* define for comparison */ diff --git a/contrib/btree_gist/btree_time.c b/contrib/btree_gist/btree_time.c index d89401c0f51..344b5637ea5 100644 --- a/contrib/btree_gist/btree_time.c +++ b/contrib/btree_gist/btree_time.c @@ -7,6 +7,7 @@ #include "btree_utils_num.h" #include "utils/builtins.h" #include "utils/date.h" +#include "utils/sortsupport.h" #include "utils/timestamp.h" typedef struct @@ -28,6 +29,8 @@ PG_FUNCTION_INFO_V1(gbt_time_distance); PG_FUNCTION_INFO_V1(gbt_timetz_consistent); PG_FUNCTION_INFO_V1(gbt_time_penalty); PG_FUNCTION_INFO_V1(gbt_time_same); +PG_FUNCTION_INFO_V1(gbt_time_sortsupport); +PG_FUNCTION_INFO_V1(gbt_timetz_sortsupport); #ifdef USE_FLOAT8_BYVAL @@ -37,6 +40,18 @@ PG_FUNCTION_INFO_V1(gbt_time_same); #endif +static int +gbt_timekey_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + timeKEY *arg1 = (timeKEY *) DatumGetPointer(x); + timeKEY *arg2 = (timeKEY *) DatumGetPointer(y); + + /* lower and upper are equal during sortsupport comparison */ + return DatumGetInt32(DirectFunctionCall2(time_cmp, + TimeADTGetDatumFast(arg1->lower), + TimeADTGetDatumFast(arg2->lower))); +} + static bool gbt_timegt(const void *a, const void *b, FmgrInfo *flinfo) { @@ -332,3 +347,14 @@ gbt_time_same(PG_FUNCTION_ARGS) *result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo); PG_RETURN_POINTER(result); } + +Datum +gbt_time_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_timekey_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} diff --git a/contrib/btree_gist/btree_ts.c b/contrib/btree_gist/btree_ts.c index 3f5ba91891d..ef13fc15bba 100644 --- a/contrib/btree_gist/btree_ts.c +++ b/contrib/btree_gist/btree_ts.c @@ -10,6 +10,7 @@ #include "utils/builtins.h" #include "utils/datetime.h" #include "utils/float.h" +#include "utils/sortsupport.h" typedef struct { @@ -31,6 +32,7 @@ PG_FUNCTION_INFO_V1(gbt_tstz_consistent); PG_FUNCTION_INFO_V1(gbt_tstz_distance); PG_FUNCTION_INFO_V1(gbt_ts_penalty); PG_FUNCTION_INFO_V1(gbt_ts_same); +PG_FUNCTION_INFO_V1(gbt_ts_sortsupport); #ifdef USE_FLOAT8_BYVAL @@ -39,6 +41,29 @@ PG_FUNCTION_INFO_V1(gbt_ts_same); #define TimestampGetDatumFast(X) PointerGetDatum(&(X)) #endif +/* Sortsupport functions */ +static int +gbt_ts_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + tsKEY *arg1 = (tsKEY *) DatumGetPointer(x); + tsKEY *arg2 = (tsKEY *) DatumGetPointer(y); + + /* Sortsupport always gets the same lower and upper value for input keys */ + return DatumGetInt32(DirectFunctionCall2(timestamp_cmp, + TimestampGetDatumFast(arg1->lower), + TimestampGetDatumFast(arg2->lower))); +} + +Datum +gbt_ts_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_ts_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} static bool gbt_tsgt(const void *a, const void *b, FmgrInfo *flinfo) diff --git a/contrib/btree_gist/btree_utils_var.h b/contrib/btree_gist/btree_utils_var.h index 2f8def655c8..16ff165ad3c 100644 --- a/contrib/btree_gist/btree_utils_var.h +++ b/contrib/btree_gist/btree_utils_var.h @@ -42,7 +42,17 @@ typedef struct GBT_VARKEY *(*f_l2n) (GBT_VARKEY *, FmgrInfo *flinfo); /* convert leaf to node */ } gbtree_vinfo; - +/* + * Free ptr1 in case its a copy of ptr2. + * + * This is adapted from varlena's PG_FREE_IF_COPY, though + * doesn't require fcinfo access. + */ +#define GBT_FREE_IF_COPY(ptr1, ptr2) \ + do { \ + if ((Pointer) (ptr1) != DatumGetPointer(ptr2)) \ + pfree(ptr1); \ + } while (0) extern GBT_VARKEY_R gbt_var_key_readable(const GBT_VARKEY *k); diff --git a/contrib/btree_gist/btree_uuid.c b/contrib/btree_gist/btree_uuid.c index fe8c679cbed..08372fc8bc9 100644 --- a/contrib/btree_gist/btree_uuid.c +++ b/contrib/btree_gist/btree_uuid.c @@ -6,6 +6,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" #include "port/pg_bswap.h" +#include "utils/sortsupport.h" #include "utils/uuid.h" typedef struct @@ -25,7 +26,31 @@ PG_FUNCTION_INFO_V1(gbt_uuid_picksplit); PG_FUNCTION_INFO_V1(gbt_uuid_consistent); PG_FUNCTION_INFO_V1(gbt_uuid_penalty); PG_FUNCTION_INFO_V1(gbt_uuid_same); +PG_FUNCTION_INFO_V1(gbt_uuid_sortsupport); +static int uuid_internal_cmp(const pg_uuid_t *arg1, const pg_uuid_t *arg2); + +/* Sortsupport functions */ +static int +gbt_uuid_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + uuidKEY *arg1 = (uuidKEY *) DatumGetPointer(x); + uuidKEY *arg2 = (uuidKEY *) DatumGetPointer(y); + + /* Sortsupport gets equal upper and lower values for each key to compare */ + return uuid_internal_cmp(&arg1->lower, &arg2->lower); +} + +Datum +gbt_uuid_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_uuid_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} static int uuid_internal_cmp(const pg_uuid_t *arg1, const pg_uuid_t *arg2) diff --git a/contrib/btree_gist/meson.build b/contrib/btree_gist/meson.build index 73b1bbf52a6..5b4caff5baf 100644 --- a/contrib/btree_gist/meson.build +++ b/contrib/btree_gist/meson.build @@ -51,6 +51,7 @@ install_data( 'btree_gist--1.5--1.6.sql', 'btree_gist--1.6--1.7.sql', 'btree_gist--1.7--1.8.sql', + 'btree_gist--1.8--1.9.sql', kwargs: contrib_data_args, ) diff --git a/doc/src/sgml/btree-gist.sgml b/doc/src/sgml/btree-gist.sgml index 31e7c78aaef..261856f6f68 100644 --- a/doc/src/sgml/btree-gist.sgml +++ b/doc/src/sgml/btree-gist.sgml @@ -52,6 +52,14 @@ <type>oid</type>, and <type>money</type>. </para> + <para> + Per default <filename>btree_gist</filename> builts <acronym>GiST</acronym> indexe with + <function>sortsupport</function> in <firstterm>sorted</firstterm> mode. This usually results in a + much better index quality and smaller index sizes by much faster index built speed. It is still + possible to revert to buffered built strategy by using the <literal>buffering</literal> parameter + when creating the index. + </para> + <para> This module is considered <quote>trusted</quote>, that is, it can be installed by non-superusers who have <literal>CREATE</literal> privilege [text/x-patch] v6-Add-GIST-sortsupport-rangetypes.patch (4.2K, ../../[email protected]/4-v6-Add-GIST-sortsupport-rangetypes.patch) download | inline diff: diff --git a/src/backend/utils/adt/rangetypes_gist.c b/src/backend/utils/adt/rangetypes_gist.c index cb28e9859ab..b0c77390024 100644 --- a/src/backend/utils/adt/rangetypes_gist.c +++ b/src/backend/utils/adt/rangetypes_gist.c @@ -21,6 +21,7 @@ #include "utils/fmgrprotos.h" #include "utils/multirangetypes.h" #include "utils/rangetypes.h" +#include "utils/sortsupport.h" /* * Range class properties used to segregate different classes of ranges in @@ -177,6 +178,7 @@ static void range_gist_double_sorting_split(TypeCacheEntry *typcache, static void range_gist_consider_split(ConsiderSplitContext *context, RangeBound *right_lower, int min_left_count, RangeBound *left_upper, int max_left_count); +static int range_gist_cmp(Datum a, Datum b, SortSupport ssup); static int get_gist_range_class(RangeType *range); static int single_bound_cmp(const void *a, const void *b, void *arg); static int interval_cmp_lower(const void *a, const void *b, void *arg); @@ -773,6 +775,20 @@ range_gist_picksplit(PG_FUNCTION_ARGS) PG_RETURN_POINTER(v); } +/* + * Sort support routine for fast GiST index build by sorting. + */ +Datum +range_gist_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = range_gist_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} + /* equality comparator for GiST */ Datum range_gist_same(PG_FUNCTION_ARGS) @@ -1693,6 +1709,60 @@ range_gist_consider_split(ConsiderSplitContext *context, } } +/* + * GiST sortsupport comparator for ranges. + * similar to range_cmp, but range typcache is cached. + */ +static int +range_gist_cmp(Datum a, Datum b, SortSupport ssup) +{ + RangeType *range_a = DatumGetRangeTypeP(a); + RangeType *range_b = DatumGetRangeTypeP(b); + TypeCacheEntry *typcache = ssup->ssup_extra; + RangeBound lower1, + lower2; + RangeBound upper1, + upper2; + bool empty1, + empty2; + int result; + + if (typcache == NULL) + { + Assert(RangeTypeGetOid(range_a) == RangeTypeGetOid(range_b)); + typcache = lookup_type_cache(RangeTypeGetOid(range_a), TYPECACHE_RANGE_INFO); + + /* + * Cache the range info between calls to avoid having to call + * lookup_type_cache() for each comparison. + */ + ssup->ssup_extra = typcache; + } + + range_deserialize(typcache, range_a, &lower1, &upper1, &empty1); + range_deserialize(typcache, range_b, &lower2, &upper2, &empty2); + + if (empty1 && empty2) + result = 0; + else if (empty1) + result = -1; + else if (empty2) + result = 1; + else + { + result = range_cmp_bounds(typcache, &lower1, &lower2); + if (result == 0) + result = range_cmp_bounds(typcache, &upper1, &upper2); + } + + if ((Datum) range_a != a) + pfree(range_a); + if ((Datum) range_b != b) + pfree(range_b); + + return result; +} + /* * Find class number for range. * diff --git a/src/include/catalog/pg_amproc.dat b/src/include/catalog/pg_amproc.dat index 352558c1f06..fc229d99845 100644 --- a/src/include/catalog/pg_amproc.dat +++ b/src/include/catalog/pg_amproc.dat @@ -607,6 +607,9 @@ { amprocfamily => 'gist/range_ops', amproclefttype => 'anyrange', amprocrighttype => 'anyrange', amprocnum => '7', amproc => 'range_gist_same' }, +{ amprocfamily => 'gist/range_ops', amproclefttype => 'anyrange', + amprocrighttype => 'anyrange', amprocnum => '11', + amproc => 'range_gist_sortsupport' }, { amprocfamily => 'gist/range_ops', amproclefttype => 'anyrange', amprocrighttype => 'anyrange', amprocnum => '12', amproc => 'gist_stratnum_identity' }, diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 29af4ce65d5..22315fefc9b 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -10589,6 +10589,9 @@ { oid => '3881', descr => 'GiST support', proname => 'range_gist_same', prorettype => 'internal', proargtypes => 'anyrange anyrange internal', prosrc => 'range_gist_same' }, +{ oid => '8849', descr => 'GiST support', + proname => 'range_gist_sortsupport', prorettype => 'void', + proargtypes => 'internal', prosrc => 'range_gist_sortsupport' }, { oid => '6154', descr => 'GiST support', proname => 'multirange_gist_consistent', prorettype => 'bool', proargtypes => 'internal anymultirange int2 oid internal', ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PATCH] Add sortsupport for range types and btree_gist @ 2024-02-12 13:00 jian he <[email protected]> parent: Bernd Helmle <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: jian he @ 2024-02-12 13:00 UTC (permalink / raw) To: Bernd Helmle <[email protected]>; +Cc: [email protected] On Fri, Feb 9, 2024 at 2:14 AM Bernd Helmle <[email protected]> wrote: > > Am Mittwoch, dem 10.01.2024 um 22:18 +0800 schrieb jian he: > > > > I split the original author's patch into 2. > > 1. Add GiST sortsupport function for all the btree-gist module data > > types except anyrange data type (which actually does not in this > > module) > > 2. Add GiST sortsupport function for anyrange data type. > > > > > what I am confused: > > In fmgr.h > > > > /* > > * Support for cleaning up detoasted copies of inputs. This must > > only > > * be used for pass-by-ref datatypes, and normally would only be used > > * for toastable types. If the given pointer is different from the > > * original argument, assume it's a palloc'd detoasted copy, and > > pfree it. > > * NOTE: most functions on toastable types do not have to worry about > > this, > > * but we currently require that support functions for indexes not > > leak > > * memory. > > */ > > #define PG_FREE_IF_COPY(ptr,n) \ > > do { \ > > if ((Pointer) (ptr) != PG_GETARG_POINTER(n)) \ > > pfree(ptr); \ > > } while (0) > > > > but the doc > > (https://www.postgresql.org/docs/current/gist-extensibility.html) > > says: > > All the GiST support methods are normally called in short-lived > > memory > > contexts; that is, CurrentMemoryContext will get reset after each > > tuple is processed. It is therefore not very important to worry about > > pfree'ing everything you palloc. > > ENDOF_QUOTE > > > > so I am not sure in range_gist_cmp, we need the following: > > ` > > if ((Datum) range_a != a) > > pfree(range_a); > > if ((Datum) range_b != b) > > pfree(range_b); > > ` > > Turns out this is not true for sortsupport: the comparison function is > called for each tuple during sorting, which will leak the detoasted > (and probably copied datum) in the sort memory context. See the same > for e.g. numeric and text, which i needed to change to pass the key > values correctly to the text_cmp() or numeric_cmp() function in these > cases. > + <para> + Per default <filename>btree_gist</filename> builts <acronym>GiST</acronym> indexe with + <function>sortsupport</function> in <firstterm>sorted</firstterm> mode. This usually results in a + much better index quality and smaller index sizes by much faster index built speed. It is still + possible to revert to buffered built strategy by using the <literal>buffering</literal> parameter + when creating the index. + </para> + I believe `built` |`builts` should be `build`. Also maybe we can simply copy some texts from https://www.postgresql.org/docs/current/gist-implementation.html. how about the following: <para> The sorted method is only available if each of the opclasses used by the index provides a <function>sortsupport</function> function, as described in <xref linkend="gist-extensibility"/>. If they do, this method is usually the best, so it is used by default. It is still possible to change to a buffered build strategy by using the <literal>buffering</literal> parameter to the CREATE INDEX command. </para> you've changed contrib/btree_gist/meson.build, seems we also need to change contrib/btree_gist/Makefile gist_point_sortsupport have `if (ssup->abbreviate)`, does range_gist_sortsupport also this part? I think the `if(ssup->abbreviate)` part is optional? Can we add some comments on it? ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PATCH] Add sortsupport for range types and btree_gist @ 2024-02-13 11:03 Bernd Helmle <[email protected]> parent: jian he <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Bernd Helmle @ 2024-02-13 11:03 UTC (permalink / raw) To: jian he <[email protected]>; +Cc: [email protected] Am Montag, dem 12.02.2024 um 21:00 +0800 schrieb jian he: > + <para> > + Per default <filename>btree_gist</filename> builts > <acronym>GiST</acronym> indexe with > + <function>sortsupport</function> in <firstterm>sorted</firstterm> > mode. This usually results in a > + much better index quality and smaller index sizes by much faster > index built speed. It is still > + possible to revert to buffered built strategy by using the > <literal>buffering</literal> parameter > + when creating the index. > + </para> > + > I believe `built` |`builts` should be `build`. Right, Fixed. > Also > maybe we can simply copy some texts from > https://www.postgresql.org/docs/current/gist-implementation.html. > how about the following: > <para> > The sorted method is only available if each of the opclasses used > by the > index provides a <function>sortsupport</function> function, as > described > in <xref linkend="gist-extensibility"/>. If they do, this method > is > usually the best, so it is used by default. > It is still possible to change to a buffered build strategy by > using > the <literal>buffering</literal> parameter > to the CREATE INDEX command. > </para> Hmm not sure what you are trying to achieve with this? The opclasses in btree_gist provides sortsupport, but by reading the above i would get the impression they're still optional. > > you've changed contrib/btree_gist/meson.build, seems we also need to > change contrib/btree_gist/Makefile > Oh, good catch. I'm so focused on meson already that i totally forgot the good old Makefile. Fixed. > gist_point_sortsupport have `if (ssup->abbreviate)`, does > range_gist_sortsupport also this part? > I think the `if(ssup->abbreviate)` part is optional? > Can we add some comments on it? I've thought about abbreviated keys support but put that aside for later. I wanted to focus on general sortsupport first before getting my hands on it and so postponed it for another round. If we agree that this patch needs support for abbreviated keys now, i certainly can work on it. Thanks for your review, Bernd Attachments: [text/x-patch] v7-Add-GIST-sortsupport-btree-gist.patch (39.0K, ../../[email protected]/2-v7-Add-GIST-sortsupport-btree-gist.patch) download | inline diff: diff --git a/contrib/btree_gist/Makefile b/contrib/btree_gist/Makefile index 7ac2df26c10..68190ac5e46 100644 --- a/contrib/btree_gist/Makefile +++ b/contrib/btree_gist/Makefile @@ -34,7 +34,7 @@ DATA = btree_gist--1.0--1.1.sql \ btree_gist--1.1--1.2.sql btree_gist--1.2.sql btree_gist--1.2--1.3.sql \ btree_gist--1.3--1.4.sql btree_gist--1.4--1.5.sql \ btree_gist--1.5--1.6.sql btree_gist--1.6--1.7.sql \ - btree_gist--1.7--1.8.sql + btree_gist--1.7--1.8.sql btree_gist--1.8--1.9.sql PGFILEDESC = "btree_gist - B-tree equivalent GiST operator classes" REGRESS = init int2 int4 int8 float4 float8 cash oid timestamp timestamptz \ diff --git a/contrib/btree_gist/btree_bit.c b/contrib/btree_gist/btree_bit.c index 6790f22b4b6..67ea5d9e182 100644 --- a/contrib/btree_gist/btree_bit.c +++ b/contrib/btree_gist/btree_bit.c @@ -6,7 +6,7 @@ #include "btree_gist.h" #include "btree_utils_var.h" #include "utils/builtins.h" -#include "utils/bytea.h" +#include "utils/sortsupport.h" #include "utils/varbit.h" @@ -19,10 +19,33 @@ PG_FUNCTION_INFO_V1(gbt_bit_picksplit); PG_FUNCTION_INFO_V1(gbt_bit_consistent); PG_FUNCTION_INFO_V1(gbt_bit_penalty); PG_FUNCTION_INFO_V1(gbt_bit_same); +PG_FUNCTION_INFO_V1(gbt_bit_sortsupport); +PG_FUNCTION_INFO_V1(gbt_varbit_sortsupport); /* define for comparison */ +static int +gbt_bit_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + GBT_VARKEY *key1 = PG_DETOAST_DATUM(x); + GBT_VARKEY *key2 = PG_DETOAST_DATUM(y); + + GBT_VARKEY_R arg1 = gbt_var_key_readable(key1); + GBT_VARKEY_R arg2 = gbt_var_key_readable(key2); + Datum result; + + /* lower is always equal to upper, so just compare those fields */ + result = DirectFunctionCall2(bitcmp, + PointerGetDatum(arg1.lower), + PointerGetDatum(arg2.lower)); + + GBT_FREE_IF_COPY(key1, x); + GBT_FREE_IF_COPY(key2, y); + + return DatumGetInt32(result); +} + static bool gbt_bitgt(const void *a, const void *b, Oid collation, FmgrInfo *flinfo) { @@ -208,3 +231,25 @@ gbt_bit_penalty(PG_FUNCTION_ARGS) PG_RETURN_POINTER(gbt_var_penalty(result, o, n, PG_GET_COLLATION(), &tinfo, fcinfo->flinfo)); } + +Datum +gbt_bit_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_bit_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} + +Datum +gbt_varbit_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_bit_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} \ No newline at end of file diff --git a/contrib/btree_gist/btree_bool.c b/contrib/btree_gist/btree_bool.c index 8b2af129b52..5058dba2986 100644 --- a/contrib/btree_gist/btree_bool.c +++ b/contrib/btree_gist/btree_bool.c @@ -6,6 +6,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" #include "common/int.h" +#include "utils/sortsupport.h" typedef struct boolkey { @@ -23,6 +24,16 @@ PG_FUNCTION_INFO_V1(gbt_bool_picksplit); PG_FUNCTION_INFO_V1(gbt_bool_consistent); PG_FUNCTION_INFO_V1(gbt_bool_penalty); PG_FUNCTION_INFO_V1(gbt_bool_same); +PG_FUNCTION_INFO_V1(gbt_bool_sortsupport); + +static int +gbt_bool_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + boolKEY *arg1 = (boolKEY *) DatumGetPointer(x); + boolKEY *arg2 = (boolKEY *) DatumGetPointer(y); + + return arg1->lower - arg2->lower; +} static bool gbt_boolgt(const void *a, const void *b, FmgrInfo *flinfo) @@ -167,3 +178,14 @@ gbt_bool_same(PG_FUNCTION_ARGS) *result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo); PG_RETURN_POINTER(result); } + +Datum +gbt_bool_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_bool_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} diff --git a/contrib/btree_gist/btree_bytea.c b/contrib/btree_gist/btree_bytea.c index 6b005f0157e..2047e3ddee0 100644 --- a/contrib/btree_gist/btree_bytea.c +++ b/contrib/btree_gist/btree_bytea.c @@ -6,7 +6,7 @@ #include "btree_gist.h" #include "btree_utils_var.h" #include "utils/builtins.h" -#include "utils/bytea.h" +#include "utils/sortsupport.h" /* @@ -18,7 +18,41 @@ PG_FUNCTION_INFO_V1(gbt_bytea_picksplit); PG_FUNCTION_INFO_V1(gbt_bytea_consistent); PG_FUNCTION_INFO_V1(gbt_bytea_penalty); PG_FUNCTION_INFO_V1(gbt_bytea_same); +PG_FUNCTION_INFO_V1(gbt_bytea_sortsupport); +/* sortsupport support */ + +static int +gbt_bytea_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + GBT_VARKEY *key1 = PG_DETOAST_DATUM(x); + GBT_VARKEY *key2 = PG_DETOAST_DATUM(y); + + GBT_VARKEY_R xkey = gbt_var_key_readable(key1); + GBT_VARKEY_R ykey = gbt_var_key_readable(key2); + Datum result; + + /* lower and upper are always the same, so it is enough to compare lower */ + result = DirectFunctionCall2(byteacmp, + PointerGetDatum(xkey.lower), + PointerGetDatum(ykey.lower)); + + GBT_FREE_IF_COPY(key1, x); + GBT_FREE_IF_COPY(key2, y); + + return DatumGetInt32(result); +} + +Datum +gbt_bytea_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_bytea_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} /* define for comparison */ diff --git a/contrib/btree_gist/btree_cash.c b/contrib/btree_gist/btree_cash.c index 546b948ea40..94090c9ac0d 100644 --- a/contrib/btree_gist/btree_cash.c +++ b/contrib/btree_gist/btree_cash.c @@ -7,6 +7,7 @@ #include "btree_utils_num.h" #include "common/int.h" #include "utils/cash.h" +#include "utils/sortsupport.h" typedef struct { @@ -25,6 +26,21 @@ PG_FUNCTION_INFO_V1(gbt_cash_consistent); PG_FUNCTION_INFO_V1(gbt_cash_distance); PG_FUNCTION_INFO_V1(gbt_cash_penalty); PG_FUNCTION_INFO_V1(gbt_cash_same); +PG_FUNCTION_INFO_V1(gbt_cash_sortsupport); + +extern Datum cash_cmp(PG_FUNCTION_ARGS); + +static int +gbt_cash_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + cashKEY *arg1 = (cashKEY *) DatumGetPointer(x); + cashKEY *arg2 = (cashKEY *) DatumGetPointer(y); + + /* Since lower and upper are always equal, it is enough to compare lower */ + return DatumGetInt32(DirectFunctionCall2(cash_cmp, + CashGetDatum(arg1->lower), + CashGetDatum(arg2->lower))); +} static bool gbt_cashgt(const void *a, const void *b, FmgrInfo *flinfo) @@ -215,3 +231,14 @@ gbt_cash_same(PG_FUNCTION_ARGS) *result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo); PG_RETURN_POINTER(result); } + +Datum +gbt_cash_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_cash_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} diff --git a/contrib/btree_gist/btree_date.c b/contrib/btree_gist/btree_date.c index 68a4107dbf0..c4ff15f27b4 100644 --- a/contrib/btree_gist/btree_date.c +++ b/contrib/btree_gist/btree_date.c @@ -6,6 +6,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" #include "utils/builtins.h" +#include "utils/sortsupport.h" #include "utils/date.h" typedef struct @@ -25,6 +26,30 @@ PG_FUNCTION_INFO_V1(gbt_date_consistent); PG_FUNCTION_INFO_V1(gbt_date_distance); PG_FUNCTION_INFO_V1(gbt_date_penalty); PG_FUNCTION_INFO_V1(gbt_date_same); +PG_FUNCTION_INFO_V1(gbt_date_sortsupport); + +/* sortsupport functions */ + +static int gbt_date_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + dateKEY *akey = (dateKEY *) DatumGetPointer(x); + dateKEY *bkey = (dateKEY *) DatumGetPointer(y); + + return DatumGetInt32(DirectFunctionCall2(date_cmp, + DateADTGetDatum(akey->lower), + DateADTGetDatum(bkey->lower))); +} + +Datum +gbt_date_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_date_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} static bool gbt_dategt(const void *a, const void *b, FmgrInfo *flinfo) diff --git a/contrib/btree_gist/btree_enum.c b/contrib/btree_gist/btree_enum.c index d4dc38a38e5..67395182341 100644 --- a/contrib/btree_gist/btree_enum.c +++ b/contrib/btree_gist/btree_enum.c @@ -7,6 +7,7 @@ #include "btree_utils_num.h" #include "fmgr.h" #include "utils/builtins.h" +#include "utils/sortsupport.h" /* enums are really Oids, so we just use the same structure */ @@ -26,8 +27,20 @@ PG_FUNCTION_INFO_V1(gbt_enum_picksplit); PG_FUNCTION_INFO_V1(gbt_enum_consistent); PG_FUNCTION_INFO_V1(gbt_enum_penalty); PG_FUNCTION_INFO_V1(gbt_enum_same); +PG_FUNCTION_INFO_V1(gbt_enum_sortsupport); +static int +gbt_enum_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + oidKEY *arg1 = (oidKEY *) DatumGetPointer(x); + oidKEY *arg2 = (oidKEY *) DatumGetPointer(y); + + /* Since lower and upper in oidKEY are always the same, just compare lower */ + return DatumGetInt32(CallerFInfoFunctionCall2(enum_cmp, ssup->ssup_extra, + InvalidOid, arg1->lower, arg2->lower)); +} + static bool gbt_enumgt(const void *a, const void *b, FmgrInfo *flinfo) { @@ -183,3 +196,20 @@ gbt_enum_same(PG_FUNCTION_ARGS) *result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo); PG_RETURN_POINTER(result); } + +Datum +gbt_enum_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_enum_ssup_cmp; + + /* + * Since enum_fast_cmp() also uses enum_cmp() like the rest of the + * comparsion functions, it also needs to pass on flinfo when calling + * it. Thus save it in ->ssup_extra and later retrieve it in enum_fast_cmp(). + */ + ssup->ssup_extra = fcinfo->flinfo; + + PG_RETURN_VOID(); +} diff --git a/contrib/btree_gist/btree_float4.c b/contrib/btree_gist/btree_float4.c index 84ca5eee501..b2229cdb04d 100644 --- a/contrib/btree_gist/btree_float4.c +++ b/contrib/btree_gist/btree_float4.c @@ -5,6 +5,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" +#include "utils/sortsupport.h" #include "utils/float.h" typedef struct float4key @@ -24,6 +25,33 @@ PG_FUNCTION_INFO_V1(gbt_float4_consistent); PG_FUNCTION_INFO_V1(gbt_float4_distance); PG_FUNCTION_INFO_V1(gbt_float4_penalty); PG_FUNCTION_INFO_V1(gbt_float4_same); +PG_FUNCTION_INFO_V1(gbt_float4_sortsupport); + +extern Datum btfloat4cmp(PG_FUNCTION_ARGS); + +/* sortsupport functions */ +static int +gbt_float4_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + float4KEY *arg1 = (float4KEY *) DatumGetPointer(x); + float4KEY *arg2 = (float4KEY *) DatumGetPointer(y); + + /* Since lower and upper for float4KEYs here are always equal it is okay to compare them only */ + return DatumGetInt32(DirectFunctionCall2(btfloat4cmp, + Float4GetDatum(arg1->lower), + Float4GetDatum(arg2->lower))); +} + +Datum +gbt_float4_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_float4_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} static bool gbt_float4gt(const void *a, const void *b, FmgrInfo *flinfo) diff --git a/contrib/btree_gist/btree_float8.c b/contrib/btree_gist/btree_float8.c index 081a719b006..2f312f5bd83 100644 --- a/contrib/btree_gist/btree_float8.c +++ b/contrib/btree_gist/btree_float8.c @@ -5,6 +5,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" +#include "utils/sortsupport.h" #include "utils/float.h" typedef struct float8key @@ -24,7 +25,33 @@ PG_FUNCTION_INFO_V1(gbt_float8_consistent); PG_FUNCTION_INFO_V1(gbt_float8_distance); PG_FUNCTION_INFO_V1(gbt_float8_penalty); PG_FUNCTION_INFO_V1(gbt_float8_same); +PG_FUNCTION_INFO_V1(gbt_float8_sortsupport); +extern Datum btfloat8cmp(PG_FUNCTION_ARGS); + +/* sortsupport functions */ +static int +gbt_float8_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + float8KEY *arg1 = (float8KEY *) DatumGetPointer(x); + float8KEY *arg2 = (float8KEY *) DatumGetPointer(y); + + /* upper and lower for input keys are equal here */ + return DatumGetInt32(DirectFunctionCall2(btfloat8cmp, + Float8GetDatum(arg1->lower), + Float8GetDatum(arg2->lower))); +} + +Datum +gbt_float8_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_float8_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} static bool gbt_float8gt(const void *a, const void *b, FmgrInfo *flinfo) diff --git a/contrib/btree_gist/btree_gist--1.7--1.8.sql b/contrib/btree_gist/btree_gist--1.7--1.8.sql index 307bfe574b0..657285ca7ce 100644 --- a/contrib/btree_gist/btree_gist--1.7--1.8.sql +++ b/contrib/btree_gist/btree_gist--1.7--1.8.sql @@ -85,3 +85,4 @@ ALTER OPERATOR FAMILY gist_enum_ops USING gist ADD ALTER OPERATOR FAMILY gist_bool_ops USING gist ADD FUNCTION 12 (bool, bool) gist_stratnum_btree (int2) ; + diff --git a/contrib/btree_gist/btree_gist--1.8--1.9.sql b/contrib/btree_gist/btree_gist--1.8--1.9.sql new file mode 100644 index 00000000000..c52e750ed1a --- /dev/null +++ b/contrib/btree_gist/btree_gist--1.8--1.9.sql @@ -0,0 +1,192 @@ +/* contrib/btree_gist/btree_gist--1.7--1.8.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "ALTER EXTENSION btree_gist UPDATE TO '1.9'" to load this file. \quit + +CREATE FUNCTION gbt_bit_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_varbit_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_bool_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_cash_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_enum_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_inet_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_intv_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_macad8_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_time_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_int2_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_int4_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_int8_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_bytea_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_date_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_float4_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_float8_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_macaddr_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_numeric_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_oid_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_text_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_ts_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_uuid_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +ALTER OPERATOR FAMILY gist_vbit_ops USING gist ADD + FUNCTION 11 (varbit, varbit) gbt_varbit_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_bit_ops USING gist ADD + FUNCTION 11 (bit, bit) gbt_bit_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_bool_ops USING gist ADD + FUNCTION 11 (bool, bool) gbt_bool_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_bytea_ops USING gist ADD + FUNCTION 11 (bytea, bytea) gbt_bytea_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_cash_ops USING gist ADD + FUNCTION 11 (money, money) gbt_cash_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_date_ops USING gist ADD + FUNCTION 11 (date, date) gbt_date_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_enum_ops USING gist ADD + FUNCTION 11 (anyenum, anyenum) gbt_enum_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_float4_ops USING gist ADD + FUNCTION 11 (float4, float4) gbt_float4_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_float8_ops USING gist ADD + FUNCTION 11 (float8, float8) gbt_float8_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_inet_ops USING gist ADD + FUNCTION 11 (inet, inet) gbt_inet_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_cidr_ops USING gist ADD + FUNCTION 11 (inet, inet) gbt_inet_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_int2_ops USING gist ADD + FUNCTION 11 (int2, int2) gbt_int2_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_int4_ops USING gist ADD + FUNCTION 11 (int4, int4) gbt_int4_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_int8_ops USING gist ADD + FUNCTION 11 (int8, int8) gbt_int8_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_interval_ops USING gist ADD + FUNCTION 11 (interval, interval) gbt_intv_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_macaddr_ops USING gist ADD + FUNCTION 11 (macaddr, macaddr) gbt_macad8_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_macaddr8_ops USING gist ADD + FUNCTION 11 (macaddr8, macaddr8) gbt_macad8_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_numeric_ops USING gist ADD + FUNCTION 11 (numeric, numeric) gbt_numeric_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_oid_ops USING gist ADD + FUNCTION 11 (oid, oid) gbt_oid_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_text_ops USING gist ADD + FUNCTION 11 (text, text) gbt_text_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_bpchar_ops USING gist ADD + FUNCTION 11 (bpchar, bpchar) bpchar_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_time_ops USING gist ADD + FUNCTION 11 (time, time) gbt_time_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_timestamp_ops USING gist ADD + FUNCTION 11 (timestamp, timestamp) gbt_ts_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_timestamptz_ops USING gist ADD + FUNCTION 11 (timestamptz, timestamptz) gbt_ts_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_timetz_ops USING gist ADD + FUNCTION 11 (timetz, timetz) gbt_time_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_uuid_ops USING gist ADD + FUNCTION 11 (uuid, uuid) gbt_uuid_sortsupport (internal) ; diff --git a/contrib/btree_gist/btree_gist.c b/contrib/btree_gist/btree_gist.c index 5fd4cce27d0..c8acbe76b55 100644 --- a/contrib/btree_gist/btree_gist.c +++ b/contrib/btree_gist/btree_gist.c @@ -6,6 +6,9 @@ #include "access/stratnum.h" #include "utils/builtins.h" +#include "access/gist.h" +#include "btree_gist.h" + PG_MODULE_MAGIC; PG_FUNCTION_INFO_V1(gbt_decompress); diff --git a/contrib/btree_gist/btree_gist.control b/contrib/btree_gist/btree_gist.control index abf66538f32..69d9341a0ad 100644 --- a/contrib/btree_gist/btree_gist.control +++ b/contrib/btree_gist/btree_gist.control @@ -1,6 +1,6 @@ # btree_gist extension comment = 'support for indexing common datatypes in GiST' -default_version = '1.8' +default_version = '1.9' module_pathname = '$libdir/btree_gist' relocatable = true trusted = true diff --git a/contrib/btree_gist/btree_gist.h b/contrib/btree_gist/btree_gist.h index 0db8522c8a7..245c3559281 100644 --- a/contrib/btree_gist/btree_gist.h +++ b/contrib/btree_gist/btree_gist.h @@ -9,6 +9,12 @@ #define BtreeGistNotEqualStrategyNumber 6 +typedef struct int32key +{ + int32 lower; + int32 upper; +} int32KEY; + /* indexed types */ enum gbtree_type diff --git a/contrib/btree_gist/btree_inet.c b/contrib/btree_gist/btree_inet.c index 2fb952dca83..77ef14a4f7d 100644 --- a/contrib/btree_gist/btree_inet.c +++ b/contrib/btree_gist/btree_inet.c @@ -8,6 +8,7 @@ #include "catalog/pg_type.h" #include "utils/builtins.h" #include "utils/inet.h" +#include "utils/sortsupport.h" typedef struct inetkey { @@ -24,8 +25,23 @@ PG_FUNCTION_INFO_V1(gbt_inet_picksplit); PG_FUNCTION_INFO_V1(gbt_inet_consistent); PG_FUNCTION_INFO_V1(gbt_inet_penalty); PG_FUNCTION_INFO_V1(gbt_inet_same); +PG_FUNCTION_INFO_V1(gbt_inet_sortsupport); +static int +gbt_inet_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + inetKEY *arg1 = (inetKEY *) DatumGetPointer(x); + inetKEY *arg2 = (inetKEY *) DatumGetPointer(y); + + if (arg1->lower == arg2->lower) + return 0; + else if (arg1->lower > arg2->lower) + return 1; + else + return -1; +} + static bool gbt_inetgt(const void *a, const void *b, FmgrInfo *flinfo) { @@ -185,3 +201,14 @@ gbt_inet_same(PG_FUNCTION_ARGS) *result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo); PG_RETURN_POINTER(result); } + +Datum +gbt_inet_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_inet_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} diff --git a/contrib/btree_gist/btree_int2.c b/contrib/btree_gist/btree_int2.c index fdbf156586c..b19770ff67f 100644 --- a/contrib/btree_gist/btree_int2.c +++ b/contrib/btree_gist/btree_int2.c @@ -5,6 +5,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" +#include "utils/sortsupport.h" #include "common/int.h" typedef struct int16key @@ -24,6 +25,31 @@ PG_FUNCTION_INFO_V1(gbt_int2_consistent); PG_FUNCTION_INFO_V1(gbt_int2_distance); PG_FUNCTION_INFO_V1(gbt_int2_penalty); PG_FUNCTION_INFO_V1(gbt_int2_same); +PG_FUNCTION_INFO_V1(gbt_int2_sortsupport); + +/* sortsupport functions */ +static int +gbt_int2_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + int16KEY *arg1 = (int16KEY *) DatumGetPointer(x); + int16KEY *arg2 = (int16KEY *) DatumGetPointer(y); + + if (arg1->lower < arg2->lower) + return -1; + else if (arg1->lower > arg2->lower) + return 1; + else + return 0; +} + +Datum +gbt_int2_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_int2_ssup_cmp; + PG_RETURN_VOID(); +} static bool gbt_int2gt(const void *a, const void *b, FmgrInfo *flinfo) diff --git a/contrib/btree_gist/btree_int4.c b/contrib/btree_gist/btree_int4.c index 8915fb5d087..60418511ede 100644 --- a/contrib/btree_gist/btree_int4.c +++ b/contrib/btree_gist/btree_int4.c @@ -2,16 +2,10 @@ * contrib/btree_gist/btree_int4.c */ #include "postgres.h" - +#include "common/int.h" +#include "utils/sortsupport.h" #include "btree_gist.h" #include "btree_utils_num.h" -#include "common/int.h" - -typedef struct int32key -{ - int32 lower; - int32 upper; -} int32KEY; /* ** int32 ops @@ -24,6 +18,7 @@ PG_FUNCTION_INFO_V1(gbt_int4_consistent); PG_FUNCTION_INFO_V1(gbt_int4_distance); PG_FUNCTION_INFO_V1(gbt_int4_penalty); PG_FUNCTION_INFO_V1(gbt_int4_same); +PG_FUNCTION_INFO_V1(gbt_int4_sortsupport); static bool @@ -90,6 +85,29 @@ static const gbtree_ninfo tinfo = gbt_int4_dist }; +static int +gbt_int4_ssup_cmp(Datum a, Datum b, SortSupport ssup) +{ + int32KEY *ia = (int32KEY *) DatumGetPointer(a); + int32KEY *ib = (int32KEY *) DatumGetPointer(b); + + /* int4KEY upper and lower are always the same for input keys here. */ + if (ia->lower < ib->lower) + return -1; + else if (ia->lower > ib->lower) + return 1; + else + return 0; +} + +Datum +gbt_int4_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_int4_ssup_cmp; + PG_RETURN_VOID(); +} PG_FUNCTION_INFO_V1(int4_dist); Datum diff --git a/contrib/btree_gist/btree_int8.c b/contrib/btree_gist/btree_int8.c index 7c63a5b6dc1..00d55163fc9 100644 --- a/contrib/btree_gist/btree_int8.c +++ b/contrib/btree_gist/btree_int8.c @@ -6,6 +6,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" #include "common/int.h" +#include "utils/sortsupport.h" typedef struct int64key { @@ -24,7 +25,32 @@ PG_FUNCTION_INFO_V1(gbt_int8_consistent); PG_FUNCTION_INFO_V1(gbt_int8_distance); PG_FUNCTION_INFO_V1(gbt_int8_penalty); PG_FUNCTION_INFO_V1(gbt_int8_same); +PG_FUNCTION_INFO_V1(gbt_int8_sortsupport); +/* sortsupport functions */ +static int +gbt_int8_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + int64KEY *arg1 = (int64KEY *) DatumGetPointer(x); + int64KEY *arg2 = (int64KEY *) DatumGetPointer(y); + + if (arg1->lower < arg2->lower) + return -1; + else if (arg1->lower > arg2->lower) + return 1; + else + return 0; + +} + +Datum +gbt_int8_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_int8_ssup_cmp; + PG_RETURN_VOID(); +} static bool gbt_int8gt(const void *a, const void *b, FmgrInfo *flinfo) diff --git a/contrib/btree_gist/btree_interval.c b/contrib/btree_gist/btree_interval.c index b0afdf02bb5..4da30be9e46 100644 --- a/contrib/btree_gist/btree_interval.c +++ b/contrib/btree_gist/btree_interval.c @@ -6,6 +6,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" #include "utils/builtins.h" +#include "utils/sortsupport.h" #include "utils/timestamp.h" typedef struct @@ -27,8 +28,21 @@ PG_FUNCTION_INFO_V1(gbt_intv_consistent); PG_FUNCTION_INFO_V1(gbt_intv_distance); PG_FUNCTION_INFO_V1(gbt_intv_penalty); PG_FUNCTION_INFO_V1(gbt_intv_same); +PG_FUNCTION_INFO_V1(gbt_intv_sortsupport); +static int +gbt_intv_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + intvKEY *arg1 = (intvKEY *) DatumGetPointer(x); + intvKEY *arg2 = (intvKEY *) DatumGetPointer(y); + + /* intvKEY lower and upper are always equal here, so compare just lower members is enough */ + return DatumGetInt32(DirectFunctionCall2(interval_cmp, + IntervalPGetDatum(&arg1->lower), + IntervalPGetDatum(&arg2->lower))); +} + static bool gbt_intvgt(const void *a, const void *b, FmgrInfo *flinfo) { @@ -295,3 +309,14 @@ gbt_intv_same(PG_FUNCTION_ARGS) *result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo); PG_RETURN_POINTER(result); } + +Datum +gbt_intv_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_intv_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} diff --git a/contrib/btree_gist/btree_macaddr.c b/contrib/btree_gist/btree_macaddr.c index 17290529c02..b95fd8d3409 100644 --- a/contrib/btree_gist/btree_macaddr.c +++ b/contrib/btree_gist/btree_macaddr.c @@ -7,6 +7,7 @@ #include "btree_utils_num.h" #include "utils/builtins.h" #include "utils/inet.h" +#include "utils/sortsupport.h" typedef struct { @@ -25,6 +26,34 @@ PG_FUNCTION_INFO_V1(gbt_macad_picksplit); PG_FUNCTION_INFO_V1(gbt_macad_consistent); PG_FUNCTION_INFO_V1(gbt_macad_penalty); PG_FUNCTION_INFO_V1(gbt_macad_same); +PG_FUNCTION_INFO_V1(gbt_macaddr_sortsupport); + +/* sortsupport functions */ +static int +gbt_macaddr_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + macKEY *arg1 = (macKEY *) DatumGetPointer(x); + macKEY *arg2 = (macKEY *) DatumGetPointer(y); + + /* macKEY lower and upper members are always equal here, + * so its enough to compare just lower. + */ + return DatumGetInt32(DirectFunctionCall2(macaddr_cmp, + MacaddrPGetDatum(&arg1->lower), + MacaddrPGetDatum(&arg2->lower))); + +} + +Datum +gbt_macaddr_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_macaddr_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} static bool diff --git a/contrib/btree_gist/btree_macaddr8.c b/contrib/btree_gist/btree_macaddr8.c index 796cc4efee3..c3ff7eeb0b5 100644 --- a/contrib/btree_gist/btree_macaddr8.c +++ b/contrib/btree_gist/btree_macaddr8.c @@ -7,6 +7,7 @@ #include "btree_utils_num.h" #include "utils/builtins.h" #include "utils/inet.h" +#include "utils/sortsupport.h" typedef struct { @@ -25,8 +26,20 @@ PG_FUNCTION_INFO_V1(gbt_macad8_picksplit); PG_FUNCTION_INFO_V1(gbt_macad8_consistent); PG_FUNCTION_INFO_V1(gbt_macad8_penalty); PG_FUNCTION_INFO_V1(gbt_macad8_same); +PG_FUNCTION_INFO_V1(gbt_macad8_sortsupport); +static int +gbt_macaddr8_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + mac8KEY *arg1 = (mac8KEY *) DatumGetPointer(x); + mac8KEY *arg2 = (mac8KEY *) DatumGetPointer(y); + + return DatumGetInt32(DirectFunctionCall2(macaddr8_cmp, + Macaddr8PGetDatum(&arg1->lower), + Macaddr8PGetDatum(&arg2->lower))); +} + static bool gbt_macad8gt(const void *a, const void *b, FmgrInfo *flinfo) { @@ -194,3 +207,14 @@ gbt_macad8_same(PG_FUNCTION_ARGS) *result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo); PG_RETURN_POINTER(result); } + +Datum +gbt_macad8_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_macaddr8_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} diff --git a/contrib/btree_gist/btree_numeric.c b/contrib/btree_gist/btree_numeric.c index 35e466cdd94..227712645da 100644 --- a/contrib/btree_gist/btree_numeric.c +++ b/contrib/btree_gist/btree_numeric.c @@ -11,6 +11,7 @@ #include "utils/builtins.h" #include "utils/numeric.h" #include "utils/rel.h" +#include "utils/sortsupport.h" /* ** Bytea ops @@ -21,7 +22,39 @@ PG_FUNCTION_INFO_V1(gbt_numeric_picksplit); PG_FUNCTION_INFO_V1(gbt_numeric_consistent); PG_FUNCTION_INFO_V1(gbt_numeric_penalty); PG_FUNCTION_INFO_V1(gbt_numeric_same); +PG_FUNCTION_INFO_V1(gbt_numeric_sortsupport); +/* Sortsupport functions */ +static int +gbt_numeric_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + GBT_VARKEY *key1 = PG_DETOAST_DATUM(x); + GBT_VARKEY *key2 = PG_DETOAST_DATUM(y); + + GBT_VARKEY_R arg1 = gbt_var_key_readable(key1); + GBT_VARKEY_R arg2 = gbt_var_key_readable(key2); + Datum result; + + result = DirectFunctionCall2(numeric_cmp, + PointerGetDatum(arg1.lower), + PointerGetDatum(arg2.lower)); + + GBT_FREE_IF_COPY(key1, x); + GBT_FREE_IF_COPY(key2, y); + + return DatumGetInt32(result); +} + +Datum +gbt_numeric_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_numeric_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} /* define for comparison */ diff --git a/contrib/btree_gist/btree_oid.c b/contrib/btree_gist/btree_oid.c index 3cc7d4245d4..4ce4d3ff87e 100644 --- a/contrib/btree_gist/btree_oid.c +++ b/contrib/btree_gist/btree_oid.c @@ -5,6 +5,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" +#include "utils/sortsupport.h" typedef struct { @@ -23,7 +24,34 @@ PG_FUNCTION_INFO_V1(gbt_oid_consistent); PG_FUNCTION_INFO_V1(gbt_oid_distance); PG_FUNCTION_INFO_V1(gbt_oid_penalty); PG_FUNCTION_INFO_V1(gbt_oid_same); +PG_FUNCTION_INFO_V1(gbt_oid_sortsupport); +/* Sortsupport functions */ +static int +gbt_oid_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + oidKEY *arg1 = (oidKEY *) DatumGetPointer(x); + oidKEY *arg2 = (oidKEY *) DatumGetPointer(y); + + /* upper and lower fields are equal for each oidKEY, so just compare lower */ + if (arg1->lower > arg2->lower) + return 1; + else if (arg1->lower < arg2->lower) + return -1; + else + return 0; +} + +Datum +gbt_oid_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_oid_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} static bool gbt_oidgt(const void *a, const void *b, FmgrInfo *flinfo) diff --git a/contrib/btree_gist/btree_text.c b/contrib/btree_gist/btree_text.c index be0eac7975b..530648cb629 100644 --- a/contrib/btree_gist/btree_text.c +++ b/contrib/btree_gist/btree_text.c @@ -6,6 +6,7 @@ #include "btree_gist.h" #include "btree_utils_var.h" #include "utils/builtins.h" +#include "utils/sortsupport.h" /* ** Text ops @@ -18,7 +19,40 @@ PG_FUNCTION_INFO_V1(gbt_text_consistent); PG_FUNCTION_INFO_V1(gbt_bpchar_consistent); PG_FUNCTION_INFO_V1(gbt_text_penalty); PG_FUNCTION_INFO_V1(gbt_text_same); +PG_FUNCTION_INFO_V1(gbt_text_sortsupport); +/* Sortsupport functions */ +static int +gbt_text_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + GBT_VARKEY *key1 = PG_DETOAST_DATUM(x); + GBT_VARKEY *key2 = PG_DETOAST_DATUM(y); + + GBT_VARKEY_R arg1 = gbt_var_key_readable(key1); + GBT_VARKEY_R arg2 = gbt_var_key_readable(key2); + Datum result; + + result = DirectFunctionCall2Coll(bttextcmp, + ssup->ssup_collation, + PointerGetDatum(arg1.lower), + PointerGetDatum(arg2.lower)); + + GBT_FREE_IF_COPY(key1, x); + GBT_FREE_IF_COPY(key2, y); + + return DatumGetInt32(result); +} + +Datum +gbt_text_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_text_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} /* define for comparison */ diff --git a/contrib/btree_gist/btree_time.c b/contrib/btree_gist/btree_time.c index d89401c0f51..344b5637ea5 100644 --- a/contrib/btree_gist/btree_time.c +++ b/contrib/btree_gist/btree_time.c @@ -7,6 +7,7 @@ #include "btree_utils_num.h" #include "utils/builtins.h" #include "utils/date.h" +#include "utils/sortsupport.h" #include "utils/timestamp.h" typedef struct @@ -28,6 +29,8 @@ PG_FUNCTION_INFO_V1(gbt_time_distance); PG_FUNCTION_INFO_V1(gbt_timetz_consistent); PG_FUNCTION_INFO_V1(gbt_time_penalty); PG_FUNCTION_INFO_V1(gbt_time_same); +PG_FUNCTION_INFO_V1(gbt_time_sortsupport); +PG_FUNCTION_INFO_V1(gbt_timetz_sortsupport); #ifdef USE_FLOAT8_BYVAL @@ -37,6 +40,18 @@ PG_FUNCTION_INFO_V1(gbt_time_same); #endif +static int +gbt_timekey_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + timeKEY *arg1 = (timeKEY *) DatumGetPointer(x); + timeKEY *arg2 = (timeKEY *) DatumGetPointer(y); + + /* lower and upper are equal during sortsupport comparison */ + return DatumGetInt32(DirectFunctionCall2(time_cmp, + TimeADTGetDatumFast(arg1->lower), + TimeADTGetDatumFast(arg2->lower))); +} + static bool gbt_timegt(const void *a, const void *b, FmgrInfo *flinfo) { @@ -332,3 +347,14 @@ gbt_time_same(PG_FUNCTION_ARGS) *result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo); PG_RETURN_POINTER(result); } + +Datum +gbt_time_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_timekey_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} diff --git a/contrib/btree_gist/btree_ts.c b/contrib/btree_gist/btree_ts.c index 3f5ba91891d..ef13fc15bba 100644 --- a/contrib/btree_gist/btree_ts.c +++ b/contrib/btree_gist/btree_ts.c @@ -10,6 +10,7 @@ #include "utils/builtins.h" #include "utils/datetime.h" #include "utils/float.h" +#include "utils/sortsupport.h" typedef struct { @@ -31,6 +32,7 @@ PG_FUNCTION_INFO_V1(gbt_tstz_consistent); PG_FUNCTION_INFO_V1(gbt_tstz_distance); PG_FUNCTION_INFO_V1(gbt_ts_penalty); PG_FUNCTION_INFO_V1(gbt_ts_same); +PG_FUNCTION_INFO_V1(gbt_ts_sortsupport); #ifdef USE_FLOAT8_BYVAL @@ -39,6 +41,29 @@ PG_FUNCTION_INFO_V1(gbt_ts_same); #define TimestampGetDatumFast(X) PointerGetDatum(&(X)) #endif +/* Sortsupport functions */ +static int +gbt_ts_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + tsKEY *arg1 = (tsKEY *) DatumGetPointer(x); + tsKEY *arg2 = (tsKEY *) DatumGetPointer(y); + + /* Sortsupport always gets the same lower and upper value for input keys */ + return DatumGetInt32(DirectFunctionCall2(timestamp_cmp, + TimestampGetDatumFast(arg1->lower), + TimestampGetDatumFast(arg2->lower))); +} + +Datum +gbt_ts_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_ts_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} static bool gbt_tsgt(const void *a, const void *b, FmgrInfo *flinfo) diff --git a/contrib/btree_gist/btree_utils_var.h b/contrib/btree_gist/btree_utils_var.h index 2f8def655c8..16ff165ad3c 100644 --- a/contrib/btree_gist/btree_utils_var.h +++ b/contrib/btree_gist/btree_utils_var.h @@ -42,7 +42,17 @@ typedef struct GBT_VARKEY *(*f_l2n) (GBT_VARKEY *, FmgrInfo *flinfo); /* convert leaf to node */ } gbtree_vinfo; - +/* + * Free ptr1 in case its a copy of ptr2. + * + * This is adapted from varlena's PG_FREE_IF_COPY, though + * doesn't require fcinfo access. + */ +#define GBT_FREE_IF_COPY(ptr1, ptr2) \ + do { \ + if ((Pointer) (ptr1) != DatumGetPointer(ptr2)) \ + pfree(ptr1); \ + } while (0) extern GBT_VARKEY_R gbt_var_key_readable(const GBT_VARKEY *k); diff --git a/contrib/btree_gist/btree_uuid.c b/contrib/btree_gist/btree_uuid.c index fe8c679cbed..08372fc8bc9 100644 --- a/contrib/btree_gist/btree_uuid.c +++ b/contrib/btree_gist/btree_uuid.c @@ -6,6 +6,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" #include "port/pg_bswap.h" +#include "utils/sortsupport.h" #include "utils/uuid.h" typedef struct @@ -25,7 +26,31 @@ PG_FUNCTION_INFO_V1(gbt_uuid_picksplit); PG_FUNCTION_INFO_V1(gbt_uuid_consistent); PG_FUNCTION_INFO_V1(gbt_uuid_penalty); PG_FUNCTION_INFO_V1(gbt_uuid_same); +PG_FUNCTION_INFO_V1(gbt_uuid_sortsupport); +static int uuid_internal_cmp(const pg_uuid_t *arg1, const pg_uuid_t *arg2); + +/* Sortsupport functions */ +static int +gbt_uuid_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + uuidKEY *arg1 = (uuidKEY *) DatumGetPointer(x); + uuidKEY *arg2 = (uuidKEY *) DatumGetPointer(y); + + /* Sortsupport gets equal upper and lower values for each key to compare */ + return uuid_internal_cmp(&arg1->lower, &arg2->lower); +} + +Datum +gbt_uuid_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_uuid_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} static int uuid_internal_cmp(const pg_uuid_t *arg1, const pg_uuid_t *arg2) diff --git a/contrib/btree_gist/meson.build b/contrib/btree_gist/meson.build index 73b1bbf52a6..5b4caff5baf 100644 --- a/contrib/btree_gist/meson.build +++ b/contrib/btree_gist/meson.build @@ -51,6 +51,7 @@ install_data( 'btree_gist--1.5--1.6.sql', 'btree_gist--1.6--1.7.sql', 'btree_gist--1.7--1.8.sql', + 'btree_gist--1.8--1.9.sql', kwargs: contrib_data_args, ) diff --git a/doc/src/sgml/btree-gist.sgml b/doc/src/sgml/btree-gist.sgml index 31e7c78aaef..8def2d3798e 100644 --- a/doc/src/sgml/btree-gist.sgml +++ b/doc/src/sgml/btree-gist.sgml @@ -52,6 +52,14 @@ <type>oid</type>, and <type>money</type>. </para> + <para> + Per default <filename>btree_gist</filename> builds <acronym>GiST</acronym> indexe with + <function>sortsupport</function> in <firstterm>sorted</firstterm> mode. This usually results in a + much better index quality and smaller index sizes by much faster index built speed. It is still + possible to revert to buffered built strategy by using the <literal>buffering</literal> parameter + when creating the index. + </para> + <para> This module is considered <quote>trusted</quote>, that is, it can be installed by non-superusers who have <literal>CREATE</literal> privilege [text/x-patch] v7-Add-GIST-sortsupport-rangetypes.patch (4.2K, ../../[email protected]/3-v7-Add-GIST-sortsupport-rangetypes.patch) download | inline diff: diff --git a/src/backend/utils/adt/rangetypes_gist.c b/src/backend/utils/adt/rangetypes_gist.c index cb28e9859ab..b0c77390024 100644 --- a/src/backend/utils/adt/rangetypes_gist.c +++ b/src/backend/utils/adt/rangetypes_gist.c @@ -21,6 +21,7 @@ #include "utils/fmgrprotos.h" #include "utils/multirangetypes.h" #include "utils/rangetypes.h" +#include "utils/sortsupport.h" /* * Range class properties used to segregate different classes of ranges in @@ -177,6 +178,7 @@ static void range_gist_double_sorting_split(TypeCacheEntry *typcache, static void range_gist_consider_split(ConsiderSplitContext *context, RangeBound *right_lower, int min_left_count, RangeBound *left_upper, int max_left_count); +static int range_gist_cmp(Datum a, Datum b, SortSupport ssup); static int get_gist_range_class(RangeType *range); static int single_bound_cmp(const void *a, const void *b, void *arg); static int interval_cmp_lower(const void *a, const void *b, void *arg); @@ -773,6 +775,20 @@ range_gist_picksplit(PG_FUNCTION_ARGS) PG_RETURN_POINTER(v); } +/* + * Sort support routine for fast GiST index build by sorting. + */ +Datum +range_gist_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = range_gist_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} + /* equality comparator for GiST */ Datum range_gist_same(PG_FUNCTION_ARGS) @@ -1693,6 +1709,60 @@ range_gist_consider_split(ConsiderSplitContext *context, } } +/* + * GiST sortsupport comparator for ranges. + * similar to range_cmp, but range typcache is cached. + */ +static int +range_gist_cmp(Datum a, Datum b, SortSupport ssup) +{ + RangeType *range_a = DatumGetRangeTypeP(a); + RangeType *range_b = DatumGetRangeTypeP(b); + TypeCacheEntry *typcache = ssup->ssup_extra; + RangeBound lower1, + lower2; + RangeBound upper1, + upper2; + bool empty1, + empty2; + int result; + + if (typcache == NULL) + { + Assert(RangeTypeGetOid(range_a) == RangeTypeGetOid(range_b)); + typcache = lookup_type_cache(RangeTypeGetOid(range_a), TYPECACHE_RANGE_INFO); + + /* + * Cache the range info between calls to avoid having to call + * lookup_type_cache() for each comparison. + */ + ssup->ssup_extra = typcache; + } + + range_deserialize(typcache, range_a, &lower1, &upper1, &empty1); + range_deserialize(typcache, range_b, &lower2, &upper2, &empty2); + + if (empty1 && empty2) + result = 0; + else if (empty1) + result = -1; + else if (empty2) + result = 1; + else + { + result = range_cmp_bounds(typcache, &lower1, &lower2); + if (result == 0) + result = range_cmp_bounds(typcache, &upper1, &upper2); + } + + if ((Datum) range_a != a) + pfree(range_a); + if ((Datum) range_b != b) + pfree(range_b); + + return result; +} + /* * Find class number for range. * diff --git a/src/include/catalog/pg_amproc.dat b/src/include/catalog/pg_amproc.dat index 352558c1f06..fc229d99845 100644 --- a/src/include/catalog/pg_amproc.dat +++ b/src/include/catalog/pg_amproc.dat @@ -607,6 +607,9 @@ { amprocfamily => 'gist/range_ops', amproclefttype => 'anyrange', amprocrighttype => 'anyrange', amprocnum => '7', amproc => 'range_gist_same' }, +{ amprocfamily => 'gist/range_ops', amproclefttype => 'anyrange', + amprocrighttype => 'anyrange', amprocnum => '11', + amproc => 'range_gist_sortsupport' }, { amprocfamily => 'gist/range_ops', amproclefttype => 'anyrange', amprocrighttype => 'anyrange', amprocnum => '12', amproc => 'gist_stratnum_identity' }, diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 29af4ce65d5..22315fefc9b 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -10589,6 +10589,9 @@ { oid => '3881', descr => 'GiST support', proname => 'range_gist_same', prorettype => 'internal', proargtypes => 'anyrange anyrange internal', prosrc => 'range_gist_same' }, +{ oid => '8849', descr => 'GiST support', + proname => 'range_gist_sortsupport', prorettype => 'void', + proargtypes => 'internal', prosrc => 'range_gist_sortsupport' }, { oid => '6154', descr => 'GiST support', proname => 'multirange_gist_consistent', prorettype => 'bool', proargtypes => 'internal anymultirange int2 oid internal', ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PATCH] Add sortsupport for range types and btree_gist @ 2024-03-22 13:20 Bernd Helmle <[email protected]> parent: Bernd Helmle <[email protected]> 0 siblings, 0 replies; 12+ messages in thread From: Bernd Helmle @ 2024-03-22 13:20 UTC (permalink / raw) To: jian he <[email protected]>; +Cc: [email protected] Hi, Here is a rebased version of the patch. Since i don't have anything to add at the moment and the numbers looks promising and to move on, i've marked this patch "Ready For Committer". Thanks, Bernd Attachments: [text/x-patch] v7.1-Add-GIST-sortsupport-btree-gist.patch (39.0K, ../../[email protected]/2-v7.1-Add-GIST-sortsupport-btree-gist.patch) download | inline diff: diff --git a/contrib/btree_gist/Makefile b/contrib/btree_gist/Makefile index 7ac2df26c10..68190ac5e46 100644 --- a/contrib/btree_gist/Makefile +++ b/contrib/btree_gist/Makefile @@ -34,7 +34,7 @@ DATA = btree_gist--1.0--1.1.sql \ btree_gist--1.1--1.2.sql btree_gist--1.2.sql btree_gist--1.2--1.3.sql \ btree_gist--1.3--1.4.sql btree_gist--1.4--1.5.sql \ btree_gist--1.5--1.6.sql btree_gist--1.6--1.7.sql \ - btree_gist--1.7--1.8.sql + btree_gist--1.7--1.8.sql btree_gist--1.8--1.9.sql PGFILEDESC = "btree_gist - B-tree equivalent GiST operator classes" REGRESS = init int2 int4 int8 float4 float8 cash oid timestamp timestamptz \ diff --git a/contrib/btree_gist/btree_bit.c b/contrib/btree_gist/btree_bit.c index 6790f22b4b6..67ea5d9e182 100644 --- a/contrib/btree_gist/btree_bit.c +++ b/contrib/btree_gist/btree_bit.c @@ -6,7 +6,7 @@ #include "btree_gist.h" #include "btree_utils_var.h" #include "utils/builtins.h" -#include "utils/bytea.h" +#include "utils/sortsupport.h" #include "utils/varbit.h" @@ -19,10 +19,33 @@ PG_FUNCTION_INFO_V1(gbt_bit_picksplit); PG_FUNCTION_INFO_V1(gbt_bit_consistent); PG_FUNCTION_INFO_V1(gbt_bit_penalty); PG_FUNCTION_INFO_V1(gbt_bit_same); +PG_FUNCTION_INFO_V1(gbt_bit_sortsupport); +PG_FUNCTION_INFO_V1(gbt_varbit_sortsupport); /* define for comparison */ +static int +gbt_bit_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + GBT_VARKEY *key1 = PG_DETOAST_DATUM(x); + GBT_VARKEY *key2 = PG_DETOAST_DATUM(y); + + GBT_VARKEY_R arg1 = gbt_var_key_readable(key1); + GBT_VARKEY_R arg2 = gbt_var_key_readable(key2); + Datum result; + + /* lower is always equal to upper, so just compare those fields */ + result = DirectFunctionCall2(bitcmp, + PointerGetDatum(arg1.lower), + PointerGetDatum(arg2.lower)); + + GBT_FREE_IF_COPY(key1, x); + GBT_FREE_IF_COPY(key2, y); + + return DatumGetInt32(result); +} + static bool gbt_bitgt(const void *a, const void *b, Oid collation, FmgrInfo *flinfo) { @@ -208,3 +231,25 @@ gbt_bit_penalty(PG_FUNCTION_ARGS) PG_RETURN_POINTER(gbt_var_penalty(result, o, n, PG_GET_COLLATION(), &tinfo, fcinfo->flinfo)); } + +Datum +gbt_bit_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_bit_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} + +Datum +gbt_varbit_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_bit_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} \ No newline at end of file diff --git a/contrib/btree_gist/btree_bool.c b/contrib/btree_gist/btree_bool.c index 8b2af129b52..5058dba2986 100644 --- a/contrib/btree_gist/btree_bool.c +++ b/contrib/btree_gist/btree_bool.c @@ -6,6 +6,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" #include "common/int.h" +#include "utils/sortsupport.h" typedef struct boolkey { @@ -23,6 +24,16 @@ PG_FUNCTION_INFO_V1(gbt_bool_picksplit); PG_FUNCTION_INFO_V1(gbt_bool_consistent); PG_FUNCTION_INFO_V1(gbt_bool_penalty); PG_FUNCTION_INFO_V1(gbt_bool_same); +PG_FUNCTION_INFO_V1(gbt_bool_sortsupport); + +static int +gbt_bool_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + boolKEY *arg1 = (boolKEY *) DatumGetPointer(x); + boolKEY *arg2 = (boolKEY *) DatumGetPointer(y); + + return arg1->lower - arg2->lower; +} static bool gbt_boolgt(const void *a, const void *b, FmgrInfo *flinfo) @@ -167,3 +178,14 @@ gbt_bool_same(PG_FUNCTION_ARGS) *result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo); PG_RETURN_POINTER(result); } + +Datum +gbt_bool_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_bool_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} diff --git a/contrib/btree_gist/btree_bytea.c b/contrib/btree_gist/btree_bytea.c index 6b005f0157e..2047e3ddee0 100644 --- a/contrib/btree_gist/btree_bytea.c +++ b/contrib/btree_gist/btree_bytea.c @@ -6,7 +6,7 @@ #include "btree_gist.h" #include "btree_utils_var.h" #include "utils/builtins.h" -#include "utils/bytea.h" +#include "utils/sortsupport.h" /* @@ -18,7 +18,41 @@ PG_FUNCTION_INFO_V1(gbt_bytea_picksplit); PG_FUNCTION_INFO_V1(gbt_bytea_consistent); PG_FUNCTION_INFO_V1(gbt_bytea_penalty); PG_FUNCTION_INFO_V1(gbt_bytea_same); +PG_FUNCTION_INFO_V1(gbt_bytea_sortsupport); +/* sortsupport support */ + +static int +gbt_bytea_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + GBT_VARKEY *key1 = PG_DETOAST_DATUM(x); + GBT_VARKEY *key2 = PG_DETOAST_DATUM(y); + + GBT_VARKEY_R xkey = gbt_var_key_readable(key1); + GBT_VARKEY_R ykey = gbt_var_key_readable(key2); + Datum result; + + /* lower and upper are always the same, so it is enough to compare lower */ + result = DirectFunctionCall2(byteacmp, + PointerGetDatum(xkey.lower), + PointerGetDatum(ykey.lower)); + + GBT_FREE_IF_COPY(key1, x); + GBT_FREE_IF_COPY(key2, y); + + return DatumGetInt32(result); +} + +Datum +gbt_bytea_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_bytea_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} /* define for comparison */ diff --git a/contrib/btree_gist/btree_cash.c b/contrib/btree_gist/btree_cash.c index 546b948ea40..94090c9ac0d 100644 --- a/contrib/btree_gist/btree_cash.c +++ b/contrib/btree_gist/btree_cash.c @@ -7,6 +7,7 @@ #include "btree_utils_num.h" #include "common/int.h" #include "utils/cash.h" +#include "utils/sortsupport.h" typedef struct { @@ -25,6 +26,21 @@ PG_FUNCTION_INFO_V1(gbt_cash_consistent); PG_FUNCTION_INFO_V1(gbt_cash_distance); PG_FUNCTION_INFO_V1(gbt_cash_penalty); PG_FUNCTION_INFO_V1(gbt_cash_same); +PG_FUNCTION_INFO_V1(gbt_cash_sortsupport); + +extern Datum cash_cmp(PG_FUNCTION_ARGS); + +static int +gbt_cash_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + cashKEY *arg1 = (cashKEY *) DatumGetPointer(x); + cashKEY *arg2 = (cashKEY *) DatumGetPointer(y); + + /* Since lower and upper are always equal, it is enough to compare lower */ + return DatumGetInt32(DirectFunctionCall2(cash_cmp, + CashGetDatum(arg1->lower), + CashGetDatum(arg2->lower))); +} static bool gbt_cashgt(const void *a, const void *b, FmgrInfo *flinfo) @@ -215,3 +231,14 @@ gbt_cash_same(PG_FUNCTION_ARGS) *result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo); PG_RETURN_POINTER(result); } + +Datum +gbt_cash_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_cash_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} diff --git a/contrib/btree_gist/btree_date.c b/contrib/btree_gist/btree_date.c index 68a4107dbf0..c4ff15f27b4 100644 --- a/contrib/btree_gist/btree_date.c +++ b/contrib/btree_gist/btree_date.c @@ -6,6 +6,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" #include "utils/builtins.h" +#include "utils/sortsupport.h" #include "utils/date.h" typedef struct @@ -25,6 +26,30 @@ PG_FUNCTION_INFO_V1(gbt_date_consistent); PG_FUNCTION_INFO_V1(gbt_date_distance); PG_FUNCTION_INFO_V1(gbt_date_penalty); PG_FUNCTION_INFO_V1(gbt_date_same); +PG_FUNCTION_INFO_V1(gbt_date_sortsupport); + +/* sortsupport functions */ + +static int gbt_date_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + dateKEY *akey = (dateKEY *) DatumGetPointer(x); + dateKEY *bkey = (dateKEY *) DatumGetPointer(y); + + return DatumGetInt32(DirectFunctionCall2(date_cmp, + DateADTGetDatum(akey->lower), + DateADTGetDatum(bkey->lower))); +} + +Datum +gbt_date_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_date_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} static bool gbt_dategt(const void *a, const void *b, FmgrInfo *flinfo) diff --git a/contrib/btree_gist/btree_enum.c b/contrib/btree_gist/btree_enum.c index d4dc38a38e5..67395182341 100644 --- a/contrib/btree_gist/btree_enum.c +++ b/contrib/btree_gist/btree_enum.c @@ -7,6 +7,7 @@ #include "btree_utils_num.h" #include "fmgr.h" #include "utils/builtins.h" +#include "utils/sortsupport.h" /* enums are really Oids, so we just use the same structure */ @@ -26,8 +27,20 @@ PG_FUNCTION_INFO_V1(gbt_enum_picksplit); PG_FUNCTION_INFO_V1(gbt_enum_consistent); PG_FUNCTION_INFO_V1(gbt_enum_penalty); PG_FUNCTION_INFO_V1(gbt_enum_same); +PG_FUNCTION_INFO_V1(gbt_enum_sortsupport); +static int +gbt_enum_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + oidKEY *arg1 = (oidKEY *) DatumGetPointer(x); + oidKEY *arg2 = (oidKEY *) DatumGetPointer(y); + + /* Since lower and upper in oidKEY are always the same, just compare lower */ + return DatumGetInt32(CallerFInfoFunctionCall2(enum_cmp, ssup->ssup_extra, + InvalidOid, arg1->lower, arg2->lower)); +} + static bool gbt_enumgt(const void *a, const void *b, FmgrInfo *flinfo) { @@ -183,3 +196,20 @@ gbt_enum_same(PG_FUNCTION_ARGS) *result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo); PG_RETURN_POINTER(result); } + +Datum +gbt_enum_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_enum_ssup_cmp; + + /* + * Since enum_fast_cmp() also uses enum_cmp() like the rest of the + * comparsion functions, it also needs to pass on flinfo when calling + * it. Thus save it in ->ssup_extra and later retrieve it in enum_fast_cmp(). + */ + ssup->ssup_extra = fcinfo->flinfo; + + PG_RETURN_VOID(); +} diff --git a/contrib/btree_gist/btree_float4.c b/contrib/btree_gist/btree_float4.c index 84ca5eee501..b2229cdb04d 100644 --- a/contrib/btree_gist/btree_float4.c +++ b/contrib/btree_gist/btree_float4.c @@ -5,6 +5,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" +#include "utils/sortsupport.h" #include "utils/float.h" typedef struct float4key @@ -24,6 +25,33 @@ PG_FUNCTION_INFO_V1(gbt_float4_consistent); PG_FUNCTION_INFO_V1(gbt_float4_distance); PG_FUNCTION_INFO_V1(gbt_float4_penalty); PG_FUNCTION_INFO_V1(gbt_float4_same); +PG_FUNCTION_INFO_V1(gbt_float4_sortsupport); + +extern Datum btfloat4cmp(PG_FUNCTION_ARGS); + +/* sortsupport functions */ +static int +gbt_float4_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + float4KEY *arg1 = (float4KEY *) DatumGetPointer(x); + float4KEY *arg2 = (float4KEY *) DatumGetPointer(y); + + /* Since lower and upper for float4KEYs here are always equal it is okay to compare them only */ + return DatumGetInt32(DirectFunctionCall2(btfloat4cmp, + Float4GetDatum(arg1->lower), + Float4GetDatum(arg2->lower))); +} + +Datum +gbt_float4_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_float4_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} static bool gbt_float4gt(const void *a, const void *b, FmgrInfo *flinfo) diff --git a/contrib/btree_gist/btree_float8.c b/contrib/btree_gist/btree_float8.c index 081a719b006..2f312f5bd83 100644 --- a/contrib/btree_gist/btree_float8.c +++ b/contrib/btree_gist/btree_float8.c @@ -5,6 +5,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" +#include "utils/sortsupport.h" #include "utils/float.h" typedef struct float8key @@ -24,7 +25,33 @@ PG_FUNCTION_INFO_V1(gbt_float8_consistent); PG_FUNCTION_INFO_V1(gbt_float8_distance); PG_FUNCTION_INFO_V1(gbt_float8_penalty); PG_FUNCTION_INFO_V1(gbt_float8_same); +PG_FUNCTION_INFO_V1(gbt_float8_sortsupport); +extern Datum btfloat8cmp(PG_FUNCTION_ARGS); + +/* sortsupport functions */ +static int +gbt_float8_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + float8KEY *arg1 = (float8KEY *) DatumGetPointer(x); + float8KEY *arg2 = (float8KEY *) DatumGetPointer(y); + + /* upper and lower for input keys are equal here */ + return DatumGetInt32(DirectFunctionCall2(btfloat8cmp, + Float8GetDatum(arg1->lower), + Float8GetDatum(arg2->lower))); +} + +Datum +gbt_float8_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_float8_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} static bool gbt_float8gt(const void *a, const void *b, FmgrInfo *flinfo) diff --git a/contrib/btree_gist/btree_gist--1.7--1.8.sql b/contrib/btree_gist/btree_gist--1.7--1.8.sql index 307bfe574b0..657285ca7ce 100644 --- a/contrib/btree_gist/btree_gist--1.7--1.8.sql +++ b/contrib/btree_gist/btree_gist--1.7--1.8.sql @@ -85,3 +85,4 @@ ALTER OPERATOR FAMILY gist_enum_ops USING gist ADD ALTER OPERATOR FAMILY gist_bool_ops USING gist ADD FUNCTION 12 (bool, bool) gist_stratnum_btree (int2) ; + diff --git a/contrib/btree_gist/btree_gist--1.8--1.9.sql b/contrib/btree_gist/btree_gist--1.8--1.9.sql new file mode 100644 index 00000000000..c52e750ed1a --- /dev/null +++ b/contrib/btree_gist/btree_gist--1.8--1.9.sql @@ -0,0 +1,192 @@ +/* contrib/btree_gist/btree_gist--1.7--1.8.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "ALTER EXTENSION btree_gist UPDATE TO '1.9'" to load this file. \quit + +CREATE FUNCTION gbt_bit_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_varbit_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_bool_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_cash_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_enum_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_inet_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_intv_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_macad8_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_time_sortsupport(internal) +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_int2_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_int4_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_int8_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_bytea_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_date_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_float4_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_float8_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_macaddr_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_numeric_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_oid_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_text_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_ts_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_uuid_sortsupport(internal) + RETURNS void +AS 'MODULE_PATHNAME' + LANGUAGE C IMMUTABLE STRICT; + +ALTER OPERATOR FAMILY gist_vbit_ops USING gist ADD + FUNCTION 11 (varbit, varbit) gbt_varbit_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_bit_ops USING gist ADD + FUNCTION 11 (bit, bit) gbt_bit_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_bool_ops USING gist ADD + FUNCTION 11 (bool, bool) gbt_bool_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_bytea_ops USING gist ADD + FUNCTION 11 (bytea, bytea) gbt_bytea_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_cash_ops USING gist ADD + FUNCTION 11 (money, money) gbt_cash_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_date_ops USING gist ADD + FUNCTION 11 (date, date) gbt_date_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_enum_ops USING gist ADD + FUNCTION 11 (anyenum, anyenum) gbt_enum_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_float4_ops USING gist ADD + FUNCTION 11 (float4, float4) gbt_float4_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_float8_ops USING gist ADD + FUNCTION 11 (float8, float8) gbt_float8_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_inet_ops USING gist ADD + FUNCTION 11 (inet, inet) gbt_inet_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_cidr_ops USING gist ADD + FUNCTION 11 (inet, inet) gbt_inet_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_int2_ops USING gist ADD + FUNCTION 11 (int2, int2) gbt_int2_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_int4_ops USING gist ADD + FUNCTION 11 (int4, int4) gbt_int4_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_int8_ops USING gist ADD + FUNCTION 11 (int8, int8) gbt_int8_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_interval_ops USING gist ADD + FUNCTION 11 (interval, interval) gbt_intv_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_macaddr_ops USING gist ADD + FUNCTION 11 (macaddr, macaddr) gbt_macad8_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_macaddr8_ops USING gist ADD + FUNCTION 11 (macaddr8, macaddr8) gbt_macad8_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_numeric_ops USING gist ADD + FUNCTION 11 (numeric, numeric) gbt_numeric_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_oid_ops USING gist ADD + FUNCTION 11 (oid, oid) gbt_oid_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_text_ops USING gist ADD + FUNCTION 11 (text, text) gbt_text_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_bpchar_ops USING gist ADD + FUNCTION 11 (bpchar, bpchar) bpchar_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_time_ops USING gist ADD + FUNCTION 11 (time, time) gbt_time_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_timestamp_ops USING gist ADD + FUNCTION 11 (timestamp, timestamp) gbt_ts_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_timestamptz_ops USING gist ADD + FUNCTION 11 (timestamptz, timestamptz) gbt_ts_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_timetz_ops USING gist ADD + FUNCTION 11 (timetz, timetz) gbt_time_sortsupport (internal) ; + +ALTER OPERATOR FAMILY gist_uuid_ops USING gist ADD + FUNCTION 11 (uuid, uuid) gbt_uuid_sortsupport (internal) ; diff --git a/contrib/btree_gist/btree_gist.c b/contrib/btree_gist/btree_gist.c index 5fd4cce27d0..c8acbe76b55 100644 --- a/contrib/btree_gist/btree_gist.c +++ b/contrib/btree_gist/btree_gist.c @@ -6,6 +6,9 @@ #include "access/stratnum.h" #include "utils/builtins.h" +#include "access/gist.h" +#include "btree_gist.h" + PG_MODULE_MAGIC; PG_FUNCTION_INFO_V1(gbt_decompress); diff --git a/contrib/btree_gist/btree_gist.control b/contrib/btree_gist/btree_gist.control index abf66538f32..69d9341a0ad 100644 --- a/contrib/btree_gist/btree_gist.control +++ b/contrib/btree_gist/btree_gist.control @@ -1,6 +1,6 @@ # btree_gist extension comment = 'support for indexing common datatypes in GiST' -default_version = '1.8' +default_version = '1.9' module_pathname = '$libdir/btree_gist' relocatable = true trusted = true diff --git a/contrib/btree_gist/btree_gist.h b/contrib/btree_gist/btree_gist.h index 0db8522c8a7..245c3559281 100644 --- a/contrib/btree_gist/btree_gist.h +++ b/contrib/btree_gist/btree_gist.h @@ -9,6 +9,12 @@ #define BtreeGistNotEqualStrategyNumber 6 +typedef struct int32key +{ + int32 lower; + int32 upper; +} int32KEY; + /* indexed types */ enum gbtree_type diff --git a/contrib/btree_gist/btree_inet.c b/contrib/btree_gist/btree_inet.c index 2fb952dca83..77ef14a4f7d 100644 --- a/contrib/btree_gist/btree_inet.c +++ b/contrib/btree_gist/btree_inet.c @@ -8,6 +8,7 @@ #include "catalog/pg_type.h" #include "utils/builtins.h" #include "utils/inet.h" +#include "utils/sortsupport.h" typedef struct inetkey { @@ -24,8 +25,23 @@ PG_FUNCTION_INFO_V1(gbt_inet_picksplit); PG_FUNCTION_INFO_V1(gbt_inet_consistent); PG_FUNCTION_INFO_V1(gbt_inet_penalty); PG_FUNCTION_INFO_V1(gbt_inet_same); +PG_FUNCTION_INFO_V1(gbt_inet_sortsupport); +static int +gbt_inet_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + inetKEY *arg1 = (inetKEY *) DatumGetPointer(x); + inetKEY *arg2 = (inetKEY *) DatumGetPointer(y); + + if (arg1->lower == arg2->lower) + return 0; + else if (arg1->lower > arg2->lower) + return 1; + else + return -1; +} + static bool gbt_inetgt(const void *a, const void *b, FmgrInfo *flinfo) { @@ -185,3 +201,14 @@ gbt_inet_same(PG_FUNCTION_ARGS) *result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo); PG_RETURN_POINTER(result); } + +Datum +gbt_inet_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_inet_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} diff --git a/contrib/btree_gist/btree_int2.c b/contrib/btree_gist/btree_int2.c index fdbf156586c..b19770ff67f 100644 --- a/contrib/btree_gist/btree_int2.c +++ b/contrib/btree_gist/btree_int2.c @@ -5,6 +5,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" +#include "utils/sortsupport.h" #include "common/int.h" typedef struct int16key @@ -24,6 +25,31 @@ PG_FUNCTION_INFO_V1(gbt_int2_consistent); PG_FUNCTION_INFO_V1(gbt_int2_distance); PG_FUNCTION_INFO_V1(gbt_int2_penalty); PG_FUNCTION_INFO_V1(gbt_int2_same); +PG_FUNCTION_INFO_V1(gbt_int2_sortsupport); + +/* sortsupport functions */ +static int +gbt_int2_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + int16KEY *arg1 = (int16KEY *) DatumGetPointer(x); + int16KEY *arg2 = (int16KEY *) DatumGetPointer(y); + + if (arg1->lower < arg2->lower) + return -1; + else if (arg1->lower > arg2->lower) + return 1; + else + return 0; +} + +Datum +gbt_int2_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_int2_ssup_cmp; + PG_RETURN_VOID(); +} static bool gbt_int2gt(const void *a, const void *b, FmgrInfo *flinfo) diff --git a/contrib/btree_gist/btree_int4.c b/contrib/btree_gist/btree_int4.c index 8915fb5d087..60418511ede 100644 --- a/contrib/btree_gist/btree_int4.c +++ b/contrib/btree_gist/btree_int4.c @@ -2,16 +2,10 @@ * contrib/btree_gist/btree_int4.c */ #include "postgres.h" - +#include "common/int.h" +#include "utils/sortsupport.h" #include "btree_gist.h" #include "btree_utils_num.h" -#include "common/int.h" - -typedef struct int32key -{ - int32 lower; - int32 upper; -} int32KEY; /* ** int32 ops @@ -24,6 +18,7 @@ PG_FUNCTION_INFO_V1(gbt_int4_consistent); PG_FUNCTION_INFO_V1(gbt_int4_distance); PG_FUNCTION_INFO_V1(gbt_int4_penalty); PG_FUNCTION_INFO_V1(gbt_int4_same); +PG_FUNCTION_INFO_V1(gbt_int4_sortsupport); static bool @@ -90,6 +85,29 @@ static const gbtree_ninfo tinfo = gbt_int4_dist }; +static int +gbt_int4_ssup_cmp(Datum a, Datum b, SortSupport ssup) +{ + int32KEY *ia = (int32KEY *) DatumGetPointer(a); + int32KEY *ib = (int32KEY *) DatumGetPointer(b); + + /* int4KEY upper and lower are always the same for input keys here. */ + if (ia->lower < ib->lower) + return -1; + else if (ia->lower > ib->lower) + return 1; + else + return 0; +} + +Datum +gbt_int4_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_int4_ssup_cmp; + PG_RETURN_VOID(); +} PG_FUNCTION_INFO_V1(int4_dist); Datum diff --git a/contrib/btree_gist/btree_int8.c b/contrib/btree_gist/btree_int8.c index 7c63a5b6dc1..00d55163fc9 100644 --- a/contrib/btree_gist/btree_int8.c +++ b/contrib/btree_gist/btree_int8.c @@ -6,6 +6,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" #include "common/int.h" +#include "utils/sortsupport.h" typedef struct int64key { @@ -24,7 +25,32 @@ PG_FUNCTION_INFO_V1(gbt_int8_consistent); PG_FUNCTION_INFO_V1(gbt_int8_distance); PG_FUNCTION_INFO_V1(gbt_int8_penalty); PG_FUNCTION_INFO_V1(gbt_int8_same); +PG_FUNCTION_INFO_V1(gbt_int8_sortsupport); +/* sortsupport functions */ +static int +gbt_int8_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + int64KEY *arg1 = (int64KEY *) DatumGetPointer(x); + int64KEY *arg2 = (int64KEY *) DatumGetPointer(y); + + if (arg1->lower < arg2->lower) + return -1; + else if (arg1->lower > arg2->lower) + return 1; + else + return 0; + +} + +Datum +gbt_int8_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_int8_ssup_cmp; + PG_RETURN_VOID(); +} static bool gbt_int8gt(const void *a, const void *b, FmgrInfo *flinfo) diff --git a/contrib/btree_gist/btree_interval.c b/contrib/btree_gist/btree_interval.c index b0afdf02bb5..4da30be9e46 100644 --- a/contrib/btree_gist/btree_interval.c +++ b/contrib/btree_gist/btree_interval.c @@ -6,6 +6,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" #include "utils/builtins.h" +#include "utils/sortsupport.h" #include "utils/timestamp.h" typedef struct @@ -27,8 +28,21 @@ PG_FUNCTION_INFO_V1(gbt_intv_consistent); PG_FUNCTION_INFO_V1(gbt_intv_distance); PG_FUNCTION_INFO_V1(gbt_intv_penalty); PG_FUNCTION_INFO_V1(gbt_intv_same); +PG_FUNCTION_INFO_V1(gbt_intv_sortsupport); +static int +gbt_intv_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + intvKEY *arg1 = (intvKEY *) DatumGetPointer(x); + intvKEY *arg2 = (intvKEY *) DatumGetPointer(y); + + /* intvKEY lower and upper are always equal here, so compare just lower members is enough */ + return DatumGetInt32(DirectFunctionCall2(interval_cmp, + IntervalPGetDatum(&arg1->lower), + IntervalPGetDatum(&arg2->lower))); +} + static bool gbt_intvgt(const void *a, const void *b, FmgrInfo *flinfo) { @@ -295,3 +309,14 @@ gbt_intv_same(PG_FUNCTION_ARGS) *result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo); PG_RETURN_POINTER(result); } + +Datum +gbt_intv_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_intv_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} diff --git a/contrib/btree_gist/btree_macaddr.c b/contrib/btree_gist/btree_macaddr.c index 17290529c02..b95fd8d3409 100644 --- a/contrib/btree_gist/btree_macaddr.c +++ b/contrib/btree_gist/btree_macaddr.c @@ -7,6 +7,7 @@ #include "btree_utils_num.h" #include "utils/builtins.h" #include "utils/inet.h" +#include "utils/sortsupport.h" typedef struct { @@ -25,6 +26,34 @@ PG_FUNCTION_INFO_V1(gbt_macad_picksplit); PG_FUNCTION_INFO_V1(gbt_macad_consistent); PG_FUNCTION_INFO_V1(gbt_macad_penalty); PG_FUNCTION_INFO_V1(gbt_macad_same); +PG_FUNCTION_INFO_V1(gbt_macaddr_sortsupport); + +/* sortsupport functions */ +static int +gbt_macaddr_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + macKEY *arg1 = (macKEY *) DatumGetPointer(x); + macKEY *arg2 = (macKEY *) DatumGetPointer(y); + + /* macKEY lower and upper members are always equal here, + * so its enough to compare just lower. + */ + return DatumGetInt32(DirectFunctionCall2(macaddr_cmp, + MacaddrPGetDatum(&arg1->lower), + MacaddrPGetDatum(&arg2->lower))); + +} + +Datum +gbt_macaddr_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_macaddr_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} static bool diff --git a/contrib/btree_gist/btree_macaddr8.c b/contrib/btree_gist/btree_macaddr8.c index 796cc4efee3..c3ff7eeb0b5 100644 --- a/contrib/btree_gist/btree_macaddr8.c +++ b/contrib/btree_gist/btree_macaddr8.c @@ -7,6 +7,7 @@ #include "btree_utils_num.h" #include "utils/builtins.h" #include "utils/inet.h" +#include "utils/sortsupport.h" typedef struct { @@ -25,8 +26,20 @@ PG_FUNCTION_INFO_V1(gbt_macad8_picksplit); PG_FUNCTION_INFO_V1(gbt_macad8_consistent); PG_FUNCTION_INFO_V1(gbt_macad8_penalty); PG_FUNCTION_INFO_V1(gbt_macad8_same); +PG_FUNCTION_INFO_V1(gbt_macad8_sortsupport); +static int +gbt_macaddr8_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + mac8KEY *arg1 = (mac8KEY *) DatumGetPointer(x); + mac8KEY *arg2 = (mac8KEY *) DatumGetPointer(y); + + return DatumGetInt32(DirectFunctionCall2(macaddr8_cmp, + Macaddr8PGetDatum(&arg1->lower), + Macaddr8PGetDatum(&arg2->lower))); +} + static bool gbt_macad8gt(const void *a, const void *b, FmgrInfo *flinfo) { @@ -194,3 +207,14 @@ gbt_macad8_same(PG_FUNCTION_ARGS) *result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo); PG_RETURN_POINTER(result); } + +Datum +gbt_macad8_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_macaddr8_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} diff --git a/contrib/btree_gist/btree_numeric.c b/contrib/btree_gist/btree_numeric.c index 35e466cdd94..227712645da 100644 --- a/contrib/btree_gist/btree_numeric.c +++ b/contrib/btree_gist/btree_numeric.c @@ -11,6 +11,7 @@ #include "utils/builtins.h" #include "utils/numeric.h" #include "utils/rel.h" +#include "utils/sortsupport.h" /* ** Bytea ops @@ -21,7 +22,39 @@ PG_FUNCTION_INFO_V1(gbt_numeric_picksplit); PG_FUNCTION_INFO_V1(gbt_numeric_consistent); PG_FUNCTION_INFO_V1(gbt_numeric_penalty); PG_FUNCTION_INFO_V1(gbt_numeric_same); +PG_FUNCTION_INFO_V1(gbt_numeric_sortsupport); +/* Sortsupport functions */ +static int +gbt_numeric_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + GBT_VARKEY *key1 = PG_DETOAST_DATUM(x); + GBT_VARKEY *key2 = PG_DETOAST_DATUM(y); + + GBT_VARKEY_R arg1 = gbt_var_key_readable(key1); + GBT_VARKEY_R arg2 = gbt_var_key_readable(key2); + Datum result; + + result = DirectFunctionCall2(numeric_cmp, + PointerGetDatum(arg1.lower), + PointerGetDatum(arg2.lower)); + + GBT_FREE_IF_COPY(key1, x); + GBT_FREE_IF_COPY(key2, y); + + return DatumGetInt32(result); +} + +Datum +gbt_numeric_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_numeric_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} /* define for comparison */ diff --git a/contrib/btree_gist/btree_oid.c b/contrib/btree_gist/btree_oid.c index 3cc7d4245d4..4ce4d3ff87e 100644 --- a/contrib/btree_gist/btree_oid.c +++ b/contrib/btree_gist/btree_oid.c @@ -5,6 +5,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" +#include "utils/sortsupport.h" typedef struct { @@ -23,7 +24,34 @@ PG_FUNCTION_INFO_V1(gbt_oid_consistent); PG_FUNCTION_INFO_V1(gbt_oid_distance); PG_FUNCTION_INFO_V1(gbt_oid_penalty); PG_FUNCTION_INFO_V1(gbt_oid_same); +PG_FUNCTION_INFO_V1(gbt_oid_sortsupport); +/* Sortsupport functions */ +static int +gbt_oid_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + oidKEY *arg1 = (oidKEY *) DatumGetPointer(x); + oidKEY *arg2 = (oidKEY *) DatumGetPointer(y); + + /* upper and lower fields are equal for each oidKEY, so just compare lower */ + if (arg1->lower > arg2->lower) + return 1; + else if (arg1->lower < arg2->lower) + return -1; + else + return 0; +} + +Datum +gbt_oid_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_oid_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} static bool gbt_oidgt(const void *a, const void *b, FmgrInfo *flinfo) diff --git a/contrib/btree_gist/btree_text.c b/contrib/btree_gist/btree_text.c index be0eac7975b..530648cb629 100644 --- a/contrib/btree_gist/btree_text.c +++ b/contrib/btree_gist/btree_text.c @@ -6,6 +6,7 @@ #include "btree_gist.h" #include "btree_utils_var.h" #include "utils/builtins.h" +#include "utils/sortsupport.h" /* ** Text ops @@ -18,7 +19,40 @@ PG_FUNCTION_INFO_V1(gbt_text_consistent); PG_FUNCTION_INFO_V1(gbt_bpchar_consistent); PG_FUNCTION_INFO_V1(gbt_text_penalty); PG_FUNCTION_INFO_V1(gbt_text_same); +PG_FUNCTION_INFO_V1(gbt_text_sortsupport); +/* Sortsupport functions */ +static int +gbt_text_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + GBT_VARKEY *key1 = PG_DETOAST_DATUM(x); + GBT_VARKEY *key2 = PG_DETOAST_DATUM(y); + + GBT_VARKEY_R arg1 = gbt_var_key_readable(key1); + GBT_VARKEY_R arg2 = gbt_var_key_readable(key2); + Datum result; + + result = DirectFunctionCall2Coll(bttextcmp, + ssup->ssup_collation, + PointerGetDatum(arg1.lower), + PointerGetDatum(arg2.lower)); + + GBT_FREE_IF_COPY(key1, x); + GBT_FREE_IF_COPY(key2, y); + + return DatumGetInt32(result); +} + +Datum +gbt_text_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_text_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} /* define for comparison */ diff --git a/contrib/btree_gist/btree_time.c b/contrib/btree_gist/btree_time.c index d89401c0f51..344b5637ea5 100644 --- a/contrib/btree_gist/btree_time.c +++ b/contrib/btree_gist/btree_time.c @@ -7,6 +7,7 @@ #include "btree_utils_num.h" #include "utils/builtins.h" #include "utils/date.h" +#include "utils/sortsupport.h" #include "utils/timestamp.h" typedef struct @@ -28,6 +29,8 @@ PG_FUNCTION_INFO_V1(gbt_time_distance); PG_FUNCTION_INFO_V1(gbt_timetz_consistent); PG_FUNCTION_INFO_V1(gbt_time_penalty); PG_FUNCTION_INFO_V1(gbt_time_same); +PG_FUNCTION_INFO_V1(gbt_time_sortsupport); +PG_FUNCTION_INFO_V1(gbt_timetz_sortsupport); #ifdef USE_FLOAT8_BYVAL @@ -37,6 +40,18 @@ PG_FUNCTION_INFO_V1(gbt_time_same); #endif +static int +gbt_timekey_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + timeKEY *arg1 = (timeKEY *) DatumGetPointer(x); + timeKEY *arg2 = (timeKEY *) DatumGetPointer(y); + + /* lower and upper are equal during sortsupport comparison */ + return DatumGetInt32(DirectFunctionCall2(time_cmp, + TimeADTGetDatumFast(arg1->lower), + TimeADTGetDatumFast(arg2->lower))); +} + static bool gbt_timegt(const void *a, const void *b, FmgrInfo *flinfo) { @@ -332,3 +347,14 @@ gbt_time_same(PG_FUNCTION_ARGS) *result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo); PG_RETURN_POINTER(result); } + +Datum +gbt_time_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_timekey_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} diff --git a/contrib/btree_gist/btree_ts.c b/contrib/btree_gist/btree_ts.c index 3f5ba91891d..ef13fc15bba 100644 --- a/contrib/btree_gist/btree_ts.c +++ b/contrib/btree_gist/btree_ts.c @@ -10,6 +10,7 @@ #include "utils/builtins.h" #include "utils/datetime.h" #include "utils/float.h" +#include "utils/sortsupport.h" typedef struct { @@ -31,6 +32,7 @@ PG_FUNCTION_INFO_V1(gbt_tstz_consistent); PG_FUNCTION_INFO_V1(gbt_tstz_distance); PG_FUNCTION_INFO_V1(gbt_ts_penalty); PG_FUNCTION_INFO_V1(gbt_ts_same); +PG_FUNCTION_INFO_V1(gbt_ts_sortsupport); #ifdef USE_FLOAT8_BYVAL @@ -39,6 +41,29 @@ PG_FUNCTION_INFO_V1(gbt_ts_same); #define TimestampGetDatumFast(X) PointerGetDatum(&(X)) #endif +/* Sortsupport functions */ +static int +gbt_ts_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + tsKEY *arg1 = (tsKEY *) DatumGetPointer(x); + tsKEY *arg2 = (tsKEY *) DatumGetPointer(y); + + /* Sortsupport always gets the same lower and upper value for input keys */ + return DatumGetInt32(DirectFunctionCall2(timestamp_cmp, + TimestampGetDatumFast(arg1->lower), + TimestampGetDatumFast(arg2->lower))); +} + +Datum +gbt_ts_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_ts_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} static bool gbt_tsgt(const void *a, const void *b, FmgrInfo *flinfo) diff --git a/contrib/btree_gist/btree_utils_var.h b/contrib/btree_gist/btree_utils_var.h index 2f8def655c8..16ff165ad3c 100644 --- a/contrib/btree_gist/btree_utils_var.h +++ b/contrib/btree_gist/btree_utils_var.h @@ -42,7 +42,17 @@ typedef struct GBT_VARKEY *(*f_l2n) (GBT_VARKEY *, FmgrInfo *flinfo); /* convert leaf to node */ } gbtree_vinfo; - +/* + * Free ptr1 in case its a copy of ptr2. + * + * This is adapted from varlena's PG_FREE_IF_COPY, though + * doesn't require fcinfo access. + */ +#define GBT_FREE_IF_COPY(ptr1, ptr2) \ + do { \ + if ((Pointer) (ptr1) != DatumGetPointer(ptr2)) \ + pfree(ptr1); \ + } while (0) extern GBT_VARKEY_R gbt_var_key_readable(const GBT_VARKEY *k); diff --git a/contrib/btree_gist/btree_uuid.c b/contrib/btree_gist/btree_uuid.c index fe8c679cbed..08372fc8bc9 100644 --- a/contrib/btree_gist/btree_uuid.c +++ b/contrib/btree_gist/btree_uuid.c @@ -6,6 +6,7 @@ #include "btree_gist.h" #include "btree_utils_num.h" #include "port/pg_bswap.h" +#include "utils/sortsupport.h" #include "utils/uuid.h" typedef struct @@ -25,7 +26,31 @@ PG_FUNCTION_INFO_V1(gbt_uuid_picksplit); PG_FUNCTION_INFO_V1(gbt_uuid_consistent); PG_FUNCTION_INFO_V1(gbt_uuid_penalty); PG_FUNCTION_INFO_V1(gbt_uuid_same); +PG_FUNCTION_INFO_V1(gbt_uuid_sortsupport); +static int uuid_internal_cmp(const pg_uuid_t *arg1, const pg_uuid_t *arg2); + +/* Sortsupport functions */ +static int +gbt_uuid_ssup_cmp(Datum x, Datum y, SortSupport ssup) +{ + uuidKEY *arg1 = (uuidKEY *) DatumGetPointer(x); + uuidKEY *arg2 = (uuidKEY *) DatumGetPointer(y); + + /* Sortsupport gets equal upper and lower values for each key to compare */ + return uuid_internal_cmp(&arg1->lower, &arg2->lower); +} + +Datum +gbt_uuid_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = gbt_uuid_ssup_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} static int uuid_internal_cmp(const pg_uuid_t *arg1, const pg_uuid_t *arg2) diff --git a/contrib/btree_gist/meson.build b/contrib/btree_gist/meson.build index 73b1bbf52a6..5b4caff5baf 100644 --- a/contrib/btree_gist/meson.build +++ b/contrib/btree_gist/meson.build @@ -51,6 +51,7 @@ install_data( 'btree_gist--1.5--1.6.sql', 'btree_gist--1.6--1.7.sql', 'btree_gist--1.7--1.8.sql', + 'btree_gist--1.8--1.9.sql', kwargs: contrib_data_args, ) diff --git a/doc/src/sgml/btree-gist.sgml b/doc/src/sgml/btree-gist.sgml index 31e7c78aaef..8def2d3798e 100644 --- a/doc/src/sgml/btree-gist.sgml +++ b/doc/src/sgml/btree-gist.sgml @@ -52,6 +52,14 @@ <type>oid</type>, and <type>money</type>. </para> + <para> + Per default <filename>btree_gist</filename> builds <acronym>GiST</acronym> indexe with + <function>sortsupport</function> in <firstterm>sorted</firstterm> mode. This usually results in a + much better index quality and smaller index sizes by much faster index built speed. It is still + possible to revert to buffered built strategy by using the <literal>buffering</literal> parameter + when creating the index. + </para> + <para> This module is considered <quote>trusted</quote>, that is, it can be installed by non-superusers who have <literal>CREATE</literal> privilege [text/x-patch] v7.1-Add-GIST-sortsupport-rangetypes.patch (4.2K, ../../[email protected]/3-v7.1-Add-GIST-sortsupport-rangetypes.patch) download | inline diff: diff --git a/src/backend/utils/adt/rangetypes_gist.c b/src/backend/utils/adt/rangetypes_gist.c index cb28e9859ab..b0c77390024 100644 --- a/src/backend/utils/adt/rangetypes_gist.c +++ b/src/backend/utils/adt/rangetypes_gist.c @@ -21,6 +21,7 @@ #include "utils/fmgrprotos.h" #include "utils/multirangetypes.h" #include "utils/rangetypes.h" +#include "utils/sortsupport.h" /* * Range class properties used to segregate different classes of ranges in @@ -177,6 +178,7 @@ static void range_gist_double_sorting_split(TypeCacheEntry *typcache, static void range_gist_consider_split(ConsiderSplitContext *context, RangeBound *right_lower, int min_left_count, RangeBound *left_upper, int max_left_count); +static int range_gist_cmp(Datum a, Datum b, SortSupport ssup); static int get_gist_range_class(RangeType *range); static int single_bound_cmp(const void *a, const void *b, void *arg); static int interval_cmp_lower(const void *a, const void *b, void *arg); @@ -773,6 +775,20 @@ range_gist_picksplit(PG_FUNCTION_ARGS) PG_RETURN_POINTER(v); } +/* + * Sort support routine for fast GiST index build by sorting. + */ +Datum +range_gist_sortsupport(PG_FUNCTION_ARGS) +{ + SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0); + + ssup->comparator = range_gist_cmp; + ssup->ssup_extra = NULL; + + PG_RETURN_VOID(); +} + /* equality comparator for GiST */ Datum range_gist_same(PG_FUNCTION_ARGS) @@ -1693,6 +1709,60 @@ range_gist_consider_split(ConsiderSplitContext *context, } } +/* + * GiST sortsupport comparator for ranges. + * similar to range_cmp, but range typcache is cached. + */ +static int +range_gist_cmp(Datum a, Datum b, SortSupport ssup) +{ + RangeType *range_a = DatumGetRangeTypeP(a); + RangeType *range_b = DatumGetRangeTypeP(b); + TypeCacheEntry *typcache = ssup->ssup_extra; + RangeBound lower1, + lower2; + RangeBound upper1, + upper2; + bool empty1, + empty2; + int result; + + if (typcache == NULL) + { + Assert(RangeTypeGetOid(range_a) == RangeTypeGetOid(range_b)); + typcache = lookup_type_cache(RangeTypeGetOid(range_a), TYPECACHE_RANGE_INFO); + + /* + * Cache the range info between calls to avoid having to call + * lookup_type_cache() for each comparison. + */ + ssup->ssup_extra = typcache; + } + + range_deserialize(typcache, range_a, &lower1, &upper1, &empty1); + range_deserialize(typcache, range_b, &lower2, &upper2, &empty2); + + if (empty1 && empty2) + result = 0; + else if (empty1) + result = -1; + else if (empty2) + result = 1; + else + { + result = range_cmp_bounds(typcache, &lower1, &lower2); + if (result == 0) + result = range_cmp_bounds(typcache, &upper1, &upper2); + } + + if ((Datum) range_a != a) + pfree(range_a); + if ((Datum) range_b != b) + pfree(range_b); + + return result; +} + /* * Find class number for range. * diff --git a/src/include/catalog/pg_amproc.dat b/src/include/catalog/pg_amproc.dat index 352558c1f06..fc229d99845 100644 --- a/src/include/catalog/pg_amproc.dat +++ b/src/include/catalog/pg_amproc.dat @@ -607,6 +607,9 @@ { amprocfamily => 'gist/range_ops', amproclefttype => 'anyrange', amprocrighttype => 'anyrange', amprocnum => '7', amproc => 'range_gist_same' }, +{ amprocfamily => 'gist/range_ops', amproclefttype => 'anyrange', + amprocrighttype => 'anyrange', amprocnum => '11', + amproc => 'range_gist_sortsupport' }, { amprocfamily => 'gist/range_ops', amproclefttype => 'anyrange', amprocrighttype => 'anyrange', amprocnum => '12', amproc => 'gist_stratnum_identity' }, diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 71c74350a0d..60eaa2f81f7 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -10595,6 +10595,9 @@ { oid => '3881', descr => 'GiST support', proname => 'range_gist_same', prorettype => 'internal', proargtypes => 'anyrange anyrange internal', prosrc => 'range_gist_same' }, +{ oid => '8849', descr => 'GiST support', + proname => 'range_gist_sortsupport', prorettype => 'void', + proargtypes => 'internal', prosrc => 'range_gist_sortsupport' }, { oid => '6154', descr => 'GiST support', proname => 'multirange_gist_consistent', prorettype => 'bool', proargtypes => 'internal anymultirange int2 oid internal', ^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2024-03-22 13:20 UTC | newest] Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-11-11 14:21 [PATCH v9 2/2] New command ALTER TABLE ALL IN TABLESPACE SET LOGGED/UNLOGGED Kyotaro Horiguchi <[email protected]> 2024-01-10 00:00 Re: [PATCH] Add sortsupport for range types and btree_gist jian he <[email protected]> 2024-01-10 14:18 ` Re: [PATCH] Add sortsupport for range types and btree_gist jian he <[email protected]> 2024-01-10 15:13 ` Re: [PATCH] Add sortsupport for range types and btree_gist Andrey M. Borodin <[email protected]> 2024-01-10 17:04 ` Re: [PATCH] Add sortsupport for range types and btree_gist Bernd Helmle <[email protected]> 2024-01-26 13:01 ` Re: [PATCH] Add sortsupport for range types and btree_gist vignesh C <[email protected]> 2024-01-26 18:22 ` Re: [PATCH] Add sortsupport for range types and btree_gist Bernd Helmle <[email protected]> 2024-02-08 18:14 ` Re: [PATCH] Add sortsupport for range types and btree_gist Bernd Helmle <[email protected]> 2024-02-12 13:00 ` Re: [PATCH] Add sortsupport for range types and btree_gist jian he <[email protected]> 2024-02-13 11:03 ` Re: [PATCH] Add sortsupport for range types and btree_gist Bernd Helmle <[email protected]> 2024-03-22 13:20 ` Re: [PATCH] Add sortsupport for range types and btree_gist Bernd Helmle <[email protected]> 2024-01-10 16:35 ` Re: [PATCH] Add sortsupport for range types and btree_gist Bernd Helmle <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox