<!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 8.4.12 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="2012-05-31T23:30:11"></HEAD ><BODY CLASS="SECT1" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="5" ALIGN="center" VALIGN="bottom" >PostgreSQL 8.4.12 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="indexes-intro.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="indexes.html" >Fast Backward</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" >Chapter 11. Indexes</TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="indexes.html" >Fast Forward</A ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A 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 and GIN. 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 > <A NAME="AEN18942" ></A > <A NAME="AEN18945" ></A > 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 > 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 > <A NAME="AEN18974" ></A > <A NAME="AEN18977" ></A > 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. (Hash indexes do not support <TT CLASS="LITERAL" >IS NULL</TT > searches.) 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="NOTE" ><BLOCKQUOTE CLASS="NOTE" ><P ><B >Note: </B > 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. For this reason, hash index use is presently discouraged. </P ></BLOCKQUOTE ></DIV ><P > <A NAME="AEN18990" ></A > <A NAME="AEN18993" ></A > 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.) 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 51</A >. </P ><P > <A NAME="AEN19027" ></A > <A NAME="AEN19030" ></A > GIN indexes are inverted indexes which can handle values that contain more than one key, arrays for example. Like 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.17</A > for the meaning of these operators.) 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 52</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 >