← Back to writing
EngineeringJul 10, 2026· 3 min

The Postgres indexes that fix most slow queries

Most "the database is slow" problems aren't really database problems — they're missing-index problems. Postgres will happily scan a million rows to answer a query you thought was instant. Here's the small set of indexing ideas that resolves the large majority of slow queries, and — just as important — when an index is the wrong tool.

First, make Postgres show you its plan

Never guess. Prefix the real query with EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;

The words to look for: Seq Scan on a big table is the smell of a missing index. Index Scan or Index Only Scan is what you want. The numbers in actual time tell you where the milliseconds actually go — not where you assume they go.

1. Index the columns you filter and join on

The default B-tree index covers the everyday cases: equality (=), ranges (<, >, BETWEEN), and ORDER BY. Any column that regularly appears in a WHERE clause or a JOIN condition on a large table is a candidate:

CREATE INDEX ON orders (customer_id);

Foreign keys are not indexed automatically in Postgres. That surprises people and quietly makes joins slow.

2. Composite indexes — and why column order is everything

For a query that filters on two columns, one composite index usually beats two separate ones:

CREATE INDEX ON orders (customer_id, created_at DESC);

The rule: equality columns first, then the range or sort column. This index serves WHERE customer_id = 42 ORDER BY created_at DESC perfectly — Postgres seeks to the customer, then walks rows already in the right order. Flip the column order and it can't. A composite index also helps queries that filter on just the leading column, so (customer_id, created_at) covers plain customer_id lookups too — but not created_at alone.

3. Partial indexes for the row you actually query

If you almost always query a slice of the table, index only that slice. Smaller index, faster writes, less bloat:

CREATE INDEX ON orders (created_at)
WHERE status = 'open';

A dashboard that only ever shows open orders never pays to index the millions of closed ones.

4. Covering indexes to skip the table entirely

An Index Only Scan answers a query from the index alone, never touching the table. INCLUDE bolts on the extra columns you select:

CREATE INDEX ON orders (customer_id) INCLUDE (status, total);

Now SELECT status, total FROM orders WHERE customer_id = 42 is served straight from the index.

When an index is the wrong answer

  • Small tables. A few thousand rows fit in memory; a sequential scan is often faster than an index lookup, and Postgres knows it.
  • Low-selectivity columns. Indexing a boolean or a status with three values rarely helps — the planner will skip it. A partial index on the rare value is the better move.
  • Write-heavy tables. Every index is maintained on every insert, update, and delete. Ten indexes on a hot table is ten times the write amplification. Index deliberately, not defensively.

The workflow

Find the slow query (pg_stat_statements is your friend), run EXPLAIN ANALYZE, add the narrowest index that turns a Seq Scan into an Index Scan, and measure again. Ninety percent of the time it's one of the four patterns above — and the fix is one line.