Skip to content

Commit 701fd0b

Browse files
tglsfdctglsfdc
committed
Drop the rule against included index columns duplicating key columns.
The initial version of the included-index-column feature stated that included columns couldn't be the same as any key column of the index. While it'd be pretty silly to do that, since the included column would be entirely redundant, we've never prohibited redundant index columns before so it's not very consistent to do so here. Moreover, the prohibition was itself badly implemented, so that it failed to reject columns that were effectively identical but not spelled quite alike, as reported by Aditya Toshniwal. (Moreover, it's not hard to imagine that for some non-btree index types, such cases would be non-silly anyhow: the index might use a lossy representation for key columns but be able to support retrieval of the original form of included columns.) Hence, let's just drop the prohibition. In passing, do some copy-editing on the documentation for the included-column feature. Yugo Nagata; documentation and test corrections by me Discussion: https://postgr.es/m/CAM9w-_mhBCys4fQNfaiQKTRrVWtoFrZ-wXmDuE9Nj5y-Y7aDKQ@mail.gmail.com
1 parent 3cb6462 commit 701fd0b

File tree

8 files changed

+58
-64
lines changed

8 files changed

+58
-64
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 14 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -3753,15 +3753,16 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
37533753
<entry><type>int2</type></entry>
37543754
<entry></entry>
37553755
<entry>The total number of columns in the index (duplicates
3756-
<literal>pg_class.relnatts</literal>). This number includes both key and included attributes.</entry>
3756+
<literal>pg_class.relnatts</literal>); this number includes both key and included attributes</entry>
37573757
</row>
37583758

37593759
<row>
37603760
<entry><structfield>indnkeyatts</structfield></entry>
37613761
<entry><type>int2</type></entry>
37623762
<entry></entry>
3763-
<entry>The number of key columns in the index. "Key columns" are ordinary
3764-
index columns (as opposed to "included" columns).</entry>
3763+
<entry>The number of <firstterm>key columns</firstterm> in the index,
3764+
not counting any <firstterm>included columns</firstterm>, which are
3765+
merely stored and do not participate in the index semantics</entry>
37653766
</row>
37663767

37673768
<row>
@@ -3867,7 +3868,8 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
38673868
This is an array of <structfield>indnatts</structfield> values that
38683869
indicate which table columns this index indexes. For example a value
38693870
of <literal>1 3</literal> would mean that the first and the third table
3870-
columns make up the index key. A zero in this array indicates that the
3871+
columns make up the index entries. Key columns come before non-key
3872+
(included) columns. A zero in this array indicates that the
38713873
corresponding index attribute is an expression over the table columns,
38723874
rather than a simple column reference.
38733875
</entry>
@@ -3878,9 +3880,10 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
38783880
<entry><type>oidvector</type></entry>
38793881
<entry><literal><link linkend="catalog-pg-collation"><structname>pg_collation</structname></link>.oid</literal></entry>
38803882
<entry>
3881-
For each column in the index key, this contains the OID of the
3882-
collation to use for the index, or zero if the column is not
3883-
of a collatable data type.
3883+
For each column in the index key
3884+
(<structfield>indnkeyatts</structfield> values), this contains the OID
3885+
of the collation to use for the index, or zero if the column is not of
3886+
a collatable data type.
38843887
</entry>
38853888
</row>
38863889

@@ -3889,8 +3892,9 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
38893892
<entry><type>oidvector</type></entry>
38903893
<entry><literal><link linkend="catalog-pg-opclass"><structname>pg_opclass</structname></link>.oid</literal></entry>
38913894
<entry>
3892-
For each column in the index key, this contains the OID of
3893-
the operator class to use. See
3895+
For each column in the index key
3896+
(<structfield>indnkeyatts</structfield> values), this contains the OID
3897+
of the operator class to use. See
38943898
<link linkend="catalog-pg-opclass"><structname>pg_opclass</structname></link> for details.
38953899
</entry>
38963900
</row>
@@ -3900,7 +3904,7 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
39003904
<entry><type>int2vector</type></entry>
39013905
<entry></entry>
39023906
<entry>
3903-
This is an array of <structfield>indnatts</structfield> values that
3907+
This is an array of <structfield>indnkeyatts</structfield> values that
39043908
store per-column flag bits. The meaning of the bits is defined by
39053909
the index's access method.
39063910
</entry>

‎doc/src/sgml/indexam.sgml

Lines changed: 5 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -112,10 +112,10 @@ typedef struct IndexAmRoutine
112112
bool ampredlocks;
113113
/* does AM support parallel scan? */
114114
bool amcanparallel;
115-
/* type of data stored in index, or InvalidOid if variable */
116-
Oid amkeytype;
117115
/* does AM support columns included with clause INCLUDE? */
118116
bool amcaninclude;
117+
/* type of data stored in index, or InvalidOid if variable */
118+
Oid amkeytype;
119119

120120
/* interface functions */
121121
ambuild_function ambuild;
@@ -987,8 +987,9 @@ amparallelrescan (IndexScanDesc scan);
987987
using <firstterm>unique indexes</firstterm>, which are indexes that disallow
988988
multiple entries with identical keys. An access method that supports this
989989
feature sets <structfield>amcanunique</structfield> true.
990-
(At present, only b-tree supports it.) Columns listed in the
991-
<literal>INCLUDE</literal> clause are not used to enforce uniqueness.
990+
(At present, only b-tree supports it.) Columns listed in the
991+
<literal>INCLUDE</literal> clause are not considered when enforcing
992+
uniqueness.
992993
</para>
993994

994995
<para>

‎doc/src/sgml/indices.sgml

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -639,7 +639,7 @@ CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);
639639
or the uniqueness of the combined values of more than one column.
640640
<synopsis>
641641
CREATE UNIQUE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> (<replaceable>column</replaceable> <optional>, ...</optional>)
642-
[ INCLUDE (<replaceable>column</replaceable> <optional>, ...</optional>) ];
642+
<optional> INCLUDE (<replaceable>column</replaceable> <optional>, ...</optional>) </optional>;
643643
</synopsis>
644644
Currently, only B-tree indexes can be declared unique.
645645
</para>
@@ -648,9 +648,9 @@ CREATE UNIQUE INDEX <replaceable>name</replaceable> ON <replaceable>table</repla
648648
When an index is declared unique, multiple table rows with equal
649649
indexed values are not allowed. Null values are not considered
650650
equal. A multicolumn unique index will only reject cases where all
651-
indexed columns are equal in multiple rows. Columns listed in the
652-
<literal>INCLUDE</literal> clause aren't used to enforce constraints
653-
(UNIQUE, PRIMARY KEY, etc).
651+
indexed columns are equal. Columns listed in
652+
the <literal>INCLUDE</literal> clause, if any, aren't considered when
653+
determining whether index entries are equal.
654654
</para>
655655

656656
<para>

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

Lines changed: 20 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -149,25 +149,28 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
149149
<listitem>
150150
<para>
151151
The optional <literal>INCLUDE</literal> clause specifies a
152-
list of columns which will be included as a non-key part in the index.
153-
Columns listed in this clause cannot also be present as index key columns.
154-
The <literal>INCLUDE</literal> columns exist solely to
155-
allow more queries to benefit from <firstterm>index-only scans</firstterm>
156-
by including the values of the specified columns in the index. These values
157-
would otherwise have to be obtained by reading the table's heap.
152+
list of columns which will be included in the index
153+
as <firstterm>non-key</firstterm> columns. A non-key column cannot
154+
be used in an index scan search qualification, and it is disregarded
155+
for purposes of any uniqueness or exclusion constraint enforced by
156+
the index. However, an index-only scan can return the contents of
157+
non-key columns without having to visit the index's table, since
158+
they are available directly from the index entry. Thus, addition of
159+
non-key columns allows index-only scans to be used for queries that
160+
otherwise could not use them.
158161
</para>
159162

160163
<para>
161-
In <literal>UNIQUE</literal> indexes, uniqueness is only enforced
162-
for key columns. Columns listed in the <literal>INCLUDE</literal>
163-
clause have no effect on uniqueness enforcement. Other constraints
164-
(<literal>PRIMARY KEY</literal> and <literal>EXCLUDE</literal>) work
165-
the same way.
164+
It's wise to be conservative about adding non-key columns to an
165+
index, especially wide columns. If an index tuple exceeds the
166+
maximum size allowed for the index type, data insertion will fail.
167+
In any case, non-key columns duplicate data from the index's table
168+
and bloat the size of the index, thus potentially slowing searches.
166169
</para>
167170

168171
<para>
169172
Columns listed in the <literal>INCLUDE</literal> clause don't need
170-
appropriate operator classes; the clause can contain non-key index
173+
appropriate operator classes; the clause can include
171174
columns whose data types don't have operator classes defined for
172175
a given access method.
173176
</para>
@@ -181,15 +184,8 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
181184
Currently, only the B-tree index access method supports this feature.
182185
In B-tree indexes, the values of columns listed in the
183186
<literal>INCLUDE</literal> clause are included in leaf tuples which
184-
are linked to the heap tuples, but are not included into pivot tuples
185-
used for tree navigation. Therefore, moving columns from the list of
186-
key columns to the <literal>INCLUDE</literal> clause can slightly
187-
reduce index size and improve the tree branching factor.
188-
</para>
189-
190-
<para>
191-
Indexes with columns listed in the <literal>INCLUDE</literal> clause
192-
are also called <quote>covering indexes</quote>.
187+
correspond to heap tuples, but are not included in upper-level
188+
index entries used for tree navigation.
193189
</para>
194190
</listitem>
195191
</varlistentry>
@@ -376,7 +372,7 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
376372
columns is updated and so the recheck is not worth the additional cost
377373
of executing the function.
378374
</para>
379-
375+
380376
<para>
381377
Functional indexes are used frequently for the case where the function
382378
returns a subset of the argument. Examples of this would be accessing
@@ -789,8 +785,8 @@ CREATE UNIQUE INDEX title_idx ON films (title);
789785

790786
<para>
791787
To create a unique B-tree index on the column <literal>title</literal>
792-
and included columns <literal>director</literal> and <literal>rating</literal>
793-
in the table <literal>films</literal>:
788+
with included columns <literal>director</literal>
789+
and <literal>rating</literal> in the table <literal>films</literal>:
794790
<programlisting>
795791
CREATE UNIQUE INDEX title_idx ON films (title) INCLUDE (director, rating);
796792
</programlisting>

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

Lines changed: 4 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -869,9 +869,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
869869
one or more columns on which the uniqueness is not enforced.
870870
Note that although the constraint is not enforced on the included columns,
871871
it still depends on them. Consequently, some operations on these columns
872-
(e.g. <literal>DROP COLUMN</literal>) can cause cascade constraint and
873-
index deletion. See paragraph about <literal>INCLUDE</literal> in
874-
<xref linkend="sql-createindex"/> for more information.
872+
(e.g. <literal>DROP COLUMN</literal>) can cause cascaded constraint and
873+
index deletion.
875874
</para>
876875
</listitem>
877876
</varlistentry>
@@ -916,9 +915,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
916915
of columns to be specified which will be included in the non-key portion
917916
of the index. Although uniqueness is not enforced on the included columns,
918917
the constraint still depends on them. Consequently, some operations on the
919-
included columns (e.g. <literal>DROP COLUMN</literal>) can cause cascade
920-
constraint and index deletion. See paragraph about <literal>INCLUDE</literal>
921-
in <xref linkend="sql-createindex"/> for more information.
918+
included columns (e.g. <literal>DROP COLUMN</literal>) can cause cascaded
919+
constraint and index deletion.
922920
</para>
923921
</listitem>
924922
</varlistentry>

‎src/backend/commands/indexcmds.c

Lines changed: 1 addition & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -369,11 +369,6 @@ DefineIndex(Oid relationId,
369369
Snapshot snapshot;
370370
int i;
371371

372-
if (list_intersection(stmt->indexParams, stmt->indexIncludingParams) != NIL)
373-
ereport(ERROR,
374-
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
375-
errmsg("included columns must not intersect with key columns")));
376-
377372
/*
378373
* count key attributes in index
379374
*/
@@ -596,7 +591,7 @@ DefineIndex(Oid relationId,
596591
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
597592
errmsg("access method \"%s\" does not support unique indexes",
598593
accessMethodName)));
599-
if (list_length(stmt->indexIncludingParams) > 0 && !amRoutine->amcaninclude)
594+
if (stmt->indexIncludingParams != NIL && !amRoutine->amcaninclude)
600595
ereport(ERROR,
601596
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
602597
errmsg("access method \"%s\" does not support included columns",

‎src/test/regress/expected/index_including.out

Lines changed: 7 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -7,17 +7,17 @@
77
-- Regular index with included columns
88
CREATE TABLE tbl_include_reg (c1 int, c2 int, c3 int, c4 box);
99
INSERT INTO tbl_include_reg SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
10-
CREATE INDEX tbl_include_reg_idx ON tbl_include_reg using btree (c1, c2) INCLUDE (c3,c4);
11-
-- must fail because of intersection of key and included columns
12-
CREATE INDEX tbl_include_reg_idx ON tbl_include_reg using btree (c1, c2) INCLUDE (c1,c3);
13-
ERROR: included columns must not intersect with key columns
10+
CREATE INDEX tbl_include_reg_idx ON tbl_include_reg (c1, c2) INCLUDE (c3, c4);
11+
-- duplicate column is pretty pointless, but we allow it anyway
12+
CREATE INDEX ON tbl_include_reg (c1, c2) INCLUDE (c1, c3);
1413
SELECT pg_get_indexdef(i.indexrelid)
1514
FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid
1615
WHERE i.indrelid = 'tbl_include_reg'::regclass ORDER BY c.relname;
17-
pg_get_indexdef
18-
--------------------------------------------------------------------------------------------------
16+
pg_get_indexdef
17+
---------------------------------------------------------------------------------------------------------------
18+
CREATE INDEX tbl_include_reg_c1_c2_c11_c3_idx ON public.tbl_include_reg USING btree (c1, c2) INCLUDE (c1, c3)
1919
CREATE INDEX tbl_include_reg_idx ON public.tbl_include_reg USING btree (c1, c2) INCLUDE (c3, c4)
20-
(1 row)
20+
(2 rows)
2121

2222
-- Unique index and unique constraint
2323
CREATE TABLE tbl_include_unique1 (c1 int, c2 int, c3 int, c4 box);

‎src/test/regress/sql/index_including.sql

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,9 +8,9 @@
88
-- Regular index with included columns
99
CREATE TABLE tbl_include_reg (c1 int, c2 int, c3 int, c4 box);
1010
INSERT INTO tbl_include_reg SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
11-
CREATE INDEX tbl_include_reg_idx ON tbl_include_reg using btree (c1, c2) INCLUDE (c3,c4);
12-
-- must fail because of intersection of key and included columns
13-
CREATE INDEX tbl_include_reg_idx ON tbl_include_reg using btree (c1, c2) INCLUDE (c1,c3);
11+
CREATE INDEX tbl_include_reg_idx ON tbl_include_reg (c1, c2) INCLUDE (c3, c4);
12+
-- duplicate column is pretty pointless, but we allow it anyway
13+
CREATE INDEX ON tbl_include_reg (c1, c2) INCLUDE (c1, c3);
1414
SELECT pg_get_indexdef(i.indexrelid)
1515
FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid
1616
WHERE i.indrelid = 'tbl_include_reg'::regclass ORDER BY c.relname;

0 commit comments

Comments
 (0)