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';
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
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',