From 386e3d7609c49505e079c40c65919d99feb82505 Mon Sep 17 00:00:00 2001 From: Teodor Sigaev Date: Fri, 8 Apr 2016 19:31:49 +0300 Subject: CREATE INDEX ... INCLUDING (column[, ...]) Now indexes (but only B-tree for now) can contain "extra" column(s) which doesn't participate in index structure, they are just stored in leaf tuples. It allows to use index only scan by using single index instead of two or more indexes. Author: Anastasia Lubennikova with minor editorializing by me Reviewers: David Rowley, Peter Geoghegan, Jeff Janes --- doc/src/sgml/catalogs.sgml | 8 ++++++++ doc/src/sgml/indexam.sgml | 5 ++++- doc/src/sgml/indices.sgml | 7 +++++-- doc/src/sgml/ref/create_index.sgml | 41 +++++++++++++++++++++++++++++++++++++- doc/src/sgml/ref/create_table.sgml | 36 ++++++++++++++++++++++++++++----- 5 files changed, 88 insertions(+), 9 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index d6b60db0744..342d5ecb037 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -3557,6 +3557,14 @@ pg_class.relnatts) + + indnkeyatts + int2 + + The number of key columns in the index. "Key columns" are ordinary + index columns in contrast with "included" columns. + + indisunique bool diff --git a/doc/src/sgml/indexam.sgml b/doc/src/sgml/indexam.sgml index b36889b856b..340904142e4 100644 --- a/doc/src/sgml/indexam.sgml +++ b/doc/src/sgml/indexam.sgml @@ -117,6 +117,8 @@ typedef struct IndexAmRoutine bool amclusterable; /* does AM handle predicate locks? */ bool ampredlocks; + /* does AM support columns included with clause INCLUDING? */ + bool amcaninclude; /* type of data stored in index, or InvalidOid if variable */ Oid amkeytype; @@ -858,7 +860,8 @@ amrestrpos (IndexScanDesc scan); using unique indexes, which are indexes that disallow multiple entries with identical keys. An access method that supports this feature sets amcanunique true. - (At present, only b-tree supports it.) + (At present, only B-tree supports it.) Columns which are present in the + INCLUDING clause are not used to enforce uniqueness. diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml index 5f72e7d0735..7c4fdc0403f 100644 --- a/doc/src/sgml/indices.sgml +++ b/doc/src/sgml/indices.sgml @@ -643,7 +643,8 @@ CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST); Indexes can also be used to enforce uniqueness of a column's value, or the uniqueness of the combined values of more than one column. -CREATE UNIQUE INDEX name ON table (column , ...); +CREATE UNIQUE INDEX name ON table (column , ...) +INCLUDING (column , ...); Currently, only B-tree indexes can be declared unique. @@ -652,7 +653,9 @@ CREATE UNIQUE INDEX name ON tableINCLUDING aren't used to enforce constraints (UNIQUE, + PRIMARY KEY, etc). diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml index 7dee4055dbc..25b3c26f551 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -23,6 +23,7 @@ PostgreSQL documentation CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ] ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ) + [ INCLUDING ( column_name [, ...] ) ] [ WITH ( storage_parameter = value [, ... ] ) ] [ TABLESPACE tablespace_name ] [ WHERE predicate ] @@ -138,6 +139,35 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] + + INCLUDING + + + An optional INCLUDING clause allows a list of columns to be + specified which will be included in the index, in the non-key portion of + the index. Columns which are part of this clause cannot also exist in + the key columns portion of the index, and vice versa. The + INCLUDING columns exist solely to allow more queries to + benefit from index-only scans by including certain + columns in the index, the value of which would otherwise have to be + obtained by reading + the table's heap. Having these columns in the INCLUDING + clause in some cases allows PostgreSQL to skip the heap + read completely. This also allows UNIQUE indexes to be + defined on one set of columns, which can include another set of column + in the INCLUDING clause, on which the uniqueness is not + enforced upon. It's the same with other constraints (PRIMARY KEY and + EXCLUDE). This can also can be used for non-unique indexes as any + columns which are not required for the searching or ordering of records + can be included in the INCLUDING clause, which can slightly + reduce the size of the index, due to storing included attributes only + in leaf index pages. Currently, only the B-tree access method supports + this feature. Expressions as included columns are not supported since + they cannot be used in index-only scan. + + + + name @@ -599,13 +629,22 @@ Indexes: Examples - To create a B-tree index on the column title in + To create a unique B-tree index on the column title in the table films: CREATE UNIQUE INDEX title_idx ON films (title); + + To create a unique B-tree index on the column title + and included columns director and rating + in the table films: + +CREATE UNIQUE INDEX title_idx ON films (title) INCLUDING (director, rating); + + + To create an index on the expression lower(title), allowing efficient case-insensitive searches: diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index d1807ed0dbf..473023e88e4 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -59,8 +59,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI [ CONSTRAINT constraint_name ] { CHECK ( expression ) [ NO INHERIT ] | - UNIQUE ( column_name [, ... ] ) index_parameters | - PRIMARY KEY ( column_name [, ... ] ) index_parameters | + UNIQUE ( column_name [, ... ] ) index_parameters INCLUDING (column_name [, ...]) | + PRIMARY KEY ( column_name [, ... ] ) index_parameters INCLUDING (column_name [, ...]) | EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] | FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } @@ -476,8 +476,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI UNIQUE (column constraint) - UNIQUE ( column_name [, ... ] ) (table constraint) - + UNIQUE ( column_name [, ... ] ) + INCLUDING ( column_name [, ...]) (table constraint) The UNIQUE constraint specifies that a @@ -498,12 +498,26 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI primary key constraint defined for the table. (Otherwise it would just be the same constraint listed twice.) + + + Adding a unique constraint will automatically create a unique btree + index on the column or group of columns used in the constraint. + Optional clause INCLUDING allows to add into the index + a portion of columns on which the uniqueness is not enforced upon. + Note, that althogh constraint is not enforced upon included columns, it still + depends on them. Consequently, some operations on these columns (e.g. DROP COLUMN) + can cause cascade constraint and index deletion. + See paragraph about INCLUDING in + for more information. + + PRIMARY KEY (column constraint) - PRIMARY KEY ( column_name [, ... ] ) (table constraint) + PRIMARY KEY ( column_name [, ... ] ) + INCLUDING ( column_name [, ...]) (table constraint) The PRIMARY KEY constraint specifies that a column or @@ -526,6 +540,18 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI about the design of the schema, since a primary key implies that other tables can rely on this set of columns as a unique identifier for rows. + + + Adding a PRIMARY KEY constraint will automatically create a unique btree + index on the column or group of columns used in the constraint. + Optional clause INCLUDING allows to add into the index + a portion of columns on which the constraint is not enforced upon. + Note, that althogh constraint is not enforced upon included columns, it still + depends on them. Consequently, some operations on these columns (e.g. DROP COLUMN) + can cause cascade constraint and index deletion. + See paragraph about INCLUDING in + for more information. + -- cgit v1.2.3