Last updated April 23, 2026

There are many types of indexes in Postgres, as well as different ways to use them. In this article, we give an overview of the types of indexes available, and explain different ways of using and maintaining the most common index type: B-trees.

An index is a way to efficiently retrieve a relatively small number of rows from a table. It’s only useful if the number of rows to be retrieved from a table is relatively small. For example, the condition for retrieving rows, the WHERE clause, is selective. B-tree indexes are also useful for avoiding sorting.

Index Types

Postgres supports many different index types:

• B-Tree is the default index type that you get when you run CREATE INDEX. Virtually all databases have some B-tree indexes. B-trees attempt to remain balanced, with the amount of data in each branch of the tree being roughly the same. Therefore, the number of levels that it must traverse to find rows is always in the same ballpark. You can use B-tree indexes for equality and range queries efficiently. They can operate against all datatypes, and you can also use them to retrieve NULL values. B-trees are designed to work well with caching, even when only partially cached.
• Hash indexes are useful for equality comparisons.
• Generalized Inverted Indexes (GIN) are useful when an index must map many values to one row, whereas B-tree indexes are optimized for when a row has a single key value. GINs are good for indexing array values and for implementing full-text search.
• Generalized Search Tree (GiST) indexes allow you to build general balanced tree structures, and you can use them for operations beyond equality and range comparisons. Use them to index the geometric data types and implement full-text search.

This article explains how to get the most out of default B-Tree indexes. For examples of GIN and GiST index usage, see PostgreSQL’s contrib packages documentation.

Why Is My Query Not Using an Index?

There are many reasons why the Postgres planner doesn’t use an index. Most of the time, the planner selects an index correctly, even if it isn’t obvious why. The same query can use an index scan on some occasions but not others. The number of rows retrieved from the table can vary based on the particular constant values the query retrieves. For example, it’s correct for the query planner to use an index for the query SELECT * FROM foo WHERE bar = 1, and yet not use one for the query SELECT * FROM foo WHERE bar = 2 if there were far more rows with “bar” values of 2. When this scenario happens, a sequential scan is most likely faster than an index scan, so the query planner has in fact correctly judged that the cost of performing the query that way is lower.

Partial Indexes

A partial index covers just a subset of a table’s data. It’s an index with a WHERE clause. The idea is to increase the efficiency of the index by reducing its size. A smaller index takes less storage, is easier to maintain, and is faster to scan.

For example, suppose that users can flag comments on your site, which in turn sets the flagged boolean to true. You then process flagged comments in batches. You create a partial index as:

CREATE INDEX articles_flagged_created_at_index ON articles(created_at) WHERE flagged IS TRUE;

This index remains fairly small, and you can also use it with other indexes on the more complex queries that require it.

Expression Indexes

Expression indexes are useful for queries that match on some function or modification of your data. Postgres allows you to index the result of that function so that searches become as efficient as searching by raw data values. For example, you can require users to store their email addresses for signing in, but you want case-insensitive authentication. In that case, it’s possible to store the email address as users provide it, but perform searches on WHERE lower(email) = '<lowercased-email>'. The only way to use an index in such a query is with an expression index like so:

CREATE INDEX users_lower_email ON users(lower(email));

Another common usage pattern of expression indexes is finding rows for a given date, where we stored timestamps in a datetime field but want to find them by a date casted value. A query containing WHERE date(articles.published_at) = date('2025-03-07') can use an index like CREATE INDEX articles_day ON articles (date(published_at)).

Unique Indexes

A unique index guarantees that the table doesn’t have more than one row with the same value. It’s advantageous to create unique indexes for two reasons: data integrity and performance. Lookups on a unique index are fast.

In terms of data integrity, application-level uniqueness validations, such as Rails’ validates_uniqueness_of on an ActiveModel class, don’t guarantee uniqueness because concurrent users can create invalid duplicate records. Always create the constraint at the database level, either with an index or a unique constraint.

There’s little distinction between unique indexes and unique constraints. You can think of unique indexes as lower level since you can’t create expression indexes and partial indexes as unique constraints. Even partial unique indexes on expressions are possible.

Multi-Column Indexes

The Postgres query planner can combine and use multiple single-column indexes in a multi-column query by performing a bitmap index scan. You can create an index on each column that covers query conditions, and Postgres uses them usually. Benchmark and justify the creation of multi-column indexes before creating them. Indexes come with a cost, and multi-column indexes can only optimize the queries that reference the columns in the same order as the index. In contrast, multiple single column indexes provide performance improvements to a larger number of queries.

Multi-column indexes make sense in specific cases. Queries containing WHERE a = x AND b = y, or queries using WHERE a = x only can use an index on columns (a, b). Queries with WHERE b = y can’t use an index on columns (a, b). If this example matches your app’s query patterns, use a multi-column index. Creating an index on a alone is redundant in this case.

B-trees and Sorting

B-Tree index entries are sorted in ascending order by default. You can specify a different sort order for an index to match your query patterns. For example, when you display a paginated list of articles that’s sorted by the most recent published first, you can create a published_at column on your articles table. For unpublished articles, the published_at value is NULL.

Create an index that matches your query sort order:

CREATE INDEX articles_published_at_index ON articles(published_at DESC NULLS LAST);

When you query the table in sorted order by published_at and limit the result, creating an index in the same order improves performance. Postgres finds the rows it needs from the index in the correct order and then goes to the data blocks to retrieve the data. Without a sorted index Postgres must instead read the data blocks sequentially and sort the results.

Use this technique with single-column indexes when you require “nulls to sort last” behavior. Otherwise, the order is already available since an index can be scanned in any direction. This technique is especially useful with multi-column indexes when a query requests a mixed sort order, like a ASC, b DESC.

Managing and Maintaining Indexes

Indexes in Postgres don’t hold all row data. Even when a query uses an index and it finds matching rows, Postgres goes to disk to fetch the row data. Additionally, Postgres doesn’t store row visibility information on the index either, so Postgres must also go to disk to fetch that information.

Using an index doesn’t always make sense. An index must be selective enough to reduce the number of disk lookups for it to be worth it. For example, a primary key lookup with a large enough table makes good use of an index. In this scenario, Postgres finds the targeted rows in an index and fetches them from disk selectively, instead of sequentially scanning the table. For very small tables, such as a cities lookup table, an index can be undesirable, even when you search by city name. Postgres ignores the index in favor of a sequential scan. Postgres performs a sequential scan on any query that hits a significant portion of a table. If you have an index on that column that you never use, it’s a dead index. Indexes come at a cost in terms of storage and maintenance, so remove unused indexes to reduce overhead.

When tuning queries and determining which indexes improve performance, use a database as similar as possible to your production environment. Whether queries use an index depends on several factors, including the Postgres server configuration, the data in the table, the index, and the query. Testing index usage on your development machine with a small subset of test data can be misleading.

Postgres determines that the dataset is so small that it’s not worth the overhead of reading through the index and then fetching the data from disk. Random I/O is much slower than sequential I/O, so the cost of a sequential scan is lower than the random I/O from reading the index and selectively finding the data on disk. Perform index tuning in production, or in a staging environment that closely matches production. For testing, you can fork your production database or copy your data to a different environment on Heroku Postgres.

When you create an index on your production database, keep in mind that creating an index locks the table against writes. For large tables, this scenario can cause downtime for hours. Use CREATE INDEX CONCURRENTLY to avoid blocking writes during index creation. This option takes longer to build but doesn’t require a lock that blocks writes. Ordinary CREATE INDEX commands require a lock that blocks writes but not reads.

Indexes become fragmented and unoptimized over time, especially if the rows in the table are often updated or deleted. In those cases, perform a REINDEX to rebuild the index into a balanced and optimized state.

The standard REINDEX requires a while lock on the parent table, which can be problematic for large indexes in production. Use REINDEX CONCURRENTLY to rebuild indexes on production databases without blocking writes. This option takes longer to complete but allows rebuilding an index with minimum locking of writes.

Alternatively, you can build a new index concurrently on the same table and columns with a different name, drop the original index, and rename the new one. This approach achieves the same result without requiring long-running locks on live tables.

Postgres provides flexibility for creating B-tree indexes optimized to your specific use cases and managing your database. These practices help you keep your database healthy, and your queries fast.