postgres.git / summary / log / commit / refs
commit 85b4ba73423b480902206ca04330c1cbea371c3c
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Wed Nov 25 19:04:28 2020 +0000
Doc: minor improvements for section 11.2 "Index Types".
Break the per-index-type discussions into <sect2>'s so as to make
them more visually separate and easier to find. Improve the markup,
and make a couple of small wording adjustments.
This also fixes one stray reference to the now-deprecated point
operators <^ and >^.
Dagfinn Ilmari Mannsåker, reviewed by David Johnston and Jürgen Purtz
Discussion: https://postgr.es/m/877dukhvzg.fsf@wibble.ilmari.org
doc/src/sgml/indices.sgml | 126 +++++++++++++++++++++++++---------------------
1 file 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>
[parent: 2432b1a04087]