On 04/18/2018 12:52 PM, Heikki Linnakangas wrote:
On 11/04/18 04:20, Michael Paquier wrote:
Hi all,
The documentation of covering indexes is incorrect for CREATE and ALTER
TABLE:
- ALTER TABLE's page is missing the call.
- Exclusion constraints can use INCLUDE clauses.
In order to simplify the documentation, please let me suggest the
attached which moves the definition of the INCLUDE clause into the
section index_parameters, which is compatible with what I read from the
parser.
Committed, thanks!
- Heikki
Following this change, I believe we need to modify UNIQUE and PRIMARY
KEY descriptions in CREATE TABLE as they still mention INCLUDE but not
the other index_parameters. The attached patch fixes this inconsistency,
as well as adds a separate paragraph for INCLUDE in CREATE TABLE to
clarify its purpose and avoid repetition in constraint descriptions. It
also reorders the paragraphs in CREATE INDEX to follow the syntax.
--
Liudmila Mantrova
Technical writer at Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index 3c1223b..c67f187 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -145,52 +145,6 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
</varlistentry>
<varlistentry>
- <term><literal>INCLUDE</literal></term>
- <listitem>
- <para>
- The optional <literal>INCLUDE</literal> clause specifies a
- list of columns which will be included in the index
- as <firstterm>non-key</firstterm> columns. A non-key column cannot
- be used in an index scan search qualification, and it is disregarded
- for purposes of any uniqueness or exclusion constraint enforced by
- the index. However, an index-only scan can return the contents of
- non-key columns without having to visit the index's table, since
- they are available directly from the index entry. Thus, addition of
- non-key columns allows index-only scans to be used for queries that
- otherwise could not use them.
- </para>
-
- <para>
- It's wise to be conservative about adding non-key columns to an
- index, especially wide columns. If an index tuple exceeds the
- maximum size allowed for the index type, data insertion will fail.
- In any case, non-key columns duplicate data from the index's table
- and bloat the size of the index, thus potentially slowing searches.
- </para>
-
- <para>
- Columns listed in the <literal>INCLUDE</literal> clause don't need
- appropriate operator classes; the clause can include
- columns whose data types don't have operator classes defined for
- a given access method.
- </para>
-
- <para>
- Expressions are not supported as included columns since they cannot be
- used in index-only scans.
- </para>
-
- <para>
- Currently, only the B-tree index access method supports this feature.
- In B-tree indexes, the values of columns listed in the
- <literal>INCLUDE</literal> clause are included in leaf tuples which
- correspond to heap tuples, but are not included in upper-level
- index entries used for tree navigation.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
<term><replaceable class="parameter">name</replaceable></term>
<listitem>
<para>
@@ -317,6 +271,52 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
</varlistentry>
<varlistentry>
+ <term><literal>INCLUDE</literal></term>
+ <listitem>
+ <para>
+ The optional <literal>INCLUDE</literal> clause specifies a
+ list of columns which will be included in the index
+ as <firstterm>non-key</firstterm> columns. A non-key column cannot
+ be used in an index scan search qualification, and it is disregarded
+ for purposes of any uniqueness or exclusion constraint enforced by
+ the index. However, an index-only scan can return the contents of
+ non-key columns without having to visit the index's table, since
+ they are available directly from the index entry. Thus, addition of
+ non-key columns allows index-only scans to be used for queries that
+ otherwise could not use them.
+ </para>
+
+ <para>
+ It's wise to be conservative about adding non-key columns to an
+ index, especially wide columns. If an index tuple exceeds the
+ maximum size allowed for the index type, data insertion will fail.
+ In any case, non-key columns duplicate data from the index's table
+ and bloat the size of the index, thus potentially slowing searches.
+ </para>
+
+ <para>
+ Columns listed in the <literal>INCLUDE</literal> clause don't need
+ appropriate operator classes; the clause can include
+ columns whose data types don't have operator classes defined for
+ a given access method.
+ </para>
+
+ <para>
+ Expressions are not supported as included columns since they cannot be
+ used in index-only scans.
+ </para>
+
+ <para>
+ Currently, only the B-tree index access method supports this feature.
+ In B-tree indexes, the values of columns listed in the
+ <literal>INCLUDE</literal> clause are included in leaf tuples which
+ correspond to heap tuples, but are not included in upper-level
+ index entries used for tree navigation.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="parameter">storage_parameter</replaceable></term>
<listitem>
<para>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index d936de3..460312e 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -830,9 +830,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</varlistentry>
<varlistentry>
- <term><literal>UNIQUE</literal> (column constraint)</term>
+ <term><literal>UNIQUE</literal> <replaceable class="parameter">index_parameters</replaceable> (column constraint)</term>
<term><literal>UNIQUE ( <replaceable class="parameter">column_name</replaceable> [, ... ] )</literal>
- <optional> INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ...]) </optional> (table constraint)</term>
+ <replaceable class="parameter">index_parameters</replaceable> (table constraint)</term>
<listitem>
<para>
@@ -863,22 +863,19 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- Adding a unique constraint will automatically create a unique btree
+ Adding a unique constraint will automatically create a unique B-tree
index on the column or group of columns used in the constraint.
- The optional clause <literal>INCLUDE</literal> adds to that index
- one or more columns on which the uniqueness is not enforced.
- Note that although the constraint is not enforced on the included columns,
- it still depends on them. Consequently, some operations on these columns
- (e.g. <literal>DROP COLUMN</literal>) can cause cascaded constraint and
- index deletion.
+ Optionally, you can adjust index properties using
+ <literal>INCLUDE</literal>, <literal>WITH</literal>, and
+ <literal>USING INDEX TABLESPACES</literal> clauses, as explained below.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>PRIMARY KEY</literal> (column constraint)</term>
+ <term><literal>PRIMARY KEY</literal> <replaceable class="parameter">index_parameters</replaceable> (column constraint)</term>
<term><literal>PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] )</literal>
- <optional> INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ...]) </optional> (table constraint)</term>
+ <replaceable class="parameter">index_parameters</replaceable> (table constraint)</term>
<listitem>
<para>
The <literal>PRIMARY KEY</literal> constraint specifies that a column or
@@ -910,13 +907,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
Adding a <literal>PRIMARY KEY</literal> constraint will automatically
- create a unique btree index on the column or group of columns used in the
- constraint. The optional <literal>INCLUDE</literal> clause allows a list
- of columns to be specified which will be included in the non-key portion
- of the index. Although uniqueness is not enforced on the included columns,
- the constraint still depends on them. Consequently, some operations on the
- included columns (e.g. <literal>DROP COLUMN</literal>) can cause cascaded
- constraint and index deletion.
+ create a unique B-tree index on the column or group of columns used in
+ the constraint. Optionally, you can adjust index properties using
+ <literal>INCLUDE</literal>, <literal>WITH</literal>, and
+ <literal>USING INDEX TABLESPACES</literal> clauses, as explained below.
</para>
</listitem>
</varlistentry>
@@ -951,6 +945,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
can optionally specify an operator class and/or ordering options;
these are described fully under
<xref linkend="sql-createindex"/>.
+ You can also adjust index properties using <literal>INCLUDE</literal>,
+ <literal>WITH</literal>, and <literal>USING INDEX TABLESPACES</literal>
+ clauses, as explained below.
</para>
<para>
@@ -1137,6 +1134,27 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</varlistentry>
<varlistentry>
+ <term><literal>INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ... ] )</literal></term>
+ <listitem>
+ <para>
+ The optional <literal>INCLUDE</literal> clause adds non-key columns
+ to the index created for <literal>UNIQUE</literal>,
+ <literal>PRIMARY KEY</literal>, and <literal>EXCLUDE</literal>
+ constraints, without enforcing the constraint on these columns.
+ The contents of non-key columns can be returned by index-only scans,
+ so you can use this clause to expand the constraint-related index to
+ the columns that are likely to be queried.
+ Note that although the constraint is not enforced on the non-key columns,
+ it still depends on them. Consequently, some operations on these columns
+ (e.g. <literal>DROP COLUMN</literal>) can cause cascaded constraint and
+ index deletion.
+ For details on non-key columns, see the <literal>INCLUDE</literal>
+ description in <xref linkend="sql-createindex"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
<listitem>
<para>
