[PATCH] Add sortsupport for range types and btree

($INBOX_DIR/description missing)  
help / color / mirror / Atom feed

[PATCH] Add sortsupport for range types and btree_gist
25+ messages / 11 participants
[nested] [flat]

* [PATCH] Add sortsupport for range types and btree_gist
@ 2022-06-15 10:45  Christoph Heiss <[email protected]>
  0 siblings, 3 replies; 25+ messages in thread

From: Christoph Heiss @ 2022-06-15 10:45 UTC (permalink / raw)
  To: pgsql-hackers; +Cc: Hans-Jürgen Schönig <[email protected]>

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

Attachments:

  [text/x-patch] 0001-Add-sortsupport-for-range-types-and-btree_gist.patch (18.8K, ../../[email protected]/2-0001-Add-sortsupport-for-range-types-and-btree_gist.patch)
  download | inline diff:
From 22e1b60929c39309e06c2477d8d027e60ad49b9d Mon Sep 17 00:00:00 2001
From: Christoph Heiss <[email protected]>
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 protected]>
---
 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



  [text/x-python] plot.py (771B, ../../[email protected]/3-plot.py)
  download | inline:
#!/usr/bin/env python3

import sys
import matplotlib.pyplot as plt
import pandas as pd

df = pd.concat(pd.read_csv(f) for f in sys.argv[1:])
print(df)

grp = df.groupby('tbl')
print(grp['hit'].mean())
print(grp['time'].mean())
print(grp['read'].mean())
print(grp['iotime'].mean())

fig, axs = plt.subplots(1, 4, figsize=(16, 6))

stats = {
    'hit': 'Shared block hits',
    'time':'Execution time in ms',
    'read': 'Shared block reads',
    'iotime': 'I/O read time in ms'
}

for index, (name, desc) in enumerate(stats.items()):
    df.boxplot(by='tbl', ax=axs[index], column=name, rot=45, showfliers=False)
    axs[index].set_xlabel('')
    axs[index].set_ylabel(desc)

plt.tight_layout(w_pad=4)
plt.suptitle('Sortsupport for range types and btree_gist')
plt.show()

  [text/x-python] bench-gist.py (1.6K, ../../[email protected]/4-bench-gist.py)
  download | inline:
#!/usr/bin/env python3

import csv
import psycopg2
import logging
import argparse

logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")

parser = argparse.ArgumentParser()
parser.add_argument("--output", "-o")
parser.add_argument("--iters", "-n", default=10, type=int)
parser.add_argument("--points", "-p", default=10000, type=int)
parser.add_argument("--port", default=5432, type=int)
parser.add_argument("inputs", nargs="*")
args = parser.parse_args()

conn = psycopg2.connect(f'host=/tmp user=postgres dbname=postgres port={args.port}')
cur = conn.cursor()

output = open(args.output, 'w')
out = csv.writer(output)
out.writerow(['schema', 'tbl', 'i', 'point', 'time', 'hit', 'read', 'iotime'])

for schema, tbl in [i.split(".") for i in args.inputs]:
    logging.info(f"Fetching points for {schema}.{tbl}")

    cur.execute(f"SELECT id, block_range FROM {schema}.{tbl} ORDER BY RANDOM() LIMIT {args.points}")
    datapoints = cur.fetchall()

    logging.info(f"Benching {schema}.{tbl}")
    for iteration in range(args.iters):
        logging.info(f" iter {iteration}")
        for pointid, point in enumerate(datapoints):
            cur.execute(f"EXPLAIN (BUFFERS, ANALYZE, FORMAT 'json') SELECT * FROM {schema}.{tbl} WHERE id = %s AND block_range && %s", point)
            explain = cur.fetchone()[0][0]
            plan = explain['Plan']
            exec_time = explain['Execution Time']
            hit = plan['Shared Hit Blocks']
            read = plan['Shared Read Blocks']
            readtime = plan['I/O Read Time']
            out.writerow(list(map(str, [schema, tbl, iteration+1, pointid+1, exec_time, hit, read, readtime])))

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [PATCH] Add sortsupport for range types and btree_gist
@ 2022-06-15 14:39  Andrey Borodin <[email protected]>
  parent: Christoph Heiss <[email protected]>
  2 siblings, 1 reply; 25+ messages in thread

From: Andrey Borodin @ 2022-06-15 14:39 UTC (permalink / raw)
  To: Christoph Heiss <[email protected]>; +Cc: pgsql-hackers; Hans-Jürgen Schönig <[email protected]>

Hi Christoph!

> On 15 Jun 2022, at 15:45, Christoph Heiss <[email protected]> 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/

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [PATCH] Add sortsupport for range types and btree_gist
@ 2022-07-05 18:13  Jacob Champion <[email protected]>
  parent: Christoph Heiss <[email protected]>
  2 siblings, 0 replies; 25+ messages in thread

From: Jacob Champion @ 2022-07-05 18:13 UTC (permalink / raw)
  To: Christoph Heiss <[email protected]>; +Cc: pgsql-hackers; Hans-Jürgen Schönig <[email protected]>

On Wed, Jun 15, 2022 at 3:45 AM Christoph Heiss
<[email protected]> wrote:
> `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





^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [PATCH] Add sortsupport for range types and btree_gist
@ 2022-08-02 18:01  Jacob Champion <[email protected]>
  parent: Andrey Borodin <[email protected]>
  0 siblings, 1 reply; 25+ messages in thread

From: Jacob Champion @ 2022-08-02 18:01 UTC (permalink / raw)
  To: Andrey Borodin <[email protected]>; Christoph Heiss <[email protected]>; +Cc: pgsql-hackers; Hans-Jürgen Schönig <[email protected]>

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

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* [PATCH v3] Add sortsupport for range types and btree_gist
@ 2022-08-31 17:20  Christoph Heiss <[email protected]>
  0 siblings, 0 replies; 25+ messages in thread

From: Christoph Heiss @ 2022-08-31 17:20 UTC (permalink / raw)

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 protected]>
---
 src/include/catalog/pg_amproc.dat           |   3 +
 src/include/catalog/pg_proc.dat             |   3 +
 src/backend/utils/adt/rangetypes_gist.c     |  70 +++++++++++++
 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 ++++
 14 files changed, 354 insertions(+), 2 deletions(-)
 create mode 100644 contrib/btree_gist/btree_gist--1.7--1.8.sql

diff --git a/src/include/catalog/pg_amproc.dat b/src/include/catalog/pg_amproc.dat
index 4cc129bebd8..9318ad5fd84 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 68bb032d3ea..951d864f3bb 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10313,6 +10313,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',
diff --git a/src/backend/utils/adt/rangetypes_gist.c b/src/backend/utils/adt/rangetypes_gist.c
index 777fdf0e2e9..f77fc213f83 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.
+ *
+ * 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/contrib/btree_gist/Makefile b/contrib/btree_gist/Makefile
index 48997c75f63..4096de73ea3 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 5b246bcde4b..bf32bfd6280 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 8b2af129b52..3a9f230e680 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 6ac97e2b12a..389b7251309 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 d4dc38a38e5..33b9a34235e 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 00000000000..a5ac9657ccc
--- /dev/null
+++ b/contrib/btree_gist/btree_gist--1.7--1.8.sql
@@ -0,0 +1,110 @@
+/* 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_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;
+
+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) 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_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 fa9171a80a2..abf66538f32 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 2fb952dca83..1f519cfd611 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 c2bf82086df..80179adc993 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 796cc4efee3..034e34bf908 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 fd8774a2f08..fb3454987be 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();
+}
-- 
2.37.3.542.gdd3f6c4cae


--uf47yrsdd3qk5zgt--





^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* [PATCH v4] Add sortsupport for range types and btree_gist
@ 2022-08-31 17:20  Christoph Heiss <[email protected]>
  0 siblings, 0 replies; 25+ messages in thread

From: Christoph Heiss @ 2022-08-31 17:20 UTC (permalink / raw)

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 protected]>
---
 src/include/catalog/pg_amproc.dat           |   3 +
 src/include/catalog/pg_proc.dat             |   3 +
 src/backend/utils/adt/rangetypes_gist.c     |  70 +++++++++++++
 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 ++++
 contrib/btree_gist/meson.build              |   1 +
 15 files changed, 355 insertions(+), 2 deletions(-)
 create mode 100644 contrib/btree_gist/btree_gist--1.7--1.8.sql

diff --git a/src/include/catalog/pg_amproc.dat b/src/include/catalog/pg_amproc.dat
index 4cc129bebd8..9318ad5fd84 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 68bb032d3ea..951d864f3bb 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10313,6 +10313,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',
diff --git a/src/backend/utils/adt/rangetypes_gist.c b/src/backend/utils/adt/rangetypes_gist.c
index 777fdf0e2e9..f77fc213f83 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.
+ *
+ * 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/contrib/btree_gist/Makefile b/contrib/btree_gist/Makefile
index 48997c75f63..4096de73ea3 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 5b246bcde4b..bf32bfd6280 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 8b2af129b52..3a9f230e680 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 6ac97e2b12a..389b7251309 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 d4dc38a38e5..33b9a34235e 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 00000000000..a5ac9657ccc
--- /dev/null
+++ b/contrib/btree_gist/btree_gist--1.7--1.8.sql
@@ -0,0 +1,110 @@
+/* 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_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;
+
+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) 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_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 fa9171a80a2..abf66538f32 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 2fb952dca83..1f519cfd611 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 c2bf82086df..80179adc993 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 796cc4efee3..034e34bf908 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 fd8774a2f08..fb3454987be 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/contrib/btree_gist/meson.build b/contrib/btree_gist/meson.build
index c0a8d238540..6cb2cddbb45 100644
--- a/contrib/btree_gist/meson.build
+++ b/contrib/btree_gist/meson.build
@@ -41,6 +41,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.37.3.542.gdd3f6c4cae


--fn5cgvpuomc627kp--





^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [PATCH] Add sortsupport for range types and btree_gist
@ 2022-08-31 19:15  Christoph Heiss <[email protected]>
  parent: Jacob Champion <[email protected]>
  0 siblings, 1 reply; 25+ messages in thread

From: Christoph Heiss @ 2022-08-31 19:15 UTC (permalink / raw)
  To: Jacob Champion <[email protected]>; +Cc: pgsql-hackers; Hans-Jürgen Schönig <[email protected]>; [email protected]

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

Attachments:

  [text/x-patch] v2-0001-Add-sortsupport-for-range-types-and-btree_gist.patch (20.4K, ../../[email protected]/2-v2-0001-Add-sortsupport-for-range-types-and-btree_gist.patch)
  download | inline diff:
From 667779bc0761c1356141722181c5a54ac46d96b9 Mon Sep 17 00:00:00 2001
From: Christoph Heiss <[email protected]>
Date: Wed, 31 Aug 2022 19:20:43 +0200
Subject: [PATCH v2 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 protected]>
---
 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

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..33b9a34235 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 0000000000..a5ac9657cc
--- /dev/null
+++ b/contrib/btree_gist/btree_gist--1.7--1.8.sql
@@ -0,0 +1,110 @@
+/* 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_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;
+
+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) 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_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_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"
 #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 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 777fdf0e2e..f77fc213f8 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.
+ *
+ * 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 be47583122..e0c1169871 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10384,6 +10384,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.37.2



^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [PATCH] Add sortsupport for range types and btree_gist
@ 2022-10-02 07:23  Andres Freund <[email protected]>
  parent: Christoph Heiss <[email protected]>
  0 siblings, 1 reply; 25+ messages in thread

From: Andres Freund @ 2022-10-02 07:23 UTC (permalink / raw)
  To: Christoph Heiss <[email protected]>; +Cc: Jacob Champion <[email protected]>; pgsql-hackers; Hans-Jürgen Schönig <[email protected]>; [email protected]

Hi,

On 2022-08-31 21:15:40 +0200, Christoph Heiss wrote:
> 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

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

Greetings,

Andres Freund


^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [PATCH] Add sortsupport for range types and btree_gist
@ 2022-10-02 07:29  Andres Freund <[email protected]>
  parent: Andres Freund <[email protected]>
  0 siblings, 0 replies; 25+ messages in thread

From: Andres Freund @ 2022-10-02 07:29 UTC (permalink / raw)
  To: Christoph Heiss <[email protected]>; +Cc: Jacob Champion <[email protected]>; pgsql-hackers; Hans-Jürgen Schönig <[email protected]>; [email protected]

On 2022-10-02 00:23:32 -0700, Andres Freund wrote:
> 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.


^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [PATCH] Add sortsupport for range types and btree_gist
@ 2022-11-30 17:25  Bernd Helmle <[email protected]>
  parent: Christoph Heiss <[email protected]>
  2 siblings, 1 reply; 25+ messages in thread

From: Bernd Helmle @ 2022-11-30 17:25 UTC (permalink / raw)
  To: [email protected]

Hi,

No deep code review yet, but CF is approaching its end and i didn't
have time to look at this earlier :/ 

Below are some things i've tested so far.

Am Mittwoch, dem 15.06.2022 um 12:45 +0200 schrieb Christoph Heiss:


> Testing was done using following setup, with about 50 million rows:
> 
>     CREATE EXTENSION btree_gist;
>     CREATE TABLE t (id uuid, block_range int4range);
>     CREATE INDEX ON before USING GIST (id, block_range);
>     COPY t FROM '..' DELIMITER ',' CSV HEADER;
> 
> using
> 
>     SELECT * FROM t WHERE id = '..' AND block_range && '..'
> 
> as test query, using a unpatched instance and one with the patch
> applied.
> 
> Some stats for fetching 10,000 random rows using the query above,
> 100 iterations to get good averages.
> 

Here are my results with repeating this:

HEAD:
-- token index (buffering=auto)
CREATE INDEX Time: 700213,110 ms (11:40,213)

HEAD patched:

-- token index (buffering=auto)
CREATE INDEX Time: 136229,400 ms (02:16,229)

So index creation speed on the test set (table filled with the tokens
and then creating the index afterwards) gets a lot of speedup with this
patch and default buffering strategy.

> The benchmarking was done on a unpatched instance compiled using the 
> > exact same options as with the patch applied.
> > [ Results are noted in a unpatched -> patched fashion. ]
> > 
> > First set of results are after the initial CREATE TABLE, CREATE
> INDEX
> > and a COPY to the table, thereby incrementally building the index.
> > 
> > Shared Hit Blocks (average): 110.97 -> 78.58
> > Shared Read Blocks (average): 58.90 -> 47.42
> > Execution Time (average): 1.10 -> 0.83 ms
> > I/O Read Time (average): 0.19 -> 0.15 ms

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

    CREATE EXTENSION btree_gist;
    CREATE TABLE t (id uuid, block_range int4range);
    COPY t FROM '..' DELIMITER ',' CSV HEADER;
    CREATE INDEX ON before USING GIST (id, block_range);

So creating the index _after_ having loaded the tokens.
My configuration was:

shared_buffers = 4G
max_wal_size = 6G
effective_cache_size = 4g # (default, index fits)
maintenance_work_mem = 1G


Here are my numbers from the attached benchmark script 

HEAD -> HEAD patched:

Shared Hit Blocks (avg) : 76.81 -> 9.17
Shared Read Blocks (avg): 0.43 -> 0.11
Execution Time (avg)    : 0.40 -> 0.05
IO Read Time (avg)      : 0.001 -> 0.0007

So with these settings i see an improvement with the provided test set.
Since this patches adds sortsupport for all other existing opclasses, i
thought to give it a try with another test set. What i did was to adapt
the benchmark script (see attached) to use the "pgbench_accounts" table
which i changed to instead using the primary key to have a btree_gist
index on column "aid".

I let pgbench fill its tables with scale = 1000, dropped the primary
key, create the btree_gist on "aid" with default buffering strategy:

pgbench -s 1000 -i bernd

ALTER TABLE pgbench_accounts DROP CONSTRAINT pgbench_accounts_pkey ;
CREATE INDEX ON pgbench_accounts USING gist(aid);

Ran the benchmark script bench-gist-pgbench_accounts.py:

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.

[...]


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

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


ALTER SYSTEM SET shared_buffers TO '4GB';
ALTER SYSTEM SET max_wal_size TO '6GB';
ALTER SYSTEM SET maintenance_work_mem TO '1GB';
ALTER SYSTEM SET track_io_timing TO on;


Attachments:

  [text/x-python3] bench-gist-pgbench_accounts.py (1.6K, ../../[email protected]/2-bench-gist-pgbench_accounts.py)
  download | inline:
#!/usr/bin/env python3

import csv
import psycopg2
import logging
import argparse

logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")

parser = argparse.ArgumentParser()
parser.add_argument("--output", "-o")
parser.add_argument("--iters", "-n", default=10, type=int)
parser.add_argument("--points", "-p", default=10000, type=int)
parser.add_argument("--port", default=5432, type=int)
parser.add_argument("inputs", nargs="*")
args = parser.parse_args()

conn = psycopg2.connect(f'host=/tmp user=bernd dbname=bernd port={args.port}')
cur = conn.cursor()

output = open(args.output, 'w')
out = csv.writer(output)
out.writerow(['schema', 'tbl', 'i', 'point', 'time', 'hit', 'read', 'iotime'])

for schema, tbl in [i.split(".") for i in args.inputs]:
    logging.info(f"Fetching points for {schema}.{tbl}")

    cur.execute(f"SELECT aid, abalance FROM {schema}.{tbl} ORDER BY RANDOM() LIMIT {args.points}")
    datapoints = cur.fetchall()

    logging.info(f"Benching {schema}.{tbl}")
    for iteration in range(args.iters):
        logging.info(f" iter {iteration}")
        for pointid, point in enumerate(datapoints):
            cur.execute(f"EXPLAIN (BUFFERS, ANALYZE, FORMAT 'json') SELECT * FROM {schema}.{tbl} WHERE aid = %s AND abalance = %s",  point)
            explain = cur.fetchone()[0][0]
            plan = explain['Plan']
            exec_time = explain['Execution Time']
            hit = plan['Shared Hit Blocks']
            read = plan['Shared Read Blocks']
            readtime = plan['I/O Read Time']
            out.writerow(list(map(str, [schema, tbl, iteration+1, pointid+1, exec_time, hit, read, readtime])))

  [image/png] HEAD-patched.png (42.6K, ../../[email protected]/3-HEAD-patched.png)
  download | view image

  [image/png] HEAD-patched_btree_gist_pgbench_accounts.png (46.1K, ../../[email protected]/4-HEAD-patched_btree_gist_pgbench_accounts.png)
  download | view image

  [image/png] HEAD.png (40.2K, ../../[email protected]/5-HEAD.png)
  download | view image

  [image/png] HEAD-master_btree_gist_pgbench_accounts.png (46.2K, ../../[email protected]/6-HEAD-master_btree_gist_pgbench_accounts.png)
  download | view image

  [text/plain] benchmark.pgsql.config (170B, ../../[email protected]/7-benchmark.pgsql.config)
  download | inline:
ALTER SYSTEM SET shared_buffers TO '4GB';
ALTER SYSTEM SET max_wal_size TO '6GB';
ALTER SYSTEM SET maintenance_work_mem TO '1GB';
ALTER SYSTEM SET track_io_timing TO on;

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Views no longer in rangeTabls?
@ 2023-06-10 06:51  David Steele <[email protected]>
  0 siblings, 1 reply; 25+ messages in thread

From: David Steele @ 2023-06-10 06:51 UTC (permalink / raw)
  To: Tom Lane <[email protected]>; +Cc: Amit Langote <[email protected]>; pgsql-hackers

On 6/9/23 19:14, Tom Lane wrote:
> David Steele <[email protected]> writes:
>> Thank you, this was very helpful. I am able to get the expected result
>> now with:
> 
>> /* We only care about tables/views and can ignore subqueries, etc. */
>> if (!(rte->rtekind == RTE_RELATION ||
>>        (rte->rtekind == RTE_SUBQUERY && OidIsValid(rte->relid))))
>>       continue;
> 
> Right, that matches places like add_rtes_to_flat_rtable().

Good to have confirmation of that, thanks!

>> One thing, though, rte->relkind is not set for views, so I still need to
>> call get_rel_relkind(rte->relid). Not a big deal, but do you think it
>> would make sense to set rte->relkind for views?
> 
> If you see "rte->rtekind == RTE_SUBQUERY && OidIsValid(rte->relid)",
> it's dead certain that relid refers to a view, so you could just wire
> in that knowledge.

Yeah, that's a good trick. Even so, I wonder why relkind is not set when 
relid is set?

Regards,
-David






^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Views no longer in rangeTabls?
@ 2023-06-10 06:57  Amit Langote <[email protected]>
  parent: David Steele <[email protected]>
  0 siblings, 1 reply; 25+ messages in thread

From: Amit Langote @ 2023-06-10 06:57 UTC (permalink / raw)
  To: David Steele <[email protected]>; +Cc: pgsql-hackers; Tom Lane <[email protected]>

On Sat, Jun 10, 2023 at 15:51 David Steele <[email protected]> wrote:

> On 6/9/23 19:14, Tom Lane wrote:
> > David Steele <[email protected]> writes:
> >> Thank you, this was very helpful. I am able to get the expected result
> >> now with:
> >
> >> /* We only care about tables/views and can ignore subqueries, etc. */
> >> if (!(rte->rtekind == RTE_RELATION ||
> >>        (rte->rtekind == RTE_SUBQUERY && OidIsValid(rte->relid))))
> >>       continue;
> >
> > Right, that matches places like add_rtes_to_flat_rtable().
>
> Good to have confirmation of that, thanks!
>
> >> One thing, though, rte->relkind is not set for views, so I still need to
> >> call get_rel_relkind(rte->relid). Not a big deal, but do you think it
> >> would make sense to set rte->relkind for views?
> >
> > If you see "rte->rtekind == RTE_SUBQUERY && OidIsValid(rte->relid)",
> > it's dead certain that relid refers to a view, so you could just wire
> > in that knowledge.
>
> Yeah, that's a good trick. Even so, I wonder why relkind is not set when
> relid is set?


I too have been thinking that setting relkind might be a good idea, even if
only as a crosscheck that only view relations can look like that in the
range table.

> --
Thanks, Amit Langote
EDB: http://www.enterprisedb.com


^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Views no longer in rangeTabls?
@ 2023-06-10 07:30  David Steele <[email protected]>
  parent: Amit Langote <[email protected]>
  0 siblings, 1 reply; 25+ messages in thread

From: David Steele @ 2023-06-10 07:30 UTC (permalink / raw)
  To: Amit Langote <[email protected]>; +Cc: pgsql-hackers; Tom Lane <[email protected]>

On 6/10/23 09:57, Amit Langote wrote:
> On Sat, Jun 10, 2023 at 15:51 David Steele <[email protected] 
> <mailto:[email protected]>> wrote:
>     On 6/9/23 19:14, Tom Lane wrote:
>      >
>      > If you see "rte->rtekind == RTE_SUBQUERY && OidIsValid(rte->relid)",
>      > it's dead certain that relid refers to a view, so you could just wire
>      > in that knowledge.
> 
>     Yeah, that's a good trick. Even so, I wonder why relkind is not set
>     when
>     relid is set?
> 
> I too have been thinking that setting relkind might be a good idea, even 
> if only as a crosscheck that only view relations can look like that in 
> the range table.

+1. Even better if we can do it for PG16.

Regards,
-David






^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Views no longer in rangeTabls?
@ 2023-06-10 12:56  Tom Lane <[email protected]>
  parent: David Steele <[email protected]>
  0 siblings, 1 reply; 25+ messages in thread

From: Tom Lane @ 2023-06-10 12:56 UTC (permalink / raw)
  To: David Steele <[email protected]>; +Cc: Amit Langote <[email protected]>; pgsql-hackers

David Steele <[email protected]> writes:
> On 6/10/23 09:57, Amit Langote wrote:
>> I too have been thinking that setting relkind might be a good idea, even 
>> if only as a crosscheck that only view relations can look like that in 
>> the range table.

> +1. Even better if we can do it for PG16.

Well, if we're gonna do it we should do it for v16, rather than
change the data structure twice.  It wouldn't be hard exactly:

    /*
     * Clear fields that should not be set in a subquery RTE.  Note that we
     * leave the relid, rellockmode, and perminfoindex fields set, so that the
     * view relation can be appropriately locked before execution and its
     * permissions checked.
     */
-   rte->relkind = 0;
    rte->tablesample = NULL;
    rte->inh = false;            /* must not be set for a subquery */

plus adjustment of that comment and probably also the comment
for struct RangeTblEntry.

			regards, tom lane






^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Views no longer in rangeTabls?
@ 2023-06-10 13:18  Julien Rouhaud <[email protected]>
  parent: Tom Lane <[email protected]>
  0 siblings, 1 reply; 25+ messages in thread

From: Julien Rouhaud @ 2023-06-10 13:18 UTC (permalink / raw)
  To: Tom Lane <[email protected]>; +Cc: David Steele <[email protected]>; Amit Langote <[email protected]>; pgsql-hackers

On Sat, Jun 10, 2023 at 08:56:47AM -0400, Tom Lane wrote:
>
> Well, if we're gonna do it we should do it for v16, rather than
> change the data structure twice.  It wouldn't be hard exactly:
>
>     /*
>      * Clear fields that should not be set in a subquery RTE.  Note that we
>      * leave the relid, rellockmode, and perminfoindex fields set, so that the
>      * view relation can be appropriately locked before execution and its
>      * permissions checked.
>      */
> -   rte->relkind = 0;
>     rte->tablesample = NULL;
>     rte->inh = false;            /* must not be set for a subquery */
>
> plus adjustment of that comment and probably also the comment
> for struct RangeTblEntry.

and also handle that field in (read|out)funcs.c






^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Views no longer in rangeTabls?
@ 2023-06-10 13:27  Tom Lane <[email protected]>
  parent: Julien Rouhaud <[email protected]>
  0 siblings, 1 reply; 25+ messages in thread

From: Tom Lane @ 2023-06-10 13:27 UTC (permalink / raw)
  To: Julien Rouhaud <[email protected]>; +Cc: David Steele <[email protected]>; Amit Langote <[email protected]>; pgsql-hackers

Julien Rouhaud <[email protected]> writes:
> On Sat, Jun 10, 2023 at 08:56:47AM -0400, Tom Lane wrote:
>> -   rte->relkind = 0;

> and also handle that field in (read|out)funcs.c

Oh, right.  Ugh, that means a catversion bump.  It's not like
we've never done that during beta, but it's kind of an annoying
cost for a detail as tiny as this.

			regards, tom lane

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Views no longer in rangeTabls?
@ 2023-06-13 04:09  Amit Langote <[email protected]>
  parent: Tom Lane <[email protected]>
  0 siblings, 1 reply; 25+ messages in thread

From: Amit Langote @ 2023-06-13 04:09 UTC (permalink / raw)
  To: Tom Lane <[email protected]>; +Cc: Julien Rouhaud <[email protected]>; David Steele <[email protected]>; pgsql-hackers

On Sat, Jun 10, 2023 at 10:27 PM Tom Lane <[email protected]> wrote:
> Julien Rouhaud <[email protected]> writes:
> > On Sat, Jun 10, 2023 at 08:56:47AM -0400, Tom Lane wrote:
> >> -   rte->relkind = 0;
>
> > and also handle that field in (read|out)funcs.c
>
> Oh, right.  Ugh, that means a catversion bump.  It's not like
> we've never done that during beta, but it's kind of an annoying
> cost for a detail as tiny as this.

OK, so how about the attached?

I considered adding Assert(relkind == RELKIND_VIEW) in all places that
have the "rte->rtekind == RTE_SUBQUERY && OidIsValid(rte->relid)"
condition, but that seemed like an overkill, so only added one in the
#ifdef USE_ASSERT_CHECKING block in ExecCheckPermissions() that
f75cec4fff877 added.

-- 
Thanks, Amit Langote
EDB: http://www.enterprisedb.com


Attachments:

  [application/x-patch] v1-0001-Retain-relkind-too-in-RTE_SUBQUERY-entries-for-vi.patch (5.3K, ../../CA+HiwqF1CgsZKvBW_Gz9tErKfOp4t=PQgUKvrvWBG0HP8LdAXQ@mail.gmail.com/2-v1-0001-Retain-relkind-too-in-RTE_SUBQUERY-entries-for-vi.patch)
  download | inline diff:
From f7390a898b7e156d75372d28ea5698d2ced9795b Mon Sep 17 00:00:00 2001
From: Amit Langote <[email protected]>
Date: Tue, 13 Jun 2023 12:52:47 +0900
Subject: [PATCH v1] Retain relkind too in RTE_SUBQUERY entries for views.

47bb9db75 modified the ApplyRetrieveRule()'s conversion of a view's
original RTE_RELATION entry into an RTE_SUBQUERY one to retain relid,
rellockmode, and perminfoindex so that the executor can lock the view
and check its permissions.  It seems better to also retain
relkind for cross-checking that the exception of an
RTE_SUBQUERY entry being allowed to carry relation details only
applies to views, so do so.

Bump catversion because this changes the output format of
RTE_SUBQUERY RTEs.

Suggested-by: David Steele <[email protected]>
Discussion: https://postgr.es/m/3953179e-9540-e5d1-a743-4bef368785b0%40pgmasters.net
---
 src/backend/executor/execMain.c      |  3 +++
 src/backend/nodes/outfuncs.c         |  1 +
 src/backend/nodes/readfuncs.c        |  1 +
 src/backend/rewrite/rewriteHandler.c |  7 +++----
 src/include/catalog/catversion.h     |  2 +-
 src/include/nodes/parsenodes.h       | 14 +++++++-------
 6 files changed, 16 insertions(+), 12 deletions(-)

diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index c76fdf59ec..7aed5e7b36 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -595,6 +595,9 @@ ExecCheckPermissions(List *rangeTable, List *rteperminfos,
 		if (rte->perminfoindex != 0)
 		{
 			/* Sanity checks */
+			Assert(rte->rtekind == RTE_RELATION ||
+				   (rte->rtekind == RTE_SUBQUERY &&
+					rte->relkind == RELKIND_VIEW));
 			(void) getRTEPermissionInfo(rteperminfos, rte);
 			/* Many-to-one mapping not allowed */
 			Assert(!bms_is_member(rte->perminfoindex, indexset));
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index ba00b99249..955286513d 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -513,6 +513,7 @@ _outRangeTblEntry(StringInfo str, const RangeTblEntry *node)
 			WRITE_BOOL_FIELD(security_barrier);
 			/* we re-use these RELATION fields, too: */
 			WRITE_OID_FIELD(relid);
+			WRITE_CHAR_FIELD(relkind);
 			WRITE_INT_FIELD(rellockmode);
 			WRITE_UINT_FIELD(perminfoindex);
 			break;
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 597e5b3ea8..a136ae1d60 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -503,6 +503,7 @@ _readRangeTblEntry(void)
 			READ_BOOL_FIELD(security_barrier);
 			/* we re-use these RELATION fields, too: */
 			READ_OID_FIELD(relid);
+			READ_CHAR_FIELD(relkind);
 			READ_INT_FIELD(rellockmode);
 			READ_UINT_FIELD(perminfoindex);
 			break;
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 0e4f76efa8..0967be762c 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -1849,11 +1849,10 @@ ApplyRetrieveRule(Query *parsetree,
 
 	/*
 	 * Clear fields that should not be set in a subquery RTE.  Note that we
-	 * leave the relid, rellockmode, and perminfoindex fields set, so that the
-	 * view relation can be appropriately locked before execution and its
-	 * permissions checked.
+	 * leave the relid, rellockmode, relkind, and perminfoindex fields set, so
+	 * that the view relation can be appropriately locked before execution and
+	 * its permissions checked.
 	 */
-	rte->relkind = 0;
 	rte->tablesample = NULL;
 	rte->inh = false;			/* must not be set for a subquery */
 
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index c784937a0e..fe70d8396d 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	202305211
+#define CATALOG_VERSION_NO	202306141
 
 #endif
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 0ca298f5a1..786692781d 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1056,13 +1056,13 @@ typedef struct RangeTblEntry
 	 * this RTE in the containing struct's list of same; 0 if permissions need
 	 * not be checked for this RTE.
 	 *
-	 * As a special case, relid, rellockmode, and perminfoindex can also be
-	 * set (nonzero) in an RTE_SUBQUERY RTE.  This occurs when we convert an
-	 * RTE_RELATION RTE naming a view into an RTE_SUBQUERY containing the
-	 * view's query.  We still need to perform run-time locking and permission
-	 * checks on the view, even though it's not directly used in the query
-	 * anymore, and the most expedient way to do that is to retain these
-	 * fields from the old state of the RTE.
+	 * As a special case, relid, rellockmode, relkind, and perminfoindex can
+	 * also be set (nonzero) in an RTE_SUBQUERY RTE.  This occurs when we
+	 * convert an RTE_RELATION RTE naming a view into an RTE_SUBQUERY
+	 * containing the view's query.  We still need to perform run-time locking
+	 * and permission hecks on the view, even though it's not directly used in
+	 * the query anymore, and the most expedient way to do that is to retain
+	 * these fields from the old state of the RTE.
 	 *
 	 * As a special case, RTE_NAMEDTUPLESTORE can also set relid to indicate
 	 * that the tuple format of the tuplestore is the same as the referenced
-- 
2.35.3



^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Views no longer in rangeTabls?
@ 2023-06-13 07:44  David Steele <[email protected]>
  parent: Amit Langote <[email protected]>
  0 siblings, 1 reply; 25+ messages in thread

From: David Steele @ 2023-06-13 07:44 UTC (permalink / raw)
  To: Amit Langote <[email protected]>; Tom Lane <[email protected]>; +Cc: Julien Rouhaud <[email protected]>; pgsql-hackers

On 6/13/23 06:09, Amit Langote wrote:
> On Sat, Jun 10, 2023 at 10:27 PM Tom Lane <[email protected]> wrote:
>> Julien Rouhaud <[email protected]> writes:
>>> On Sat, Jun 10, 2023 at 08:56:47AM -0400, Tom Lane wrote:
>>>> -   rte->relkind = 0;
>>
>>> and also handle that field in (read|out)funcs.c
>>
>> Oh, right.  Ugh, that means a catversion bump.  It's not like
>> we've never done that during beta, but it's kind of an annoying
>> cost for a detail as tiny as this.
> 
> OK, so how about the attached?

The patch looks good to me. I also tested it against pgAudit and 
everything worked. I decided to go with the following because I think it 
is easier to read:

/* We only care about tables/views and can ignore subqueries, etc. */
if (!(rte->rtekind == RTE_RELATION ||
       (rte->rtekind == RTE_SUBQUERY && rte->relkind == RELKIND_VIEW)))
     continue;

> I considered adding Assert(relkind == RELKIND_VIEW) in all places that
> have the "rte->rtekind == RTE_SUBQUERY && OidIsValid(rte->relid)"
> condition, but that seemed like an overkill, so only added one in the
> #ifdef USE_ASSERT_CHECKING block in ExecCheckPermissions() that
> f75cec4fff877 added.

This seems like a good place for the assert.

Thanks!
-David






^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Views no longer in rangeTabls?
@ 2023-06-13 08:27  Amit Langote <[email protected]>
  parent: David Steele <[email protected]>
  0 siblings, 1 reply; 25+ messages in thread

From: Amit Langote @ 2023-06-13 08:27 UTC (permalink / raw)
  To: David Steele <[email protected]>; +Cc: Tom Lane <[email protected]>; Julien Rouhaud <[email protected]>; pgsql-hackers

On Tue, Jun 13, 2023 at 4:44 PM David Steele <[email protected]> wrote:
> On 6/13/23 06:09, Amit Langote wrote:
> > On Sat, Jun 10, 2023 at 10:27 PM Tom Lane <[email protected]> wrote:
> >> Julien Rouhaud <[email protected]> writes:
> >>> On Sat, Jun 10, 2023 at 08:56:47AM -0400, Tom Lane wrote:
> >>>> -   rte->relkind = 0;
> >>
> >>> and also handle that field in (read|out)funcs.c
> >>
> >> Oh, right.  Ugh, that means a catversion bump.  It's not like
> >> we've never done that during beta, but it's kind of an annoying
> >> cost for a detail as tiny as this.
> >
> > OK, so how about the attached?
>
> The patch looks good to me. I also tested it against pgAudit and
> everything worked.

Thanks for the review.

> I decided to go with the following because I think it
> is easier to read:
>
> /* We only care about tables/views and can ignore subqueries, etc. */
> if (!(rte->rtekind == RTE_RELATION ||
>        (rte->rtekind == RTE_SUBQUERY && rte->relkind == RELKIND_VIEW)))
>      continue;

It didn't occur to me so far to mention it but this could be replaced with:

    if (rte->perminfoindex != 0)

and turn that condition into an Assert instead, like the loop over
range table in ExecCheckPermissions() does.

> > I considered adding Assert(relkind == RELKIND_VIEW) in all places that
> > have the "rte->rtekind == RTE_SUBQUERY && OidIsValid(rte->relid)"
> > condition, but that seemed like an overkill, so only added one in the
> > #ifdef USE_ASSERT_CHECKING block in ExecCheckPermissions() that
> > f75cec4fff877 added.
>
> This seems like a good place for the assert.

I added a comment above this Assert.

I'd like to push this tomorrow barring objections.

-- 
Thanks, Amit Langote
EDB: http://www.enterprisedb.com


Attachments:

  [application/octet-stream] v2-0001-Retain-relkind-too-in-RTE_SUBQUERY-entries-for-vi.patch (5.5K, ../../CA+HiwqHAZVm+qDmNLvS6CT2OiNoiRhr-00B3XEkVNOi26+Rpug@mail.gmail.com/2-v2-0001-Retain-relkind-too-in-RTE_SUBQUERY-entries-for-vi.patch)
  download | inline diff:
From afa3d889d82e2bd274fc6b2d28f13f8b4b280e28 Mon Sep 17 00:00:00 2001
From: Amit Langote <[email protected]>
Date: Tue, 13 Jun 2023 12:52:47 +0900
Subject: [PATCH v2] Retain relkind too in RTE_SUBQUERY entries for views.

47bb9db75 modified the ApplyRetrieveRule()'s conversion of a view's
original RTE_RELATION entry into an RTE_SUBQUERY one to retain relid,
rellockmode, and perminfoindex so that the executor can lock the view
and check its permissions.  It seems better to also retain
relkind for cross-checking that the exception of an
RTE_SUBQUERY entry being allowed to carry relation details only
applies to views, so do so.

Bump catversion because this changes the output format of
RTE_SUBQUERY RTEs.

Suggested-by: David Steele <[email protected]>
Reviewed-by: David Steele <[email protected]>
Discussion: https://postgr.es/m/3953179e-9540-e5d1-a743-4bef368785b0%40pgmasters.net
---
 src/backend/executor/execMain.c      |  9 +++++++++
 src/backend/nodes/outfuncs.c         |  1 +
 src/backend/nodes/readfuncs.c        |  1 +
 src/backend/rewrite/rewriteHandler.c |  7 +++----
 src/include/catalog/catversion.h     |  2 +-
 src/include/nodes/parsenodes.h       | 14 +++++++-------
 6 files changed, 22 insertions(+), 12 deletions(-)

diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index c76fdf59ec..4c5a7bbf62 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -595,6 +595,15 @@ ExecCheckPermissions(List *rangeTable, List *rteperminfos,
 		if (rte->perminfoindex != 0)
 		{
 			/* Sanity checks */
+
+			/*
+			 * Only relation RTEs and subquery RTEs that were once relation
+			 * RTEs (views) have their perminfoindex set.
+			 */
+			Assert(rte->rtekind == RTE_RELATION ||
+				   (rte->rtekind == RTE_SUBQUERY &&
+					rte->relkind == RELKIND_VIEW));
+
 			(void) getRTEPermissionInfo(rteperminfos, rte);
 			/* Many-to-one mapping not allowed */
 			Assert(!bms_is_member(rte->perminfoindex, indexset));
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index ba00b99249..955286513d 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -513,6 +513,7 @@ _outRangeTblEntry(StringInfo str, const RangeTblEntry *node)
 			WRITE_BOOL_FIELD(security_barrier);
 			/* we re-use these RELATION fields, too: */
 			WRITE_OID_FIELD(relid);
+			WRITE_CHAR_FIELD(relkind);
 			WRITE_INT_FIELD(rellockmode);
 			WRITE_UINT_FIELD(perminfoindex);
 			break;
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 597e5b3ea8..a136ae1d60 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -503,6 +503,7 @@ _readRangeTblEntry(void)
 			READ_BOOL_FIELD(security_barrier);
 			/* we re-use these RELATION fields, too: */
 			READ_OID_FIELD(relid);
+			READ_CHAR_FIELD(relkind);
 			READ_INT_FIELD(rellockmode);
 			READ_UINT_FIELD(perminfoindex);
 			break;
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 0e4f76efa8..0967be762c 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -1849,11 +1849,10 @@ ApplyRetrieveRule(Query *parsetree,
 
 	/*
 	 * Clear fields that should not be set in a subquery RTE.  Note that we
-	 * leave the relid, rellockmode, and perminfoindex fields set, so that the
-	 * view relation can be appropriately locked before execution and its
-	 * permissions checked.
+	 * leave the relid, rellockmode, relkind, and perminfoindex fields set, so
+	 * that the view relation can be appropriately locked before execution and
+	 * its permissions checked.
 	 */
-	rte->relkind = 0;
 	rte->tablesample = NULL;
 	rte->inh = false;			/* must not be set for a subquery */
 
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index c784937a0e..fe70d8396d 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	202305211
+#define CATALOG_VERSION_NO	202306141
 
 #endif
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 0ca298f5a1..786692781d 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1056,13 +1056,13 @@ typedef struct RangeTblEntry
 	 * this RTE in the containing struct's list of same; 0 if permissions need
 	 * not be checked for this RTE.
 	 *
-	 * As a special case, relid, rellockmode, and perminfoindex can also be
-	 * set (nonzero) in an RTE_SUBQUERY RTE.  This occurs when we convert an
-	 * RTE_RELATION RTE naming a view into an RTE_SUBQUERY containing the
-	 * view's query.  We still need to perform run-time locking and permission
-	 * checks on the view, even though it's not directly used in the query
-	 * anymore, and the most expedient way to do that is to retain these
-	 * fields from the old state of the RTE.
+	 * As a special case, relid, rellockmode, relkind, and perminfoindex can
+	 * also be set (nonzero) in an RTE_SUBQUERY RTE.  This occurs when we
+	 * convert an RTE_RELATION RTE naming a view into an RTE_SUBQUERY
+	 * containing the view's query.  We still need to perform run-time locking
+	 * and permission hecks on the view, even though it's not directly used in
+	 * the query anymore, and the most expedient way to do that is to retain
+	 * these fields from the old state of the RTE.
 	 *
 	 * As a special case, RTE_NAMEDTUPLESTORE can also set relid to indicate
 	 * that the tuple format of the tuplestore is the same as the referenced
-- 
2.35.3



^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Views no longer in rangeTabls?
@ 2023-06-13 08:57  David Steele <[email protected]>
  parent: Amit Langote <[email protected]>
  0 siblings, 0 replies; 25+ messages in thread

From: David Steele @ 2023-06-13 08:57 UTC (permalink / raw)
  To: Amit Langote <[email protected]>; +Cc: Tom Lane <[email protected]>; Julien Rouhaud <[email protected]>; pgsql-hackers

On 6/13/23 10:27, Amit Langote wrote:
> On Tue, Jun 13, 2023 at 4:44 PM David Steele <[email protected]> wrote:
> 
>> I decided to go with the following because I think it
>> is easier to read:
>>
>> /* We only care about tables/views and can ignore subqueries, etc. */
>> if (!(rte->rtekind == RTE_RELATION ||
>>         (rte->rtekind == RTE_SUBQUERY && rte->relkind == RELKIND_VIEW)))
>>       continue;
> 
> It didn't occur to me so far to mention it but this could be replaced with:
> 
>      if (rte->perminfoindex != 0)
> 
> and turn that condition into an Assert instead, like the loop over
> range table in ExecCheckPermissions() does.

Hmmm, that might work, and save us a filter on rte->perminfoindex later 
on (to filter out table partitions). Thanks for the tip!

>>> I considered adding Assert(relkind == RELKIND_VIEW) in all places that
>>> have the "rte->rtekind == RTE_SUBQUERY && OidIsValid(rte->relid)"
>>> condition, but that seemed like an overkill, so only added one in the
>>> #ifdef USE_ASSERT_CHECKING block in ExecCheckPermissions() that
>>> f75cec4fff877 added.
>>
>> This seems like a good place for the assert.
> 
> I added a comment above this Assert.
> 
> I'd like to push this tomorrow barring objections.

+1 for the new comment.

Regards,
-David






^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [PATCH] Add sortsupport for range types and btree_gist
@ 2024-01-26 13:01  vignesh C <[email protected]>
  parent: Bernd Helmle <[email protected]>
  0 siblings, 1 reply; 25+ messages in thread

From: vignesh C @ 2024-01-26 13:01 UTC (permalink / raw)
  To: jian he <[email protected]>; +Cc: Bernd Helmle <[email protected]>; [email protected]

On Wed, 10 Jan 2024 at 19:49, jian he <[email protected]> wrote:
>
> On Wed, Jan 10, 2024 at 8:00 AM jian he <[email protected]> wrote:
> >
> > `
> > from the doc, add sortsupport function will only influence index build time?
> >
> > +/*
> > + * GiST sortsupport comparator for ranges.
> > + *
> > + * Operates solely on the lower bounds of the ranges, comparing them using
> > + * range_cmp_bounds().
> > + * Empty ranges are sorted before non-empty ones.
> > + */
> > +static int
> > +range_gist_cmp(Datum a, Datum b, SortSupport ssup)
> > +{
> > + RangeType *range_a = DatumGetRangeTypeP(a);
> > + RangeType *range_b = DatumGetRangeTypeP(b);
> > + TypeCacheEntry *typcache = ssup->ssup_extra;
> > + RangeBound lower1,
> > + lower2;
> > + RangeBound upper1,
> > + upper2;
> > + bool empty1,
> > + empty2;
> > + int result;
> > +
> > + if (typcache == NULL) {
> > + Assert(RangeTypeGetOid(range_a) == RangeTypeGetOid(range_b));
> > + typcache = lookup_type_cache(RangeTypeGetOid(range_a), TYPECACHE_RANGE_INFO);
> > +
> > + /*
> > + * Cache the range info between calls to avoid having to call
> > + * lookup_type_cache() for each comparison.
> > + */
> > + ssup->ssup_extra = typcache;
> > + }
> > +
> > + range_deserialize(typcache, range_a, &lower1, &upper1, &empty1);
> > + range_deserialize(typcache, range_b, &lower2, &upper2, &empty2);
> > +
> > + /* For b-tree use, empty ranges sort before all else */
> > + if (empty1 && empty2)
> > + result = 0;
> > + else if (empty1)
> > + result = -1;
> > + else if (empty2)
> > + result = 1;
> > + else
> > + result = range_cmp_bounds(typcache, &lower1, &lower2);
> > +
> > + if ((Datum) range_a != a)
> > + pfree(range_a);
> > +
> > + if ((Datum) range_b != b)
> > + pfree(range_b);
> > +
> > + return result;
> > +}
> >
> > per https://www.postgresql.org/docs/current/gist-extensibility.html
> > QUOTE:
> > All the GiST support methods are normally called in short-lived memory
> > contexts; that is, CurrentMemoryContext will get reset after each
> > tuple is processed. It is therefore not very important to worry about
> > pfree'ing everything you palloc. However, in some cases it's useful
> > for a support method to
> > ENDOF_QUOTE
> >
> > so removing the following part should be OK.
> > + if ((Datum) range_a != a)
> > + pfree(range_a);
> > +
> > + if ((Datum) range_b != b)
> > + pfree(range_b);
> >
> > comparison solely on the lower bounds looks strange to me.
> > if lower bound is the same, then compare upper bound, so the
> > range_gist_cmp function is consistent with function range_compare.
> > so following change:
> >
> > + else
> > + result = range_cmp_bounds(typcache, &lower1, &lower2);
> > to
> > `
> > else
> > {
> > result = range_cmp_bounds(typcache, &lower1, &lower2);
> > if (result == 0)
> > result = range_cmp_bounds(typcache, &upper1, &upper2);
> > }
> > `
> >
> > does contrib/btree_gist/btree_gist--1.7--1.8.sql function be declared
> > as strict ? (I am not sure)
> > other than that, the whole patch looks good.
>
> the original author email address ([email protected])
> Address not found.
> so I don't include it.
>
> I split the original author's patch into 2.
> 1. Add GiST sortsupport function for all the btree-gist module data
> types except anyrange data type (which actually does not in this
> module)
> 2. Add GiST sortsupport function for anyrange data type.

CFBot shows that the patch does not apply anymore as in [1]:
=== Applying patches on top of PostgreSQL commit ID
7014c9a4bba2d1b67d60687afb5b2091c1d07f73 ===
=== applying patch
./v5-0001-Add-GIST-sortsupport-function-for-all-the-btree-g.patch
patching file contrib/btree_gist/Makefile
Hunk #1 FAILED at 33.
1 out of 1 hunk FAILED -- saving rejects to file contrib/btree_gist/Makefile.rej
...
The next patch would create the file
contrib/btree_gist/btree_gist--1.7--1.8.sql,
which already exists!  Applying it anyway.
patching file contrib/btree_gist/btree_gist--1.7--1.8.sql
Hunk #1 FAILED at 1.
1 out of 1 hunk FAILED -- saving rejects to file
contrib/btree_gist/btree_gist--1.7--1.8.sql.rej
patching file contrib/btree_gist/btree_gist.control
Hunk #1 FAILED at 1.
1 out of 1 hunk FAILED -- saving rejects to file
contrib/btree_gist/btree_gist.control.rej
...
patching file contrib/btree_gist/meson.build
Hunk #1 FAILED at 50.
1 out of 1 hunk FAILED -- saving rejects to file
contrib/btree_gist/meson.build.rej

Please post an updated version for the same.

[1] - http://cfbot.cputube.org/patch_46_3686.log

Regards,
Vignesh





^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [PATCH] Add sortsupport for range types and btree_gist
@ 2024-01-26 18:22  Bernd Helmle <[email protected]>
  parent: vignesh C <[email protected]>
  0 siblings, 0 replies; 25+ messages in thread

From: Bernd Helmle @ 2024-01-26 18:22 UTC (permalink / raw)
  To: vignesh C <[email protected]>; jian he <[email protected]>; +Cc: [email protected]

Am Freitag, dem 26.01.2024 um 18:31 +0530 schrieb vignesh C:
> CFBot shows that the patch does not apply anymore as in [1]:
> === Applying patches on top of PostgreSQL commit ID

I've started working on it and planning to submit a polished patch for
the upcoming CF.







^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [PATCH] Add sortsupport for range types and btree_gist
@ 2025-03-11 18:28  Bernd Helmle <[email protected]>
  0 siblings, 0 replies; 25+ messages in thread

From: Bernd Helmle @ 2025-03-11 18:28 UTC (permalink / raw)
  To: Andrey Borodin <[email protected]>; +Cc: Michael Paquier <[email protected]>; jian he <[email protected]>; PostgreSQL Hackers <[email protected]>

Hi,

Am Montag, dem 09.12.2024 um 18:10 +0100 schrieb Bernd Helmle:
> > I think we have two options:
> > 1. Just do not commit tests. We ran it manually, saw that paths are
> > taken, we are happy.
> 
> So here's a version with the original, unchanged regression tests and
> injection point removed (i hope i forgot nothing to revert).
> 
> This version also fixes cidr sortsupport (thanks again Andrey for
> spotting this), where i accidently used inet instead of the correct
> cidr datatype to pass down to the sortsupport function.

Please find a new rebased version of this patch. No changes to the
patch itself, but it didn't apply without conflicts anymore.

Thanks,
	Bernd



Attachments:

  [text/x-patch] 0001-Add-support-for-sorted-gist-index-builds-to-btree_gi.patch (44.1K, ../../[email protected]/2-0001-Add-support-for-sorted-gist-index-builds-to-btree_gi.patch)
  download | inline diff:
From 5cd326a29d5f6bb7ffb0c6328870cfd113ed1b42 Mon Sep 17 00:00:00 2001
From: Bernd Helmle <Bernd Helmle [email protected]>
Date: Tue, 11 Mar 2025 19:15:13 +0100
Subject: [PATCH] Add support for sorted gist index builds to btree_gist

This enables sortsupport in the btree_gist extension for faster
builds of gist indexes. Additionally sortsupport is also added for range types
to the backend so that gist indexes on range types also benefit from the
speed up.

Sorted gist index build strategy is the new default now. Regression tests are
unchanged and are using the sorted build strategy instead.
---
 contrib/btree_gist/Makefile                 |   2 +-
 contrib/btree_gist/btree_bit.c              |  46 +++++
 contrib/btree_gist/btree_bool.c             |  22 +++
 contrib/btree_gist/btree_bytea.c            |  35 ++++
 contrib/btree_gist/btree_cash.c             |  27 +++
 contrib/btree_gist/btree_date.c             |  25 +++
 contrib/btree_gist/btree_enum.c             |  33 ++++
 contrib/btree_gist/btree_float4.c           |  28 +++
 contrib/btree_gist/btree_float8.c           |  27 +++
 contrib/btree_gist/btree_gist--1.8--1.9.sql | 192 ++++++++++++++++++++
 contrib/btree_gist/btree_gist.control       |   2 +-
 contrib/btree_gist/btree_inet.c             |  27 +++
 contrib/btree_gist/btree_int2.c             |  26 +++
 contrib/btree_gist/btree_int4.c             |  33 +++-
 contrib/btree_gist/btree_int8.c             |  26 +++
 contrib/btree_gist/btree_interval.c         |  25 +++
 contrib/btree_gist/btree_macaddr.c          |  29 +++
 contrib/btree_gist/btree_macaddr8.c         |  24 +++
 contrib/btree_gist/btree_numeric.c          |  33 ++++
 contrib/btree_gist/btree_oid.c              |  28 +++
 contrib/btree_gist/btree_text.c             |  34 ++++
 contrib/btree_gist/btree_time.c             |  26 +++
 contrib/btree_gist/btree_ts.c               |  25 +++
 contrib/btree_gist/btree_utils_var.h        |  12 +-
 contrib/btree_gist/btree_uuid.c             |  25 +++
 contrib/btree_gist/meson.build              |   1 +
 doc/src/sgml/btree-gist.sgml                |   7 +
 src/backend/utils/adt/rangetypes_gist.c     |  70 +++++++
 src/include/catalog/pg_amproc.dat           |   3 +
 src/include/catalog/pg_proc.dat             |   3 +
 30 files changed, 888 insertions(+), 8 deletions(-)
 create mode 100644 contrib/btree_gist/btree_gist--1.8--1.9.sql

diff --git a/contrib/btree_gist/Makefile b/contrib/btree_gist/Makefile
index 7ac2df26c10..68190ac5e46 100644
--- a/contrib/btree_gist/Makefile
+++ b/contrib/btree_gist/Makefile
@@ -34,7 +34,7 @@ DATA = btree_gist--1.0--1.1.sql \
        btree_gist--1.1--1.2.sql btree_gist--1.2.sql btree_gist--1.2--1.3.sql \
        btree_gist--1.3--1.4.sql btree_gist--1.4--1.5.sql \
        btree_gist--1.5--1.6.sql btree_gist--1.6--1.7.sql \
-       btree_gist--1.7--1.8.sql
+       btree_gist--1.7--1.8.sql btree_gist--1.8--1.9.sql
 PGFILEDESC = "btree_gist - B-tree equivalent GiST operator classes"
 
 REGRESS = init int2 int4 int8 float4 float8 cash oid timestamp timestamptz \
diff --git a/contrib/btree_gist/btree_bit.c b/contrib/btree_gist/btree_bit.c
index f346b956fa9..35aa5578f83 100644
--- a/contrib/btree_gist/btree_bit.c
+++ b/contrib/btree_gist/btree_bit.c
@@ -6,6 +6,7 @@
 #include "btree_gist.h"
 #include "btree_utils_var.h"
 #include "utils/fmgrprotos.h"
+#include "utils/sortsupport.h"
 #include "utils/varbit.h"
 
 
@@ -18,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(byteacmp,
+								 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)
 {
@@ -207,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 adb724e16ac..e6b08f04e9d 100644
--- a/contrib/btree_gist/btree_bool.c
+++ b/contrib/btree_gist/btree_bool.c
@@ -5,6 +5,7 @@
 
 #include "btree_gist.h"
 #include "btree_utils_num.h"
+#include "utils/sortsupport.h"
 
 typedef struct boolkey
 {
@@ -22,6 +23,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)
@@ -166,3 +177,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 5eacb8c59a0..87b83d162bb 100644
--- a/contrib/btree_gist/btree_bytea.c
+++ b/contrib/btree_gist/btree_bytea.c
@@ -6,6 +6,7 @@
 #include "btree_gist.h"
 #include "btree_utils_var.h"
 #include "utils/fmgrprotos.h"
+#include "utils/sortsupport.h"
 
 
 /*
@@ -17,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 c18c34c8b83..4d50b0f0d9a 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 7a4a9d7a853..ee2d96e6006 100644
--- a/contrib/btree_gist/btree_date.c
+++ b/contrib/btree_gist/btree_date.c
@@ -7,6 +7,7 @@
 #include "btree_utils_num.h"
 #include "utils/fmgrprotos.h"
 #include "utils/date.h"
+#include "utils/sortsupport.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 05d02e704a0..9c4a7727102 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/fmgrprotos.h"
+#include "utils/sortsupport.h"
 
 /* enums are really Oids, so we just use the same structure */
 
@@ -26,8 +27,23 @@ 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 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 +199,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
+	 * comparison functions, it also needs to pass flinfo when calling
+	 * it. Thus save it in ssup_extra and retrieve it in enum_fast_cmp() later.
+	 */
+	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 d138aa94bf2..c23f7886e09 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 a74cd200529..129837f2a19 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.8--1.9.sql b/contrib/btree_gist/btree_gist--1.8--1.9.sql
new file mode 100644
index 00000000000..401e9965cf6
--- /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  (cidr, cidr) 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_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) 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.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_inet.c b/contrib/btree_gist/btree_inet.c
index 4cffd349091..cf94cd0f300 100644
--- a/contrib/btree_gist/btree_inet.c
+++ b/contrib/btree_gist/btree_inet.c
@@ -7,6 +7,7 @@
 #include "btree_utils_num.h"
 #include "catalog/pg_type.h"
 #include "utils/builtins.h"
+#include "utils/sortsupport.h"
 
 typedef struct inetkey
 {
@@ -23,8 +24,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)
 {
@@ -184,3 +200,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 1751a6c055d..02df5813231 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 90d183be6e8..1cef35aace0 100644
--- a/contrib/btree_gist/btree_int4.c
+++ b/contrib/btree_gist/btree_int4.c
@@ -2,15 +2,15 @@
  * 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;
+  int32		lower;
+  int32		upper;
 } int32KEY;
 
 /*
@@ -24,7 +24,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
 gbt_int4gt(const void *a, const void *b, FmgrInfo *flinfo)
@@ -90,6 +90,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 661cf8189fc..f61a4da6801 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 8f99a416965..25b4ecce192 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/fmgrprotos.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 558cfa2172f..7df85e5db3b 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/fmgrprotos.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 5d0c5509f51..e659168b1e2 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/fmgrprotos.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 d533648a295..fe6a5a48dd5 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 d1976f4f091..e3bd78658d0 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 8f80f54240f..4c6dfa9a4e7 100644
--- a/contrib/btree_gist/btree_text.c
+++ b/contrib/btree_gist/btree_text.c
@@ -7,6 +7,7 @@
 #include "btree_utils_var.h"
 #include "mb/pg_wchar.h"
 #include "utils/fmgrprotos.h"
+#include "utils/sortsupport.h"
 
 /*
 ** Text ops
@@ -19,7 +20,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 2f7859340f6..28d4447ff59 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/fmgrprotos.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 9e0d979dda9..5ec739b4813 100644
--- a/contrib/btree_gist/btree_ts.c
+++ b/contrib/btree_gist/btree_ts.c
@@ -10,6 +10,7 @@
 #include "utils/fmgrprotos.h"
 #include "utils/timestamp.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 9302d41ced6..f79b553a1e1 100644
--- a/contrib/btree_gist/btree_utils_var.h
+++ b/contrib/btree_gist/btree_utils_var.h
@@ -41,7 +41,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 f4c5c6e5892..85e57d52151 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 f4fa9574f1f..89932dd3844 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..a4c1b99be1f 100644
--- a/doc/src/sgml/btree-gist.sgml
+++ b/doc/src/sgml/btree-gist.sgml
@@ -52,6 +52,13 @@
   <type>oid</type>, and <type>money</type>.
  </para>
 
+ <para>
+  By default <filename>btree_gist</filename> builds <acronym>GiST</acronym> index with
+  <function>sortsupport</function> in <firstterm>sorted</firstterm> mode. This usually results in
+  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 a60ee985e74..fa7543be9de 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 19100482ba4..4b812b680d4 100644
--- a/src/include/catalog/pg_amproc.dat
+++ b/src/include/catalog/pg_amproc.dat
@@ -606,6 +606,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 => 'any',
   amprocrighttype => 'any', amprocnum => '12',
   amproc => 'gist_stratnum_common' },
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 42e427f8fe8..9e423d9d970 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10854,6 +10854,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.48.1



^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* [PATCH v2 2/2] fixups
@ 2026-07-07 16:35  Álvaro Herrera <[email protected]>
  0 siblings, 0 replies; 25+ messages in thread

From: Álvaro Herrera @ 2026-07-07 16:35 UTC (permalink / raw)

---
 src/backend/commands/repack.c | 54 ++++++++++++++++++++++-------------
 1 file changed, 34 insertions(+), 20 deletions(-)

diff --git a/src/backend/commands/repack.c b/src/backend/commands/repack.c
index 2879c8af574..fcc401ccdb9 100644
--- a/src/backend/commands/repack.c
+++ b/src/backend/commands/repack.c
@@ -2152,6 +2152,10 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt)
 
 		/*
 		 * For USING INDEX, scan pg_index to find those with indisclustered.
+		 *
+		 * Note we don't obtain lock of any kind on the index, which means the
+		 * index or its owning table could be gone or change at any point.  We
+		 * have to be extra careful when examining catalog state for them.
 		 */
 		catalog = table_open(IndexRelationId, AccessShareLock);
 		ScanKeyInit(&entry,
@@ -2169,7 +2173,7 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt)
 
 			index = (Form_pg_index) GETSTRUCT(tuple);
 
-			classtup = SearchSysCache1(RELOID, ObjectIdGetDatum(index->indrelid));
+			classtup = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(index->indrelid));
 			if (!HeapTupleIsValid(classtup))
 				continue;
 			classForm = (Form_pg_class) GETSTRUCT(classtup);
@@ -2178,11 +2182,11 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt)
 			if (classForm->relpersistence == RELPERSISTENCE_TEMP &&
 				!isTempOrTempToastNamespace(classForm->relnamespace))
 			{
-				ReleaseSysCache(classtup);
+				heap_freetuple(classtup);
 				continue;
 			}
 
-			ReleaseSysCache(classtup);
+			heap_freetuple(classtup);
 
 			/* noisily skip rels which the user can't process */
 			if (!repack_is_permitted_for_relation(cmd, index->indrelid,
@@ -2274,7 +2278,9 @@ get_tables_to_repack_partitioned(RepackCommand cmd, Oid relid,
 			if (get_rel_relkind(child_oid) != RELKIND_INDEX)
 				continue;
 
-			table_oid = IndexGetRelation(child_oid, false);
+			table_oid = IndexGetRelation(child_oid, true);
+			if (!OidIsValid(table_oid))
+				continue;
 			index_oid = child_oid;
 		}
 		else
@@ -2309,33 +2315,41 @@ get_tables_to_repack_partitioned(RepackCommand cmd, Oid relid,
 
 
 /*
- * Return whether userid has privileges to REPACK relid.  If not, this
- * function emits a WARNING.
+ * Return whether userid has privileges to execute REPACK on relid.
+ *
+ * Caller may not have a lock on the relation, so it could have been
+ * dropped concurrently.  In that case, silently return false.
+ *
+ * If the relation does exist but the user doesn't have the required
+ * privs, emit a WARNING and return false.  Otherwise, return true.
  */
 static bool
 repack_is_permitted_for_relation(RepackCommand cmd, Oid relid, Oid userid)
 {
 	bool		is_missing = false;
+	AclResult	result;
+	char	   *relname;
 
 	Assert(cmd == REPACK_COMMAND_CLUSTER || cmd == REPACK_COMMAND_REPACK);
 
-	if (pg_class_aclcheck_ext(relid, userid, ACL_MAINTAIN, &is_missing) == ACLCHECK_OK)
+	result = pg_class_aclcheck_ext(relid, userid, ACL_MAINTAIN, &is_missing);
+	if (is_missing)
+		return false;
+
+	if (result == ACLCHECK_OK)
 		return true;
 
-	/* Report a warning if the relation still exists. */
-	if (!is_missing)
+	/*
+	 * The relation can also be dropped after we tested its ACL and before we
+	 * read its relname, so be careful.
+	 */
+	relname = get_rel_name(relid);
+	if (relname != NULL)
 	{
-		char	   *relname;
-
-		relname = get_rel_name(relid);
-		if (relname != NULL)
-		{
-			ereport(WARNING,
-					errmsg("permission denied to execute %s on \"%s\", skipping it",
-						   RepackCommandAsString(cmd), relname));
-
-			pfree(relname);
-		}
+		ereport(WARNING,
+				errmsg("permission denied to execute %s on \"%s\", skipping it",
+					   RepackCommandAsString(cmd), relname));
+		pfree(relname);
 	}
 
 	return false;
-- 
2.47.3


--op6mnexl7cn72cto--





^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* [PATCH v2 2/2] fixups
@ 2026-07-07 16:35  Álvaro Herrera <[email protected]>
  0 siblings, 0 replies; 25+ messages in thread

From: Álvaro Herrera @ 2026-07-07 16:35 UTC (permalink / raw)

---
 src/backend/commands/repack.c | 54 ++++++++++++++++++++++-------------
 1 file changed, 34 insertions(+), 20 deletions(-)

diff --git a/src/backend/commands/repack.c b/src/backend/commands/repack.c
index 2879c8af574..fcc401ccdb9 100644
--- a/src/backend/commands/repack.c
+++ b/src/backend/commands/repack.c
@@ -2152,6 +2152,10 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt)
 
 		/*
 		 * For USING INDEX, scan pg_index to find those with indisclustered.
+		 *
+		 * Note we don't obtain lock of any kind on the index, which means the
+		 * index or its owning table could be gone or change at any point.  We
+		 * have to be extra careful when examining catalog state for them.
 		 */
 		catalog = table_open(IndexRelationId, AccessShareLock);
 		ScanKeyInit(&entry,
@@ -2169,7 +2173,7 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt)
 
 			index = (Form_pg_index) GETSTRUCT(tuple);
 
-			classtup = SearchSysCache1(RELOID, ObjectIdGetDatum(index->indrelid));
+			classtup = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(index->indrelid));
 			if (!HeapTupleIsValid(classtup))
 				continue;
 			classForm = (Form_pg_class) GETSTRUCT(classtup);
@@ -2178,11 +2182,11 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt)
 			if (classForm->relpersistence == RELPERSISTENCE_TEMP &&
 				!isTempOrTempToastNamespace(classForm->relnamespace))
 			{
-				ReleaseSysCache(classtup);
+				heap_freetuple(classtup);
 				continue;
 			}
 
-			ReleaseSysCache(classtup);
+			heap_freetuple(classtup);
 
 			/* noisily skip rels which the user can't process */
 			if (!repack_is_permitted_for_relation(cmd, index->indrelid,
@@ -2274,7 +2278,9 @@ get_tables_to_repack_partitioned(RepackCommand cmd, Oid relid,
 			if (get_rel_relkind(child_oid) != RELKIND_INDEX)
 				continue;
 
-			table_oid = IndexGetRelation(child_oid, false);
+			table_oid = IndexGetRelation(child_oid, true);
+			if (!OidIsValid(table_oid))
+				continue;
 			index_oid = child_oid;
 		}
 		else
@@ -2309,33 +2315,41 @@ get_tables_to_repack_partitioned(RepackCommand cmd, Oid relid,
 
 
 /*
- * Return whether userid has privileges to REPACK relid.  If not, this
- * function emits a WARNING.
+ * Return whether userid has privileges to execute REPACK on relid.
+ *
+ * Caller may not have a lock on the relation, so it could have been
+ * dropped concurrently.  In that case, silently return false.
+ *
+ * If the relation does exist but the user doesn't have the required
+ * privs, emit a WARNING and return false.  Otherwise, return true.
  */
 static bool
 repack_is_permitted_for_relation(RepackCommand cmd, Oid relid, Oid userid)
 {
 	bool		is_missing = false;
+	AclResult	result;
+	char	   *relname;
 
 	Assert(cmd == REPACK_COMMAND_CLUSTER || cmd == REPACK_COMMAND_REPACK);
 
-	if (pg_class_aclcheck_ext(relid, userid, ACL_MAINTAIN, &is_missing) == ACLCHECK_OK)
+	result = pg_class_aclcheck_ext(relid, userid, ACL_MAINTAIN, &is_missing);
+	if (is_missing)
+		return false;
+
+	if (result == ACLCHECK_OK)
 		return true;
 
-	/* Report a warning if the relation still exists. */
-	if (!is_missing)
+	/*
+	 * The relation can also be dropped after we tested its ACL and before we
+	 * read its relname, so be careful.
+	 */
+	relname = get_rel_name(relid);
+	if (relname != NULL)
 	{
-		char	   *relname;
-
-		relname = get_rel_name(relid);
-		if (relname != NULL)
-		{
-			ereport(WARNING,
-					errmsg("permission denied to execute %s on \"%s\", skipping it",
-						   RepackCommandAsString(cmd), relname));
-
-			pfree(relname);
-		}
+		ereport(WARNING,
+				errmsg("permission denied to execute %s on \"%s\", skipping it",
+					   RepackCommandAsString(cmd), relname));
+		pfree(relname);
 	}
 
 	return false;
-- 
2.47.3


--op6mnexl7cn72cto--





^ permalink  raw  reply  [nested|flat] 25+ messages in thread

end of thread, other threads:[~2026-07-07 16:35 UTC | newest]

Thread overview: 25+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-06-15 10:45 [PATCH] Add sortsupport for range types and btree_gist Christoph Heiss <[email protected]>
2022-06-15 14:39 ` Andrey Borodin <[email protected]>
2022-08-02 18:01   ` Jacob Champion <[email protected]>
2022-08-31 19:15     ` Christoph Heiss <[email protected]>
2022-10-02 07:23       ` Andres Freund <[email protected]>
2022-10-02 07:29         ` Andres Freund <[email protected]>
2022-07-05 18:13 ` Jacob Champion <[email protected]>
2022-11-30 17:25 ` Bernd Helmle <[email protected]>
2024-01-26 13:01   ` vignesh C <[email protected]>
2024-01-26 18:22     ` Bernd Helmle <[email protected]>
2022-08-31 17:20 [PATCH v4] Add sortsupport for range types and btree_gist Christoph Heiss <[email protected]>
2022-08-31 17:20 [PATCH v3] Add sortsupport for range types and btree_gist Christoph Heiss <[email protected]>
2023-06-10 06:51 Re: Views no longer in rangeTabls? David Steele <[email protected]>
2023-06-10 06:57 ` Re: Views no longer in rangeTabls? Amit Langote <[email protected]>
2023-06-10 07:30   ` Re: Views no longer in rangeTabls? David Steele <[email protected]>
2023-06-10 12:56     ` Re: Views no longer in rangeTabls? Tom Lane <[email protected]>
2023-06-10 13:18       ` Re: Views no longer in rangeTabls? Julien Rouhaud <[email protected]>
2023-06-10 13:27         ` Re: Views no longer in rangeTabls? Tom Lane <[email protected]>
2023-06-13 04:09           ` Re: Views no longer in rangeTabls? Amit Langote <[email protected]>
2023-06-13 07:44             ` Re: Views no longer in rangeTabls? David Steele <[email protected]>
2023-06-13 08:27               ` Re: Views no longer in rangeTabls? Amit Langote <[email protected]>
2023-06-13 08:57                 ` Re: Views no longer in rangeTabls? David Steele <[email protected]>
2025-03-11 18:28 Re: [PATCH] Add sortsupport for range types and btree_gist Bernd Helmle <[email protected]>
2026-07-07 16:35 [PATCH v2 2/2] fixups Álvaro Herrera <[email protected]>
2026-07-07 16:35 [PATCH v2 2/2] fixups Álvaro Herrera <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox