Database Reference
In-Depth Information
Index names must be unique within a given schema.
PostgreSQL Stock Indexes
To take full advantage of all that PostgreSQL has to offer, you'll want to understand the
various types of indexes and situations where they will aid or harm. The index methods
are:
B-Tree
B-Tree is a general-purpose index common in relational databases. You can usually
get by with B-Tree alone if you don't want to experiment with additional types. If
PostgreSQL automatically creates an index for you or you don't bother specifying
the index method, B-Tree will be chosen. It is currently the only index method for
primary keys and unique keys.
GiST
Generalized Search Tree (GiST) is an index optimized for full-text search, spatial
data, scientific data, unstructured data, and hierarchical data. Although you can't
use it to enforce uniqueness, you can create the same effect by using it in an exclusion
constraint.
GiST is a lossy index, in the sense that the index itself will not store the value of
what it's indexing, but merely a caricature of the value such as a box for a polygon.
This creates the need for an extra look-up step if you need to retrieve the value or
do a more fine-tuned check.
GIN
Generalized Inverted Index (GIN) is geared toward the built-in
full text search
and
jsonb
data type of PostgreSQL. Many other extensions, such as hstore and pg_trgm
also utilize it. GIN is a descendent of GiST without lossiness. GIN will make a copy
of the values in the columns that are part of the index. If you ever need to pull data
limited to covered columns, GIN is faster than GiST. However, the extra copying
required by GIN index means updating the index is slower than a comparable GiST
index. Also, because each index row is limited to a certain size, you can't use GIN
to index large objects such as large hstore documents or text. If there is a possibility
you'll be inserting a 600-page manual into a field of a table, don't use GIN to index
that column.
You can find a wonderful example of GIN in
Waiting for Faster LIKE/ILIKE
. In
version 9.3, you can index regular expressions that leverage the GIN-based
pg_trgm
