From: Liudmila Mantrova <l.mantrova@postgrespro.ru>
To: Heikki Linnakangas <hlinnaka@iki.fi>
To: Michael Paquier <michael@paquier.xyz>
To: Postgres hackers <pgsql-hackers@postgresql.org>
Cc: pgsql-docs@lists.postgresql.org
Subject: Re: Fix for documentation of Covering Indexes
Date: Mon, 30 Jul 2018 13:38:10 +0300
Message-ID: <a58527f8-dbfc-3ba9-0a0c-05c5849d97aa@postgrespro.ru> (raw)
In-Reply-To: <c8f7035c-b679-f8f6-509c-75f79fa37c0a@iki.fi>
References: <20180411082020.GD19732@paquier.xyz>
<c8f7035c-b679-f8f6-509c-75f79fa37c0a@iki.fi>
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
Attachments:include-clause-reference.patch (text/x-patch, 10.1K) downloaddiff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgmlindex 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.sgmlindex 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>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: l.mantrova@postgrespro.ru, hlinnaka@iki.fi, michael@paquier.xyz, pgsql-docs@lists.postgresql.org
Subject: Re: Fix for documentation of Covering Indexes
In-Reply-To: <a58527f8-dbfc-3ba9-0a0c-05c5849d97aa@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox