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

Commit386e3d7

Browse files
committed
CREATE INDEX ... INCLUDING (column[, ...])
Now indexes (but only B-tree for now) can contain "extra" column(s) whichdoesn't participate in index structure, they are just stored in leaftuples. It allows to use index only scan by using single index insteadof two or more indexes.Author: Anastasia Lubennikova with minor editorializing by meReviewers: David Rowley, Peter Geoghegan, Jeff Janes
1 parent339025c commit386e3d7

File tree

68 files changed

+1320
-255
lines changed

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
staticHTAB*createConnHash(void);
101101
staticvoidcreateNewConnection(constchar*name,remoteConn*rconn);
102102
staticvoiddeleteConnection(constchar*name);
103-
staticchar**get_pkey_attnames(Relationrel,int16*numatts);
103+
staticchar**get_pkey_attnames(Relationrel,int16*indnkeyatts);
104104
staticchar**get_text_array_contents(ArrayType*array,int*numitems);
105105
staticchar*get_sql_insert(Relationrel,int*pkattnums,intpknumatts,char**src_pkattvals,char**tgt_pkattvals);
106106
staticchar*get_sql_delete(Relationrel,int*pkattnums,intpknumatts,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-
int16numatts;
1488+
int16indnkeyatts;
14891489
char**results;
14901490
FuncCallContext*funcctx;
14911491
int32call_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 setnumatts = 0, if no primary key exists.
2026+
* Return NULL, and setindnkeyatts = 0, if no primary key exists.
20272027
*/
20282028
staticchar**
2029-
get_pkey_attnames(Relationrel,int16*numatts)
2029+
get_pkey_attnames(Relationrel,int16*indnkeyatts)
20302030
{
20312031
RelationindexRelation;
20322032
ScanKeyDataskey;
@@ -2036,8 +2036,8 @@ get_pkey_attnames(Relation rel, int16 *numatts)
20362036
char**result=NULL;
20372037
TupleDesctupdesc;
20382038

2039-
/* initializenumatts to 0 in case no primary key exists */
2040-
*numatts=0;
2039+
/* initializeindnkeyatts 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-
intnumatts=index->indnatts;
141+
intindnkeyatts=index->indnkeyatts;
142142

143-
if (numatts>0)
143+
if (indnkeyatts>0)
144144
{
145145
inti;
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
intcolno=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 auniqueB-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
returnresult;
443444
}
445+
446+
/*
447+
* Reform index tuple. Truncate nonkey (INCLUDING) attributes.
448+
*/
449+
IndexTuple
450+
index_truncate_tuple(Relationidxrel,IndexTupleolditup)
451+
{
452+
TupleDescitupdesc=RelationGetDescr(idxrel);
453+
Datumvalues[INDEX_MAX_KEYS];
454+
boolisnull[INDEX_MAX_KEYS];
455+
IndexTuplenewitup;
456+
intindnatts=IndexRelationGetNumberOfAttributes(idxrel);
457+
intindnkeyatts=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+
returnnewitup;
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)
[8]ページ先頭
©2009-2025 Movatter.jp