PostgreSQL Index Strategy That Actually Works

Most "PostgreSQL is slow" tickets are an indexing problem, and most indexing problems come from adding indexes by intuition rather than by reading the plan. This is the method I use, and the specific index types that solve the cases people get stuck on.

Read the plan first#

Never add an index before you have seen the plan. EXPLAIN shows the estimate; EXPLAIN (ANALYZE, BUFFERS) shows reality:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.placed_at, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
  AND o.placed_at >= now() - interval '30 days'
ORDER BY o.placed_at DESC
LIMIT 50;

Limit  (cost=0.00..52384.19 rows=50 width=48) (actual time=1284.551..1284.573 rows=50 loops=1)
  Buffers: shared hit=1204 read=88291
  ->  Nested Loop  (cost=0.00..1904337.22 rows=1817 width=48) (actual time=1284.549..1284.566 rows=50 loops=1)
        ->  Seq Scan on orders o  (cost=0.00..1893201.00 rows=1817 width=24) (actual time=1284.501..1284.509 rows=50 loops=1)
              Filter: ((status = 'pending') AND (placed_at >= (now() - '30 days'::interval)))
              Rows Removed by Filter: 4218773
Planning Time: 0.214 ms
Execution Time: 1284.612 ms

Three numbers tell the story.

Rows Removed by Filter: 4218773 — the database read 4.2 million rows to return 50. That is the problem, stated precisely.

read=88291 — 88,000 blocks fetched from disk rather than cache. At 8 KB per block that is roughly 690 MB of I/O for fifty rows.

Estimate 1817 vs actual 50 — the planner's row estimate is off by 36×. Bad estimates produce bad plans; if this persists after indexing, your statistics need attention.

Compare estimated and actual rows at every node. Where they diverge sharply is where the planner is making decisions on bad information — and where ANALYZE, raised statistics targets, or extended statistics will help more than another index.

Column order in composite indexes#

This is the rule people get wrong most often. In a B-tree index on (a, b, c), Postgres can use the index for predicates on a, on a, b, and on a, b, c — but not on b alone.

The ordering heuristic:

1. Columns tested with equality first
2. Then the column tested with a range
3. Then columns used for ordering

For the query above:

CREATE INDEX CONCURRENTLY idx_orders_status_placed
    ON orders (status, placed_at DESC);

status is equality, placed_at is both the range filter and the sort key. Putting placed_at first would force a scan of every recent order regardless of status.

Limit  (cost=0.43..18.21 rows=50 width=48) (actual time=0.041..0.128 rows=50 loops=1)
  Buffers: shared hit=54
  ->  Index Scan using idx_orders_status_placed on orders o
        Index Cond: ((status = 'pending') AND (placed_at >= (now() - '30 days'::interval)))
Execution Time: 0.163 ms

1284 ms to 0.16 ms, and 88,291 block reads to 54 cached hits.

Always build indexes on production with CREATE INDEX CONCURRENTLY. A plain CREATE INDEX takes an ACCESS EXCLUSIVE-adjacent lock that blocks writes for the duration. CONCURRENTLY takes longer and does two table passes, but does not block. If it fails it leaves an invalid index behind — check pg_index.indisvalid and drop it before retrying.

Partial indexes#

When a query always filters on the same condition, index only the matching rows:

CREATE INDEX CONCURRENTLY idx_orders_pending
    ON orders (placed_at DESC)
    WHERE status = 'pending';

If 2% of orders are pending, this index is 2% of the size. It fits in cache, it is faster to scan, and — the part people miss — it costs far less to maintain. An index that excludes a row is not updated when that row changes.

This is the standard fix for soft-deleted tables:

CREATE INDEX CONCURRENTLY idx_users_email_active
    ON users (email)
    WHERE deleted_at IS NULL;

The planner only uses a partial index when it can prove the query's predicate implies the index predicate. WHERE status = 'pending' matches. A parameterised WHERE status = $1 does not, even when the parameter happens to be 'pending'.

Covering indexes#

INCLUDE adds columns to the index leaf without adding them to the search key, enabling an index-only scan:

CREATE INDEX CONCURRENTLY idx_orders_covering
    ON orders (status, placed_at DESC)
    INCLUDE (customer_id, total_cents);

Now a query selecting only those four columns never touches the heap.

The caveat: index-only scans still consult the visibility map, and a table with heavy write churn and lazy autovacuum will have few all-visible pages — so the "index-only" scan performs heap fetches anyway. Watch for Heap Fetches: in the plan. A high count means you need more aggressive autovacuum on that table, not a different index.

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

Choosing the index type#

B-tree is the default and correct answer roughly 90% of the time. The other types earn their place in specific situations.

GIN for jsonb#

CREATE INDEX CONCURRENTLY idx_events_payload
    ON events USING gin (payload jsonb_path_ops);

SELECT * FROM events WHERE payload @> '{"level": "error"}';

jsonb_path_ops produces a smaller, faster index than the default operator class, at the cost of supporting only the containment operators (@>, @@, @?). If containment is all you query — and it usually is — take it.

BRIN for append-only tables#

On a time-series table where rows arrive in timestamp order, a BRIN index stores min/max per block range:

CREATE INDEX CONCURRENTLY idx_metrics_time_brin
    ON metrics USING brin (recorded_at) WITH (pages_per_range = 64);

A BRIN index on a 500 GB table might be 3 MB. The requirement is physical correlation — if rows are inserted out of order or heavily updated, BRIN degrades to a sequential scan with extra steps. Check first:

SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'metrics' AND attname = 'recorded_at';

Above 0.9, BRIN works well. Below 0.5, use B-tree.

Finding indexes that are not earning their keep#

Every index slows down writes and consumes cache. Audit regularly:

SELECT
    s.relname AS table_name,
    s.indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(s.indexrelid)) AS size,
    s.idx_scan AS scans
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan < 50
  AND NOT i.indisunique
  AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;

And find duplicates — an index on (a) is redundant when (a, b) exists:

SELECT
    indrelid::regclass AS table_name,
    array_agg(indexrelid::regclass) AS duplicates
FROM pg_index
GROUP BY indrelid, indkey
HAVING count(*) > 1;

idx_scan counts since the last statistics reset, and it is per-node on a replica. An index unused on the primary may be doing all the work for your reporting replica. Check pg_stat_user_indexes on every node before dropping anything.

The workflow#

1. Find the slow query with pg_stat_statements, ordered by total_exec_time — not by mean. A 40 ms query running 200,000 times a minute costs more than a 30-second report.
2. EXPLAIN (ANALYZE, BUFFERS) it.
3. Look for sequential scans with large Rows Removed by Filter, and for estimate/actual divergence.
4. Design the index: equality columns, then range, then sort. Consider INCLUDE and a WHERE clause.
5. Build it CONCURRENTLY.
6. Re-run EXPLAIN (ANALYZE, BUFFERS) and confirm the improvement is real.
7. Come back in a week and check idx_scan — if it is zero, you solved a different problem than you thought.

Step seven is the one that gets skipped, and it is why mature databases accumulate dozens of indexes nobody can account for.