From: ilmari@ilmari.org
To: pgsql-hackers@postgresql.org
Subject: [PATCH] Add section headings to index types doc
Date: Fri, 31 Jul 2020 10:30:59 +0100
Message-ID: <877dukhvzg.fsf@wibble.ilmari.org> (raw)
Hi hackers,
Every time I have to look up what kinds of operations each index type is
suitable for, I get annoyed by the index types page being virtually
unskimmable due to not having headings for each index type.
Attached is a patch that adds <sect2> tags for each index type to make
it easier to see where the description of each one starts.
- ilmari
--
"The surreality of the universe tends towards a maximum" -- Skud's Law
"Never formulate a law or axiom that you're not prepared to live with
the consequences of." -- Skud's Meta-Law
Attachments:
[text/x-diff] 0001-Add-section-headers-to-index-types-doc.patch (20.1K, ../877dukhvzg.fsf@wibble.ilmari.org/2-0001-Add-section-headers-to-index-types-doc.patch)
download | inline diff:
From 57d9550b9286e7f6298763bb51d45393aa2eab4e 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.
---
doc/src/sgml/indices.sgml | 460 ++++++++++++++++++++------------------
1 file changed, 242 insertions(+), 218 deletions(-)
diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgmlindex 28adaba72d..332d161547 100644--- a/doc/src/sgml/indices.sgml+++ b/doc/src/sgml/indices.sgml@@ -122,244 +122,268 @@
B-tree indexes, which fit the most common situations.
</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>+ <simplelist>+ <member><literal><</literal></member>+ <member><literal><=</literal></member>+ <member><literal>=</literal></member>+ <member><literal>>=</literal></member>+ <member><literal>></literal></member>+ </simplelist>- <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>- <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>+ 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>- <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:+ <sect2 id="indexes-types-hash">+ <title>Hash</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>- <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>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>+ </sect2>- (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>+ <sect2 id="indexes-type-gist">+ <title>GiST</title>- <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>+ <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>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:+ <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>- <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>+ (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 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>+ 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>+ </sect2>- <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 id="indexes-type-spgist">+ <title>SP-GiST</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>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>- 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:+ <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>- <simplelist>- <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 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-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, 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>- <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:+ <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>- <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>- 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>+ <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:++ <simplelist>+ <member><literal><@</literal></member>+ <member><literal>@></literal></member>+ <member><literal>=</literal></member>+ <member><literal>&&</literal></member>+ </simplelist>++ (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:++ <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>+ </sect2>
</sect1>
--
2.27.0
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: ilmari@ilmari.org
Subject: Re: [PATCH] Add section headings to index types doc
In-Reply-To: <877dukhvzg.fsf@wibble.ilmari.org>
* 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