Support for INCLUDE attributes in GiST indexes

Similarly to B-tree, GiST index access method gets support of INCLUDE
attributes.  These attributes aren't used for tree navigation and aren't
present in non-leaf pages.  But they are present in leaf pages and can be
fetched during index-only scan.

The point of having INCLUDE attributes in GiST indexes is slightly different
from the point of having them in B-tree.  The main point of INCLUDE attributes
in B-tree is to define UNIQUE constraint over part of attributes enabled for
index-only scan.  In GiST the main point of INCLUDE attributes is to use
index-only scan for attributes, whose data types don't have GiST opclasses.

Discussion: https://postgr.es/m/73A1A452-AD5F-40D4-BD61-978622FF75C1%40yandex-team.ru
Author: Andrey Borodin, with small changes by me
Reviewed-by: Andreas Karlsson

7 files changed, 266 insertions, 11 deletions

diff --git a/src/test/regress/expected/amutils.out b/src/test/regress/expected/amutils.out
index 4570a39b058..d92a6d12c62 100644
--- a/src/test/regress/expected/amutils.out
+++ b/src/test/regress/expected/amutils.out
@@ -75,7 +75,7 @@ select prop,
  can_unique         | f  |       | 
  can_multi_col      | t  |       | 
  can_exclude        | t  |       | 
- can_include        | f  |       | 
+ can_include        | t  |       | 
  bogus              |    |       | 
 (19 rows)
 
@@ -159,7 +159,7 @@ select amname, prop, pg_indexam_has_property(a.oid, prop) as p
  gist   | can_unique    | f
  gist   | can_multi_col | t
  gist   | can_exclude   | t
- gist   | can_include   | f
+ gist   | can_include   | t
  gist   | bogus         | 
  hash   | can_order     | f
  hash   | can_unique    | f
diff --git a/src/test/regress/expected/index_including.out b/src/test/regress/expected/index_including.out
index f86e5953fac..77ec29f2a33 100644
--- a/src/test/regress/expected/index_including.out
+++ b/src/test/regress/expected/index_including.out
@@ -330,22 +330,20 @@ SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
 
 DROP TABLE tbl;
 /*
- * 7. Check various AMs. All but btree must fail.
+ * 7. Check various AMs. All but btree and gist must fail.
  */
 CREATE TABLE tbl (c1 int,c2 int, c3 box, c4 box);
 CREATE INDEX on tbl USING brin(c1, c2) INCLUDE (c3, c4);
 ERROR:  access method "brin" does not support included columns
-CREATE INDEX on tbl USING gist(c3) INCLUDE (c4);
-ERROR:  access method "gist" does not support included columns
+CREATE INDEX on tbl USING gist(c3) INCLUDE (c1, c4);
 CREATE INDEX on tbl USING spgist(c3) INCLUDE (c4);
 ERROR:  access method "spgist" does not support included columns
 CREATE INDEX on tbl USING gin(c1, c2) INCLUDE (c3, c4);
 ERROR:  access method "gin" does not support included columns
 CREATE INDEX on tbl USING hash(c1, c2) INCLUDE (c3, c4);
 ERROR:  access method "hash" does not support included columns
-CREATE INDEX on tbl USING rtree(c1, c2) INCLUDE (c3, c4);
+CREATE INDEX on tbl USING rtree(c3) INCLUDE (c1, c4);
 NOTICE:  substituting access method "gist" for obsolete method "rtree"
-ERROR:  access method "gist" does not support included columns
 CREATE INDEX on tbl USING btree(c1, c2) INCLUDE (c3, c4);
 DROP TABLE tbl;
 /*
diff --git a/src/test/regress/expected/index_including_gist.out b/src/test/regress/expected/index_including_gist.out
new file mode 100644
index 00000000000..ed9906da669
--- /dev/null
+++ b/src/test/regress/expected/index_including_gist.out
@@ -0,0 +1,166 @@
+/*
+ * 1.1. test CREATE INDEX with buffered build
+ */
+-- Regular index with included columns
+CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box);
+-- size is chosen to exceed page size and trigger actual truncation
+INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,8000) AS x;
+CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c2,c3);
+SELECT pg_get_indexdef(i.indexrelid)
+FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid
+WHERE i.indrelid = 'tbl_gist'::regclass ORDER BY c.relname;
+                                  pg_get_indexdef                                  
+-----------------------------------------------------------------------------------
+ CREATE INDEX tbl_gist_idx ON public.tbl_gist USING gist (c4) INCLUDE (c1, c2, c3)
+(1 row)
+
+SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10));
+ c1 | c2 | c3 |     c4      
+----+----+----+-------------
+  1 |  2 |  3 | (2,3),(1,2)
+  2 |  4 |  6 | (4,5),(2,3)
+  3 |  6 |  9 | (6,7),(3,4)
+  4 |  8 | 12 | (8,9),(4,5)
+(4 rows)
+
+SET enable_bitmapscan TO off;
+EXPLAIN  (costs off) SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10));
+                   QUERY PLAN                   
+------------------------------------------------
+ Index Only Scan using tbl_gist_idx on tbl_gist
+   Index Cond: (c4 <@ '(10,10),(1,1)'::box)
+(2 rows)
+
+SET enable_bitmapscan TO default;
+DROP TABLE tbl_gist;
+/*
+ * 1.2. test CREATE INDEX with inserts
+ */
+-- Regular index with included columns
+CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box);
+-- size is chosen to exceed page size and trigger actual truncation
+CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c2,c3);
+INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,8000) AS x;
+SELECT pg_get_indexdef(i.indexrelid)
+FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid
+WHERE i.indrelid = 'tbl_gist'::regclass ORDER BY c.relname;
+                                  pg_get_indexdef                                  
+-----------------------------------------------------------------------------------
+ CREATE INDEX tbl_gist_idx ON public.tbl_gist USING gist (c4) INCLUDE (c1, c2, c3)
+(1 row)
+
+SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10));
+ c1 | c2 | c3 |     c4      
+----+----+----+-------------
+  1 |  2 |  3 | (2,3),(1,2)
+  2 |  4 |  6 | (4,5),(2,3)
+  3 |  6 |  9 | (6,7),(3,4)
+  4 |  8 | 12 | (8,9),(4,5)
+(4 rows)
+
+SET enable_bitmapscan TO off;
+EXPLAIN  (costs off) SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10));
+                   QUERY PLAN                   
+------------------------------------------------
+ Index Only Scan using tbl_gist_idx on tbl_gist
+   Index Cond: (c4 <@ '(10,10),(1,1)'::box)
+(2 rows)
+
+SET enable_bitmapscan TO default;
+DROP TABLE tbl_gist;
+/*
+ * 2. CREATE INDEX CONCURRENTLY
+ */
+CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box);
+INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,10) AS x;
+CREATE INDEX CONCURRENTLY tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c2,c3);
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl_gist' ORDER BY indexname;
+                                     indexdef                                      
+-----------------------------------------------------------------------------------
+ CREATE INDEX tbl_gist_idx ON public.tbl_gist USING gist (c4) INCLUDE (c1, c2, c3)
+(1 row)
+
+DROP TABLE tbl_gist;
+/*
+ * 3. REINDEX
+ */
+CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box);
+INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,10) AS x;
+CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c3);
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl_gist' ORDER BY indexname;
+                                   indexdef                                    
+-------------------------------------------------------------------------------
+ CREATE INDEX tbl_gist_idx ON public.tbl_gist USING gist (c4) INCLUDE (c1, c3)
+(1 row)
+
+REINDEX INDEX tbl_gist_idx;
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl_gist' ORDER BY indexname;
+                                   indexdef                                    
+-------------------------------------------------------------------------------
+ CREATE INDEX tbl_gist_idx ON public.tbl_gist USING gist (c4) INCLUDE (c1, c3)
+(1 row)
+
+ALTER TABLE tbl_gist DROP COLUMN c1;
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl_gist' ORDER BY indexname;
+ indexdef 
+----------
+(0 rows)
+
+DROP TABLE tbl_gist;
+/*
+ * 4. Update, delete values in indexed table.
+ */
+CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box);
+INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,10) AS x;
+CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c3);
+UPDATE tbl_gist SET c1 = 100 WHERE c1 = 2;
+UPDATE tbl_gist SET c1 = 1 WHERE c1 = 3;
+DELETE FROM tbl_gist WHERE c1 = 5 OR c3 = 12;
+DROP TABLE tbl_gist;
+/*
+ * 5. Alter column type.
+ */
+CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box);
+INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,10) AS x;
+CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c3);
+ALTER TABLE tbl_gist ALTER c1 TYPE bigint;
+ALTER TABLE tbl_gist ALTER c3 TYPE bigint;
+\d tbl_gist
+              Table "public.tbl_gist"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ c1     | bigint  |           |          | 
+ c2     | integer |           |          | 
+ c3     | bigint  |           |          | 
+ c4     | box     |           |          | 
+Indexes:
+    "tbl_gist_idx" gist (c4) INCLUDE (c1, c3)
+
+DROP TABLE tbl_gist;
+/*
+ * 6. EXCLUDE constraint.
+ */
+CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box, EXCLUDE USING gist (c4 WITH &&) INCLUDE (c1, c2, c3));
+INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,10) AS x;
+ERROR:  conflicting key value violates exclusion constraint "tbl_gist_c4_c1_c2_c3_excl"
+DETAIL:  Key (c4)=((4,5),(2,3)) conflicts with existing key (c4)=((2,3),(1,2)).
+INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(3*x,2*x),point(3*x+1,2*x+1)) FROM generate_series(1,10) AS x;
+EXPLAIN  (costs off) SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10));
+                         QUERY PLAN                          
+-------------------------------------------------------------
+ Index Only Scan using tbl_gist_c4_c1_c2_c3_excl on tbl_gist
+   Index Cond: (c4 <@ '(10,10),(1,1)'::box)
+(2 rows)
+
+\d tbl_gist
+              Table "public.tbl_gist"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ c1     | integer |           |          | 
+ c2     | integer |           |          | 
+ c3     | integer |           |          | 
+ c4     | box     |           |          | 
+Indexes:
+    "tbl_gist_c4_c1_c2_c3_excl" EXCLUDE USING gist (c4 WITH &&) INCLUDE (c1, c2, c3)
+
+DROP TABLE tbl_gist;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 4051a4ad4e1..ace703179e8 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -55,7 +55,7 @@ test: copy copyselect copydml
 # ----------
 test: create_misc create_operator create_procedure
 # These depend on the above two
-test: create_index create_view index_including
+test: create_index create_view index_including index_including_gist
 
 # ----------
 # Another group of parallel tests
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index ac1ea622d65..309a907ece8 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -63,6 +63,7 @@ test: create_operator
 test: create_procedure
 test: create_index
 test: index_including
+test: index_including_gist
 test: create_view
 test: create_aggregate
 test: create_function_3
diff --git a/src/test/regress/sql/index_including.sql b/src/test/regress/sql/index_including.sql
index 2c6dd6211e6..c0ae71d0cbd 100644
--- a/src/test/regress/sql/index_including.sql
+++ b/src/test/regress/sql/index_including.sql
@@ -176,15 +176,15 @@ SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
 DROP TABLE tbl;
 
 /*
- * 7. Check various AMs. All but btree must fail.
+ * 7. Check various AMs. All but btree and gist must fail.
  */
 CREATE TABLE tbl (c1 int,c2 int, c3 box, c4 box);
 CREATE INDEX on tbl USING brin(c1, c2) INCLUDE (c3, c4);
-CREATE INDEX on tbl USING gist(c3) INCLUDE (c4);
+CREATE INDEX on tbl USING gist(c3) INCLUDE (c1, c4);
 CREATE INDEX on tbl USING spgist(c3) INCLUDE (c4);
 CREATE INDEX on tbl USING gin(c1, c2) INCLUDE (c3, c4);
 CREATE INDEX on tbl USING hash(c1, c2) INCLUDE (c3, c4);
-CREATE INDEX on tbl USING rtree(c1, c2) INCLUDE (c3, c4);
+CREATE INDEX on tbl USING rtree(c3) INCLUDE (c1, c4);
 CREATE INDEX on tbl USING btree(c1, c2) INCLUDE (c3, c4);
 DROP TABLE tbl;
 
diff --git a/src/test/regress/sql/index_including_gist.sql b/src/test/regress/sql/index_including_gist.sql
new file mode 100644
index 00000000000..7d5c99b2e79
--- /dev/null
+++ b/src/test/regress/sql/index_including_gist.sql
@@ -0,0 +1,90 @@
+/*
+ * 1.1. test CREATE INDEX with buffered build
+ */
+
+-- Regular index with included columns
+CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box);
+-- size is chosen to exceed page size and trigger actual truncation
+INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,8000) AS x;
+CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c2,c3);
+SELECT pg_get_indexdef(i.indexrelid)
+FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid
+WHERE i.indrelid = 'tbl_gist'::regclass ORDER BY c.relname;
+SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10));
+SET enable_bitmapscan TO off;
+EXPLAIN  (costs off) SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10));
+SET enable_bitmapscan TO default;
+DROP TABLE tbl_gist;
+
+/*
+ * 1.2. test CREATE INDEX with inserts
+ */
+
+-- Regular index with included columns
+CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box);
+-- size is chosen to exceed page size and trigger actual truncation
+CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c2,c3);
+INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,8000) AS x;
+SELECT pg_get_indexdef(i.indexrelid)
+FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid
+WHERE i.indrelid = 'tbl_gist'::regclass ORDER BY c.relname;
+SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10));
+SET enable_bitmapscan TO off;
+EXPLAIN  (costs off) SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10));
+SET enable_bitmapscan TO default;
+DROP TABLE tbl_gist;
+
+/*
+ * 2. CREATE INDEX CONCURRENTLY
+ */
+CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box);
+INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,10) AS x;
+CREATE INDEX CONCURRENTLY tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c2,c3);
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl_gist' ORDER BY indexname;
+DROP TABLE tbl_gist;
+
+
+/*
+ * 3. REINDEX
+ */
+CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box);
+INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,10) AS x;
+CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c3);
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl_gist' ORDER BY indexname;
+REINDEX INDEX tbl_gist_idx;
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl_gist' ORDER BY indexname;
+ALTER TABLE tbl_gist DROP COLUMN c1;
+SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl_gist' ORDER BY indexname;
+DROP TABLE tbl_gist;
+
+/*
+ * 4. Update, delete values in indexed table.
+ */
+CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box);
+INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,10) AS x;
+CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c3);
+UPDATE tbl_gist SET c1 = 100 WHERE c1 = 2;
+UPDATE tbl_gist SET c1 = 1 WHERE c1 = 3;
+DELETE FROM tbl_gist WHERE c1 = 5 OR c3 = 12;
+DROP TABLE tbl_gist;
+
+/*
+ * 5. Alter column type.
+ */
+CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box);
+INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,10) AS x;
+CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c3);
+ALTER TABLE tbl_gist ALTER c1 TYPE bigint;
+ALTER TABLE tbl_gist ALTER c3 TYPE bigint;
+\d tbl_gist
+DROP TABLE tbl_gist;
+
+/*
+ * 6. EXCLUDE constraint.
+ */
+CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box, EXCLUDE USING gist (c4 WITH &&) INCLUDE (c1, c2, c3));
+INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,10) AS x;
+INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(3*x,2*x),point(3*x+1,2*x+1)) FROM generate_series(1,10) AS x;
+EXPLAIN  (costs off) SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10));
+\d tbl_gist
+DROP TABLE tbl_gist;