|PostgreSQL Reference Manual - Volume 1 - SQL Language Reference|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 716 pages
RRP £32.00 ($49.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
9.2 Index Types
PostgreSQL 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
CREATE INDEX command will create a
B-tree index, which fits the most common situations.
B-trees can handle equality and range queries on data that can be sorted into some ordering. In particular, the PostgreSQL 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
IN, can also be implemented with
a B-tree index search. (But note that
IS NULL is not
= and is not indexable.)
The optimizer can also use a B-tree index for queries involving the
pattern matching operators
if the pattern is a constant and is anchored to
the beginning of the string--for example,
col ~ '^foo', but not
col LIKE '%bar'. However, if your server 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
section 9.8 Operator Classes below. It is also possible to use
B-tree indexes for
~*, but only if the pattern starts with
non-alphabetic characters, i.e. characters that are not affected by
upper/lower case conversion.
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
= operator. The following command is used to
create a hash index:
CREATE INDEX name ON table USING hash (column);
Note: Testing has shown PostgreSQL's hash indexes to perform no better than B-tree indexes, and the index size and build time for hash indexes is much worse. Furthermore, hash index operations are not presently WAL-logged, so hash indexes may need to be rebuilt with
REINDEXafter a database crash. For these reasons, hash index use is presently discouraged.
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 operator class). As an example, the standard distribution of PostgreSQL includes GiST operator classes for several two-dimensional geometric data types, which support indexed queries using these operators:
(See section 7.10 Geometric Functions and Operators for the meaning of
Many other GiST operator
classes are available in the
contrib collection or as separate
projects. For more information see Volume 4: GiST Indexes.
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 PostgreSQL includes GIN operator classes for one-dimensional arrays, which support indexed queries using these operators:
(See section 7.14 Array Functions and Operators for the meaning of
Other GIN operator classes are available in the
For more information see Volume 4: GIN Indexes.
|ISBN 0954612027||PostgreSQL Reference Manual - Volume 1 - SQL Language Reference||See the print edition|