PostgreSQL provides several index types:
B-tree, R-tree, Hash, and GiST. 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
BETWEEN and
IN, can also be implemented with
a B-tree index search. (But note that
IS NULL is not
equivalent to
= and is not indexable.)
The optimizer can also use a B-tree index for queries involving the
pattern matching operators LIKE and ~
if the pattern is a constant and is anchored to
the beginning of the string — for example, col LIKE
'foo%' or 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 11.8 below. It is also possible to use
B-tree indexes for ILIKE and
~*, but only if the pattern starts with
non-alphabetic characters, i.e. characters that are not affected by
upper/lower case conversion.
R-tree indexes are suited for queries on two-dimensional spatial data.
To create an R-tree index, use a command of the form
CREATE INDEX name ON table USING rtree (column);
The PostgreSQL query planner will
consider using an R-tree index whenever an indexed column is
involved in a comparison using one of these operators:
| << |
| &< |
| &> |
| >> |
| <<| |
| &<| |
| |&> |
| |>> |
| ~ |
| @ |
| ~= |
| && |
(See
Section 9.10 for the meaning of
these operators.)
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);
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). The standard distribution of
PostgreSQL includes GiST operator classes
equivalent to the R-tree operator classes, and many other GiST operator
classes are available in the contrib collection or as separate
projects. For more information see Chapter 49.
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 REINDEX
after a database crash.
For these reasons, hash index use is presently discouraged.
Similarly, R-tree indexes do not seem to have any performance
advantages compared to the equivalent operations of GiST indexes.
Like hash indexes, they are not WAL-logged and may need
reindexing after a database crash.
While the problems with hash indexes may be fixed eventually,
it is likely that the R-tree index type will be retired in a future
release. Users are encouraged to migrate applications that use R-tree
indexes to GiST indexes.
