<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >Index Types</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 9.6.17 Documentation" HREF="index.html"><LINK REL="UP" TITLE="Indexes" HREF="indexes.html"><LINK REL="PREVIOUS" TITLE="Introduction" HREF="indexes-intro.html"><LINK REL="NEXT" TITLE="Multicolumn Indexes" HREF="indexes-multicolumn.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1"><META NAME="creation" CONTENT="2020-02-15T12:30:38"></HEAD ><BODY CLASS="SECT1" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="4" ALIGN="center" VALIGN="bottom" ><A HREF="index.html" >PostgreSQL 9.6.17 Documentation</A ></TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A TITLE="Introduction" HREF="indexes-intro.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="indexes.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" >Chapter 11. Indexes</TD ><TD WIDTH="20%" ALIGN="right" VALIGN="top" ><A TITLE="Multicolumn Indexes" HREF="indexes-multicolumn.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="INDEXES-TYPES" >11.2. Index Types</A ></H1 ><P > <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > provides several index types: 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 <TT CLASS="COMMAND" >CREATE INDEX</TT > command creates B-tree indexes, which fit the most common situations. </P ><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 ></P ><TABLE BORDER="0" ><TBODY ><TR ><TD ><TT CLASS="LITERAL" ><</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" ><=</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >=</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >>=</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >></TT ></TD ></TR ></TBODY ></TABLE ><P ></P > Constructs equivalent to combinations of these operators, such as <TT CLASS="LITERAL" >BETWEEN</TT > and <TT CLASS="LITERAL" >IN</TT >, can also be implemented with a B-tree index search. Also, an <TT CLASS="LITERAL" >IS NULL</TT > or <TT CLASS="LITERAL" >IS NOT NULL</TT > 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 <TT CLASS="LITERAL" >LIKE</TT > and <TT CLASS="LITERAL" >~</TT > <SPAN CLASS="emphasis" ><I CLASS="EMPHASIS" >if</I ></SPAN > the pattern is a constant and is anchored to the beginning of the string — for example, <TT CLASS="LITERAL" >col LIKE 'foo%'</TT > or <TT CLASS="LITERAL" >col ~ '^foo'</TT >, but not <TT CLASS="LITERAL" >col LIKE '%bar'</TT >. 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 HREF="indexes-opclass.html" >Section 11.9</A > below. It is also possible to use B-tree indexes for <TT CLASS="LITERAL" >ILIKE</TT > and <TT CLASS="LITERAL" >~*</TT >, 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 ><P > 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 <TT CLASS="LITERAL" >=</TT > operator. The following command is used to create a hash index: </P><PRE CLASS="SYNOPSIS" >CREATE INDEX <TT CLASS="REPLACEABLE" ><I >name</I ></TT > ON <TT CLASS="REPLACEABLE" ><I >table</I ></TT > USING HASH (<TT CLASS="REPLACEABLE" ><I >column</I ></TT >);</PRE ><P> </P ><DIV CLASS="CAUTION" ><P ></P ><TABLE CLASS="CAUTION" BORDER="1" WIDTH="100%" ><TR ><TD ALIGN="CENTER" ><B >Caution</B ></TD ></TR ><TR ><TD ALIGN="LEFT" ><P > Hash index operations are not presently WAL-logged, so hash indexes might need to be rebuilt with <TT CLASS="COMMAND" >REINDEX</TT > after a database crash if there were unwritten changes. Also, changes to hash indexes are not replicated over streaming or file-based replication after the initial base backup, so they give wrong answers to queries that subsequently use them. For these reasons, hash index use is presently discouraged. </P ></TD ></TR ></TABLE ></DIV ><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 <I CLASS="FIRSTTERM" >operator class</I >). 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 ></P ><TABLE BORDER="0" ><TBODY ><TR ><TD ><TT CLASS="LITERAL" ><<</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >&<</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >&></TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >>></TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" ><<|</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >&<|</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >|&></TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >|>></TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >@></TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" ><@</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >~=</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >&&</TT ></TD ></TR ></TBODY ></TABLE ><P ></P > (See <A HREF="functions-geometry.html" >Section 9.11</A > for the meaning of these operators.) The GiST operator classes included in the standard distribution are documented in <A HREF="gist-builtin-opclasses.html#GIST-BUILTIN-OPCLASSES-TABLE" >Table 61-1</A >. Many other GiST operator classes are available in the <TT CLASS="LITERAL" >contrib</TT > collection or as separate projects. For more information see <A HREF="gist.html" >Chapter 61</A >. </P ><P > GiST indexes are also capable of optimizing <SPAN CLASS="QUOTE" >"nearest-neighbor"</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 HREF="gist-builtin-opclasses.html#GIST-BUILTIN-OPCLASSES-TABLE" >Table 61-1</A >, operators that can be used in this way are listed in the column <SPAN CLASS="QUOTE" >"Ordering Operators"</SPAN >. </P ><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 ></P ><TABLE BORDER="0" ><TBODY ><TR ><TD ><TT CLASS="LITERAL" ><<</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >>></TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >~=</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" ><@</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" ><^</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >>^</TT ></TD ></TR ></TBODY ></TABLE ><P ></P > (See <A HREF="functions-geometry.html" >Section 9.11</A > for the meaning of these operators.) The SP-GiST operator classes included in the standard distribution are documented in <A HREF="spgist-builtin-opclasses.html#SPGIST-BUILTIN-OPCLASSES-TABLE" >Table 62-1</A >. For more information see <A HREF="spgist.html" >Chapter 62</A >. </P ><P > GIN indexes are <SPAN CLASS="QUOTE" >"inverted indexes"</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 GIN operator classes for one-dimensional arrays, which support indexed queries using these operators: <P ></P ><TABLE BORDER="0" ><TBODY ><TR ><TD ><TT CLASS="LITERAL" ><@</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >@></TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >=</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >&&</TT ></TD ></TR ></TBODY ></TABLE ><P ></P > (See <A HREF="functions-array.html" >Section 9.18</A > for the meaning of these operators.) The GIN operator classes included in the standard distribution are documented in <A HREF="gin-builtin-opclasses.html#GIN-BUILTIN-OPCLASSES-TABLE" >Table 63-1</A >. Many other GIN operator classes are available in the <TT CLASS="LITERAL" >contrib</TT > collection or as separate projects. For more information see <A HREF="gin.html" >Chapter 63</A >. </P ><P > 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: <P ></P ><TABLE BORDER="0" ><TBODY ><TR ><TD ><TT CLASS="LITERAL" ><</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" ><=</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >=</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >>=</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" >></TT ></TD ></TR ></TBODY ></TABLE ><P ></P > The BRIN operator classes included in the standard distribution are documented in <A HREF="brin-builtin-opclasses.html#BRIN-BUILTIN-OPCLASSES-TABLE" >Table 64-1</A >. For more information see <A HREF="brin.html" >Chapter 64</A >. </P ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="indexes-intro.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="indexes-multicolumn.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Introduction</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="indexes.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Multicolumn Indexes</TD ></TR ></TABLE ></DIV ></BODY ></HTML >