From c3f396e1490892ead6f32b4f0eac8a25cb877deb Mon Sep 17 00:00:00 2001 From: jian he 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