11.2. index types

postgresql provides several index types: b-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:

the optimizer can also use a b-tree index for queries involving the pattern matching operators like, ilike, ~, 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.

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). 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: