Practical guide

How to create the right indexes in PostgreSQL

Do not design an index from a column name. Design it from a concrete filter, ordering, and data volume.

25 minutes · PostgreSQL

In short

An index trades cost for speed

A database index helps PostgreSQL find and order rows without scanning the entire table. Every index also uses space and slows INSERT, UPDATE, and DELETE.

The right index starts from a real SQL query. Column order, a partial predicate, and included values must match what the application actually filters and returns.

Prepare

What you need

Choose a slow or frequent query first. An index without a concrete query has no verifiable goal.

The exact SQL including WHERE, JOIN, ORDER BY, and LIMIT, ideally taken from production statistics.
Representative data volume and distribution. A plan over a thousand test rows can differ from one over millions.
EXPLAIN (ANALYZE, BUFFERS) output before the change and a target response time.
An inventory of existing indexes and a way to monitor their size and use after deployment.

Steps 1 to 3

Design the index from the query

Start with an ordinary B-tree. Use a specialized type only when it matches the operators in the specific query.

1. Read the filter and ordering

Find columns used for equality, ranges, and joins. Also inspect ORDER BY and whether the query returns only a small part of the table.
A sequential scan is not automatically wrong. It can be cheaper when reading a large share of a small table.
Compare estimated and actual rows. A large difference can indicate stale statistics rather than a missing index.
Record baseline execution time, buffers, and returned row count.

EXPLAIN (ANALYZE, BUFFERS) SELECT id, total FROM orders WHERE customer_id = 42 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;

Official PostgreSQL EXPLAIN documentation

2. Choose columns and index type

For a multicolumn B-tree, equality columns usually come first, followed by a range or ordering column. Verify this against the real plan.
Use a partial index when queries repeatedly address a small, stable subset such as only paid orders.
INCLUDE can enable an index-only scan for returned columns, but increases size. Do not include the entire row by default.
Choose GIN, GiST, or BRIN for matching operators and data characteristics, not by reputation. B-tree is the default for equality, ranges, and ordering.

CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at DESC) INCLUDE (total) WHERE status = 'paid';

Official PostgreSQL index overview

3. Build safely and measure the impact

Consider CREATE INDEX CONCURRENTLY on a busy production table so normal writes are not blocked for the whole build.
CONCURRENTLY cannot run inside a transaction block and a failed build can leave an invalid index. Always inspect its state.
Run the same EXPLAIN ANALYZE and compare plan, buffers, and time. A tiny difference may not justify another index.
After deployment, watch pg_stat_user_indexes, index size, and write latency. Remove an unused index after sufficient observation.

CREATE INDEX CONCURRENTLY idx_orders_customer_created ON orders (customer_id, created_at DESC) INCLUDE (total) WHERE status = 'paid';

Official CREATE INDEX CONCURRENTLY documentation

Step 4

Verify reads and write cost

A new index is useful only when it helps an important query without imposing disproportionate overhead elsewhere.

Compare the plan before and after

Use the same data and parameters. Inspect actual rows, loops, buffers, and total time, not only whether the plan says Index Scan.
```
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
```

Inspect state and size

Do not deploy an invalid or unexpectedly large index.

SELECT indexrelid::regclass, indisvalid, pg_size_pretty(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid = 'orders'::regclass;

Measure normal writes

Compare INSERT and UPDATE before and after. Watch disk growth and replication lag as well.

When it goes wrong

Common mistakes

The planner still chooses a Seq Scan

That may be correct when the query returns a large share of the table. Check selectivity, statistics, parameter types, and actual timing of both plans.

ANALYZE orders;

A multicolumn index helps only some queries

Check column order and left-prefix rules. One wide index does not replace different access patterns.

Writes became slower after adding the index

Every write must maintain another structure. Remove duplicate and unused indexes and keep INCLUDE limited to necessary columns.

CONCURRENTLY left an invalid index

Find it through pg_index.indisvalid, remove it safely, fix the cause, and rebuild. Do not leave invalid indexes untracked.

Done

The index has a clear purpose and measurable benefit.

The index now matches a concrete query and its read and write impact is verified. Repeat the same process before every CREATE INDEX.

Read database index in the glossary All guides