From 27cb66fdfe862f395cefa0d498b681ce142f59d8 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 11 Jul 2008 21:06:29 +0000 Subject: Multi-column GIN indexes. Teodor Sigaev --- doc/src/sgml/indices.sgml | 37 ++++++++++++++++++++++--------------- doc/src/sgml/ref/create_index.sgml | 6 +++--- 2 files changed, 25 insertions(+), 18 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml index 3de05419226..2ab713c39be 100644 --- a/doc/src/sgml/indices.sgml +++ b/doc/src/sgml/indices.sgml @@ -1,4 +1,4 @@ - + Indexes @@ -198,7 +198,7 @@ CREATE INDEX name ON table after a database crash. For these reasons, hash index use is presently discouraged. - + @@ -250,9 +250,9 @@ CREATE INDEX name ON table GIN indexes are inverted indexes which can handle values that contain more than one key, arrays for example. Like GiST, GIN can support - many different user-defined indexing strategies and the particular - operators with which a GIN index can be used vary depending on the - indexing strategy. + many different user-defined indexing strategies and the particular + operators with which a GIN index can be used vary depending on the + indexing strategy. As an example, the standard distribution of PostgreSQL includes GIN operator classes for one-dimensional arrays, which support indexed @@ -306,7 +306,7 @@ CREATE INDEX test2_mm_idx ON test2 (major, minor); - Currently, only the B-tree and GiST index types support multicolumn + Currently, only the B-tree, GiST and GIN index types support multicolumn indexes. Up to 32 columns can be specified. (This limit can be altered when building PostgreSQL; see the file pg_config_manual.h.) @@ -336,14 +336,21 @@ CREATE INDEX test2_mm_idx ON test2 (major, minor); A multicolumn GiST index can be used with query conditions that - involve any subset of the index's columns. Conditions on additional - columns restrict the entries returned by the index, but the condition on - the first column is the most important one for determining how much of - the index needs to be scanned. A GiST index will be relatively - ineffective if its first column has only a few distinct values, even if + involve any subset of the index's columns. Conditions on additional + columns restrict the entries returned by the index, but the condition on + the first column is the most important one for determining how much of + the index needs to be scanned. A GiST index will be relatively + ineffective if its first column has only a few distinct values, even if there are many distinct values in additional columns. + + A multicolumn GIN index can be used with query conditions that + involve any subset of the index's columns. Unlike B-tree or GiST, + index search effectiveness is the same regardless of which index column(s) + the query conditions use. + + Of course, each column must be used with operators appropriate to the index type; clauses that involve other operators will not be considered. @@ -551,7 +558,7 @@ CREATE UNIQUE INDEX name ON table PostgreSQL automatically creates a unique index when a unique constraint or a primary key is defined for a table. - The index covers the columns that make up the primary key or unique + The index covers the columns that make up the primary key or unique columns (a multicolumn index, if appropriate), and is the mechanism that enforces the constraint. @@ -798,9 +805,9 @@ SELECT * FROM orders WHERE order_nr = 3501; or the index will not be recognized to be usable. Matching takes place at query planning time, not at run time. As a result, parameterized query clauses will not work with a partial index. For - example a prepared query with a parameter might specify - x < ? which will never imply - x < 2 for all possible values of the parameter. + example a prepared query with a parameter might specify + x < ? which will never imply + x < 2 for all possible values of the parameter. diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml index 32b7bbebd99..030fa1bb004 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -1,5 +1,5 @@ @@ -394,7 +394,7 @@ Indexes: - Currently, only the B-tree and GiST index methods support + Currently, only the B-tree, GiST and GIN index methods support multicolumn indexes. Up to 32 fields can be specified by default. (This limit can be altered when building PostgreSQL.) Only B-tree currently @@ -423,7 +423,7 @@ Indexes: the optional clauses ASC, DESC, NULLS FIRST, and/or NULLS LAST can be specified to reverse the normal sort direction of the index. Since an ordered index can be - scanned either forward or backward, it is not normally useful to create a + scanned either forward or backward, it is not normally useful to create a single-column DESC index — that sort ordering is already available with a regular index. The value of these options is that multicolumn indexes can be created that match the sort ordering requested -- cgit v1.2.3