---
title: Postgres Index Types
teaser: Improve query performance by using the right index type.
tags: postgresql
author: Calle Erlandsson
published_on: 2016-04-21
---

As discussed in [A Grand Piano for Your Violin][piano], indexes are quite useful
for speeding up database queries.

When creating an index using [`CREATE INDEX`][sql-create], Postgres will create
a B-Tree type index by default. The B-Tree type is great for general purpose
indexes but there are special cases when other types provide better results.

Postgres 9.5 ships with a variety of index types that use distinct algorithms to
speed up different queries. This article provides an overview of the available
types and their use-cases.

Use a `USING` clause to tell Postgres what type of index to create:

```sql
CREATE INDEX band_member_names
ON bands
USING GIN (member_names);
```

## B-Tree

The B-Tree index type uses a balanced tree structure to speed up equality and
range queries on columns of all data types. Since B-Tree index entries are
sorted, they are sometimes used to retrieve table rows in order, thereby
avoiding to manually sort them after retrieval.

## Hash

Hash indexes can only be used for equality comparisons. They are not transaction
safe, won't be replicated over streaming or file-base replication, and might
need to be rebuilt manually with `REINDEX` after a crash. Because of this, using
hash indexes is generally discouraged.

## GIN

Generalized Inverted indexes are great for indexing columns and expressions that
contain more than one value. Good examples are array columns, text search
documents (`tsvector`), and binary json documents (`jsonb`).

## GiST

GiST stands for Generalized Search Tree and isn't a single indexing scheme but
rather an infrastructure that makes it possible to implement indexing schemes
for new data types by providing a balanced tree-structured access method.

GiST indexes are used for operations beyond equality and range comparisons and
the standard Postgres distribution includes GiST operator classes for geometric
data types, network addresses, range types, and text search documents.

## SP-GiST

Space partitioned GiST is also an infrastructure for implementing new indexing
schemes, but instead of providing balanced tree-structures it provides
partitioned search trees. These can be used to implement different types of
**non-balanced** data structures that can yield fast lookups if they are
partitioned using a rule that matches the queries they are used for.

## BRIN

Block range indexes are designed to handle very large tables in which the rows'
natural sort order correlates to certain column values. For example, a table
storing log entries might have a timestamp column for when each log entry was
written. By using a BRIN index on this column, scanning large parts of the table
can be avoided when querying rows by their timestamp value with very little
overhead.

## What's next?

To learn more about Postgres indexes and performance, be sure to check out [How
to Create Postgres Indexes Concurrently in ActiveRecord
Migrations][create-indexes] and [PostgreSQL performance
considerations][perf-consider].

[create-indexes]: https://thoughtbot.com/blog/how-to-create-postgres-indexes-concurrently-in
[perf-consider]: https://thoughtbot.com/blog/postgresql-performance-considerations
[piano]: https://thoughtbot.com/blog/a-grand-piano-for-your-violin
[sql-create]: http://www.postgresql.org/docs/current/static/sql-createindex.html