diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/indices.sgml | 126 |
1 files changed, 70 insertions, 56 deletions
diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml index 671299ff059..623962d1d89 100644 --- a/doc/src/sgml/indices.sgml +++ b/doc/src/sgml/indices.sgml @@ -118,32 +118,39 @@ CREATE INDEX test1_id_index ON test1 (id); B-tree, Hash, GiST, SP-GiST, GIN and BRIN. Each index type uses a different algorithm that is best suited to different types of queries. - By default, the <command>CREATE INDEX</command> command creates + By default, the <link linkend="sql-createindex"><command>CREATE + INDEX</command></link> command creates B-tree indexes, which fit the most common situations. + The other index types are selected by writing the keyword + <literal>USING</literal> followed by the index type name. + For example, to create a Hash index: +<programlisting> +CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> USING HASH (<replaceable>column</replaceable>); +</programlisting> </para> - <para> + <sect2 id="indexes-types-btree"> + <title>B-Tree</title> + <indexterm> <primary>index</primary> - <secondary>B-tree</secondary> + <secondary>B-Tree</secondary> </indexterm> <indexterm> - <primary>B-tree</primary> + <primary>B-Tree</primary> <see>index</see> </indexterm> + + <para> B-trees can handle equality and range queries on data that can be sorted into some ordering. In particular, the <productname>PostgreSQL</productname> query planner will consider using a B-tree index whenever an indexed column is involved in a comparison using one of these operators: - <simplelist> - <member><literal><</literal></member> - <member><literal><=</literal></member> - <member><literal>=</literal></member> - <member><literal>>=</literal></member> - <member><literal>></literal></member> - </simplelist> +<synopsis> +< <= = >= > +</synopsis> Constructs equivalent to combinations of these operators, such as <literal>BETWEEN</literal> and <literal>IN</literal>, can also be implemented with @@ -172,8 +179,11 @@ CREATE INDEX test1_id_index ON test1 (id); This is not always faster than a simple scan and sort, but it is often helpful. </para> + </sect2> + + <sect2 id="indexes-types-hash"> + <title>Hash</title> - <para> <indexterm> <primary>index</primary> <secondary>hash</secondary> @@ -182,17 +192,24 @@ CREATE INDEX test1_id_index ON test1 (id); <primary>hash</primary> <see>index</see> </indexterm> - Hash indexes can only handle simple equality comparisons. + + <para> + Hash indexes store a 32-bit hash code derived from the + value of the indexed column. Hence, + such indexes can only handle simple equality comparisons. The query planner will consider using a hash index whenever an indexed column is involved in a comparison using the - <literal>=</literal> operator. - The following command is used to create a hash index: + equal operator: + <synopsis> -CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> USING HASH (<replaceable>column</replaceable>); += </synopsis> </para> + </sect2> + + <sect2 id="indexes-type-gist"> + <title>GiST</title> - <para> <indexterm> <primary>index</primary> <secondary>GiST</secondary> @@ -201,6 +218,8 @@ CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> <primary>GiST</primary> <see>index</see> </indexterm> + + <para> GiST indexes are not a single kind of index, but rather an infrastructure within which many different indexing strategies can be implemented. Accordingly, the particular operators with which a GiST index can be @@ -210,20 +229,9 @@ CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> for several two-dimensional geometric data types, which support indexed queries using these operators: - <simplelist> - <member><literal><<</literal></member> - <member><literal>&<</literal></member> - <member><literal>&></literal></member> - <member><literal>>></literal></member> - <member><literal><<|</literal></member> - <member><literal>&<|</literal></member> - <member><literal>|&></literal></member> - <member><literal>|>></literal></member> - <member><literal>@></literal></member> - <member><literal><@</literal></member> - <member><literal>~=</literal></member> - <member><literal>&&</literal></member> - </simplelist> +<synopsis> +<< &< &> >> <<| &<| |&> |>> @> <@ ~= && +</synopsis> (See <xref linkend="functions-geometry"/> for the meaning of these operators.) @@ -246,8 +254,11 @@ SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10; In <xref linkend="gist-builtin-opclasses-table"/>, operators that can be used in this way are listed in the column <quote>Ordering Operators</quote>. </para> + </sect2> + + <sect2 id="indexes-type-spgist"> + <title>SP-GiST</title> - <para> <indexterm> <primary>index</primary> <secondary>SP-GiST</secondary> @@ -256,6 +267,8 @@ SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10; <primary>SP-GiST</primary> <see>index</see> </indexterm> + + <para> SP-GiST indexes, like GiST indexes, offer an infrastructure that supports various kinds of searches. SP-GiST permits implementation of a wide range of different non-balanced disk-based data structures, such as quadtrees, @@ -264,14 +277,9 @@ SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10; for two-dimensional points, which support indexed queries using these operators: - <simplelist> - <member><literal><<</literal></member> - <member><literal>>></literal></member> - <member><literal>~=</literal></member> - <member><literal><@</literal></member> - <member><literal><^</literal></member> - <member><literal>>^</literal></member> - </simplelist> +<synopsis> +<< >> ~= <@ <<| |>> +</synopsis> (See <xref linkend="functions-geometry"/> for the meaning of these operators.) @@ -283,11 +291,14 @@ SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10; <para> Like GiST, SP-GiST supports <quote>nearest-neighbor</quote> searches. For SP-GiST operator classes that support distance ordering, the - corresponding operator is specified in the <quote>Ordering Operators</quote> + corresponding operator is listed in the <quote>Ordering Operators</quote> column in <xref linkend="spgist-builtin-opclasses-table"/>. </para> + </sect2> + + <sect2 id="indexes-types-gin"> + <title>GIN</title> - <para> <indexterm> <primary>index</primary> <secondary>GIN</secondary> @@ -296,6 +307,8 @@ SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10; <primary>GIN</primary> <see>index</see> </indexterm> + + <para> GIN indexes are <quote>inverted indexes</quote> which are appropriate for data values that contain multiple component values, such as arrays. An inverted index contains a separate entry for each component value, and @@ -312,12 +325,9 @@ SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10; <productname>PostgreSQL</productname> includes a GIN operator class for arrays, which supports indexed queries using these operators: - <simplelist> - <member><literal><@</literal></member> - <member><literal>@></literal></member> - <member><literal>=</literal></member> - <member><literal>&&</literal></member> - </simplelist> +<synopsis> +<@ @> = && +</synopsis> (See <xref linkend="functions-array"/> for the meaning of these operators.) @@ -327,8 +337,11 @@ SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10; classes are available in the <literal>contrib</literal> collection or as separate projects. For more information see <xref linkend="gin"/>. </para> + </sect2> + + <sect2 id="indexes-types-brin"> + <title>BRIN</title> - <para> <indexterm> <primary>index</primary> <secondary>BRIN</secondary> @@ -337,8 +350,12 @@ SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10; <primary>BRIN</primary> <see>index</see> </indexterm> + + <para> BRIN indexes (a shorthand for Block Range INdexes) store summaries about the values stored in consecutive physical block ranges of a table. + Thus, they are most effective for columns whose values are well-correlated + with the physical order of the table rows. Like GiST, SP-GiST and GIN, BRIN can support many different indexing strategies, and the particular operators with which a BRIN index can be used @@ -348,18 +365,15 @@ SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10; values in the column for each block range. This supports indexed queries using these operators: - <simplelist> - <member><literal><</literal></member> - <member><literal><=</literal></member> - <member><literal>=</literal></member> - <member><literal>>=</literal></member> - <member><literal>></literal></member> - </simplelist> +<synopsis> +< <= = >= > +</synopsis> The BRIN operator classes included in the standard distribution are documented in <xref linkend="brin-builtin-opclasses-table"/>. For more information see <xref linkend="brin"/>. </para> + </sect2> </sect1> |