1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>11.2. Index Types</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="indexes-intro.html" title="11.1. Introduction" /><link rel="next" href="indexes-multicolumn.html" title="11.3. Multicolumn Indexes" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">11.2. Index Types</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="indexes-intro.html" title="11.1. Introduction">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="indexes.html" title="Chapter 11. Indexes">Up</a></td><th width="60%" align="center">Chapter 11. Indexes</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.7 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="indexes-multicolumn.html" title="11.3. Multicolumn Indexes">Next</a></td></tr></table><hr /></div><div class="sect1" id="INDEXES-TYPES"><div class="titlepage"><div><div><h2 class="title" style="clear: both">11.2. Index Types</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="indexes-types.html#INDEXES-TYPES-BTREE">11.2.1. B-Tree</a></span></dt><dt><span class="sect2"><a href="indexes-types.html#INDEXES-TYPES-HASH">11.2.2. Hash</a></span></dt><dt><span class="sect2"><a href="indexes-types.html#INDEXES-TYPE-GIST">11.2.3. GiST</a></span></dt><dt><span class="sect2"><a href="indexes-types.html#INDEXES-TYPE-SPGIST">11.2.4. SP-GiST</a></span></dt><dt><span class="sect2"><a href="indexes-types.html#INDEXES-TYPES-GIN">11.2.5. GIN</a></span></dt><dt><span class="sect2"><a href="indexes-types.html#INDEXES-TYPES-BRIN">11.2.6. BRIN</a></span></dt></dl></div><p>
<span class="productname">PostgreSQL</span> provides several index types:
B-tree, Hash, GiST, SP-GiST, GIN, BRIN, and the extension <a class="link" href="bloom.html" title="F.7. bloom">bloom</a>.
Each index type uses a different
algorithm that is best suited to different types of indexable clauses.
By default, the <a class="link" href="sql-createindex.html" title="CREATE INDEX"><code class="command">CREATE
INDEX</code></a> command creates
B-tree indexes, which fit the most common situations.
The other index types are selected by writing the keyword
<code class="literal">USING</code> followed by the index type name.
For example, to create a Hash index:
</p><pre class="programlisting">
CREATE INDEX <em class="replaceable"><code>name</code></em> ON <em class="replaceable"><code>table</code></em> USING HASH (<em class="replaceable"><code>column</code></em>);
</pre><p>
</p><div class="sect2" id="INDEXES-TYPES-BTREE"><div class="titlepage"><div><div><h3 class="title">11.2.1. B-Tree</h3></div></div></div><a id="id-1.5.10.5.3.2" class="indexterm"></a><a id="id-1.5.10.5.3.3" class="indexterm"></a><p>
B-trees can handle equality and range queries on data that can be sorted
into some ordering.
In particular, the <span class="productname">PostgreSQL</span> query planner
will consider using a B-tree index whenever an indexed column is
involved in a comparison using one of these operators:
</p><pre class="synopsis">
< <= = >= >
</pre><p>
Constructs equivalent to combinations of these operators, such as
<code class="literal">BETWEEN</code> and <code class="literal">IN</code>, can also be implemented with
a B-tree index search. Also, an <code class="literal">IS NULL</code> or <code class="literal">IS NOT
NULL</code> condition on an index column can be used with a B-tree index.
</p><p>
The optimizer can also use a B-tree index for queries involving the
pattern matching operators <code class="literal">LIKE</code> and <code class="literal">~</code>
<span class="emphasis"><em>if</em></span> the pattern is a constant and is anchored to
the beginning of the string — for example, <code class="literal">col LIKE
'foo%'</code> or <code class="literal">col ~ '^foo'</code>, but not
<code class="literal">col LIKE '%bar'</code>. 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
<a class="xref" href="indexes-opclass.html" title="11.10. Operator Classes and Operator Families">Section 11.10</a> below. It is also possible to use
B-tree indexes for <code class="literal">ILIKE</code> and
<code class="literal">~*</code>, but only if the pattern starts with
non-alphabetic characters, i.e., characters that are not affected by
upper/lower case conversion.
</p><p>
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.
</p></div><div class="sect2" id="INDEXES-TYPES-HASH"><div class="titlepage"><div><div><h3 class="title">11.2.2. Hash</h3></div></div></div><a id="id-1.5.10.5.4.2" class="indexterm"></a><a id="id-1.5.10.5.4.3" class="indexterm"></a><p>
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
equal operator:
</p><pre class="synopsis">
=
</pre><p>
</p></div><div class="sect2" id="INDEXES-TYPE-GIST"><div class="titlepage"><div><div><h3 class="title">11.2.3. GiST</h3></div></div></div><a id="id-1.5.10.5.5.2" class="indexterm"></a><a id="id-1.5.10.5.5.3" class="indexterm"></a><p>
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 <em class="firstterm">operator
class</em>). As an example, the standard distribution of
<span class="productname">PostgreSQL</span> includes GiST operator classes
for several two-dimensional geometric data types, which support indexed
queries using these operators:
</p><pre class="synopsis">
<< &< &> >> <<| &<| |&> |>> @> <@ ~= &&
</pre><p>
(See <a class="xref" href="functions-geometry.html" title="9.11. Geometric Functions and Operators">Section 9.11</a> for the meaning of
these operators.)
The GiST operator classes included in the standard distribution are
documented in <a class="xref" href="gist-builtin-opclasses.html#GIST-BUILTIN-OPCLASSES-TABLE" title="Table 68.1. Built-in GiST Operator Classes">Table 68.1</a>.
Many other GiST operator
classes are available in the <code class="literal">contrib</code> collection or as separate
projects. For more information see <a class="xref" href="gist.html" title="Chapter 68. GiST Indexes">Chapter 68</a>.
</p><p>
GiST indexes are also capable of optimizing <span class="quote">“<span class="quote">nearest-neighbor</span>”</span>
searches, such as
</p><pre class="programlisting">
SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;
</pre><p>
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 <a class="xref" href="gist-builtin-opclasses.html#GIST-BUILTIN-OPCLASSES-TABLE" title="Table 68.1. Built-in GiST Operator Classes">Table 68.1</a>, operators that can be
used in this way are listed in the column <span class="quote">“<span class="quote">Ordering Operators</span>”</span>.
</p></div><div class="sect2" id="INDEXES-TYPE-SPGIST"><div class="titlepage"><div><div><h3 class="title">11.2.4. SP-GiST</h3></div></div></div><a id="id-1.5.10.5.6.2" class="indexterm"></a><a id="id-1.5.10.5.6.3" class="indexterm"></a><p>
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
<span class="productname">PostgreSQL</span> includes SP-GiST operator classes
for two-dimensional points, which support indexed
queries using these operators:
</p><pre class="synopsis">
<< >> ~= <@ <<| |>>
</pre><p>
(See <a class="xref" href="functions-geometry.html" title="9.11. Geometric Functions and Operators">Section 9.11</a> for the meaning of
these operators.)
The SP-GiST operator classes included in the standard distribution are
documented in <a class="xref" href="spgist-builtin-opclasses.html#SPGIST-BUILTIN-OPCLASSES-TABLE" title="Table 69.1. Built-in SP-GiST Operator Classes">Table 69.1</a>.
For more information see <a class="xref" href="spgist.html" title="Chapter 69. SP-GiST Indexes">Chapter 69</a>.
</p><p>
Like GiST, SP-GiST supports <span class="quote">“<span class="quote">nearest-neighbor</span>”</span> searches.
For SP-GiST operator classes that support distance ordering, the
corresponding operator is listed in the <span class="quote">“<span class="quote">Ordering Operators</span>”</span>
column in <a class="xref" href="spgist-builtin-opclasses.html#SPGIST-BUILTIN-OPCLASSES-TABLE" title="Table 69.1. Built-in SP-GiST Operator Classes">Table 69.1</a>.
</p></div><div class="sect2" id="INDEXES-TYPES-GIN"><div class="titlepage"><div><div><h3 class="title">11.2.5. GIN</h3></div></div></div><a id="id-1.5.10.5.7.2" class="indexterm"></a><a id="id-1.5.10.5.7.3" class="indexterm"></a><p>
GIN indexes are <span class="quote">“<span class="quote">inverted indexes</span>”</span> 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.
</p><p>
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
<span class="productname">PostgreSQL</span> includes a GIN operator class
for arrays, which supports indexed queries using these operators:
</p><pre class="synopsis">
<@ @> = &&
</pre><p>
(See <a class="xref" href="functions-array.html" title="9.19. Array Functions and Operators">Section 9.19</a> for the meaning of
these operators.)
The GIN operator classes included in the standard distribution are
documented in <a class="xref" href="gin-builtin-opclasses.html#GIN-BUILTIN-OPCLASSES-TABLE" title="Table 70.1. Built-in GIN Operator Classes">Table 70.1</a>.
Many other GIN operator
classes are available in the <code class="literal">contrib</code> collection or as separate
projects. For more information see <a class="xref" href="gin.html" title="Chapter 70. GIN Indexes">Chapter 70</a>.
</p></div><div class="sect2" id="INDEXES-TYPES-BRIN"><div class="titlepage"><div><div><h3 class="title">11.2.6. BRIN</h3></div></div></div><a id="id-1.5.10.5.8.2" class="indexterm"></a><a id="id-1.5.10.5.8.3" class="indexterm"></a><p>
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
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:
</p><pre class="synopsis">
< <= = >= >
</pre><p>
The BRIN operator classes included in the standard distribution are
documented in <a class="xref" href="brin-builtin-opclasses.html#BRIN-BUILTIN-OPCLASSES-TABLE" title="Table 71.1. Built-in BRIN Operator Classes">Table 71.1</a>.
For more information see <a class="xref" href="brin.html" title="Chapter 71. BRIN Indexes">Chapter 71</a>.
</p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="indexes-intro.html" title="11.1. Introduction">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="indexes.html" title="Chapter 11. Indexes">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="indexes-multicolumn.html" title="11.3. Multicolumn Indexes">Next</a></td></tr><tr><td width="40%" align="left" valign="top">11.1. Introduction </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.7 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 11.3. Multicolumn Indexes</td></tr></table></div></body></html>
|
