PostgreSQL 14 preview GiST 索引新增openapi: sortsupport - 加速gist索引创建, 紧凑组织gist index page.

digoal 2020-09-20

575

作者

digoal

日期

2020-09-20

背景

GiST 索引新增openapi: sortsupport. (是自定义GiST索引时可选的接口实现, 实现这个接口好处是提高这个GiST索引创建速度, 降低这个GiST索引split概率, 减少这个GiST索引空间占用)

意味着GiST索引可以根据指定的order operator来进行数据组织, 从而提高创建GiST索引的速度, 同时减少索引的大小(因为按指定顺序组织后, 数据更加紧凑, 可以减少gist index page split)

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=doc/src/sgml/gist.sgml;h=192338be88105af2db0839d8d071f88e9a00f8da;hp=f9226e7a35cbbae69cce918f5b17662d0114fc19;hb=16fa9b2b30a357b4aea982bd878ec2e5e002dbcc;hpb=089da3c4778fdc1931f721a265caa0c6fca38584

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=16fa9b2b30a357b4aea982bd878ec2e5e002dbcc

```
Add support for building GiST index by sorting.
author Heikki Linnakangas heikki.linnakangas@iki.fi
Thu, 17 Sep 2020 16:33:40 +0800 (11:33 +0300)
committer Heikki Linnakangas heikki.linnakangas@iki.fi
Thu, 17 Sep 2020 16:33:40 +0800 (11:33 +0300)
commit 16fa9b2b30a357b4aea982bd878ec2e5e002dbcc
tree d1ee3378a010ad424ce41db8af503d534566cf6e tree | snapshot
parent 089da3c4778fdc1931f721a265caa0c6fca38584 commit | diff
Add support for building GiST index by sorting.

This adds a new optional support function to the GiST access method:
sortsupport. If it is defined, the GiST index is built by sorting all data
to the order defined by the sortsupport's comparator function, and packing
the tuples in that order to GiST pages. This is similar to how B-tree
index build works, and is much faster than inserting the tuples one by
one. The resulting index is smaller too, because the pages are packed more
tightly, upto 'fillfactor'. The normal build method works by splitting
pages, which tends to lead to more wasted space.

The quality of the resulting index depends on how good the opclass-defined
sort order is. A good order preserves locality of the input data.

As the first user of this facility, add 'sortsupport' function to the
point_ops opclass. It sorts the points in Z-order (aka Morton Code), by
interleaving the bits of the X and Y coordinates.

Author: Andrey Borodin
Reviewed-by: Pavel Borisov, Thomas Munro
Discussion: https://www.postgresql.org/message-id/1A36620E-CAD8-4267-9067-FB31385E7C0D%40yandex-team.ru
```

例子

```
--- a/doc/src/sgml/gist.sgml
+++ b/doc/src/sgml/gist.sgml
@@ -259,6 +259,8 @@ CREATE INDEX ON my_table USING GIST (my_inet_column inet_ops);
compress method is omitted. The optional tenth method
options is needed if the operator class provides
the user-specified parameters.
+ The sortsupport method is also optional and is used to
+ speed up building a GiST index.

@@ -1065,6 +1067,74 @@ my_compress(PG_FUNCTION_ARGS)

+
+
+ sortsupport
+
+
+ Returns a comparator function to sort data in a way that preserves
+ locality. It is used by CREATE INDEX and
+ REINDEX commands. The quality of the created index
+ depends on how well the sort order determined by the comparator function
+ preserves locality of the inputs.
+
+
+ The sortsupport method is optional. If it is not
+ provided, CREATE INDEX builds the index by inserting
+ each tuple to the tree using the penalty and
+ picksplit functions, which is much slower.
+
+
+
+ The SQL declaration of the function must look like
+ this:
+
+
+CREATE OR REPLACE FUNCTION my_sortsupport(internal)
+RETURNS void
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT;
+
+
+ The argument is a pointer to a SortSupport
+ struct. At a minimum, the function must fill in its comparator field.
+ The comparator takes three arguments: two Datums to compare, and
+ a pointer to the SortSupport struct. The
+ Datums are the two indexed values in the format that they are stored
+ in the index; that is, in the format returned by the
+ compress method. The full API is defined in
+ src/include/utils/sortsupport.h.
+
+
+
+ The matching code in the C module could then follow this skeleton:
+
+
+PG_FUNCTION_INFO_V1(my_sortsupport);
+
+static int
+my_fastcmp(Datum x, Datum y, SortSupport ssup)
+{
+ / establish order between x and y by computing some sorting value z /
+
+ int z1 = ComputeSpatialCode(x);
+ int z2 = ComputeSpatialCode(y);
+
+ return z1 == z2 ? 0 : z1 > z2 ? 1 : -1;
+}
+
+Datum
+my_sortsupport(PG_FUNCTION_ARGS)
+{
+ SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0);
+
+ ssup->comparator = my_fastcmp;
+ PG_RETURN_VOID();
+}
+
+
+
+