Skip to content

Commit 5cddede

Browse files
alubennikovavbwagner
alubennikova
authored and
vbwagner
committed
Covering indexes rebased from master to PGPROP9_5.
1 parent 7580894 commit 5cddede

File tree

31 files changed

+324
-77
lines changed

31 files changed

+324
-77
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -644,6 +644,13 @@
644644
<entry>Does an index of this type manage fine-grained predicate locks?</entry>
645645
</row>
646646

647+
<row>
648+
<entry><structfield>amcanincluding</structfield></entry>
649+
<entry><type>bool</type></entry>
650+
<entry></entry>
651+
<entry>Does the access method support included columns?</entry>
652+
</row>
653+
647654
<row>
648655
<entry><structfield>amkeytype</structfield></entry>
649656
<entry><type>oid</type></entry>
@@ -3714,6 +3721,14 @@
37143721
<literal>pg_class.relnatts</literal>)</entry>
37153722
</row>
37163723

3724+
<row>
3725+
<entry><structfield>indnkeyatts</structfield></entry>
3726+
<entry><type>int2</type></entry>
3727+
<entry></entry>
3728+
<entry>The number of key columns in the index. "Key columns" are ordinary
3729+
index columns in contrast with "included" columns.</entry>
3730+
</row>
3731+
37173732
<row>
37183733
<entry><structfield>indisunique</structfield></entry>
37193734
<entry><type>bool</type></entry>

‎doc/src/sgml/indexam.sgml

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -765,9 +765,11 @@ amrestrpos (IndexScanDesc scan);
765765
<para>
766766
<productname>&productname;</productname> enforces SQL uniqueness constraints
767767
using <firstterm>unique indexes</>, which are indexes that disallow
768-
multiple entries with identical keys. An access method that supports this
768+
multiple entries with identical keys. An access method that supports this
769769
feature sets <structname>pg_am</>.<structfield>amcanunique</> true.
770-
(At present, only b-tree supports it.)
770+
Columns included with clause INCLUDING aren't used to enforce uniqueness.
771+
An access method that supports this feature sets <structname>pg_am</>.<structfield>amcanincluding</> true.
772+
(At present, only b-tree supports them.)
771773
</para>
772774

773775
<para>

‎doc/src/sgml/indices.sgml

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -633,7 +633,8 @@ CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);
633633
Indexes can also be used to enforce uniqueness of a column's value,
634634
or the uniqueness of the combined values of more than one column.
635635
<synopsis>
636-
CREATE UNIQUE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> (<replaceable>column</replaceable> <optional>, ...</optional>);
636+
CREATE UNIQUE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> (<replaceable>column</replaceable> <optional>, ...</optional>)
637+
<optional>INCLUDING (<replaceable>column</replaceable> <optional>, ...</optional>)</optional>;
637638
</synopsis>
638639
Currently, only B-tree indexes can be declared unique.
639640
</para>
@@ -642,7 +643,8 @@ CREATE UNIQUE INDEX <replaceable>name</replaceable> ON <replaceable>table</repla
642643
When an index is declared unique, multiple table rows with equal
643644
indexed values are not allowed. Null values are not considered
644645
equal. A multicolumn unique index will only reject cases where all
645-
indexed columns are equal in multiple rows.
646+
indexed columns are equal in multiple rows. Columns included with clause
647+
INCLUDING aren't used to enforce constraints (UNIQUE, PRIMARY KEY, etc).
646648
</para>
647649

648650
<para>

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

Lines changed: 31 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,7 @@ doc/src/sgml/ref/create_index.sgml
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> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
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,26 @@ 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+
This clause specifies additional columns to be appended to the set of index columns.
147+
Included columns don't support any constraints <literal>(UNIQUE, PRMARY KEY, EXCLUSION CONSTRAINT)</>.
148+
These columns can improve the performance of some queries through using advantages of index-only scan
149+
(Or so called <firstterm>covering</firstterm> indexes. Covering index is the index that
150+
covers all columns required in the query and prevents a table access).
151+
Besides that, included attributes are not stored in index inner pages.
152+
It allows to decrease index size and furthermore it provides a way to extend included
153+
columns to store atttributes without suitable opclass (not implemented yet).
154+
This clause could be applied to both unique and nonunique indexes.
155+
It's possible to have non-unique covering index, which behaves as a regular
156+
multi-column index with a bit smaller index-size.
157+
Currently, only the B-tree access method supports this feature.
158+
</para>
159+
</listitem>
160+
</varlistentry>
161+
141162
<varlistentry>
142163
<term><replaceable class="parameter">name</replaceable></term>
143164
<listitem>
@@ -596,13 +617,22 @@ Indexes:
596617
<title>Examples</title>
597618

598619
<para>
599-
To create a B-tree index on the column <literal>title</literal> in
620+
To create an unique B-tree index on the column <literal>title</literal> in
600621
the table <literal>films</literal>:
601622
<programlisting>
602623
CREATE UNIQUE INDEX title_idx ON films (title);
603624
</programlisting>
604625
</para>
605626

627+
<para>
628+
To create an unique B-tree index on the column <literal>title</literal>
629+
and included columns <literal>director</literal> and <literal>rating</literal>
630+
in the table <literal>films</literal>:
631+
<programlisting>
632+
CREATE UNIQUE INDEX title_idx ON films (title) INCLUDING (director, rating);
633+
</programlisting>
634+
</para>
635+
606636
<para>
607637
To create an index on the expression <literal>lower(title)</>,
608638
allowing efficient case-insensitive searches:

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

Lines changed: 28 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,30 @@ CopyIndexTuple(IndexTuple source)
441442
memcpy(result, source, size);
442443
return result;
443444
}
445+
446+
/*
447+
* Reform index tuple. Truncate nonkey (INCLUDED) attributes.
448+
*/
449+
IndexTuple
450+
index_reform_tuple(Relation idxrel, IndexTuple olditup, int natts, int nkeyatts)
451+
{
452+
TupleDesc itupdesc = RelationGetDescr(idxrel);
453+
Datum values[INDEX_MAX_KEYS];
454+
bool isnull[INDEX_MAX_KEYS];
455+
IndexTuple newitup;
456+
457+
Assert(natts <= INDEX_MAX_KEYS);
458+
Assert(nkeyatts > 0);
459+
Assert(nkeyatts <= natts);
460+
461+
index_deform_tuple(olditup, itupdesc, values, isnull);
462+
463+
/* form new tuple that will contain only key attributes */
464+
itupdesc->natts = nkeyatts;
465+
newitup = index_form_tuple(itupdesc, values, isnull);
466+
newitup->t_tid = olditup->t_tid;
467+
468+
itupdesc->natts = natts;
469+
470+
return newitup;
471+
}

‎src/backend/access/nbtree/nbtinsert.c

Lines changed: 24 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -108,18 +108,23 @@ _bt_doinsert(Relation rel, IndexTuple itup,
108108
IndexUniqueCheck checkUnique, Relation heapRel)
109109
{
110110
bool is_unique = false;
111-
int natts = rel->rd_rel->relnatts;
111+
int nkeyatts = rel->rd_rel->relnatts;
112112
ScanKey itup_scankey;
113113
BTStack stack;
114114
Buffer buf;
115115
OffsetNumber offset;
116116

117+
Assert (rel->rd_index != NULL);
118+
Assert(rel->rd_index->indnatts != 0);
119+
Assert(rel->rd_index->indnkeyatts != 0);
120+
nkeyatts = IndexRelationGetNumberOfKeyAttributes(rel);
121+
117122
/* we need an insertion scan key to do our search, so build one */
118123
itup_scankey = _bt_mkscankey(rel, itup);
119124

120125
top:
121126
/* find the first page containing this key */
122-
stack = _bt_search(rel, natts, itup_scankey, false, &buf, BT_WRITE);
127+
stack = _bt_search(rel, nkeyatts, itup_scankey, false, &buf, BT_WRITE);
123128

124129
offset = InvalidOffsetNumber;
125130

@@ -134,7 +139,7 @@ _bt_doinsert(Relation rel, IndexTuple itup,
134139
* move right in the tree. See Lehman and Yao for an excruciatingly
135140
* precise description.
136141
*/
137-
buf = _bt_moveright(rel, buf, natts, itup_scankey, false,
142+
buf = _bt_moveright(rel, buf, nkeyatts, itup_scankey, false,
138143
true, stack, BT_WRITE);
139144

140145
/*
@@ -163,7 +168,7 @@ _bt_doinsert(Relation rel, IndexTuple itup,
163168
TransactionId xwait;
164169
uint32 speculativeToken;
165170

166-
offset = _bt_binsrch(rel, buf, natts, itup_scankey, false);
171+
offset = _bt_binsrch(rel, buf, nkeyatts, itup_scankey, false);
167172
xwait = _bt_check_unique(rel, itup, heapRel, buf, offset, itup_scankey,
168173
checkUnique, &is_unique, &speculativeToken);
169174

@@ -199,7 +204,7 @@ _bt_doinsert(Relation rel, IndexTuple itup,
199204
*/
200205
CheckForSerializableConflictIn(rel, NULL, buf);
201206
/* do the insertion */
202-
_bt_findinsertloc(rel, &buf, &offset, natts, itup_scankey, itup,
207+
_bt_findinsertloc(rel, &buf, &offset, nkeyatts, itup_scankey, itup,
203208
stack, heapRel);
204209
_bt_insertonpg(rel, buf, InvalidBuffer, stack, itup, offset, false);
205210
}
@@ -242,7 +247,12 @@ _bt_check_unique(Relation rel, IndexTuple itup, Relation heapRel,
242247
uint32 *speculativeToken)
243248
{
244249
TupleDesc itupdesc = RelationGetDescr(rel);
245-
int natts = rel->rd_rel->relnatts;
250+
int nkeyatts = rel->rd_index->indnkeyatts;
251+
252+
Assert (rel->rd_index != NULL);
253+
Assert(rel->rd_index->indnatts != 0);
254+
Assert(rel->rd_index->indnkeyatts != 0);
255+
246256
SnapshotData SnapshotDirty;
247257
OffsetNumber maxoff;
248258
Page page;
@@ -301,7 +311,7 @@ _bt_check_unique(Relation rel, IndexTuple itup, Relation heapRel,
301311
* in real comparison, but only for ordering/finding items on
302312
* pages. - vadim 03/24/97
303313
*/
304-
if (!_bt_isequal(itupdesc, page, offset, natts, itup_scankey))
314+
if (!_bt_isequal(itupdesc, page, offset, nkeyatts, itup_scankey))
305315
break; /* we're past all the equal tuples */
306316

307317
/* okay, we gotta fetch the heap tuple ... */
@@ -457,7 +467,7 @@ _bt_check_unique(Relation rel, IndexTuple itup, Relation heapRel,
457467
if (P_RIGHTMOST(opaque))
458468
break;
459469
if (!_bt_isequal(itupdesc, page, P_HIKEY,
460-
natts, itup_scankey))
470+
nkeyatts, itup_scankey))
461471
break;
462472
/* Advance to next non-dead page --- there must be one */
463473
for (;;)
@@ -745,6 +755,11 @@ _bt_insertonpg(Relation rel,
745755
elog(ERROR, "cannot insert to incompletely split page %u",
746756
BufferGetBlockNumber(buf));
747757

758+
/* Truncate nonkey attributes when inserting on nonleaf pages. */
759+
if (rel->rd_index->indnatts != rel->rd_index->indnkeyatts)
760+
if (!P_ISLEAF(lpageop))
761+
itup = index_reform_tuple(rel, itup, rel->rd_index->indnatts, rel->rd_index->indnkeyatts);
762+
748763
itemsz = IndexTupleDSize(*itup);
749764
itemsz = MAXALIGN(itemsz); /* be safe, PageAddItem will do this but we
750765
* need to be consistent */
@@ -1962,6 +1977,7 @@ _bt_newroot(Relation rel, Buffer lbuf, Buffer rbuf)
19621977
right_item_sz = ItemIdGetLength(itemid);
19631978
item = (IndexTuple) PageGetItem(lpage, itemid);
19641979
right_item = CopyIndexTuple(item);
1980+
right_item = index_reform_tuple(rel, right_item, rel->rd_index->indnatts, rel->rd_index->indnkeyatts);
19651981
ItemPointerSet(&(right_item->t_tid), rbkno, P_HIKEY);
19661982

19671983
/* NO EREPORT(ERROR) from here till newroot op is logged */

‎src/backend/access/nbtree/nbtpage.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1254,7 +1254,7 @@ _bt_pagedel(Relation rel, Buffer buf)
12541254
/* we need an insertion scan key for the search, so build one */
12551255
itup_scankey = _bt_mkscankey(rel, targetkey);
12561256
/* find the leftmost leaf page containing this key */
1257-
stack = _bt_search(rel, rel->rd_rel->relnatts, itup_scankey,
1257+
stack = _bt_search(rel, IndexRelationGetNumberOfKeyAttributes(rel), itup_scankey,
12581258
false, &lbuf, BT_READ);
12591259
/* don't need a pin on the page */
12601260
_bt_relbuf(rel, lbuf);

‎src/backend/access/nbtree/nbtsort.c

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -593,6 +593,19 @@ _bt_buildadd(BTWriteState *wstate, BTPageState *state, IndexTuple itup)
593593
state->btps_minkey = CopyIndexTuple(itup);
594594
}
595595

596+
/* Truncate nonkey attributes when inserting on nonleaf pages */
597+
if (wstate->index->rd_index->indnatts != wstate->index->rd_index->indnkeyatts)
598+
{
599+
BTPageOpaque pageop = (BTPageOpaque) PageGetSpecialPointer(npage);
600+
601+
if (!P_ISLEAF(pageop))
602+
{
603+
itup = index_reform_tuple(wstate->index, itup, wstate->index->rd_index->indnatts, wstate->index->rd_index->indnkeyatts);
604+
itupsz = IndexTupleDSize(*itup);
605+
itupsz = MAXALIGN(itupsz);
606+
}
607+
}
608+
596609
/*
597610
* Add the new item into the current page.
598611
*/

‎src/backend/access/nbtree/nbtutils.c

Lines changed: 14 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -63,17 +63,24 @@ _bt_mkscankey(Relation rel, IndexTuple itup)
6363
{
6464
ScanKey skey;
6565
TupleDesc itupdesc;
66-
int natts;
67-
int16 *indoption;
66+
int nkeyatts = rel->rd_rel->relnatts;
67+
int16 *indoption = rel->rd_indoption;
6868
int i;
69-
7069
itupdesc = RelationGetDescr(rel);
71-
natts = RelationGetNumberOfAttributes(rel);
72-
indoption = rel->rd_indoption;
7370

74-
skey = (ScanKey) palloc(natts * sizeof(ScanKeyData));
71+
Assert(rel->rd_index != NULL);
72+
Assert(rel->rd_index->indnkeyatts != 0);
73+
Assert(rel->rd_index->indnkeyatts <= rel->rd_index->indnatts);
7574

76-
for (i = 0; i < natts; i++)
75+
nkeyatts = rel->rd_index->indnkeyatts;
76+
77+
/*
78+
* We'll execute search using ScanKey constructed on key columns.
79+
* Non key (included) columns must be omitted.
80+
*/
81+
skey = (ScanKey) palloc(nkeyatts * sizeof(ScanKeyData));
82+
83+
for (i = 0; i < nkeyatts; i++)
7784
{
7885
FmgrInfo *procinfo;
7986
Datum arg;

‎src/backend/bootstrap/bootparse.y

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -293,6 +293,7 @@ Boot_DeclareIndexStmt:
293293
stmt->accessMethod = $8;
294294
stmt->tableSpace = NULL;
295295
stmt->indexParams = $10;
296+
stmt->indexIncludingParams = NIL;
296297
stmt->options = NIL;
297298
stmt->whereClause = NULL;
298299
stmt->excludeOpNames = NIL;
@@ -336,6 +337,7 @@ Boot_DeclareUniqueIndexStmt:
336337
stmt->accessMethod = $9;
337338
stmt->tableSpace = NULL;
338339
stmt->indexParams = $11;
340+
stmt->indexIncludingParams = NIL;
339341
stmt->options = NIL;
340342
stmt->whereClause = NULL;
341343
stmt->excludeOpNames = NIL;

‎src/backend/catalog/index.c

Lines changed: 9 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -211,7 +211,7 @@ index_check_primary_key(Relation heapRel,
211211
* null, otherwise attempt to ALTER TABLE .. SET NOT NULL
212212
*/
213213
cmds = NIL;
214-
for (i = 0; i < indexInfo->ii_NumIndexAttrs; i++)
214+
for (i = 0; i < indexInfo->ii_NumIndexKeyAttrs; i++)
215215
{
216216
AttrNumber attnum = indexInfo->ii_KeyAttrNumbers[i];
217217
HeapTuple atttuple;
@@ -606,6 +606,7 @@ UpdateIndexRelation(Oid indexoid,
606606
values[Anum_pg_index_indexrelid - 1] = ObjectIdGetDatum(indexoid);
607607
values[Anum_pg_index_indrelid - 1] = ObjectIdGetDatum(heapoid);
608608
values[Anum_pg_index_indnatts - 1] = Int16GetDatum(indexInfo->ii_NumIndexAttrs);
609+
values[Anum_pg_index_indnkeyatts - 1] = Int16GetDatum(indexInfo->ii_NumIndexKeyAttrs);
609610
values[Anum_pg_index_indisunique - 1] = BoolGetDatum(indexInfo->ii_Unique);
610611
values[Anum_pg_index_indisprimary - 1] = BoolGetDatum(primary);
611612
values[Anum_pg_index_indisexclusion - 1] = BoolGetDatum(isexclusion);
@@ -1069,6 +1070,8 @@ index_create(Relation heapRelation,
10691070
else
10701071
Assert(indexRelation->rd_indexcxt != NULL);
10711072

1073+
indexRelation->rd_index->indnkeyatts = indexInfo->ii_NumIndexKeyAttrs;
1074+
10721075
/*
10731076
* If this is bootstrap (initdb) time, then we don't actually fill in the
10741077
* index yet. We'll be creating more indexes and classes later, so we
@@ -1189,7 +1192,7 @@ index_constraint_create(Relation heapRelation,
11891192
true,
11901193
RelationGetRelid(heapRelation),
11911194
indexInfo->ii_KeyAttrNumbers,
1192-
indexInfo->ii_NumIndexAttrs,
1195+
indexInfo->ii_NumIndexKeyAttrs,
11931196
InvalidOid, /* no domain */
11941197
indexRelationId, /* index OID */
11951198
InvalidOid, /* no foreign key */
@@ -1637,6 +1640,10 @@ BuildIndexInfo(Relation index)
16371640
elog(ERROR, "invalid indnatts %d for index %u",
16381641
numKeys, RelationGetRelid(index));
16391642
ii->ii_NumIndexAttrs = numKeys;
1643+
ii->ii_NumIndexKeyAttrs = indexStruct->indnkeyatts;
1644+
Assert(ii->ii_NumIndexKeyAttrs != 0);
1645+
Assert(ii->ii_NumIndexKeyAttrs <= ii->ii_NumIndexAttrs);
1646+
16401647
for (i = 0; i < numKeys; i++)
16411648
ii->ii_KeyAttrNumbers[i] = indexStruct->indkey.values[i];
16421649

‎src/backend/catalog/indexing.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -119,6 +119,7 @@ CatalogIndexInsert(CatalogIndexState indstate, HeapTuple heapTuple)
119119
Assert(indexInfo->ii_Predicate == NIL);
120120
Assert(indexInfo->ii_ExclusionOps == NULL);
121121
Assert(relationDescs[i]->rd_index->indimmediate);
122+
Assert(indexInfo->ii_NumIndexKeyAttrs != 0);
122123

123124
/*
124125
* FormIndexDatum fills in its values and isnull parameters with the

0 commit comments

Comments
 (0)