B-tree, Hash, GiST, SP-GiST, GIN and BRIN.

Each index type uses a different

algorithm that is best suited to different types of queries.

By default, the <command>CREATE INDEX</command> command creates

By default, the <link linkend="sql-createindex"><command>CREATE

INDEX</command></link> command creates

B-tree indexes, which fit the most common situations.

The other index types are selected by writing the keyword

<literal>USING</literal> followed by the index type name.

For example, to create a Hash index:

<programlisting>

CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> USING HASH (<replaceable>column</replaceable>);

</programlisting>

</para>

<para>

<sect2 id="indexes-types-btree">

<title>B-Tree</title>

<indexterm>

<primary>index</primary>

<secondary>B-tree</secondary>

<secondary>B-Tree</secondary>

</indexterm>

<indexterm>

<primary>B-tree</primary>

<primary>B-Tree</primary>

<see>index</see>

</indexterm>

<para>

B-trees can handle equality and range queries on data that can be sorted

into some ordering.

In particular, the <productname>PostgreSQL</productname> query planner

will consider using a B-tree index whenever an indexed column is

involved in a comparison using one of these operators:

<simplelist>

<member><literal>&lt;</literal></member>

<member><literal>&lt;=</literal></member>

<member><literal>=</literal></member>

<member><literal>&gt;=</literal></member>

<member><literal>&gt;</literal></member>

</simplelist>

<synopsis>

&lt; &nbsp; &lt;= &nbsp; = &nbsp; &gt;= &nbsp; &gt;

</synopsis>

Constructs equivalent to combinations of these operators, such as

<literal>BETWEEN</literal> and <literal>IN</literal>, can also be implemented with

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>

<primary>hash</primary>

<see>index</see>

</indexterm>

Hash indexes can only handle simple equality comparisons.

<para>

Hash indexes store a 32-bit hash code derived from the

value of the indexed column. Hence,

such indexes can only handle simple equality comparisons.

The query planner will consider using a hash index whenever an

indexed column is involved in a comparison using the

<literal>=</literal> operator.

equal operator:

<synopsis>

</synopsis>

</para>

</sect2>

<sect2 id="indexes-type-gist">

<title>GiST</title>

<para>

<indexterm>

<primary>index</primary>

<secondary>GiST</secondary>

<primary>GiST</primary>

<see>index</see>

</indexterm>

<para>

GiST indexes are not a single kind of index, but rather an infrastructure

within which many different indexing strategies can be implemented.

Accordingly, the particular operators with which a GiST index can be

for several two-dimensional geometric data types, which support indexed

queries using these operators:

<simplelist>

<member><literal>&lt;&lt;</literal></member>

<member><literal>&amp;&lt;</literal></member>

<member><literal>&amp;&gt;</literal></member>

<member><literal>&gt;&gt;</literal></member>

<member><literal>&lt;&lt;|</literal></member>

<member><literal>&amp;&lt;|</literal></member>

<member><literal>|&amp;&gt;</literal></member>

<member><literal>|&gt;&gt;</literal></member>

<member><literal>@&gt;</literal></member>

<member><literal>&lt;@</literal></member>

<member><literal>~=</literal></member>

<member><literal>&amp;&amp;</literal></member>

</simplelist>

<synopsis>

&lt;&lt; &nbsp; &amp;&lt; &nbsp; &amp;&gt; &nbsp; &gt;&gt; &nbsp; &lt;&lt;| &nbsp; &amp;&lt;| &nbsp; |&amp;&gt; &nbsp; |&gt;&gt; &nbsp; @&gt; &nbsp; &lt;@ &nbsp; ~= &nbsp; &amp;&amp;

</synopsis>

(See <xref linkend="functions-geometry"/> for the meaning of

these operators.)

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>

<primary>SP-GiST</primary>

<see>index</see>

</indexterm>

<para>

SP-GiST indexes, like GiST indexes, offer an infrastructure that supports

various kinds of searches. SP-GiST permits implementation of a wide range

of different non-balanced disk-based data structures, such as quadtrees,

for two-dimensional points, which support indexed

queries using these operators:

<simplelist>

<member><literal>&lt;&lt;</literal></member>

<member><literal>&gt;&gt;</literal></member>

<member><literal>~=</literal></member>

<member><literal>&lt;@</literal></member>

<member><literal>&lt;^</literal></member>

<member><literal>&gt;^</literal></member>

</simplelist>

<synopsis>

&lt;&lt; &nbsp; &gt;&gt; &nbsp; ~= &nbsp; &lt;@ &nbsp; &lt;&lt;| &nbsp; |&gt;&gt;

</synopsis>

(See <xref linkend="functions-geometry"/> for the meaning of

these operators.)

<para>

Like GiST, SP-GiST supports <quote>nearest-neighbor</quote> searches.

For SP-GiST operator classes that support distance ordering, the

corresponding operator isspecified in the <quote>Ordering Operators</quote>

corresponding operator islisted 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>

<primary>GIN</primary>

<see>index</see>

</indexterm>

<para>

GIN indexes are <quote>inverted indexes</quote> which are appropriate for

data values that contain multiple component values, such as arrays. An

inverted index contains a separate entry for each component value, and

<productname>PostgreSQL</productname> includes a GIN operator class

for arrays, which supports indexed queries using these operators:

<simplelist>

<member><literal>&lt;@</literal></member>

<member><literal>@&gt;</literal></member>

<member><literal>=</literal></member>

<member><literal>&amp;&amp;</literal></member>

</simplelist>

<synopsis>

&lt;@ &nbsp; @&gt; &nbsp; = &nbsp; &amp;&amp;

</synopsis>

(See <xref linkend="functions-array"/> for the meaning of

these operators.)

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>

<primary>BRIN</primary>

<see>index</see>

</indexterm>

<para>

BRIN indexes (a shorthand for Block Range INdexes) store summaries about

the values stored in consecutive physical block ranges of a table.

Thus, they are most effective for columns whose values are well-correlated

with the physical order of the table rows.

Like GiST, SP-GiST and GIN,

BRIN can support many different indexing strategies,

and the particular operators with which a BRIN index can be used

values in the column for each block range. This supports indexed queries

using these operators:

<simplelist>

<member><literal>&lt;</literal></member>

<member><literal>&lt;=</literal></member>

<member><literal>=</literal></member>

<member><literal>&gt;=</literal></member>

<member><literal>&gt;</literal></member>

</simplelist>

<synopsis>

&lt; &nbsp; &lt;= &nbsp; = &nbsp; &gt;= &nbsp; &gt;

</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>