On 29/01/2019 19.58, Andrey Borodin wrote:
PFA patch without redundant checks.
I hope it is ok that I fixed a typo and some wording in the comment,
plus re-added the btree query which I accidentally removed in my last mail.
I think the current state of the patch is fine, assuming the solution
with truncTupdesc is deemed to be good enough by the committer, so I
will mark this as ready for committer.
Thanks for the patch and the speedy fixes!
Andreas
>From f1a85940d01e208a6502e689616a8806146c9f3d Mon Sep 17 00:00:00 2001
From: Andreas Karlsson <andr...@proxel.se>
Date: Wed, 30 Jan 2019 01:59:14 +0100
Subject: [PATCH] Covering GiST v10
This patch allow adding INCLUDE colums to GiST index.
These colums do not participate in GiST tree build,
are not used for chosing subtree for inserts or splits.
But they can be fetched during IndexOnlyScan.
---
doc/src/sgml/ref/create_index.sgml | 4 +-
doc/src/sgml/textsearch.sgml | 6 +
src/backend/access/gist/gist.c | 33 +++-
src/backend/access/gist/gistget.c | 4 +-
src/backend/access/gist/gistscan.c | 12 +-
src/backend/access/gist/gistsplit.c | 12 +-
src/backend/access/gist/gistutil.c | 42 ++++--
src/include/access/gist_private.h | 2 +
src/test/regress/expected/amutils.out | 4 +-
src/test/regress/expected/index_including.out | 8 +-
src/test/regress/expected/index_including_gist.out | 166 +++++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/serial_schedule | 1 +
src/test/regress/sql/index_including.sql | 6 +-
src/test/regress/sql/index_including_gist.sql | 90 +++++++++++
15 files changed, 358 insertions(+), 34 deletions(-)
create mode 100644 src/test/regress/expected/index_including_gist.out
create mode 100644 src/test/regress/sql/index_including_gist.sql
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index ad619cdcfe..f8657c6ae2 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -181,8 +181,8 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
</para>
<para>
- Currently, only the B-tree index access method supports this feature.
- In B-tree indexes, the values of columns listed in the
+ Currently, the B-tree and the GiST index access methods supports this
+ feature. In B-tree indexes, the values of columns listed in the
<literal>INCLUDE</literal> clause are included in leaf tuples which
correspond to heap tuples, but are not included in upper-level
index entries used for tree navigation.
diff --git a/doc/src/sgml/textsearch.sgml b/doc/src/sgml/textsearch.sgml
index ecebade767..e4c75ebf6f 100644
--- a/doc/src/sgml/textsearch.sgml
+++ b/doc/src/sgml/textsearch.sgml
@@ -3675,6 +3675,12 @@ SELECT plainto_tsquery('supernovae stars');
</para>
<para>
+ A GiST index can be covering, i.e. use the <literal>INCLUDE</literal>
+ clause. Included columns can have data types without any GiST operator
+ class. Included attributes will be stored uncompressed.
+ </para>
+
+ <para>
Lossiness causes performance degradation due to unnecessary fetches of table
records that turn out to be false matches. Since random access to table
records is slow, this limits the usefulness of GiST indexes. The
diff --git a/src/backend/access/gist/gist.c b/src/backend/access/gist/gist.c
index b75b3a8dac..2bbf7a7f49 100644
--- a/src/backend/access/gist/gist.c
+++ b/src/backend/access/gist/gist.c
@@ -75,7 +75,7 @@ gisthandler(PG_FUNCTION_ARGS)
amroutine->amclusterable = true;
amroutine->ampredlocks = true;
amroutine->amcanparallel = false;
- amroutine->amcaninclude = false;
+ amroutine->amcaninclude = true;
amroutine->amkeytype = InvalidOid;
amroutine->ambuild = gistbuild;
@@ -1382,8 +1382,8 @@ gistSplit(Relation r,
IndexTupleSize(itup[0]), GiSTPageSize,
RelationGetRelationName(r))));
- memset(v.spl_lisnull, true, sizeof(bool) * giststate->tupdesc->natts);
- memset(v.spl_risnull, true, sizeof(bool) * giststate->tupdesc->natts);
+ memset(v.spl_lisnull, true, sizeof(bool) * giststate->truncTupdesc->natts);
+ memset(v.spl_risnull, true, sizeof(bool) * giststate->truncTupdesc->natts);
gistSplitByKey(r, page, itup, len, giststate, &v, 0);
/* form left and right vector */
@@ -1462,8 +1462,19 @@ initGISTstate(Relation index)
giststate->scanCxt = scanCxt;
giststate->tempCxt = scanCxt; /* caller must change this if needed */
giststate->tupdesc = index->rd_att;
+ /*
+ * The truncated tupdesc does not contain the INCLUDE attributes.
+ *
+ * It is used to form tuples during tuple adjustement and page split.
+ * B-tree creates shortened tuple descriptor for every truncated tuple,
+ * because it is doing this less often: it does not have to form
+ * truncated tuples during page split. Also, B-tree is not adjusting
+ * tuples on internal pages the way GiST does.
+ */
+ giststate->truncTupdesc = CreateTupleDescCopyConstr(index->rd_att);
+ giststate->truncTupdesc->natts = IndexRelationGetNumberOfKeyAttributes(index);
- for (i = 0; i < index->rd_att->natts; i++)
+ for (i = 0; i < IndexRelationGetNumberOfKeyAttributes(index); i++)
{
fmgr_info_copy(&(giststate->consistentFn[i]),
index_getprocinfo(index, i + 1, GIST_CONSISTENT_PROC),
@@ -1531,6 +1542,20 @@ initGISTstate(Relation index)
giststate->supportCollation[i] = DEFAULT_COLLATION_OID;
}
+ for (; i < index->rd_att->natts; i++)
+ {
+ giststate->consistentFn[i].fn_oid = InvalidOid;
+ giststate->unionFn[i].fn_oid = InvalidOid;
+ giststate->compressFn[i].fn_oid = InvalidOid;
+ giststate->decompressFn[i].fn_oid = InvalidOid;
+ giststate->penaltyFn[i].fn_oid = InvalidOid;
+ giststate->picksplitFn[i].fn_oid = InvalidOid;
+ giststate->equalFn[i].fn_oid = InvalidOid;
+ giststate->distanceFn[i].fn_oid = InvalidOid;
+ giststate->fetchFn[i].fn_oid = InvalidOid;
+ giststate->supportCollation[i] = InvalidOid;
+ }
+
MemoryContextSwitchTo(oldCxt);
return giststate;
diff --git a/src/backend/access/gist/gistget.c b/src/backend/access/gist/gistget.c
index a96ef5c3ac..a4ee8763f6 100644
--- a/src/backend/access/gist/gistget.c
+++ b/src/backend/access/gist/gistget.c
@@ -769,11 +769,13 @@ gistgetbitmap(IndexScanDesc scan, TIDBitmap *tbm)
*
* Opclasses that implement a fetch function support index-only scans.
* Opclasses without compression functions also support index-only scans.
+ * Included attributes always can be fetched for index-only scans.
*/
bool
gistcanreturn(Relation index, int attno)
{
- if (OidIsValid(index_getprocid(index, attno, GIST_FETCH_PROC)) ||
+ if (attno > IndexRelationGetNumberOfKeyAttributes(index) ||
+ OidIsValid(index_getprocid(index, attno, GIST_FETCH_PROC)) ||
!OidIsValid(index_getprocid(index, attno, GIST_COMPRESS_PROC)))
return true;
else
diff --git a/src/backend/access/gist/gistscan.c b/src/backend/access/gist/gistscan.c
index 78a8ede794..4fffc8b33a 100644
--- a/src/backend/access/gist/gistscan.c
+++ b/src/backend/access/gist/gistscan.c
@@ -158,6 +158,7 @@ gistrescan(IndexScanDesc scan, ScanKey key, int nkeys,
if (scan->xs_want_itup && !scan->xs_hitupdesc)
{
int natts;
+ int nkeyatts;
int attno;
/*
@@ -167,13 +168,22 @@ gistrescan(IndexScanDesc scan, ScanKey key, int nkeys,
* types.
*/
natts = RelationGetNumberOfAttributes(scan->indexRelation);
+ nkeyatts = IndexRelationGetNumberOfKeyAttributes(scan->indexRelation);
so->giststate->fetchTupdesc = CreateTemplateTupleDesc(natts);
- for (attno = 1; attno <= natts; attno++)
+ for (attno = 1; attno <= nkeyatts; attno++)
{
TupleDescInitEntry(so->giststate->fetchTupdesc, attno, NULL,
scan->indexRelation->rd_opcintype[attno - 1],
-1, 0);
}
+
+ for (; attno <= natts; attno++)
+ {
+ /* taking opcintype from giststate->tupdesc */
+ TupleDescInitEntry(so->giststate->fetchTupdesc, attno, NULL,
+ TupleDescAttr(so->giststate->tupdesc, attno - 1)->atttypid,
+ -1, 0);
+ }
scan->xs_hitupdesc = so->giststate->fetchTupdesc;
/* Also create a memory context that will hold the returned tuples */
diff --git a/src/backend/access/gist/gistsplit.c b/src/backend/access/gist/gistsplit.c
index f210e0c39f..cf3af2821a 100644
--- a/src/backend/access/gist/gistsplit.c
+++ b/src/backend/access/gist/gistsplit.c
@@ -207,7 +207,7 @@ placeOne(Relation r, GISTSTATE *giststate, GistSplitVector *v,
gistDeCompressAtt(giststate, r, itup, NULL, (OffsetNumber) 0,
identry, isnull);
- for (; attno < giststate->tupdesc->natts; attno++)
+ for (; attno < giststate->truncTupdesc->natts; attno++)
{
float lpenalty,
rpenalty;
@@ -485,7 +485,7 @@ gistUserPicksplit(Relation r, GistEntryVector *entryvec, int attno, GistSplitVec
*/
v->spl_dontcare = NULL;
- if (attno + 1 < giststate->tupdesc->natts)
+ if (attno + 1 < giststate->truncTupdesc->natts)
{
int NumDontCare;
@@ -657,7 +657,7 @@ gistSplitByKey(Relation r, Page page, IndexTuple *itup, int len,
*/
v->spl_risnull[attno] = v->spl_lisnull[attno] = true;
- if (attno + 1 < giststate->tupdesc->natts)
+ if (attno + 1 < giststate->truncTupdesc->natts)
gistSplitByKey(r, page, itup, len, giststate, v, attno + 1);
else
gistSplitHalf(&v->splitVector, len);
@@ -683,7 +683,7 @@ gistSplitByKey(Relation r, Page page, IndexTuple *itup, int len,
v->splitVector.spl_left[v->splitVector.spl_nleft++] = i;
/* Compute union keys, unless outer recursion level will handle it */
- if (attno == 0 && giststate->tupdesc->natts == 1)
+ if (attno == 0 && giststate->truncTupdesc->natts == 1)
{
v->spl_dontcare = NULL;
gistunionsubkey(giststate, itup, v);
@@ -700,7 +700,7 @@ gistSplitByKey(Relation r, Page page, IndexTuple *itup, int len,
* Splitting on attno column is not optimal, so consider
* redistributing don't-care tuples according to the next column
*/
- Assert(attno + 1 < giststate->tupdesc->natts);
+ Assert(attno + 1 < giststate->truncTupdesc->natts);
if (v->spl_dontcare == NULL)
{
@@ -771,7 +771,7 @@ gistSplitByKey(Relation r, Page page, IndexTuple *itup, int len,
* that PickSplit (or the special cases above) produced correct union
* datums.
*/
- if (attno == 0 && giststate->tupdesc->natts > 1)
+ if (attno == 0 && giststate->truncTupdesc->natts > 1)
{
v->spl_dontcare = NULL;
gistunionsubkey(giststate, itup, v);
diff --git a/src/backend/access/gist/gistutil.c b/src/backend/access/gist/gistutil.c
index 8d3dfad27b..f7024eda12 100644
--- a/src/backend/access/gist/gistutil.c
+++ b/src/backend/access/gist/gistutil.c
@@ -160,7 +160,7 @@ gistMakeUnionItVec(GISTSTATE *giststate, IndexTuple *itvec, int len,
evec = (GistEntryVector *) palloc((len + 2) * sizeof(GISTENTRY) + GEVHDRSZ);
- for (i = 0; i < giststate->tupdesc->natts; i++)
+ for (i = 0; i < giststate->truncTupdesc->natts; i++)
{
int j;
@@ -296,7 +296,7 @@ gistDeCompressAtt(GISTSTATE *giststate, Relation r, IndexTuple tuple, Page p,
{
int i;
- for (i = 0; i < r->rd_att->natts; i++)
+ for (i = 0; i < IndexRelationGetNumberOfKeyAttributes(r); i++)
{
Datum datum;
@@ -329,7 +329,7 @@ gistgetadjusted(Relation r, IndexTuple oldtup, IndexTuple addtup, GISTSTATE *gis
gistDeCompressAtt(giststate, r, addtup, NULL,
(OffsetNumber) 0, addentries, addisnull);
- for (i = 0; i < r->rd_att->natts; i++)
+ for (i = 0; i < IndexRelationGetNumberOfKeyAttributes(r); i++)
{
gistMakeUnionKey(giststate, i,
oldentries + i, oldisnull[i],
@@ -442,7 +442,7 @@ gistchoose(Relation r, Page p, IndexTuple it, /* it has compressed entry */
zero_penalty = true;
/* Loop over index attributes. */
- for (j = 0; j < r->rd_att->natts; j++)
+ for (j = 0; j < IndexRelationGetNumberOfKeyAttributes(r); j++)
{
Datum datum;
float usize;
@@ -470,7 +470,7 @@ gistchoose(Relation r, Page p, IndexTuple it, /* it has compressed entry */
result = i;
best_penalty[j] = usize;
- if (j < r->rd_att->natts - 1)
+ if (j < IndexRelationGetNumberOfKeyAttributes(r) - 1)
best_penalty[j + 1] = -1;
/* we have new best, so reset keep-it decision */
@@ -500,7 +500,7 @@ gistchoose(Relation r, Page p, IndexTuple it, /* it has compressed entry */
* If we looped past the last column, and did not update "result",
* then this tuple is exactly as good as the prior best tuple.
*/
- if (j == r->rd_att->natts && result != i)
+ if (j == IndexRelationGetNumberOfKeyAttributes(r) && result != i)
{
if (keep_current_best == -1)
{
@@ -575,11 +575,13 @@ gistFormTuple(GISTSTATE *giststate, Relation r,
Datum compatt[INDEX_MAX_KEYS];
int i;
IndexTuple res;
+ /* currently we truncate tuples iif they are on internal pages */
+ bool isTruncated = !isleaf;
/*
* Call the compress method on each attribute.
*/
- for (i = 0; i < r->rd_att->natts; i++)
+ for (i = 0; i < IndexRelationGetNumberOfKeyAttributes(r); i++)
{
if (isnull[i])
compatt[i] = (Datum) 0;
@@ -602,7 +604,21 @@ gistFormTuple(GISTSTATE *giststate, Relation r,
}
}
- res = index_form_tuple(giststate->tupdesc, compatt, isnull);
+ if (!isTruncated)
+ {
+ /*
+ * Emplace each included attribute.
+ */
+ for (; i < r->rd_att->natts; i++)
+ {
+ if (isnull[i])
+ compatt[i] = (Datum) 0;
+ else
+ compatt[i] = attdata[i];
+ }
+ }
+
+ res = index_form_tuple(isTruncated ? giststate->truncTupdesc : giststate->tupdesc, compatt, isnull);
/*
* The offset number on tuples on internal pages is unused. For historical
@@ -644,7 +660,7 @@ gistFetchTuple(GISTSTATE *giststate, Relation r, IndexTuple tuple)
bool isnull[INDEX_MAX_KEYS];
int i;
- for (i = 0; i < r->rd_att->natts; i++)
+ for (i = 0; i < IndexRelationGetNumberOfKeyAttributes(r); i++)
{
Datum datum;
@@ -679,6 +695,14 @@ gistFetchTuple(GISTSTATE *giststate, Relation r, IndexTuple tuple)
fetchatt[i] = (Datum) 0;
}
}
+
+ /*
+ * Get each included attribute.
+ */
+ for (; i < r->rd_att->natts; i++)
+ {
+ fetchatt[i] = index_getattr(tuple, i + 1, giststate->tupdesc, &isnull[i]);
+ }
MemoryContextSwitchTo(oldcxt);
return heap_form_tuple(giststate->fetchTupdesc, fetchatt, isnull);
diff --git a/src/include/access/gist_private.h b/src/include/access/gist_private.h
index 3698942f9d..ccf06e676c 100644
--- a/src/include/access/gist_private.h
+++ b/src/include/access/gist_private.h
@@ -79,6 +79,8 @@ typedef struct GISTSTATE
MemoryContext tempCxt; /* short-term context for calling functions */
TupleDesc tupdesc; /* index's tuple descriptor */
+ TupleDesc truncTupdesc; /* truncated tuple descriptor
+ * for internal pages */
TupleDesc fetchTupdesc; /* tuple descriptor for tuples returned in an
* index-only scan */
diff --git a/src/test/regress/expected/amutils.out b/src/test/regress/expected/amutils.out
index 4570a39b05..d92a6d12c6 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 16b4be34de..464629af88 100644
--- a/src/test/regress/expected/index_including.out
+++ b/src/test/regress/expected/index_including.out
@@ -313,22 +313,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 0000000000..ed9906da66
--- /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 cc0bbf5db9..5684291ab0 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 0c10c7100c..309ccec81a 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 ef5fd882f5..3f9b9b51d7 100644
--- a/src/test/regress/sql/index_including.sql
+++ b/src/test/regress/sql/index_including.sql
@@ -173,15 +173,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 0000000000..7d5c99b2e7
--- /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;
--
2.11.0
