Skip to content

Commit 747aa24

Browse files
alubennikovavbwagner
alubennikova
authored and
vbwagner
committed
fixed according to review
1 parent 5cddede commit 747aa24

File tree

15 files changed

+69
-45
lines changed

15 files changed

+69
-45
lines changed

‎doc/src/sgml/indexam.sgml

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -768,7 +768,6 @@ amrestrpos (IndexScanDesc scan);
768768
multiple entries with identical keys. An access method that supports this
769769
feature sets <structname>pg_am</>.<structfield>amcanunique</> true.
770770
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.
772771
(At present, only b-tree supports them.)
773772
</para>
774773

‎doc/src/sgml/indices.sgml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -644,7 +644,7 @@ CREATE UNIQUE INDEX <replaceable>name</replaceable> ON <replaceable>table</repla
644644
indexed values are not allowed. Null values are not considered
645645
equal. A multicolumn unique index will only reject cases where all
646646
indexed columns are equal in multiple rows. Columns included with clause
647-
INCLUDING aren't used to enforce constraints (UNIQUE, PRIMARY KEY, etc).
647+
<literal>INCLUDING</literal> aren't used to enforce constraints (UNIQUE, PRIMARY KEY, etc).
648648
</para>
649649

650650
<para>

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

Lines changed: 19 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -143,17 +143,23 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
143143
<term><literal>INCLUDING</literal></term>
144144
<listitem>
145145
<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.
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 defined 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.
157163
Currently, only the B-tree access method supports this feature.
158164
</para>
159165
</listitem>
@@ -617,15 +623,15 @@ Indexes:
617623
<title>Examples</title>
618624

619625
<para>
620-
To create an unique B-tree index on the column <literal>title</literal> in
626+
To create a unique B-tree index on the column <literal>title</literal> in
621627
the table <literal>films</literal>:
622628
<programlisting>
623629
CREATE UNIQUE INDEX title_idx ON films (title);
624630
</programlisting>
625631
</para>
626632

627633
<para>
628-
To create an unique B-tree index on the column <literal>title</literal>
634+
To create a unique B-tree index on the column <literal>title</literal>
629635
and included columns <literal>director</literal> and <literal>rating</literal>
630636
in the table <literal>films</literal>:
631637
<programlisting>

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

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -444,7 +444,7 @@ CopyIndexTuple(IndexTuple source)
444444
}
445445

446446
/*
447-
* Reform index tuple. Truncate nonkey (INCLUDED) attributes.
447+
* Reform index tuple. Truncate nonkey (INCLUDING) attributes.
448448
*/
449449
IndexTuple
450450
index_reform_tuple(Relation idxrel, IndexTuple olditup, int natts, int nkeyatts)

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

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

117-
Assert (rel->rd_index != NULL);
118117
Assert(rel->rd_index->indnatts != 0);
119118
Assert(rel->rd_index->indnkeyatts != 0);
120119
nkeyatts = IndexRelationGetNumberOfKeyAttributes(rel);
120+
is_unique = false;
121121

122122
/* we need an insertion scan key to do our search, so build one */
123123
itup_scankey = _bt_mkscankey(rel, itup);
@@ -248,18 +248,16 @@ _bt_check_unique(Relation rel, IndexTuple itup, Relation heapRel,
248248
{
249249
TupleDesc itupdesc = RelationGetDescr(rel);
250250
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-
256251
SnapshotData SnapshotDirty;
257252
OffsetNumber maxoff;
258253
Page page;
259254
BTPageOpaque opaque;
260255
Buffer nbuf = InvalidBuffer;
261256
bool found = false;
262257

258+
Assert(rel->rd_index->indnatts != 0);
259+
Assert(rel->rd_index->indnkeyatts != 0);
260+
263261
/* Assume unique until we find a duplicate */
264262
*is_unique = true;
265263

@@ -756,9 +754,12 @@ _bt_insertonpg(Relation rel,
756754
BufferGetBlockNumber(buf));
757755

758756
/* 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);
757+
if (rel->rd_index->indnatts != rel->rd_index->indnkeyatts
758+
&& !P_ISLEAF(lpageop))
759+
{
760+
itup = index_reform_tuple(rel, itup,
761+
rel->rd_index->indnatts, rel->rd_index->indnkeyatts);
762+
}
762763

763764
itemsz = IndexTupleDSize(*itup);
764765
itemsz = MAXALIGN(itemsz); /* be safe, PageAddItem will do this but we

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

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1254,8 +1254,9 @@ _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, IndexRelationGetNumberOfKeyAttributes(rel), itup_scankey,
1258-
false, &lbuf, BT_READ);
1257+
stack = _bt_search(rel,
1258+
IndexRelationGetNumberOfKeyAttributes(rel),
1259+
itup_scankey, false, &lbuf, BT_READ);
12591260
/* don't need a pin on the page */
12601261
_bt_relbuf(rel, lbuf);
12611262

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

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -594,13 +594,16 @@ _bt_buildadd(BTWriteState *wstate, BTPageState *state, IndexTuple itup)
594594
}
595595

596596
/* Truncate nonkey attributes when inserting on nonleaf pages */
597-
if (wstate->index->rd_index->indnatts != wstate->index->rd_index->indnkeyatts)
597+
if (wstate->index->rd_index->indnatts
598+
!= wstate->index->rd_index->indnkeyatts)
598599
{
599600
BTPageOpaque pageop = (BTPageOpaque) PageGetSpecialPointer(npage);
600601

601602
if (!P_ISLEAF(pageop))
602603
{
603-
itup = index_reform_tuple(wstate->index, itup, wstate->index->rd_index->indnatts, wstate->index->rd_index->indnkeyatts);
604+
itup = index_reform_tuple(wstate->index,
605+
itup, wstate->index->rd_index->indnatts,
606+
wstate->index->rd_index->indnkeyatts);
604607
itupsz = IndexTupleDSize(*itup);
605608
itupsz = MAXALIGN(itupsz);
606609
}

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

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -63,12 +63,11 @@ _bt_mkscankey(Relation rel, IndexTuple itup)
6363
{
6464
ScanKey skey;
6565
TupleDesc itupdesc;
66-
int nkeyatts = rel->rd_rel->relnatts;
66+
int nkeyatts;
6767
int16 *indoption = rel->rd_indoption;
6868
int i;
6969
itupdesc = RelationGetDescr(rel);
7070

71-
Assert(rel->rd_index != NULL);
7271
Assert(rel->rd_index->indnkeyatts != 0);
7372
Assert(rel->rd_index->indnkeyatts <= rel->rd_index->indnatts);
7473

‎src/backend/commands/indexcmds.c

Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -137,6 +137,7 @@ CheckIndexCompatible(Oid oldId,
137137
Relation irel;
138138
int i;
139139
Datum d;
140+
140141
/* Caller should already have the relation locked in some way. */
141142
relationId = IndexGetRelation(oldId, false);
142143

@@ -346,8 +347,12 @@ DefineIndex(Oid relationId,
346347
errmsg("must specify at least one key column")));
347348

348349
/*
349-
* All information about key and included cols is in numberOfKeyAttributes number.
350-
* So we can concat all index params into one list.
350+
* We append any INCLUDING columns onto the indexParams list so that
351+
* we have one list with all columns. Later we can determine which of these
352+
* are key columns, and which are just part of the INCLUDING list by check the list
353+
* position. A list item in a position less than ii_NumIndexKeyAttrs is part of
354+
* the indexed columns, and anything equal to and over is part of the
355+
* INCLUDING columns.
351356
*/
352357
stmt->indexParams = list_concat(stmt->indexParams, stmt->indexIncludingParams);
353358
numberOfAttributes = list_length(stmt->indexParams);

‎src/backend/optimizer/path/pathkeys.c

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -453,6 +453,13 @@ build_index_pathkeys(PlannerInfo *root,
453453
bool nulls_first;
454454
PathKey *cpathkey;
455455

456+
/*
457+
* INCLUDING columns are stored in index unordered,
458+
* so they don't support ordered index scan.
459+
*/
460+
if(i >= index->nkeycolumns)
461+
break;
462+
456463
/* We assume we don't need to make a copy of the tlist item */
457464
indexkey = indextle->expr;
458465

‎src/backend/utils/adt/ruleutils.c

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1159,9 +1159,10 @@ pg_get_indexdef_worker(Oid indexrelid, int colno,
11591159

11601160
attname = get_relid_attribute_name(indrelid, attnum);
11611161

1162-
if (!colno || colno == keyno + 1) {
1162+
if (!colno || colno == keyno + 1)
1163+
{
11631164
appendStringInfoString(&buf, quote_identifier(attname));
1164-
if ((attrsOnly)&&(keyno >= idxrec->indnkeyatts))
1165+
if (attrsOnly && keyno >= idxrec->indnkeyatts)
11651166
appendStringInfoString(&buf, " (included)");
11661167
}
11671168
get_atttypetypmodcoll(indrelid, attnum,

‎src/include/catalog/pg_index.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -32,7 +32,7 @@ CATALOG(pg_index,2610) BKI_WITHOUT_OIDS BKI_SCHEMA_MACRO
3232
{
3333
Oid indexrelid; /* OID of the index */
3434
Oid indrelid; /* OID of the relation it indexes */
35-
int16 indnatts; /* number of columns in index */
35+
int16 indnatts; /* total number of columns in index */
3636
int16 indnkeyatts; /* number of key columns in index */
3737
bool indisunique; /* is this a unique index? */
3838
bool indisprimary; /* is this index for primary key? */

‎src/include/nodes/execnodes.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -58,8 +58,8 @@
5858
typedef struct IndexInfo
5959
{
6060
NodeTag type;
61-
int ii_NumIndexAttrs;
62-
int ii_NumIndexKeyAttrs;
61+
int ii_NumIndexAttrs; /* total number of columns in index */
62+
int ii_NumIndexKeyAttrs; /* number of key columns in index */
6363
AttrNumber ii_KeyAttrNumbers[INDEX_MAX_KEYS];
6464
List *ii_Expressions; /* list of Expr */
6565
List *ii_ExpressionsState; /* list of ExprState */

‎src/include/nodes/parsenodes.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2419,7 +2419,8 @@ typedef struct IndexStmt
24192419
char *accessMethod; /* name of access method (eg. btree) */
24202420
char *tableSpace; /* tablespace, or NULL for default */
24212421
List *indexParams; /* columns to index: a list of IndexElem */
2422-
List *indexIncludingParams; /* additional columns to index: a list of IndexElem */
2422+
List *indexIncludingParams; /* additional columns to index:
2423+
* a list of IndexElem */
24232424
List *options; /* WITH clause options: a list of DefElem */
24242425
Node *whereClause; /* qualification (partial-index predicate) */
24252426
List *excludeOpNames; /* exclusion operator names, or NIL if none */

‎src/include/utils/rel.h

Lines changed: 5 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -346,15 +346,16 @@ typedef struct ViewOptions
346346

347347
/*
348348
* RelationGetNumberOfAttributes
349-
* Returns the number of attributes in a relation.
349+
* Returns the total number of attributes in a relation.
350350
*/
351351
#define RelationGetNumberOfAttributes(relation) ((relation)->rd_rel->relnatts)
352352

353353
/*
354-
* RelationGetNumberOfAttributes
355-
* Returns the number of attributes in a relation.
354+
* IndexRelationGetNumberOfKeyAttributes
355+
* Returns the number of key attributes in a relation.
356356
*/
357-
#define IndexRelationGetNumberOfKeyAttributes(relation) ((relation)->rd_index->indnkeyatts)
357+
#define IndexRelationGetNumberOfKeyAttributes(relation) \
358+
((relation)->rd_index->indnkeyatts)
358359

359360
/*
360361
* RelationGetDescr

0 commit comments

Comments
 (0)