Skip to content

Commit 91b4e25

Browse files
vbwagnervbwagner
committed
Applied covering indices patch by Anastasia Lubennikova from th pgsql-hackers message http://www.postgresql.org/message-id/f90aa60a-b67f-95b5-d9f5-f5d8ced178c6@postgrespro.ru/ 
1 parent 3c19892 commit 91b4e25

Some content is hidden

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

66 files changed

+1325
-254
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);
@@ -1483,7 +1483,7 @@ PG_FUNCTION_INFO_V1(dblink_get_pkey);
14831483
Datum
14841484
dblink_get_pkey(PG_FUNCTION_ARGS)
14851485
{
1486-
int16 numatts;
1486+
int16 indnkeyatts;
14871487
char **results;
14881488
FuncCallContext *funcctx;
14891489
int32 call_cntr;
@@ -1509,7 +1509,7 @@ dblink_get_pkey(PG_FUNCTION_ARGS)
15091509
rel = get_rel_from_relname(PG_GETARG_TEXT_P(0), AccessShareLock, ACL_SELECT);
15101510

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

15141514
relation_close(rel, AccessShareLock);
15151515

@@ -1529,9 +1529,9 @@ dblink_get_pkey(PG_FUNCTION_ARGS)
15291529
attinmeta = TupleDescGetAttInMetadata(tupdesc);
15301530
funcctx->attinmeta = attinmeta;
15311531

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

15361536
/* got results, keep track of them */
15371537
funcctx->user_fctx = results;
@@ -2021,10 +2021,10 @@ dblink_fdw_validator(PG_FUNCTION_ARGS)
20212021
* get_pkey_attnames
20222022
*
20232023
* Get the primary key attnames for the given relation.
2024-
* Return NULL, and set numatts = 0, if no primary key exists.
2024+
* Return NULL, and set indnkeyatts = 0, if no primary key exists.
20252025
*/
20262026
static char **
2027-
get_pkey_attnames(Relation rel, int16 *numatts)
2027+
get_pkey_attnames(Relation rel, int16 *indnkeyatts)
20282028
{
20292029
Relation indexRelation;
20302030
ScanKeyData skey;
@@ -2034,8 +2034,8 @@ get_pkey_attnames(Relation rel, int16 *numatts)
20342034
char **result = NULL;
20352035
TupleDesc tupdesc;
20362036

2037-
/* initialize numatts to 0 in case no primary key exists */
2038-
*numatts = 0;
2037+
/* initialize indnkeyatts to 0 in case no primary key exists */
2038+
*indnkeyatts = 0;
20392039

20402040
tupdesc = rel->rd_att;
20412041

@@ -2056,12 +2056,12 @@ get_pkey_attnames(Relation rel, int16 *numatts)
20562056
/* we're only interested if it is the primary key */
20572057
if (index->indisprimary)
20582058
{
2059-
*numatts = index->indnatts;
2060-
if (*numatts > 0)
2059+
*indnkeyatts = index->indnkeyatts;
2060+
if (*indnkeyatts > 0)
20612061
{
2062-
result = (char **) palloc(*numatts * sizeof(char *));
2062+
result = (char **) palloc(*indnkeyatts * sizeof(char *));
20632063

2064-
for (i = 0; i < *numatts; i++)
2064+
for (i = 0; i < *indnkeyatts; i++)
20652065
result[i] = SPI_fname(tupdesc, index->indkey.values[i]);
20662066
}
20672067
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
@@ -3564,6 +3564,14 @@
35643564
<literal>pg_class.relnatts</literal>)</entry>
35653565
</row>
35663566

3567+
<row>
3568+
<entry><structfield>indnkeyatts</structfield></entry>
3569+
<entry><type>int2</type></entry>
3570+
<entry></entry>
3571+
<entry>The number of key columns in the index. "Key columns" are ordinary
3572+
index columns in contrast with "included" columns.</entry>
3573+
</row>
3574+
35673575
<row>
35683576
<entry><structfield>indisunique</structfield></entry>
35693577
<entry><type>bool</type></entry>

‎doc/src/sgml/indexam.sgml

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -110,6 +110,8 @@ typedef struct IndexAmRoutine
110110
bool amclusterable;
111111
/* does AM handle predicate locks? */
112112
bool ampredlocks;
113+
/* does AM support columns included with clause INCLUDING? */
114+
bool amcaninclude;
113115
/* type of data stored in index, or InvalidOid if variable */
114116
Oid amkeytype;
115117

@@ -903,7 +905,8 @@ amrestrpos (IndexScanDesc scan);
903905
using <firstterm>unique indexes</>, which are indexes that disallow
904906
multiple entries with identical keys. An access method that supports this
905907
feature sets <structfield>amcanunique</> true.
906-
(At present, only b-tree supports it.)
908+
(At present, only b-tree supports it.) Columns which are present in the
909+
<literal>INCLUDING</> clause are not used to enforce uniqueness.
907910
</para>
908911

909912
<para>

‎doc/src/sgml/indices.sgml

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -651,7 +651,8 @@ CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);
651651
Indexes can also be used to enforce uniqueness of a column's value,
652652
or the uniqueness of the combined values of more than one column.
653653
<synopsis>
654-
CREATE UNIQUE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> (<replaceable>column</replaceable> <optional>, ...</optional>);
654+
CREATE UNIQUE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> (<replaceable>column</replaceable> <optional>, ...</optional>)
655+
<optional>INCLUDING (<replaceable>column</replaceable> <optional>, ...</optional>)</optional>;
655656
</synopsis>
656657
Currently, only B-tree indexes can be declared unique.
657658
</para>
@@ -660,7 +661,9 @@ CREATE UNIQUE INDEX <replaceable>name</replaceable> ON <replaceable>table</repla
660661
When an index is declared unique, multiple table rows with equal
661662
indexed values are not allowed. Null values are not considered
662663
equal. A multicolumn unique index will only reject cases where all
663-
indexed columns are equal in multiple rows.
664+
indexed columns are equal in multiple rows. Columns included with clause
665+
<literal>INCLUDING</literal> aren't used to enforce constraints (UNIQUE,
666+
PRIMARY KEY, etc).
664667
</para>
665668

666669
<para>

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

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

592621
<para>
593-
To create a B-tree index on the column <literal>title</literal> in
622+
To create a unique B-tree index on the column <literal>title</literal> in
594623
the table <literal>films</literal>:
595624
<programlisting>
596625
CREATE UNIQUE INDEX title_idx ON films (title);
597626
</programlisting>
598627
</para>
599628

629+
<para>
630+
To create a unique B-tree index on the column <literal>title</literal>
631+
and included columns <literal>director</literal> and <literal>rating</literal>
632+
in the table <literal>films</literal>:
633+
<programlisting>
634+
CREATE UNIQUE INDEX title_idx ON films (title) INCLUDING (director, rating);
635+
</programlisting>
636+
</para>
637+
600638
<para>
601639
To create an index on the expression <literal>lower(title)</>,
602640
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> ] }
@@ -485,8 +485,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
485485

486486
<varlistentry>
487487
<term><literal>UNIQUE</> (column constraint)</term>
488-
<term><literal>UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )</> (table constraint)</term>
489-
488+
<term><literal>UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )
489+
<optional>INCLUDING ( <replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional></> (table constraint)</term>
490490
<listitem>
491491
<para>
492492
The <literal>UNIQUE</literal> constraint specifies that a
@@ -507,12 +507,26 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
507507
primary key constraint defined for the table. (Otherwise it
508508
would just be the same constraint listed twice.)
509509
</para>
510+
511+
<para>
512+
Adding a unique constraint will automatically create a unique btree
513+
index on the column or group of columns used in the constraint.
514+
Optional clause <literal>INCLUDING</literal> allows to add into the index
515+
a portion of columns on which the uniqueness is not enforced upon.
516+
Note, that althogh constraint is not enforced upon included columns, it still
517+
depends on them. Consequently, some operations on these columns (e.g. <literal>DROP COLUMN</literal>)
518+
can cause cascade constraint and index deletion.
519+
See paragraph about <literal>INCLUDING</literal> in
520+
<xref linkend="SQL-CREATEINDEX"> for more information.
521+
</para>
522+
510523
</listitem>
511524
</varlistentry>
512525

513526
<varlistentry>
514527
<term><literal>PRIMARY KEY</> (column constraint)</term>
515-
<term><literal>PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )</> (table constraint)</term>
528+
<term><literal>PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )
529+
<optional>INCLUDING ( <replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional></> (table constraint)</term>
516530
<listitem>
517531
<para>
518532
The <literal>PRIMARY KEY</> constraint specifies that a column or
@@ -535,6 +549,18 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
535549
about the design of the schema, since a primary key implies that other
536550
tables can rely on this set of columns as a unique identifier for rows.
537551
</para>
552+
553+
<para>
554+
Adding a <literal>PRIMARY KEY</literal> constraint will automatically create a unique btree
555+
index on the column or group of columns used in the constraint.
556+
Optional clause <literal>INCLUDING</literal> allows to add into the index
557+
a portion of columns on which the constraint is not enforced upon.
558+
Note, that althogh constraint is not enforced upon included columns, it still
559+
depends on them. Consequently, some operations on these columns (e.g. <literal>DROP COLUMN</literal>)
560+
can cause cascade constraint and index deletion.
561+
See paragraph about <literal>INCLUDING</literal> in
562+
<xref linkend="SQL-CREATEINDEX"> for more information.
563+
</para>
538564
</listitem>
539565
</varlistentry>
540566

‎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
@@ -65,6 +65,7 @@ hashhandler(PG_FUNCTION_ARGS)
6565
amroutine->amstorage = false;
6666
amroutine->amclusterable = false;
6767
amroutine->ampredlocks = false;
68+
amroutine->amcaninclude = false;
6869
amroutine->amkeytype = INT4OID;
6970

7071
amroutine->ambuild = hashbuild;

0 commit comments

Comments
 (0)