Subject: [PATCH] Add sortsupport for range types and btree_gist

From: Christoph Heiss <[email-masked]>
Date: Wed, 15 Jun 2022 12:45:07 +0200

Hi all!

The motivation behind this is that incrementally building up a GiST 
index for certain input data can create a terrible tree structure.
Furthermore, exclusion constraints are commonly implemented using GiST 
indices and for that use case, data is mostly orderable.

By sorting the data before inserting it into the index results in a much 
better index structure, leading to significant performance improvements.

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.

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

After a REINDEX on the table, the results improve even more:

Shared Hit Blocks (average): 84.24 -> 8.54
Shared Read Blocks (average): 49.89 -> 0.74
Execution Time (average): 0.84 -> 0.065 ms
I/O Read Time (average): 0.16 -> 0.004 ms

Additionally, the time a REINDEX takes also improves significantly:

     672407.584 ms (11:12.408) -> 130670.232 ms (02:10.670)

Most of the sortsupport for btree_gist was implemented by re-using 
already existing infrastructure. For the few remaining types (bit, bool, 
cash, enum, interval, macaddress8 and time) I manually implemented them 
directly in btree_gist.
It might make sense to move them into the backend for uniformity, but I 
wanted to get other opinions on that first.

`make check-world` reports no regressions.

Attached below, besides the patch, are also two scripts for benchmarking.

`bench-gist.py` to benchmark the actual patch, example usage of this 
would be e.g. `./bench-gist.py -o results.csv public.table`. This 
expects a local instance with no authentication and default `postgres` 
user. The port can be set using the `--port` option.

`plot.py` prints average values (as used above) and creates boxplots for 
each statistic from the result files produced with `bench-gist.py`. 
Depends on matplotlib and pandas.

Additionally, if needed, the sample dataset used to benchmark this is 
available to independently verify the results [1].

Thanks,
Christoph Heiss

---

[1] https://drive.google.com/file/d/1SKRiUYd78_zl7CeD8pLDoggzCCh0wj39

From 22e1b60929c39309e06c2477d8d027e60ad49b9d Mon Sep 17 00:00:00 2001
From: Christoph Heiss <[email-masked]>
Date: Thu, 9 Jun 2022 17:07:46 +0200
Subject: [PATCH 1/1] Add sortsupport for range types and btree_gist

Incrementally building up a GiST index can result in a sub-optimal index
structure for range types.
By sorting the data before inserting it into the index will result in a
much better index.

This can provide sizeable improvements in query execution time, I/O read
time and shared block hits/reads.

Signed-off-by: Christoph Heiss <[email-masked]>
---
 contrib/btree_gist/Makefile                 |   3 +-
 contrib/btree_gist/btree_bit.c              |  19 ++++
 contrib/btree_gist/btree_bool.c             |  22 ++++
 contrib/btree_gist/btree_cash.c             |  22 ++++
 contrib/btree_gist/btree_enum.c             |  19 ++++
 contrib/btree_gist/btree_gist--1.7--1.8.sql | 105 ++++++++++++++++++++
 contrib/btree_gist/btree_gist.control       |   2 +-
 contrib/btree_gist/btree_interval.c         |  19 ++++
 contrib/btree_gist/btree_macaddr8.c         |  19 ++++
 contrib/btree_gist/btree_time.c             |  19 ++++
 src/backend/utils/adt/rangetypes_gist.c     |  70 +++++++++++++
 src/include/catalog/pg_amproc.dat           |   3 +
 src/include/catalog/pg_proc.dat             |   3 +
 13 files changed, 323 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 48997c75f6..4096de73ea 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 5b246bcde4..bf32bfd628 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,17 @@ 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);

 /* define for comparison */
 
+static int
+bit_fast_cmp(Datum x, Datum y, SortSupport ssup)
+{
+	return DatumGetInt32(DirectFunctionCall2(byteacmp, x, y));
+}
+
 static bool
 gbt_bitgt(const void *a, const void *b, Oid collation, FmgrInfo *flinfo)
 {
@@ -208,3 +216,14 @@ 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();
+}
diff --git a/contrib/btree_gist/btree_bool.c b/contrib/btree_gist/btree_bool.c
index 8b2af129b5..3a9f230e68 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 6ac97e2b12..389b725130 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 d4dc38a38e..2ab0ae9bc8 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,15 @@ 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(DirectFunctionCall2(enum_cmp, x, y));
+}
+
 static bool
 gbt_enumgt(const void *a, const void *b, FmgrInfo *flinfo)
 {
@@ -183,3 +191,14 @@ 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;
+	ssup->ssup_extra = NULL;
+
+	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 0000000000..01bd8f9f64
--- /dev/null
+++ b/contrib/btree_gist/btree_gist--1.7--1.8.sql
@@ -0,0 +1,105 @@
+/* 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_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_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;
+
+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) 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) network_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_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 fa9171a80a..abf66538f3 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_interval.c b/contrib/btree_gist/btree_interval.c
index c2bf82086d..80179adc99 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 796cc4efee..034e34bf90 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 fd8774a2f0..fb3454987b 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,7 @@ 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);

 #ifdef USE_FLOAT8_BYVAL
@@ -37,6 +39,12 @@ 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 bool
 gbt_timegt(const void *a, const void *b, FmgrInfo *flinfo)
 {
@@ -332,3 +340,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 = time_fast_cmp;
+	ssup->ssup_extra = NULL;
+
+	PG_RETURN_VOID();
+}
diff --git a/src/backend/utils/adt/rangetypes_gist.c b/src/backend/utils/adt/rangetypes_gist.c
index fbf39dbf30..2ded365dc2 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)
@@ -1692,6 +1708,60 @@ range_gist_consider_split(ConsiderSplitContext *context,
 	}
 }
 
+/*
+ * 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;
+}
+
 /*
  * Find class number for range.
  *
diff --git a/src/include/catalog/pg_amproc.dat b/src/include/catalog/pg_amproc.dat
index 4cc129bebd..9318ad5fd8 100644
--- a/src/include/catalog/pg_amproc.dat
+++ b/src/include/catalog/pg_amproc.dat
@@ -600,6 +600,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 87aa571a33..47676fb53e 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10376,6 +10376,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.36.1

---

From: Andrey Borodin <[email-masked]>
Date: Wed, 15 Jun 2022 19:39:34 +0500

Hi Christoph!

> On 15 Jun 2022, at 15:45, Christoph Heiss <[email-masked]> wrote:
> 
> By sorting the data before inserting it into the index results in a much better index structure, leading to significant performance improvements.

Here's my version of the very similar idea [0]. It lacks range types support.
On a quick glance your version lacks support of abbreviated sort, so I think benchmarks can be pushed event further :)
Let's merge our efforts and create combined patch?

Please, create a new entry for the patch on Commitfest.

Thank you!

Best regards, Andrey Borodin.

[0] https://commitfest.postgresql.org/37/2824/

---

From: Jacob Champion <[email-masked]>
Date: Tue, 5 Jul 2022 11:13:41 -0700

> `make check-world` reports no regressions.

cfbot is reporting a crash in contrib/btree_gist:

    https://cirrus-ci.com/github/postgresql-cfbot/postgresql/commitfest/38/3686

Thanks,
--Jacob

---

From: Jacob Champion <[email-masked]>
Date: Tue, 2 Aug 2022 11:01:00 -0700

This entry has been waiting on author input for a while (our current
threshold is roughly two weeks), so I've marked it Returned with
Feedback.

Once you think the patchset is ready for review again, you (or any
interested party) can resurrect the patch entry by visiting

    https://commitfest.postgresql.org/38/3686/

and changing the status to "Needs Review", and then changing the
status again to "Move to next CF". (Don't forget the second step;
hopefully we will have streamlined this in the near future!)

Thanks,
--Jacob

---

From: Christoph Heiss <[email-masked]>
Date: Wed, 31 Aug 2022 21:15:40 +0200

Hi!

Sorry for the long delay.

This fixes the crashes, now all tests run fine w/ and w/o debug 
configuration. That shouldn't even have slipped through as such.

Notable changes from v1:
- gbt_enum_sortsupport() now passes on fcinfo->flinfo
   enum_cmp_internal() needs a place to cache the typcache entry.
- inet sortsupport now uses network_cmp() directly

Thanks,
Christoph Heiss

From 667779bc0761c1356141722181c5a54ac46d96b9 Mon Sep 17 00:00:00 2001
From: Christoph Heiss <[email-masked]>
Date: Wed, 31 Aug 2022 19:20:43 +0200
Subject: [PATCH v2 1/1] Add sortsupport for range types and btree_gist

Signed-off-by: Christoph Heiss <[email-masked]>
---
 contrib/btree_gist/Makefile                 |   3 +-
 contrib/btree_gist/btree_bit.c              |  19 ++++
 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 | 110 ++++++++++++++++++++
 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             |  19 ++++
 src/backend/utils/adt/rangetypes_gist.c     |  70 +++++++++++++
 src/include/catalog/pg_amproc.dat           |   3 +
 src/include/catalog/pg_proc.dat             |   3 +
 14 files changed, 354 insertions(+), 2 deletions(-)
 create mode 100644 contrib/btree_gist/btree_gist--1.7--1.8.sql

 
+static int
 
 typedef struct boolkey
 {
 
 static bool
 
 typedef struct
 {
 
 static bool
index d4dc38a38e..33b9a34235 100644

@@ -26,8 +27,16 @@ PG_FUNCTION_INFO_V1(gbt_enum_picksplit);

+static int
+	return DatumGetInt32(CallerFInfoFunctionCall2(enum_cmp, ssup->ssup_extra,
+												  InvalidOid, x, y));
@@ -183,3 +192,20 @@ gbt_enum_same(PG_FUNCTION_ARGS)
+	 * 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;
index 0000000000..a5ac9657cc
@@ -0,0 +1,110 @@
+CREATE FUNCTION gbt_inet_sortsupport(internal)
+    FUNCTION    11  (inet, inet) gbt_inet_sortsupport (internal) ;
diff --git a/contrib/btree_gist/btree_inet.c b/contrib/btree_gist/btree_inet.c
index 2fb952dca8..1f519cfd61 100644
--- a/contrib/btree_gist/btree_inet.c
+++ b/contrib/btree_gist/btree_inet.c
@@ -8,6 +8,7 @@
 #include "catalog/pg_type.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 c2bf82086d..80179adc99 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

+static int
 
 typedef struct
 {

+static int
 
 typedef struct

 #ifdef USE_FLOAT8_BYVAL

+static int
index 777fdf0e2e..f77fc213f8 100644
 
 /*
 
+/*
@@ -1693,6 +1709,60 @@ range_gist_consider_split(ConsiderSplitContext *context,
 	}
 }
 
+/*
index be47583122..e0c1169871 100644
@@ -10384,6 +10384,9 @@
2.37.2

---

From: Andres Freund <[email-masked]>
Date: Sun, 2 Oct 2022 00:23:32 -0700

Hi,

> - inet sortsupport now uses network_cmp() directly

Updated the patch to add the minimal change for meson compat.

Greetings,

Andres Freund

---

From: Andres Freund <[email-masked]>
Date: Sun, 2 Oct 2022 00:29:01 -0700

> Updated the patch to add the minimal change for meson compat.

Now I made the same mistake of not adding the change... Clearly I need to stop
for tonight. Either way, here's the hopefully correct change.

---

From: Bernd Helmle <[email-masked]>
Date: Wed, 30 Nov 2022 18:25:25 +0100

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.

> 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.

> > I/O Read Time (average): 0.19 -> 0.15 ms

I've changed this a little and did the following:

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:

The numbers are:

HEAD -> HEAD patched

Shared Hit Blocks (avg) : 4.85 -> 8.75
Shared Read Blocks (avg): 0.14 -> 0.17
Execution Time (avg)    : 0.01 -> 0.05
IO Read Time (avg)      : 0.0003 -> 0.0009

So numbers got worse here. You can uncover this when using pgbench
against that modified table in a much more worse outcome.

Running

pgbench -s 1000 -c 16 -j 16 -S -Mprepared -T 300 

on my workstation at least 3 times gives me the following numbers:

HEAD:

tps = 215338.784398 (without initial connection time)
tps = 212826.513727 (without initial connection time)
tps = 212102.857891 (without initial connection time)

HEAD patched:

tps = 126487.796716 (without initial connection time)
tps = 125076.391528 (without initial connection time)
tps = 124538.946388 (without initial connection time)

So this doesn't look good. While this patch gets a real improvement for
the provided tokens, it makes performance for at least int4 on this
test worse. Though the picture changes again if you build the index
buffered:

tps = 198409.248911 (without initial connection time)
tps = 194431.827394 (without initial connection time)
tps = 195657.532281 (without initial connection time)

which is again close to current HEAD (i have no idea why it is even
*that* slower, since "buffered=on" shouldn't employ sortsupport, no?).
Of course, built time for the index in this case is much slower again:

-- pgbench_accounts index (buffered)
CREATE INDEX Time: 900912,924 ms (15:00,913)

So while providing a huge improvement on index creation speed it's
sometimes still required to carefully check the index quality.

[...]

> wanted to get other opinions on that first.

Hmm i'd say we leave them in the contrib module until they are required
somewhere else, too or make a separate patch for them? Do we have plans
to have such requirement in the backend already?

Attached is a rebased patch against current HEAD.

Thanks

	Bernd

---

From: vignesh C <[email-masked]>
Date: Fri, 26 Jan 2024 18:31:12 +0530

On Wed, 10 Jan 2024 at 19:49, jian he <[email-masked]> wrote:
>
> On Wed, Jan 10, 2024 at 8:00 AM jian he <[email-masked]> wrote:
> >
> > `
> > from the doc, add sortsupport function will only influence index build time?
> >
> > +/*
> > 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.
> > 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
> > 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-masked])
> 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

---

From: Bernd Helmle <[email-masked]>
Date: Fri, 26 Jan 2024 19:22:28 +0100

> === 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.