On 23.10.20 18:08, David G. Johnston wrote:
On Fri, Oct 23, 2020 at 3:18 AM Jürgen Purtz <juer...@purtz.de
<mailto:juer...@purtz.de>> wrote:
and add a link to the "CREATE INDEX" command from the chapter
preamble.
is the link necessary?
I suppose it would make more sense to add it to the previous section -
the introduction page. I do think having a link (or more than one) to
CREATE INDEX from the Indexes chapter is reader friendly. Having
links to SQL commands is never truly necessary - the reader knows a
SQL command reference exists and the name of the command allows them
to find the correct page.
David J.
I'm afraid I haven't grasped everything of your intentions and
suggestions of your last two mails.
- equal operator in standalone paragraph: ok, integrated.
- shift "create index ... using HASH" to a different place: You suggest
shifting the statement or a link to the previous (sub-)chapter "11.1
Introduction"? But there is already a "create index" example. Please
read my suggestion/modification in the first paragraph of the "11.2
Index Types" page.
- "rewording hash": I don't know what is missing here. But I have added
a few words about the nature of this index type.
Attached are two patches: a) summary against master and b) standalone of
my current changes.
--
J. Purtz
diff --git a/doc/src/sgml/0001-Add-section-headers-to-index-types-doc_1.patch b/doc/src/sgml/0001-Add-section-headers-to-index-types-doc_1.patch
new file mode 100644
index 0000000000..1919080bfc
--- /dev/null
+++ b/doc/src/sgml/0001-Add-section-headers-to-index-types-doc_1.patch
@@ -0,0 +1,183 @@
+>From 84522dc77afd1b8ce0bf111279302888d9d3edcb Mon Sep 17 00:00:00 2001
+From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= <ilm...@ilmari.org>
+Date: Fri, 31 Jul 2020 10:20:48 +0100
+Subject: [PATCH] Add section headers to index types doc
+
+This makes it easier to compare the properties of different index
+types at a glance.
+
+In passing, make the index operator lists a single line each.
+---
+ doc/src/sgml/indices.sgml | 81 +++++++++++++++++++--------------------
+ 1 file changed, 39 insertions(+), 42 deletions(-)
+
+diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml
+index 671299ff05..f22253f4c3 100644
+--- a/doc/src/sgml/indices.sgml
++++ b/doc/src/sgml/indices.sgml
+@@ -122,6 +122,9 @@
+ B-tree indexes, which fit the most common situations.
+ </para>
+
++ <sect2 id="indexes-types-btree">
++ <title>B-tree</title>
++
+ <para>
+ <indexterm>
+ <primary>index</primary>
+@@ -137,13 +140,9 @@
+ 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,6 +171,10 @@
+ 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>
+@@ -191,6 +194,10 @@
+ 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>
+@@ -210,20 +217,9 @@
+ 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,6 +242,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>
+@@ -264,14 +264,9 @@
+ 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.)
+@@ -286,6 +281,10 @@
+ corresponding operator is specified 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>
+@@ -312,12 +311,9 @@
+ <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,6 +323,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>
+@@ -348,18 +348,15 @@
+ 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>
+
+
+--
+2.27.0
+
diff --git a/doc/src/sgml/0002-Reindent-index-types-docs-after-previous-commit.patch b/doc/src/sgml/0002-Reindent-index-types-docs-after-previous-commit.patch
new file mode 100644
index 0000000000..74d05fb5a5
--- /dev/null
+++ b/doc/src/sgml/0002-Reindent-index-types-docs-after-previous-commit.patch
@@ -0,0 +1,427 @@
+From 2a75f535e60e89ed7e2276f4775395162f364e8c Mon Sep 17 00:00:00 2001
+From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= <ilm...@ilmari.org>
+Date: Tue, 1 Sep 2020 11:48:59 +0100
+Subject: [PATCH 2/2] Reindent index types docs after previous commit
+
+The previous commit wrapped each index type descripiton in a `<sect2>`
+tag, but avoided reidenting for ease of review. This commit reindents
+everything, except the `<synopsis>` and `<programlisting>` tags, which
+appear to need to be in column zero.
+---
+ doc/src/sgml/indices.sgml | 338 +++++++++++++++++++-------------------
+ 1 file changed, 169 insertions(+), 169 deletions(-)
+
+diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml
+index f22253f4c3..3e11d3917f 100644
+--- a/doc/src/sgml/indices.sgml
++++ b/doc/src/sgml/indices.sgml
+@@ -125,237 +125,237 @@
+ <sect2 id="indexes-types-btree">
+ <title>B-tree</title>
+
+- <para>
+- <indexterm>
+- <primary>index</primary>
+- <secondary>B-tree</secondary>
+- </indexterm>
+- <indexterm>
+- <primary>B-tree</primary>
+- <see>index</see>
+- </indexterm>
+- 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:
++ <para>
++ <indexterm>
++ <primary>index</primary>
++ <secondary>B-tree</secondary>
++ </indexterm>
++ <indexterm>
++ <primary>B-tree</primary>
++ <see>index</see>
++ </indexterm>
++ 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:
+
+ <synopsis>
+ < <= = >= >
+ </synopsis>
+
+- Constructs equivalent to combinations of these operators, such as
+- <literal>BETWEEN</literal> and <literal>IN</literal>, can also be implemented with
+- a B-tree index search. Also, an <literal>IS NULL</literal> or <literal>IS NOT
+- NULL</literal> condition on an index column can be used with a B-tree index.
+- </para>
++ Constructs equivalent to combinations of these operators, such as
++ <literal>BETWEEN</literal> and <literal>IN</literal>, can also be implemented with
++ a B-tree index search. Also, an <literal>IS NULL</literal> or <literal>IS NOT
++ NULL</literal> condition on an index column can be used with a B-tree index.
++ </para>
+
+- <para>
+- The optimizer can also use a B-tree index for queries involving the
+- pattern matching operators <literal>LIKE</literal> and <literal>~</literal>
+- <emphasis>if</emphasis> the pattern is a constant and is anchored to
+- the beginning of the string — for example, <literal>col LIKE
+- 'foo%'</literal> or <literal>col ~ '^foo'</literal>, but not
+- <literal>col LIKE '%bar'</literal>. However, if your database does not
+- use the C locale you will need to create the index with a special
+- operator class to support indexing of pattern-matching queries; see
+- <xref linkend="indexes-opclass"/> below. It is also possible to use
+- B-tree indexes for <literal>ILIKE</literal> and
+- <literal>~*</literal>, but only if the pattern starts with
+- non-alphabetic characters, i.e., characters that are not affected by
+- upper/lower case conversion.
+- </para>
++ <para>
++ The optimizer can also use a B-tree index for queries involving the
++ pattern matching operators <literal>LIKE</literal> and <literal>~</literal>
++ <emphasis>if</emphasis> the pattern is a constant and is anchored to
++ the beginning of the string — for example, <literal>col LIKE
++ 'foo%'</literal> or <literal>col ~ '^foo'</literal>, but not
++ <literal>col LIKE '%bar'</literal>. However, if your database does not
++ use the C locale you will need to create the index with a special
++ operator class to support indexing of pattern-matching queries; see
++ <xref linkend="indexes-opclass"/> below. It is also possible to use
++ B-tree indexes for <literal>ILIKE</literal> and
++ <literal>~*</literal>, but only if the pattern starts with
++ non-alphabetic characters, i.e., characters that are not affected by
++ upper/lower case conversion.
++ </para>
+
+- <para>
+- B-tree indexes can also be used to retrieve data in sorted order.
+- This is not always faster than a simple scan and sort, but it is
+- often helpful.
+- </para>
++ <para>
++ B-tree indexes can also be used to retrieve data in sorted order.
++ 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>
+- </indexterm>
+- <indexterm>
+- <primary>hash</primary>
+- <see>index</see>
+- </indexterm>
+- Hash 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:
++ <para>
++ <indexterm>
++ <primary>index</primary>
++ <secondary>hash</secondary>
++ </indexterm>
++ <indexterm>
++ <primary>hash</primary>
++ <see>index</see>
++ </indexterm>
++ Hash 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:
+ <synopsis>
+ CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> USING HASH (<replaceable>column</replaceable>);
+ </synopsis>
+- </para>
++ </para>
+ </sect2>
+
+ <sect2 id="indexes-type-gist">
+ <title>GiST</title>
+
+- <para>
+- <indexterm>
+- <primary>index</primary>
+- <secondary>GiST</secondary>
+- </indexterm>
+- <indexterm>
+- <primary>GiST</primary>
+- <see>index</see>
+- </indexterm>
+- 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
+- used vary depending on the indexing strategy (the <firstterm>operator
+- class</firstterm>). As an example, the standard distribution of
+- <productname>PostgreSQL</productname> includes GiST operator classes
+- for several two-dimensional geometric data types, which support indexed
+- queries using these operators:
++ <para>
++ <indexterm>
++ <primary>index</primary>
++ <secondary>GiST</secondary>
++ </indexterm>
++ <indexterm>
++ <primary>GiST</primary>
++ <see>index</see>
++ </indexterm>
++ 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
++ used vary depending on the indexing strategy (the <firstterm>operator
++ class</firstterm>). As an example, the standard distribution of
++ <productname>PostgreSQL</productname> includes GiST operator classes
++ for several two-dimensional geometric data types, which support indexed
++ queries using these operators:
+
+ <synopsis>
+ << &< &> >> <<| &<| |&> |>> @> <@ ~= &&
+ </synopsis>
+
+- (See <xref linkend="functions-geometry"/> for the meaning of
+- these operators.)
+- The GiST operator classes included in the standard distribution are
+- documented in <xref linkend="gist-builtin-opclasses-table"/>.
+- Many other GiST operator
+- classes are available in the <literal>contrib</literal> collection or as separate
+- projects. For more information see <xref linkend="gist"/>.
+- </para>
++ (See <xref linkend="functions-geometry"/> for the meaning of
++ these operators.)
++ The GiST operator classes included in the standard distribution are
++ documented in <xref linkend="gist-builtin-opclasses-table"/>.
++ Many other GiST operator
++ classes are available in the <literal>contrib</literal> collection or as separate
++ projects. For more information see <xref linkend="gist"/>.
++ </para>
+
+- <para>
+- GiST indexes are also capable of optimizing <quote>nearest-neighbor</quote>
+- searches, such as
++ <para>
++ GiST indexes are also capable of optimizing <quote>nearest-neighbor</quote>
++ searches, such as
+ <programlisting><![CDATA[
+ SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;
+ ]]>
+ </programlisting>
+- which finds the ten places closest to a given target point. The ability
+- to do this is again dependent on the particular operator class being used.
+- 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>
++ which finds the ten places closest to a given target point. The ability
++ to do this is again dependent on the particular operator class being used.
++ 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>
+- </indexterm>
+- <indexterm>
+- <primary>SP-GiST</primary>
+- <see>index</see>
+- </indexterm>
+- 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,
+- k-d trees, and radix trees (tries). As an example, the standard distribution of
+- <productname>PostgreSQL</productname> includes SP-GiST operator classes
+- for two-dimensional points, which support indexed
+- queries using these operators:
++ <para>
++ <indexterm>
++ <primary>index</primary>
++ <secondary>SP-GiST</secondary>
++ </indexterm>
++ <indexterm>
++ <primary>SP-GiST</primary>
++ <see>index</see>
++ </indexterm>
++ 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,
++ k-d trees, and radix trees (tries). As an example, the standard distribution of
++ <productname>PostgreSQL</productname> includes SP-GiST operator classes
++ for two-dimensional points, which support indexed
++ queries using these operators:
+
+ <synopsis>
+ << >> ~= <@ <^ >^
+ </synopsis>
+
+- (See <xref linkend="functions-geometry"/> for the meaning of
+- these operators.)
+- The SP-GiST operator classes included in the standard distribution are
+- documented in <xref linkend="spgist-builtin-opclasses-table"/>.
+- For more information see <xref linkend="spgist"/>.
+- </para>
++ (See <xref linkend="functions-geometry"/> for the meaning of
++ these operators.)
++ The SP-GiST operator classes included in the standard distribution are
++ documented in <xref linkend="spgist-builtin-opclasses-table"/>.
++ For more information see <xref linkend="spgist"/>.
++ </para>
+
+- <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>
+- column in <xref linkend="spgist-builtin-opclasses-table"/>.
+- </para>
++ <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>
++ 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>
+- </indexterm>
+- <indexterm>
+- <primary>GIN</primary>
+- <see>index</see>
+- </indexterm>
+- 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
+- can efficiently handle queries that test for the presence of specific
+- component values.
+- </para>
++ <para>
++ <indexterm>
++ <primary>index</primary>
++ <secondary>GIN</secondary>
++ </indexterm>
++ <indexterm>
++ <primary>GIN</primary>
++ <see>index</see>
++ </indexterm>
++ 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
++ can efficiently handle queries that test for the presence of specific
++ component values.
++ </para>
+
+- <para>
+- Like GiST and SP-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.
+- As an example, the standard distribution of
+- <productname>PostgreSQL</productname> includes a GIN operator class
+- for arrays, which supports indexed queries using these operators:
++ <para>
++ Like GiST and SP-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.
++ As an example, the standard distribution of
++ <productname>PostgreSQL</productname> includes a GIN operator class
++ for arrays, which supports indexed queries using these operators:
+
+ <synopsis>
+ <@ @> = &&
+ </synopsis>
+
+- (See <xref linkend="functions-array"/> for the meaning of
+- these operators.)
+- The GIN operator classes included in the standard distribution are
+- documented in <xref linkend="gin-builtin-opclasses-table"/>.
+- Many other GIN operator
+- classes are available in the <literal>contrib</literal> collection or as separate
+- projects. For more information see <xref linkend="gin"/>.
+- </para>
++ (See <xref linkend="functions-array"/> for the meaning of
++ these operators.)
++ The GIN operator classes included in the standard distribution are
++ documented in <xref linkend="gin-builtin-opclasses-table"/>.
++ Many other GIN operator
++ 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>
+- </indexterm>
+- <indexterm>
+- <primary>BRIN</primary>
+- <see>index</see>
+- </indexterm>
+- BRIN indexes (a shorthand for Block Range INdexes) store summaries about
+- the values stored in consecutive physical block ranges of a table.
+- 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
+- vary depending on the indexing strategy.
+- For data types that have a linear sort order, the indexed data
+- corresponds to the minimum and maximum values of the
+- values in the column for each block range. This supports indexed queries
+- using these operators:
++ <para>
++ <indexterm>
++ <primary>index</primary>
++ <secondary>BRIN</secondary>
++ </indexterm>
++ <indexterm>
++ <primary>BRIN</primary>
++ <see>index</see>
++ </indexterm>
++ BRIN indexes (a shorthand for Block Range INdexes) store summaries about
++ the values stored in consecutive physical block ranges of a table.
++ 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
++ vary depending on the indexing strategy.
++ For data types that have a linear sort order, the indexed data
++ corresponds to the minimum and maximum values of the
++ values in the column for each block range. This supports indexed queries
++ using these operators:
+
+ <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>
++ 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>
+
+--
+2.27.0
+
diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml
index 671299ff05..85d740b60a 100644
--- a/doc/src/sgml/indices.sgml
+++ b/doc/src/sgml/indices.sgml
@@ -120,246 +120,252 @@ CREATE INDEX test1_id_index ON test1 (id);
algorithm that is best suited to different types of queries.
By default, the <command>CREATE INDEX</command> command creates
B-tree indexes, which fit the most common situations.
+ The other index types are established by the keyword
+ <literal>USING</literal> followed by the type name, e.g.,
+ for a Hash index:
+<programlisting>
+CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> USING HASH (<replaceable>column</replaceable>);
+</programlisting>
</para>
- <para>
- <indexterm>
- <primary>index</primary>
- <secondary>B-tree</secondary>
- </indexterm>
- <indexterm>
- <primary>B-tree</primary>
- <see>index</see>
- </indexterm>
- 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:
+ <sect2 id="indexes-types-btree">
+ <title>B-tree</title>
- <simplelist>
- <member><literal><</literal></member>
- <member><literal><=</literal></member>
- <member><literal>=</literal></member>
- <member><literal>>=</literal></member>
- <member><literal>></literal></member>
- </simplelist>
+ <para>
+ <indexterm>
+ <primary>index</primary>
+ <secondary>B-tree</secondary>
+ </indexterm>
+ <indexterm>
+ <primary>B-tree</primary>
+ <see>index</see>
+ </indexterm>
+ 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:
- Constructs equivalent to combinations of these operators, such as
- <literal>BETWEEN</literal> and <literal>IN</literal>, can also be implemented with
- a B-tree index search. Also, an <literal>IS NULL</literal> or <literal>IS NOT
- NULL</literal> condition on an index column can be used with a B-tree index.
- </para>
+<synopsis>
+< <= = >= >
+</synopsis>
- <para>
- The optimizer can also use a B-tree index for queries involving the
- pattern matching operators <literal>LIKE</literal> and <literal>~</literal>
- <emphasis>if</emphasis> the pattern is a constant and is anchored to
- the beginning of the string — for example, <literal>col LIKE
- 'foo%'</literal> or <literal>col ~ '^foo'</literal>, but not
- <literal>col LIKE '%bar'</literal>. However, if your database does not
- use the C locale you will need to create the index with a special
- operator class to support indexing of pattern-matching queries; see
- <xref linkend="indexes-opclass"/> below. It is also possible to use
- B-tree indexes for <literal>ILIKE</literal> and
- <literal>~*</literal>, but only if the pattern starts with
- non-alphabetic characters, i.e., characters that are not affected by
- upper/lower case conversion.
- </para>
+ Constructs equivalent to combinations of these operators, such as
+ <literal>BETWEEN</literal> and <literal>IN</literal>, can also be implemented with
+ a B-tree index search. Also, an <literal>IS NULL</literal> or <literal>IS NOT
+ NULL</literal> condition on an index column can be used with a B-tree index.
+ </para>
- <para>
- B-tree indexes can also be used to retrieve data in sorted order.
- This is not always faster than a simple scan and sort, but it is
- often helpful.
- </para>
+ <para>
+ The optimizer can also use a B-tree index for queries involving the
+ pattern matching operators <literal>LIKE</literal> and <literal>~</literal>
+ <emphasis>if</emphasis> the pattern is a constant and is anchored to
+ the beginning of the string — for example, <literal>col LIKE
+ 'foo%'</literal> or <literal>col ~ '^foo'</literal>, but not
+ <literal>col LIKE '%bar'</literal>. However, if your database does not
+ use the C locale you will need to create the index with a special
+ operator class to support indexing of pattern-matching queries; see
+ <xref linkend="indexes-opclass"/> below. It is also possible to use
+ B-tree indexes for <literal>ILIKE</literal> and
+ <literal>~*</literal>, but only if the pattern starts with
+ non-alphabetic characters, i.e., characters that are not affected by
+ upper/lower case conversion.
+ </para>
+
+ <para>
+ B-tree indexes can also be used to retrieve data in sorted order.
+ 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>
+ </indexterm>
+ <indexterm>
+ <primary>hash</primary>
+ <see>index</see>
+ </indexterm>
+ Hash indexes store a 32-bit hash code derived from the
+ value of the involved 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
+ equal operator:
- <para>
- <indexterm>
- <primary>index</primary>
- <secondary>hash</secondary>
- </indexterm>
- <indexterm>
- <primary>hash</primary>
- <see>index</see>
- </indexterm>
- Hash 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:
<synopsis>
-CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> USING HASH (<replaceable>column</replaceable>);
+=
</synopsis>
- </para>
- <para>
- <indexterm>
- <primary>index</primary>
- <secondary>GiST</secondary>
- </indexterm>
- <indexterm>
- <primary>GiST</primary>
- <see>index</see>
- </indexterm>
- 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
- used vary depending on the indexing strategy (the <firstterm>operator
- class</firstterm>). As an example, the standard distribution of
- <productname>PostgreSQL</productname> includes GiST operator classes
- 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>
-
- (See <xref linkend="functions-geometry"/> for the meaning of
- these operators.)
- The GiST operator classes included in the standard distribution are
- documented in <xref linkend="gist-builtin-opclasses-table"/>.
- Many other GiST operator
- classes are available in the <literal>contrib</literal> collection or as separate
- projects. For more information see <xref linkend="gist"/>.
- </para>
-
- <para>
- GiST indexes are also capable of optimizing <quote>nearest-neighbor</quote>
- searches, such as
+ </para>
+ </sect2>
+
+ <sect2 id="indexes-type-gist">
+ <title>GiST</title>
+
+ <para>
+ <indexterm>
+ <primary>index</primary>
+ <secondary>GiST</secondary>
+ </indexterm>
+ <indexterm>
+ <primary>GiST</primary>
+ <see>index</see>
+ </indexterm>
+ 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
+ used vary depending on the indexing strategy (the <firstterm>operator
+ class</firstterm>). As an example, the standard distribution of
+ <productname>PostgreSQL</productname> includes GiST operator classes
+ for several two-dimensional geometric data types, which support indexed
+ queries using these operators:
+
+<synopsis>
+<< &< &> >> <<| &<| |&> |>> @> <@ ~= &&
+</synopsis>
+
+ (See <xref linkend="functions-geometry"/> for the meaning of
+ these operators.)
+ The GiST operator classes included in the standard distribution are
+ documented in <xref linkend="gist-builtin-opclasses-table"/>.
+ Many other GiST operator
+ classes are available in the <literal>contrib</literal> collection or as separate
+ projects. For more information see <xref linkend="gist"/>.
+ </para>
+
+ <para>
+ GiST indexes are also capable of optimizing <quote>nearest-neighbor</quote>
+ searches, such as
<programlisting><![CDATA[
SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;
]]>
</programlisting>
- which finds the ten places closest to a given target point. The ability
- to do this is again dependent on the particular operator class being used.
- 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>
+ which finds the ten places closest to a given target point. The ability
+ to do this is again dependent on the particular operator class being used.
+ 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>
- <para>
- <indexterm>
- <primary>index</primary>
- <secondary>SP-GiST</secondary>
- </indexterm>
- <indexterm>
- <primary>SP-GiST</primary>
- <see>index</see>
- </indexterm>
- 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,
- k-d trees, and radix trees (tries). As an example, the standard distribution of
- <productname>PostgreSQL</productname> includes SP-GiST operator classes
- for two-dimensional points, which support indexed
- queries using these operators:
+ <sect2 id="indexes-type-spgist">
+ <title>SP-GiST</title>
- <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>
+ <para>
+ <indexterm>
+ <primary>index</primary>
+ <secondary>SP-GiST</secondary>
+ </indexterm>
+ <indexterm>
+ <primary>SP-GiST</primary>
+ <see>index</see>
+ </indexterm>
+ 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,
+ k-d trees, and radix trees (tries). As an example, the standard distribution of
+ <productname>PostgreSQL</productname> includes SP-GiST operator classes
+ for two-dimensional points, which support indexed
+ queries using these operators:
- (See <xref linkend="functions-geometry"/> for the meaning of
- these operators.)
- The SP-GiST operator classes included in the standard distribution are
- documented in <xref linkend="spgist-builtin-opclasses-table"/>.
- For more information see <xref linkend="spgist"/>.
- </para>
+<synopsis>
+<< >> ~= <@ <^ >^
+</synopsis>
- <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>
- column in <xref linkend="spgist-builtin-opclasses-table"/>.
- </para>
+ (See <xref linkend="functions-geometry"/> for the meaning of
+ these operators.)
+ The SP-GiST operator classes included in the standard distribution are
+ documented in <xref linkend="spgist-builtin-opclasses-table"/>.
+ For more information see <xref linkend="spgist"/>.
+ </para>
- <para>
- <indexterm>
- <primary>index</primary>
- <secondary>GIN</secondary>
- </indexterm>
- <indexterm>
- <primary>GIN</primary>
- <see>index</see>
- </indexterm>
- 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
- can efficiently handle queries that test for the presence of specific
- component values.
- </para>
+ <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>
+ column in <xref linkend="spgist-builtin-opclasses-table"/>.
+ </para>
+ </sect2>
- <para>
- Like GiST and SP-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.
- As an example, the standard distribution of
- <productname>PostgreSQL</productname> includes a GIN operator class
- for arrays, which supports indexed queries using these operators:
+ <sect2 id="indexes-types-gin">
+ <title>GIN</title>
- <simplelist>
- <member><literal><@</literal></member>
- <member><literal>@></literal></member>
- <member><literal>=</literal></member>
- <member><literal>&&</literal></member>
- </simplelist>
+ <para>
+ <indexterm>
+ <primary>index</primary>
+ <secondary>GIN</secondary>
+ </indexterm>
+ <indexterm>
+ <primary>GIN</primary>
+ <see>index</see>
+ </indexterm>
+ 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
+ can efficiently handle queries that test for the presence of specific
+ component values.
+ </para>
- (See <xref linkend="functions-array"/> for the meaning of
- these operators.)
- The GIN operator classes included in the standard distribution are
- documented in <xref linkend="gin-builtin-opclasses-table"/>.
- Many other GIN operator
- classes are available in the <literal>contrib</literal> collection or as separate
- projects. For more information see <xref linkend="gin"/>.
- </para>
+ <para>
+ Like GiST and SP-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.
+ As an example, the standard distribution of
+ <productname>PostgreSQL</productname> includes a GIN operator class
+ for arrays, which supports indexed queries using these operators:
- <para>
- <indexterm>
- <primary>index</primary>
- <secondary>BRIN</secondary>
- </indexterm>
- <indexterm>
- <primary>BRIN</primary>
- <see>index</see>
- </indexterm>
- BRIN indexes (a shorthand for Block Range INdexes) store summaries about
- the values stored in consecutive physical block ranges of a table.
- 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
- vary depending on the indexing strategy.
- For data types that have a linear sort order, the indexed data
- corresponds to the minimum and maximum values of the
- 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>
-
- 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>
+<synopsis>
+<@ @> = &&
+</synopsis>
+
+ (See <xref linkend="functions-array"/> for the meaning of
+ these operators.)
+ The GIN operator classes included in the standard distribution are
+ documented in <xref linkend="gin-builtin-opclasses-table"/>.
+ Many other GIN operator
+ 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>
+ </indexterm>
+ <indexterm>
+ <primary>BRIN</primary>
+ <see>index</see>
+ </indexterm>
+ BRIN indexes (a shorthand for Block Range INdexes) store summaries about
+ the values stored in consecutive physical block ranges of a table.
+ 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
+ vary depending on the indexing strategy.
+ For data types that have a linear sort order, the indexed data
+ corresponds to the minimum and maximum values of the
+ values in the column for each block range. This supports indexed queries
+ using these operators:
+
+<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>
diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml
index 3e11d3917f..85d740b60a 100644
--- a/doc/src/sgml/indices.sgml
+++ b/doc/src/sgml/indices.sgml
@@ -120,6 +120,12 @@ CREATE INDEX test1_id_index ON test1 (id);
algorithm that is best suited to different types of queries.
By default, the <command>CREATE INDEX</command> command creates
B-tree indexes, which fit the most common situations.
+ The other index types are established by the keyword
+ <literal>USING</literal> followed by the type name, e.g.,
+ for a Hash index:
+<programlisting>
+CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> USING HASH (<replaceable>column</replaceable>);
+</programlisting>
</para>
<sect2 id="indexes-types-btree">
@@ -185,14 +191,17 @@ CREATE INDEX test1_id_index ON test1 (id);
<primary>hash</primary>
<see>index</see>
</indexterm>
- Hash indexes can only handle simple equality comparisons.
+ Hash indexes store a 32-bit hash code derived from the
+ value of the involved 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>
