From: Jürgen Purtz <juergen@purtz.de>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Dagfinn Ilmari Mannsåker <ilmari@ilmari.org>
Cc: Michael Paquier <michael@paquier.xyz>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: [PATCH] Add section headings to index types doc
Date: Sun, 25 Oct 2020 10:40:07 +0100
Message-ID: <dade356d-057a-d488-709b-bdb17713dd3c@purtz.de> (raw)
In-Reply-To: <CAKFQuwaQNTZnpEiGWY4=-QyiR8dEaD-_894O4Dwdi+v6rBiBeQ@mail.gmail.com>
References: <877dukhvzg.fsf@wibble.ilmari.org>
<2268714.1596457003@sss.pgh.pa.us>
<159706393772.790.11301900224459454375.pgcf@coridan.postgresql.org>
<20200930063423.GG1996@paquier.xyz>
<87pn63ecc0.fsf@wibble.ilmari.org>
<CAKFQuwYEx+4qwrLJ0ne6vaJef1eurrvL2rv7TDcgcpfLOQA02Q@mail.gmail.com>
<8d0b636d-95e1-e802-fa8f-98708cc2d75b@purtz.de>
<CAKFQuwaQNTZnpEiGWY4=-QyiR8dEaD-_894O4Dwdi+v6rBiBeQ@mail.gmail.com>
On 23.10.20 18:08, David G. Johnston wrote:
> On Fri, Oct 23, 2020 at 3:18 AM Jürgen Purtz <juergen@purtz.de
> <mailto:juergen@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
Attachments:download all (2 files, .tar.gz)0003-index-types-vs-master.patch (text/x-patch, 42.6K) downloaddiff --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.patchnew file mode 100644index 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?= <ilmari@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.patchnew file mode 100644index 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?= <ilmari@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.sgmlindex 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>
0003-index-types.patch (text/x-patch, 1.5K) downloaddiff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgmlindex 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>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: juergen@purtz.de, david.g.johnston@gmail.com, ilmari@ilmari.org, michael@paquier.xyz, pgsql-hackers@lists.postgresql.org
Subject: Re: [PATCH] Add section headings to index types doc
In-Reply-To: <dade356d-057a-d488-709b-bdb17713dd3c@purtz.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox