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

Commit91b4e25

Browse files
committed
Applied covering indices patch by Anastasia Lubennikova from th pgsql-hackers messagehttp://www.postgresql.org/message-id/f90aa60a-b67f-95b5-d9f5-f5d8ced178c6@postgrespro.ru/
1 parent3c19892 commit91b4e25

File tree

66 files changed

+1325
-254
lines changed

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
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);
@@ -1483,7 +1483,7 @@ PG_FUNCTION_INFO_V1(dblink_get_pkey);
14831483
Datum
14841484
dblink_get_pkey(PG_FUNCTION_ARGS)
14851485
{
1486-
int16numatts;
1486+
int16indnkeyatts;
14871487
char**results;
14881488
FuncCallContext*funcctx;
14891489
int32call_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 setnumatts = 0, if no primary key exists.
2024+
* Return NULL, and setindnkeyatts = 0, if no primary key exists.
20252025
*/
20262026
staticchar**
2027-
get_pkey_attnames(Relationrel,int16*numatts)
2027+
get_pkey_attnames(Relationrel,int16*indnkeyatts)
20282028
{
20292029
RelationindexRelation;
20302030
ScanKeyDataskey;
@@ -2034,8 +2034,8 @@ get_pkey_attnames(Relation rel, int16 *numatts)
20342034
char**result=NULL;
20352035
TupleDesctupdesc;
20362036

2037-
/* initializenumatts to 0 in case no primary key exists */
2038-
*numatts=0;
2037+
/* initializeindnkeyatts 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-
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
@@ -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 auniqueB-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
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
@@ -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)
[8]ページ先頭
©2009-2025 Movatter.jp