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

Commita9b16e0

Browse files
committed
Merge branch 'CORE-155-covering-indexes' into PGPRO9_5
2 parentsdfad4a7 +cf7c1d0 commita9b16e0

File tree

46 files changed

+1011
-264
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

46 files changed

+1011
-264
lines changed

‎contrib/dblink/dblink.c‎

Lines changed: 13 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -101,7 +101,7 @@ static remoteConn *getConnectionByName(const char *name);
101101
staticHTAB*createConnHash(void);
102102
staticvoidcreateNewConnection(constchar*name,remoteConn*rconn);
103103
staticvoiddeleteConnection(constchar*name);
104-
staticchar**get_pkey_attnames(Relationrel,int16*numatts);
104+
staticchar**get_pkey_attnames(Relationrel,int16*indnkeyatts);
105105
staticchar**get_text_array_contents(ArrayType*array,int*numitems);
106106
staticchar*get_sql_insert(Relationrel,int*pkattnums,intpknumatts,char**src_pkattvals,char**tgt_pkattvals);
107107
staticchar*get_sql_delete(Relationrel,int*pkattnums,intpknumatts,char**tgt_pkattvals);
@@ -1486,7 +1486,7 @@ PG_FUNCTION_INFO_V1(dblink_get_pkey);
14861486
Datum
14871487
dblink_get_pkey(PG_FUNCTION_ARGS)
14881488
{
1489-
int16numatts;
1489+
int16indnkeyatts;
14901490
char**results;
14911491
FuncCallContext*funcctx;
14921492
int32call_cntr;
@@ -1512,7 +1512,7 @@ dblink_get_pkey(PG_FUNCTION_ARGS)
15121512
rel=get_rel_from_relname(PG_GETARG_TEXT_P(0),AccessShareLock,ACL_SELECT);
15131513

15141514
/* get the array of attnums */
1515-
results=get_pkey_attnames(rel,&numatts);
1515+
results=get_pkey_attnames(rel,&indnkeyatts);
15161516

15171517
relation_close(rel,AccessShareLock);
15181518

@@ -1532,9 +1532,9 @@ dblink_get_pkey(PG_FUNCTION_ARGS)
15321532
attinmeta=TupleDescGetAttInMetadata(tupdesc);
15331533
funcctx->attinmeta=attinmeta;
15341534

1535-
if ((results!=NULL)&& (numatts>0))
1535+
if ((results!=NULL)&& (indnkeyatts>0))
15361536
{
1537-
funcctx->max_calls=numatts;
1537+
funcctx->max_calls=indnkeyatts;
15381538

15391539
/* got results, keep track of them */
15401540
funcctx->user_fctx=results;
@@ -2024,10 +2024,10 @@ dblink_fdw_validator(PG_FUNCTION_ARGS)
20242024
* get_pkey_attnames
20252025
*
20262026
* Get the primary key attnames for the given relation.
2027-
* Return NULL, and setnumatts = 0, if no primary key exists.
2027+
* Return NULL, and setindnkeyatts = 0, if no primary key exists.
20282028
*/
20292029
staticchar**
2030-
get_pkey_attnames(Relationrel,int16*numatts)
2030+
get_pkey_attnames(Relationrel,int16*indnkeyatts)
20312031
{
20322032
RelationindexRelation;
20332033
ScanKeyDataskey;
@@ -2037,8 +2037,8 @@ get_pkey_attnames(Relation rel, int16 *numatts)
20372037
char**result=NULL;
20382038
TupleDesctupdesc;
20392039

2040-
/* initializenumatts to 0 in case no primary key exists */
2041-
*numatts=0;
2040+
/* initializeindnkeyatts to 0 in case no primary key exists */
2041+
*indnkeyatts=0;
20422042

20432043
tupdesc=rel->rd_att;
20442044

@@ -2059,12 +2059,12 @@ get_pkey_attnames(Relation rel, int16 *numatts)
20592059
/* we're only interested if it is the primary key */
20602060
if (index->indisprimary)
20612061
{
2062-
*numatts=index->indnkeyatts;
2063-
if (*numatts>0)
2062+
*indnkeyatts=index->indnkeyatts;
2063+
if (*indnkeyatts>0)
20642064
{
2065-
result= (char**)palloc(*numatts*sizeof(char*));
2065+
result= (char**)palloc(*indnkeyatts*sizeof(char*));
20662066

2067-
for (i=0;i<*numatts;i++)
2067+
for (i=0;i<*indnkeyatts;i++)
20682068
result[i]=SPI_fname(tupdesc,index->indkey.values[i]);
20692069
}
20702070
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: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3721,7 +3721,7 @@
37213721
<literal>pg_class.relnatts</literal>)</entry>
37223722
</row>
37233723

3724-
<row>
3724+
<row>
37253725
<entry><structfield>indnkeyatts</structfield></entry>
37263726
<entry><type>int2</type></entry>
37273727
<entry></entry>

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

Lines changed: 20 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -23,7 +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 } ] [, ...] )
26+
[ INCLUDING ( <replaceable class="parameter">column_name</replaceable>[, ...] ) ]
2727
[ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] ) ]
2828
[ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
2929
[ WHERE <replaceable class="parameter">predicate</replaceable> ]
@@ -145,22 +145,25 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
145145
<para>
146146
An optional <literal>INCLUDING</> clause allows a list of columns to be
147147
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.
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 the table's heap. Having these columns in the
154+
<literal>INCLUDING</> clause in some cases allows
155+
<productname>PostgreSQL</> to skip the heap read completely. This
156+
also allows <literal>UNIQUE</> indexes to be defined on one set of
157+
columns, which can include another set of column in the
158+
<literal>INCLUDING</> clause, on which the uniqueness is not enforced
159+
upon. It's the same with other constraints (PRIMARY KEY and EXCLUDE).
160+
This can also can be used for non-unique indexes as any columns which
161+
are not required for the searching or ordering of records can be
162+
included in the <literal>INCLUDING</> clause, which can slightly reduce
163+
the size of the index, due to storing included attributes only in leaf
164+
index pages. Currently, only the B-tree access method supports this
165+
feature. Expressions as included columns are not supported since
166+
they cannot be used in index-only scan.
164167
</para>
165168
</listitem>
166169
</varlistentry>

‎doc/src/sgml/ref/create_table.sgml‎

Lines changed: 32 additions & 4 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,7 +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>
479+
<term><literal>UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )
480+
<optional>INCLUDING ( <replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional></> (table constraint)</term>
480481

481482
<listitem>
482483
<para>
@@ -498,12 +499,27 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
498499
primary key constraint defined for the table. (Otherwise it
499500
would just be the same constraint listed twice.)
500501
</para>
502+
503+
<para>
504+
Adding a unique constraint will automatically create a unique btree
505+
index on the column or group of columns used in the constraint.
506+
Optional clause <literal>INCLUDING</literal> allows to add into the index
507+
a portion of columns on which the uniqueness is not enforced upon.
508+
Note, that althogh constraint is not enforced upon included columns, it still
509+
depends on them. Consequently, some operations on these columns (e.g. <literal>DROP COLUMN</literal>)
510+
can cause cascade constraint and index deletion.
511+
See paragraph about <literal>INCLUDING</literal> in
512+
<xref linkend="SQL-CREATEINDEX"> for more information.
513+
</para>
514+
501515
</listitem>
502516
</varlistentry>
503517

504518
<varlistentry>
505519
<term><literal>PRIMARY KEY</> (column constraint)</term>
506-
<term><literal>PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )</> (table constraint)</term>
520+
<term><literal>PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )
521+
<optional>INCLUDING ( <replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional></> (table constraint)</term>
522+
507523
<listitem>
508524
<para>
509525
The <literal>PRIMARY KEY</> constraint specifies that a column or
@@ -526,6 +542,18 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
526542
about the design of the schema, since a primary key implies that other
527543
tables can rely on this set of columns as a unique identifier for rows.
528544
</para>
545+
546+
<para>
547+
Adding a <literal>PRIMARY KEY</literal> constraint will automatically create a unique btree
548+
index on the column or group of columns used in the constraint.
549+
Optional clause <literal>INCLUDING</literal> allows to add into the index
550+
a portion of columns on which the constraint is not enforced upon.
551+
Note, that althogh constraint is not enforced upon included columns, it still
552+
depends on them. Consequently, some operations on these columns (e.g. <literal>DROP COLUMN</literal>)
553+
can cause cascade constraint and index deletion.
554+
See paragraph about <literal>INCLUDING</literal> in
555+
<xref linkend="SQL-CREATEINDEX"> for more information.
556+
</para>
529557
</listitem>
530558
</varlistentry>
531559

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

Lines changed: 9 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -448,25 +448,28 @@ CopyIndexTuple(IndexTuple source)
448448
* Reform index tuple. Truncate nonkey (INCLUDING) attributes.
449449
*/
450450
IndexTuple
451-
index_reform_tuple(Relationidxrel,IndexTupleolditup,intnatts,intnkeyatts)
451+
index_truncate_tuple(Relationidxrel,IndexTupleolditup)
452452
{
453453
TupleDescitupdesc=RelationGetDescr(idxrel);
454454
Datumvalues[INDEX_MAX_KEYS];
455455
boolisnull[INDEX_MAX_KEYS];
456456
IndexTuplenewitup;
457+
intindnatts=IndexRelationGetNumberOfAttributes(idxrel);
458+
intindnkeyatts=IndexRelationGetNumberOfKeyAttributes(idxrel);
457459

458-
Assert(natts <=INDEX_MAX_KEYS);
459-
Assert(nkeyatts>0);
460-
Assert(nkeyatts <=natts);
460+
Assert(indnatts <=INDEX_MAX_KEYS);
461+
Assert(indnkeyatts>0);
462+
Assert(indnkeyatts <=indnatts);
461463

462464
index_deform_tuple(olditup,itupdesc,values,isnull);
463465

464466
/* form new tuple that will contain only key attributes */
465-
itupdesc->natts=nkeyatts;
467+
itupdesc->natts=indnkeyatts;
466468
newitup=index_form_tuple(itupdesc,values,isnull);
467469
newitup->t_tid=olditup->t_tid;
468470

469-
itupdesc->natts=natts;
471+
itupdesc->natts=indnatts;
470472

473+
Assert(IndexTupleSize(newitup) <=IndexTupleSize(olditup));
471474
returnnewitup;
472475
}

‎src/backend/access/index/genam.c‎

Lines changed: 21 additions & 34 deletions
Original file line numberDiff line numberDiff line change
@@ -175,14 +175,15 @@ BuildIndexValueDescription(Relation indexRelation,
175175
StringInfoDatabuf;
176176
Form_pg_indexidxrec;
177177
HeapTupleht_idx;
178-
intnatts=indexRelation->rd_rel->relnatts;
179-
intnkeyatts;
178+
intindnkeyatts;
180179
inti;
181180
intkeyno;
182181
Oidindexrelid=RelationGetRelid(indexRelation);
183182
Oidindrelid;
184183
AclResultaclresult;
185184

185+
indnkeyatts=IndexRelationGetNumberOfKeyAttributes(indexRelation);
186+
186187
/*
187188
* Check permissions- if the user does not have access to view all of the
188189
* key columns then return NULL to avoid leaking data.
@@ -220,7 +221,7 @@ BuildIndexValueDescription(Relation indexRelation,
220221
* No table-level access, so step through the columns in the index and
221222
* make sure the user has SELECT rights on all of them.
222223
*/
223-
for (keyno=0;keyno<idxrec->indnatts;keyno++)
224+
for (keyno=0;keyno<idxrec->indnkeyatts;keyno++)
224225
{
225226
AttrNumberattnum=idxrec->indkey.values[keyno];
226227

@@ -246,8 +247,7 @@ BuildIndexValueDescription(Relation indexRelation,
246247
appendStringInfo(&buf,"(%s)=(",
247248
pg_get_indexdef_columns(indexrelid, true));
248249

249-
nkeyatts=IndexRelationGetNumberOfKeyAttributes(indexRelation);
250-
for (i=0;i<natts;i++)
250+
for (i=0;i<indnkeyatts;i++)
251251
{
252252
char*val;
253253

@@ -257,38 +257,25 @@ BuildIndexValueDescription(Relation indexRelation,
257257
{
258258
Oidfoutoid;
259259
booltypisvarlena;
260-
TupleDesctupdesc=RelationGetDescr(indexRelation);
261260
/*
262-
* For key attributes the provided data is not necessarily of the
263-
* type stored in the index; rather it is of the index opclass's
264-
* input type. So look at rd_opcintype not the index tupdesc.
265-
*
266-
* Note: this is a bit shaky for opclasses that have pseudotype
267-
* input types such as ANYARRAY or RECORD. Currently, the
268-
* typoutput functions associated with the pseudotypes will work
269-
* okay, but we might have to try harder in future.
270-
*
271-
* For included attributes just use info stored in the index
272-
* tupdesc.
273-
*/
274-
if (i<nkeyatts)
275-
{
276-
getTypeOutputInfo(indexRelation->rd_opcintype[i],
277-
&foutoid,&typisvarlena);
278-
val=OidOutputFunctionCall(foutoid,values[i]);
279-
}
280-
else
281-
{
282-
getTypeOutputInfo(tupdesc->attrs[i]->atttypid,&foutoid,&typisvarlena);
283-
val=OidOutputFunctionCall(foutoid,values[i]);
284-
}
261+
* The provided data is not necessarily of the type stored in the
262+
* index; rather it is of the index opclass's input type. So look
263+
* at rd_opcintype not the index tupdesc.
264+
*
265+
* Note: this is a bit shaky for opclasses that have pseudotype
266+
* input types such as ANYARRAY or RECORD. Currently, the
267+
* typoutput functions associated with the pseudotypes will work
268+
* okay, but we might have to try harder in future.
269+
*/
270+
getTypeOutputInfo(indexRelation->rd_opcintype[i],
271+
&foutoid,&typisvarlena);
272+
val=OidOutputFunctionCall(foutoid,values[i]);
285273
}
286274

287275
if (i>0)
288276
appendStringInfoString(&buf,", ");
289277
appendStringInfoString(&buf,val);
290278
}
291-
292279
appendStringInfoChar(&buf,')');
293280

294281
returnbuf.data;
@@ -376,15 +363,15 @@ systable_beginscan(Relation heapRelation,
376363
{
377364
intj;
378365

379-
for (j=0;j<irel->rd_index->indnatts;j++)
366+
for (j=0;j<IndexRelationGetNumberOfAttributes(irel);j++)
380367
{
381368
if (key[i].sk_attno==irel->rd_index->indkey.values[j])
382369
{
383370
key[i].sk_attno=j+1;
384371
break;
385372
}
386373
}
387-
if (j==irel->rd_index->indnatts)
374+
if (j==IndexRelationGetNumberOfAttributes(irel))
388375
elog(ERROR,"column is not in index");
389376
}
390377

@@ -578,15 +565,15 @@ systable_beginscan_ordered(Relation heapRelation,
578565
{
579566
intj;
580567

581-
for (j=0;j<indexRelation->rd_index->indnatts;j++)
568+
for (j=0;j<IndexRelationGetNumberOfAttributes(indexRelation);j++)
582569
{
583570
if (key[i].sk_attno==indexRelation->rd_index->indkey.values[j])
584571
{
585572
key[i].sk_attno=j+1;
586573
break;
587574
}
588575
}
589-
if (j==indexRelation->rd_index->indnatts)
576+
if (j==IndexRelationGetNumberOfAttributes(indexRelation))
590577
elog(ERROR,"column is not in index");
591578
}
592579

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp