Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit5cddede

Browse files
alubennikovavbwagner
authored andcommitted
Covering indexes rebased from master to PGPROP9_5.
1 parent7580894 commit5cddede

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 createa B-tree index on the column <literal>title</literal> in
620+
To createan 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
returnresult;
443444
}
445+
446+
/*
447+
* Reform index tuple. Truncate nonkey (INCLUDED) attributes.
448+
*/
449+
IndexTuple
450+
index_reform_tuple(Relationidxrel,IndexTupleolditup,intnatts,intnkeyatts)
451+
{
452+
TupleDescitupdesc=RelationGetDescr(idxrel);
453+
Datumvalues[INDEX_MAX_KEYS];
454+
boolisnull[INDEX_MAX_KEYS];
455+
IndexTuplenewitup;
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+
returnnewitup;
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
IndexUniqueCheckcheckUnique,RelationheapRel)
109109
{
110110
boolis_unique= false;
111-
intnatts=rel->rd_rel->relnatts;
111+
intnkeyatts=rel->rd_rel->relnatts;
112112
ScanKeyitup_scankey;
113113
BTStackstack;
114114
Bufferbuf;
115115
OffsetNumberoffset;
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
TransactionIdxwait;
164169
uint32speculativeToken;
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
TupleDescitupdesc=RelationGetDescr(rel);
245-
intnatts=rel->rd_rel->relnatts;
250+
intnkeyatts=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
SnapshotDataSnapshotDirty;
247257
OffsetNumbermaxoff;
248258
Pagepage;
@@ -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+
BTPageOpaquepageop= (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
ScanKeyskey;
6565
TupleDescitupdesc;
66-
intnatts;
67-
int16*indoption;
66+
intnkeyatts=rel->rd_rel->relnatts;
67+
int16*indoption=rel->rd_indoption;
6868
inti;
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
Datumarg;

‎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
AttrNumberattnum=indexInfo->ii_KeyAttrNumbers[i];
217217
HeapTupleatttuple;
@@ -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)
[8]ページ先頭
©2009-2025 Movatter.jp