Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fk5ZE-0001nB-Sp for pgsql-hackers@arkaria.postgresql.org; Mon, 30 Jul 2018 10:38:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fk5ZC-00050Z-Te for pgsql-hackers@arkaria.postgresql.org; Mon, 30 Jul 2018 10:38:18 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fk5ZC-00050E-Hk for pgsql-hackers@lists.postgresql.org; Mon, 30 Jul 2018 10:38:18 +0000 Received: from mail.postgrespro.ru ([93.174.131.138]) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fk5Z7-000371-0q for pgsql-hackers@postgresql.org; Mon, 30 Jul 2018 10:38:17 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id 5E9D321C1C94; Mon, 30 Jul 2018 13:38:11 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru X-Spam-Flag: NO X-Spam-Score: 0 X-Spam-Level: X-Spam-Status: No, score=x tagged_above=-99 required=4 WHITELISTED tests=[] autolearn=unavailable Received: from [192.168.27.108] (gw.postgrespro.ru [93.174.131.141]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id 2751A21C03E8; Mon, 30 Jul 2018 13:38:11 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1532947091; bh=lgTgPtWpCxLUL3MJ5dpPaKOXfjo6pdhSaR+fcI2dz6w=; h=Subject:To:References:From:Cc:Date:In-Reply-To; b=D1Ol8ZM65yV+F0A9TJxqfi9kvuDdVuSUTM/Vq/ky7nXLA/kV+0iWYYDcUwz66Eods mTyN6VCx57s1tKJ4vnvugaBFLSdYflS0v3uVMRy7nEYgBta6uRDhmJZwIevHQeWMtZ epYMIdZp5GBUBQ5hCEFo612NnsvKT8n02LkdruFU= Subject: Re: Fix for documentation of Covering Indexes To: Heikki Linnakangas , Michael Paquier , Postgres hackers References: <20180411082020.GD19732@paquier.xyz> From: Liudmila Mantrova Cc: pgsql-docs@lists.postgresql.org Message-ID: Date: Mon, 30 Jul 2018 13:38:10 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.9.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/mixed; boundary="------------901E9CAE9DAFAE1A738A9169" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------901E9CAE9DAFAE1A738A9169 Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 7bit 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 --------------901E9CAE9DAFAE1A738A9169 Content-Type: text/x-patch; name="include-clause-reference.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="include-clause-reference.patch" 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 ] - INCLUDE - - - The optional INCLUDE clause specifies a - list of columns which will be included in the index - as non-key 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. - - - - 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. - - - - Columns listed in the INCLUDE 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. - - - - Expressions are not supported as included columns since they cannot be - used in index-only scans. - - - - Currently, only the B-tree index access method supports this feature. - In B-tree indexes, the values of columns listed in the - INCLUDE clause are included in leaf tuples which - correspond to heap tuples, but are not included in upper-level - index entries used for tree navigation. - - - - - name @@ -317,6 +271,52 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] + INCLUDE + + + The optional INCLUDE clause specifies a + list of columns which will be included in the index + as non-key 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. + + + + 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. + + + + Columns listed in the INCLUDE 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. + + + + Expressions are not supported as included columns since they cannot be + used in index-only scans. + + + + Currently, only the B-tree index access method supports this feature. + In B-tree indexes, the values of columns listed in the + INCLUDE clause are included in leaf tuples which + correspond to heap tuples, but are not included in upper-level + index entries used for tree navigation. + + + + + storage_parameter 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 numeric_literal, REM - UNIQUE (column constraint) + UNIQUE index_parameters (column constraint) UNIQUE ( column_name [, ... ] ) - INCLUDE ( column_name [, ...]) (table constraint) + index_parameters (table constraint) @@ -863,22 +863,19 @@ WITH ( MODULUS numeric_literal, REM - 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 INCLUDE 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. DROP COLUMN) can cause cascaded constraint and - index deletion. + Optionally, you can adjust index properties using + INCLUDE, WITH, and + USING INDEX TABLESPACES clauses, as explained below. - PRIMARY KEY (column constraint) + PRIMARY KEY index_parameters (column constraint) PRIMARY KEY ( column_name [, ... ] ) - INCLUDE ( column_name [, ...]) (table constraint) + index_parameters (table constraint) The PRIMARY KEY constraint specifies that a column or @@ -910,13 +907,10 @@ WITH ( MODULUS numeric_literal, REM Adding a PRIMARY KEY constraint will automatically - create a unique btree index on the column or group of columns used in the - constraint. The optional INCLUDE 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. DROP COLUMN) 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 + INCLUDE, WITH, and + USING INDEX TABLESPACES clauses, as explained below. @@ -951,6 +945,9 @@ WITH ( MODULUS numeric_literal, REM can optionally specify an operator class and/or ordering options; these are described fully under . + You can also adjust index properties using INCLUDE, + WITH, and USING INDEX TABLESPACES + clauses, as explained below. @@ -1137,6 +1134,27 @@ WITH ( MODULUS numeric_literal, REM + INCLUDE ( column_name [, ... ] ) + + + The optional INCLUDE clause adds non-key columns + to the index created for UNIQUE, + PRIMARY KEY, and EXCLUDE + 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. DROP COLUMN) can cause cascaded constraint and + index deletion. + For details on non-key columns, see the INCLUDE + description in . + + + + + WITH ( storage_parameter [= value] [, ... ] ) --------------901E9CAE9DAFAE1A738A9169--