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 — not a misestimate. Under a LIMIT, a child node's estimate is shown as if it ran to completion; the scan simply stopped once the Limit had its 50 rows. Only compare estimates with actuals at nodes that actually finished — the Limit's own rows=50 is the like-for-like figure here.

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 it is far less efficient for 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 locks the table against writes (reads continue) 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, and so is an exact copy of another index. This matches column lists by left prefix, with the same access method and operator classes:

SELECT
    a.indrelid::regclass   AS table_name,
    a.indexrelid::regclass AS redundant,
    b.indexrelid::regclass AS covered_by
FROM pg_index a
JOIN pg_index b ON b.indrelid = a.indrelid AND b.indexrelid <> a.indexrelid
JOIN pg_class ca ON ca.oid = a.indexrelid
JOIN pg_class cb ON cb.oid = b.indexrelid
WHERE ca.relam = cb.relam
  AND (b.indkey::text   || ' ') LIKE (a.indkey::text   || ' %')
  AND (b.indclass::text || ' ') LIKE (a.indclass::text || ' %')
  AND a.indexprs IS NULL AND b.indexprs IS NULL  -- expression indexes: compare by hand
  AND a.indpred  IS NULL AND b.indpred  IS NULL  -- partial indexes: likewise
  AND NOT a.indisunique                          -- a unique index enforces something
  AND (a.indnatts < b.indnatts OR a.indexrelid > b.indexrelid);

Treat the output as candidates, not a drop list: it does not compare collations, sort direction or INCLUDE columns.

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.

Sources#

• PostgreSQL: Documentation: 18: CREATE INDEX — write lock of a plain build, CONCURRENTLY's two table scans and invalid-index failure mode, INCLUDE columns.
• PostgreSQL: Documentation: 18: 14.1. Using EXPLAIN — BUFFERS hit/read counts, Rows Removed by Filter, comparing estimated and actual rows, and estimates under a LIMIT shown as if run to completion.
• PostgreSQL: Documentation: 18: 11.3. Multicolumn Indexes — leading-column efficiency of multicolumn B-tree indexes.
• PostgreSQL: Documentation: 18: 11.8. Partial Indexes — smaller, cheaper-to-maintain partial indexes; parameterised clauses cannot match them.
• PostgreSQL: Documentation: 18: 11.9. Index-Only Scans and Covering Indexes — visibility map dependency and heap fetches for index-only scans.
• PostgreSQL: Documentation: 18: 8.14. JSON Types — jsonb_path_ops operators (@>, @?, @@) and its smaller index size.
• PostgreSQL: Documentation: 18: 65.5. BRIN Indexes — per-block-range min/max summaries and reliance on physical correlation.
• PostgreSQL: Documentation: 18: F.32. pg_stat_statements — track statistics of SQL planning and execution — the total_exec_time column.