Skip to content

Commit 386e3d7

Browse files
feodorfeodor
committed
CREATE INDEX ... INCLUDING (column[, ...])
Now indexes (but only B-tree for now) can contain "extra" column(s) which doesn't participate in index structure, they are just stored in leaf tuples. It allows to use index only scan by using single index instead of two or more indexes. Author: Anastasia Lubennikova with minor editorializing by me Reviewers: David Rowley, Peter Geoghegan, Jeff Janes
1 parent 339025c commit 386e3d7

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

68 files changed

+1320
-255
lines changed

‎contrib/dblink/dblink.c

Lines changed: 13 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -100,7 +100,7 @@ static remoteConn *getConnectionByName(const char *name);
100100
static HTAB *createConnHash(void);
101101
static void createNewConnection(const char *name, remoteConn *rconn);
102102
static void deleteConnection(const char *name);
103-
static char **get_pkey_attnames(Relation rel, int16 *numatts);
103+
static char **get_pkey_attnames(Relation rel, int16 *indnkeyatts);
104104
static char **get_text_array_contents(ArrayType *array, int *numitems);
105105
static char *get_sql_insert(Relation rel, int *pkattnums, int pknumatts, char **src_pkattvals, char **tgt_pkattvals);
106106
static char *get_sql_delete(Relation rel, int *pkattnums, int pknumatts, char **tgt_pkattvals);
@@ -1485,7 +1485,7 @@ PG_FUNCTION_INFO_V1(dblink_get_pkey);
14851485
Datum
14861486
dblink_get_pkey(PG_FUNCTION_ARGS)
14871487
{
1488-
int16 numatts;
1488+
int16 indnkeyatts;
14891489
char **results;
14901490
FuncCallContext *funcctx;
14911491
int32 call_cntr;
@@ -1511,7 +1511,7 @@ dblink_get_pkey(PG_FUNCTION_ARGS)
15111511
rel = get_rel_from_relname(PG_GETARG_TEXT_P(0), AccessShareLock, ACL_SELECT);
15121512

15131513
/* get the array of attnums */
1514-
results = get_pkey_attnames(rel, &numatts);
1514+
results = get_pkey_attnames(rel, &indnkeyatts);
15151515

15161516
relation_close(rel, AccessShareLock);
15171517

@@ -1531,9 +1531,9 @@ dblink_get_pkey(PG_FUNCTION_ARGS)
15311531
attinmeta = TupleDescGetAttInMetadata(tupdesc);
15321532
funcctx->attinmeta = attinmeta;
15331533

1534-
if ((results != NULL) && (numatts > 0))
1534+
if ((results != NULL) && (indnkeyatts > 0))
15351535
{
1536-
funcctx->max_calls = numatts;
1536+
funcctx->max_calls = indnkeyatts;
15371537

15381538
/* got results, keep track of them */
15391539
funcctx->user_fctx = results;
@@ -2023,10 +2023,10 @@ dblink_fdw_validator(PG_FUNCTION_ARGS)
20232023
* get_pkey_attnames
20242024
*
20252025
* Get the primary key attnames for the given relation.
2026-
* Return NULL, and set numatts = 0, if no primary key exists.
2026+
* Return NULL, and set indnkeyatts = 0, if no primary key exists.
20272027
*/
20282028
static char **
2029-
get_pkey_attnames(Relation rel, int16 *numatts)
2029+
get_pkey_attnames(Relation rel, int16 *indnkeyatts)
20302030
{
20312031
Relation indexRelation;
20322032
ScanKeyData skey;
@@ -2036,8 +2036,8 @@ get_pkey_attnames(Relation rel, int16 *numatts)
20362036
char **result = NULL;
20372037
TupleDesc tupdesc;
20382038

2039-
/* initialize numatts to 0 in case no primary key exists */
2040-
*numatts = 0;
2039+
/* initialize indnkeyatts to 0 in case no primary key exists */
2040+
*indnkeyatts = 0;
20412041

20422042
tupdesc = rel->rd_att;
20432043

@@ -2058,12 +2058,12 @@ get_pkey_attnames(Relation rel, int16 *numatts)
20582058
/* we're only interested if it is the primary key */
20592059
if (index->indisprimary)
20602060
{
2061-
*numatts = index->indnatts;
2062-
if (*numatts > 0)
2061+
*indnkeyatts = index->indnkeyatts;
2062+
if (*indnkeyatts > 0)
20632063
{
2064-
result = (char **) palloc(*numatts * sizeof(char *));
2064+
result = (char **) palloc(*indnkeyatts * sizeof(char *));
20652065

2066-
for (i = 0; i < *numatts; i++)
2066+
for (i = 0; i < *indnkeyatts; i++)
20672067
result[i] = SPI_fname(tupdesc, index->indkey.values[i]);
20682068
}
20692069
break;

‎contrib/tcn/tcn.c

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -138,9 +138,9 @@ triggered_change_notification(PG_FUNCTION_ARGS)
138138
/* we're only interested if it is the primary key and valid */
139139
if (index->indisprimary && IndexIsValid(index))
140140
{
141-
int numatts = index->indnatts;
141+
int indnkeyatts = index->indnkeyatts;
142142

143-
if (numatts > 0)
143+
if (indnkeyatts > 0)
144144
{
145145
int i;
146146

@@ -150,7 +150,7 @@ triggered_change_notification(PG_FUNCTION_ARGS)
150150
appendStringInfoCharMacro(payload, ',');
151151
appendStringInfoCharMacro(payload, operation);
152152

153-
for (i = 0; i < numatts; i++)
153+
for (i = 0; i < indnkeyatts; i++)
154154
{
155155
int colno = index->indkey.values[i];
156156

‎doc/src/sgml/catalogs.sgml

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3557,6 +3557,14 @@
35573557
<literal>pg_class.relnatts</literal>)</entry>
35583558
</row>
35593559

3560+
<row>
3561+
<entry><structfield>indnkeyatts</structfield></entry>
3562+
<entry><type>int2</type></entry>
3563+
<entry></entry>
3564+
<entry>The number of key columns in the index. "Key columns" are ordinary
3565+
index columns in contrast with "included" columns.</entry>
3566+
</row>
3567+
35603568
<row>
35613569
<entry><structfield>indisunique</structfield></entry>
35623570
<entry><type>bool</type></entry>

‎doc/src/sgml/indexam.sgml

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -117,6 +117,8 @@ typedef struct IndexAmRoutine
117117
bool amclusterable;
118118
/* does AM handle predicate locks? */
119119
bool ampredlocks;
120+
/* does AM support columns included with clause INCLUDING? */
121+
bool amcaninclude;
120122
/* type of data stored in index, or InvalidOid if variable */
121123
Oid amkeytype;
122124

@@ -858,7 +860,8 @@ amrestrpos (IndexScanDesc scan);
858860
using <firstterm>unique indexes</>, which are indexes that disallow
859861
multiple entries with identical keys. An access method that supports this
860862
feature sets <structfield>amcanunique</> true.
861-
(At present, only b-tree supports it.)
863+
(At present, only B-tree supports it.) Columns which are present in the
864+
<literal>INCLUDING</> clause are not used to enforce uniqueness.
862865
</para>
863866

864867
<para>

‎doc/src/sgml/indices.sgml

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -643,7 +643,8 @@ CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);
643643
Indexes can also be used to enforce uniqueness of a column's value,
644644
or the uniqueness of the combined values of more than one column.
645645
<synopsis>
646-
CREATE UNIQUE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> (<replaceable>column</replaceable> <optional>, ...</optional>);
646+
CREATE UNIQUE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> (<replaceable>column</replaceable> <optional>, ...</optional>)
647+
<optional>INCLUDING (<replaceable>column</replaceable> <optional>, ...</optional>)</optional>;
647648
</synopsis>
648649
Currently, only B-tree indexes can be declared unique.
649650
</para>
@@ -652,7 +653,9 @@ CREATE UNIQUE INDEX <replaceable>name</replaceable> ON <replaceable>table</repla
652653
When an index is declared unique, multiple table rows with equal
653654
indexed values are not allowed. Null values are not considered
654655
equal. A multicolumn unique index will only reject cases where all
655-
indexed columns are equal in multiple rows.
656+
indexed columns are equal in multiple rows. Columns included with clause
657+
<literal>INCLUDING</literal> aren't used to enforce constraints (UNIQUE,
658+
PRIMARY KEY, etc).
656659
</para>
657660

658661
<para>

‎doc/src/sgml/ref/create_index.sgml

Lines changed: 40 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,7 @@ PostgreSQL documentation
2323
<synopsis>
2424
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> ] ON <replaceable class="parameter">table_name</replaceable> [ USING <replaceable class="parameter">method</replaceable> ]
2525
( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
26+
[ INCLUDING ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
2627
[ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] ) ]
2728
[ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
2829
[ WHERE <replaceable class="parameter">predicate</replaceable> ]
@@ -138,6 +139,35 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
138139
</listitem>
139140
</varlistentry>
140141

142+
<varlistentry>
143+
<term><literal>INCLUDING</literal></term>
144+
<listitem>
145+
<para>
146+
An optional <literal>INCLUDING</> clause allows a list of columns to be
147+
specified which will be included in the index, in the non-key portion of
148+
the index. Columns which are part of this clause cannot also exist in
149+
the key columns portion of the index, and vice versa. The
150+
<literal>INCLUDING</> columns exist solely to allow more queries to
151+
benefit from <firstterm>index-only scans</> by including certain
152+
columns in the index, the value of which would otherwise have to be
153+
obtained by reading
154+
the table's heap. Having these columns in the <literal>INCLUDING</>
155+
clause in some cases allows <productname>PostgreSQL</> to skip the heap
156+
read completely. This also allows <literal>UNIQUE</> indexes to be
157+
defined on one set of columns, which can include another set of column
158+
in the <literal>INCLUDING</> clause, on which the uniqueness is not
159+
enforced upon. It's the same with other constraints (PRIMARY KEY and
160+
EXCLUDE). This can also can be used for non-unique indexes as any
161+
columns which are not required for the searching or ordering of records
162+
can be included in the <literal>INCLUDING</> clause, which can slightly
163+
reduce the size of the index, due to storing included attributes only
164+
in leaf index pages. Currently, only the B-tree access method supports
165+
this feature. Expressions as included columns are not supported since
166+
they cannot be used in index-only scan.
167+
</para>
168+
</listitem>
169+
</varlistentry>
170+
141171
<varlistentry>
142172
<term><replaceable class="parameter">name</replaceable></term>
143173
<listitem>
@@ -599,13 +629,22 @@ Indexes:
599629
<title>Examples</title>
600630

601631
<para>
602-
To create a B-tree index on the column <literal>title</literal> in
632+
To create a unique B-tree index on the column <literal>title</literal> in
603633
the table <literal>films</literal>:
604634
<programlisting>
605635
CREATE UNIQUE INDEX title_idx ON films (title);
606636
</programlisting>
607637
</para>
608638

639+
<para>
640+
To create a unique B-tree index on the column <literal>title</literal>
641+
and included columns <literal>director</literal> and <literal>rating</literal>
642+
in the table <literal>films</literal>:
643+
<programlisting>
644+
CREATE UNIQUE INDEX title_idx ON films (title) INCLUDING (director, rating);
645+
</programlisting>
646+
</para>
647+
609648
<para>
610649
To create an index on the expression <literal>lower(title)</>,
611650
allowing efficient case-insensitive searches:

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 31 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -59,8 +59,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
5959

6060
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
6161
{ CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) [ NO INHERIT ] |
62-
UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> |
63-
PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> |
62+
UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> <optional>INCLUDING (<replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional> |
63+
PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> <optional>INCLUDING (<replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional> |
6464
EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] |
6565
FOREIGN KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> [, ... ] ) ]
6666
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] }
@@ -476,8 +476,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
476476

477477
<varlistentry>
478478
<term><literal>UNIQUE</> (column constraint)</term>
479-
<term><literal>UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )</> (table constraint)</term>
480-
479+
<term><literal>UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )
480+
<optional>INCLUDING ( <replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional></> (table constraint)</term>
481481
<listitem>
482482
<para>
483483
The <literal>UNIQUE</literal> constraint specifies that a
@@ -498,12 +498,26 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
498498
primary key constraint defined for the table. (Otherwise it
499499
would just be the same constraint listed twice.)
500500
</para>
501+
502+
<para>
503+
Adding a unique constraint will automatically create a unique btree
504+
index on the column or group of columns used in the constraint.
505+
Optional clause <literal>INCLUDING</literal> allows to add into the index
506+
a portion of columns on which the uniqueness is not enforced upon.
507+
Note, that althogh constraint is not enforced upon included columns, it still
508+
depends on them. Consequently, some operations on these columns (e.g. <literal>DROP COLUMN</literal>)
509+
can cause cascade constraint and index deletion.
510+
See paragraph about <literal>INCLUDING</literal> in
511+
<xref linkend="SQL-CREATEINDEX"> for more information.
512+
</para>
513+
501514
</listitem>
502515
</varlistentry>
503516

504517
<varlistentry>
505518
<term><literal>PRIMARY KEY</> (column constraint)</term>
506-
<term><literal>PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )</> (table constraint)</term>
519+
<term><literal>PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )
520+
<optional>INCLUDING ( <replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional></> (table constraint)</term>
507521
<listitem>
508522
<para>
509523
The <literal>PRIMARY KEY</> constraint specifies that a column or
@@ -526,6 +540,18 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
526540
about the design of the schema, since a primary key implies that other
527541
tables can rely on this set of columns as a unique identifier for rows.
528542
</para>
543+
544+
<para>
545+
Adding a <literal>PRIMARY KEY</literal> constraint will automatically create a unique btree
546+
index on the column or group of columns used in the constraint.
547+
Optional clause <literal>INCLUDING</literal> allows to add into the index
548+
a portion of columns on which the constraint is not enforced upon.
549+
Note, that althogh constraint is not enforced upon included columns, it still
550+
depends on them. Consequently, some operations on these columns (e.g. <literal>DROP COLUMN</literal>)
551+
can cause cascade constraint and index deletion.
552+
See paragraph about <literal>INCLUDING</literal> in
553+
<xref linkend="SQL-CREATEINDEX"> for more information.
554+
</para>
529555
</listitem>
530556
</varlistentry>
531557

‎src/backend/access/brin/brin.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -92,6 +92,7 @@ brinhandler(PG_FUNCTION_ARGS)
9292
amroutine->amstorage = true;
9393
amroutine->amclusterable = false;
9494
amroutine->ampredlocks = false;
95+
amroutine->amcaninclude = false;
9596
amroutine->amkeytype = InvalidOid;
9697

9798
amroutine->ambuild = brinbuild;

‎src/backend/access/common/indextuple.c

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -19,6 +19,7 @@
1919
#include "access/heapam.h"
2020
#include "access/itup.h"
2121
#include "access/tuptoaster.h"
22+
#include "utils/rel.h"
2223

2324

2425
/* ----------------------------------------------------------------
@@ -441,3 +442,33 @@ CopyIndexTuple(IndexTuple source)
441442
memcpy(result, source, size);
442443
return result;
443444
}
445+
446+
/*
447+
* Reform index tuple. Truncate nonkey (INCLUDING) attributes.
448+
*/
449+
IndexTuple
450+
index_truncate_tuple(Relation idxrel, IndexTuple olditup)
451+
{
452+
TupleDesc itupdesc = RelationGetDescr(idxrel);
453+
Datum values[INDEX_MAX_KEYS];
454+
bool isnull[INDEX_MAX_KEYS];
455+
IndexTuple newitup;
456+
int indnatts = IndexRelationGetNumberOfAttributes(idxrel);
457+
int indnkeyatts = IndexRelationGetNumberOfKeyAttributes(idxrel);
458+
459+
Assert(indnatts <= INDEX_MAX_KEYS);
460+
Assert(indnkeyatts > 0);
461+
Assert(indnkeyatts < indnatts);
462+
463+
index_deform_tuple(olditup, itupdesc, values, isnull);
464+
465+
/* form new tuple that will contain only key attributes */
466+
itupdesc->natts = indnkeyatts;
467+
newitup = index_form_tuple(itupdesc, values, isnull);
468+
newitup->t_tid = olditup->t_tid;
469+
470+
itupdesc->natts = indnatts;
471+
472+
Assert(IndexTupleSize(newitup) <= IndexTupleSize(olditup));
473+
return newitup;
474+
}

‎src/backend/access/gin/ginutil.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -47,6 +47,7 @@ ginhandler(PG_FUNCTION_ARGS)
4747
amroutine->amstorage = true;
4848
amroutine->amclusterable = false;
4949
amroutine->ampredlocks = false;
50+
amroutine->amcaninclude = false;
5051
amroutine->amkeytype = InvalidOid;
5152

5253
amroutine->ambuild = ginbuild;

‎src/backend/access/gist/gist.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -69,6 +69,7 @@ gisthandler(PG_FUNCTION_ARGS)
6969
amroutine->amstorage = true;
7070
amroutine->amclusterable = true;
7171
amroutine->ampredlocks = false;
72+
amroutine->amcaninclude = false;
7273
amroutine->amkeytype = InvalidOid;
7374

7475
amroutine->ambuild = gistbuild;

‎src/backend/access/hash/hash.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -64,6 +64,7 @@ hashhandler(PG_FUNCTION_ARGS)
6464
amroutine->amstorage = false;
6565
amroutine->amclusterable = false;
6666
amroutine->ampredlocks = false;
67+
amroutine->amcaninclude = false;
6768
amroutine->amkeytype = INT4OID;
6869

6970
amroutine->ambuild = hashbuild;

0 commit comments

Comments
 (0)