pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedFix for documentation of Covering Indexes
4+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2018-04-18 fe7fc52645 Improve docs for the new INCLUDE directive in CREATE/ALTER TABLE.
* Fix for documentation of Covering Indexes
@ 2018-04-11 08:20 Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 4+ messages in thread
From: Michael Paquier @ 2018-04-11 08:20 UTC (permalink / raw)
To: Postgres hackers <pgsql-hackers@postgresql.org>
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.
Thanks,
--
Michael
Attachments:
covering-index-docs.patch (text/x-diff, 3.3K) download
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index bd2262761e..8c7b9619b8 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -125,6 +125,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<phrase><replaceable class="parameter">index_parameters</replaceable> in <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and <literal>EXCLUDE</literal> constraints are:</phrase>
+[ INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ]
[ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
[ USING INDEX TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index cb3867dbd5..f2bd562d55 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -73,8 +73,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ]
{ CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
- UNIQUE ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> <optional> INCLUDE (<replaceable class="parameter">column_name</replaceable> [, ...]) </optional> |
- PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> <optional> INCLUDE (<replaceable class="parameter">column_name</replaceable> [, ...]) </optional> |
+ UNIQUE ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
+ PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
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> ) ] |
FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] }
@@ -93,6 +93,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<phrase><replaceable class="parameter">index_parameters</replaceable> in <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and <literal>EXCLUDE</literal> constraints are:</phrase>
+[ INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ]
[ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
[ USING INDEX TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
* Re: Fix for documentation of Covering Indexes
@ 2018-04-18 09:52 Heikki Linnakangas <hlinnaka@iki.fi>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 2 replies; 4+ messages in thread
From: Heikki Linnakangas @ 2018-04-18 09:52 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; Postgres hackers <pgsql-hackers@postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Fix for documentation of Covering Indexes
@ 2018-04-19 00:15 Michael Paquier <michael@paquier.xyz>
parent: Heikki Linnakangas <hlinnaka@iki.fi>
1 sibling, 0 replies; 4+ messages in thread
From: Michael Paquier @ 2018-04-19 00:15 UTC (permalink / raw)
To: Heikki Linnakangas <hlinnaka@iki.fi>; +Cc: Postgres hackers <pgsql-hackers@postgresql.org>
On Wed, Apr 18, 2018 at 05:52:01AM -0400, Heikki Linnakangas wrote:
> Committed, thanks!
Thanks for the commit.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Fix for documentation of Covering Indexes
@ 2018-07-30 10:38 Liudmila Mantrova <l.mantrova@postgrespro.ru>
parent: Heikki Linnakangas <hlinnaka@iki.fi>
1 sibling, 0 replies; 4+ messages in thread
From: Liudmila Mantrova @ 2018-07-30 10:38 UTC (permalink / raw)
To: Heikki Linnakangas <hlinnaka@iki.fi>; Michael Paquier <michael@paquier.xyz>; Postgres hackers <pgsql-hackers@postgresql.org>; +Cc: pgsql-docs@lists.postgresql.org
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) download
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>
end of thread, other threads:[~2018-07-30 10:38 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-04-11 08:20 Fix for documentation of Covering Indexes Michael Paquier <michael@paquier.xyz>
2018-04-18 09:52 ` Heikki Linnakangas <hlinnaka@iki.fi>
2018-04-19 00:15 ` Michael Paquier <michael@paquier.xyz>
2018-07-30 10:38 ` Liudmila Mantrova <l.mantrova@postgrespro.ru>
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