# PostgreSQL Index Strategy That Actually Works

> Reading the plan, ordering composite columns, and choosing the right index type

Canonical: https://everettkildare.com/blog/postgresql-index-strategy-that-actually-works
Author: Everett Kildare
Published: 2026-06-28
Updated: 2026-10-31

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:

```sql
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;
```

```text
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.

> [!TIP]
> 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:

```sql
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.

```text
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.

> [!IMPORTANT]
> 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:

```sql
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:

```sql
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:

```sql
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.

```sql
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.

| Type | Use for | Notes |
|---|---|---|
| B-tree | Equality, ranges, sorting | The default; supports `UNIQUE` |
| GIN | `jsonb`, arrays, full-text | Large, slow to update, very fast to search |
| GiST | Geometry, ranges, nearest-neighbour | Supports exclusion constraints |
| BRIN | Huge tables with physical correlation | Tiny; only for naturally ordered data |
| Hash | Equality only | Rarely worth it over B-tree |

### GIN for jsonb

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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.

> [!WARNING]
> `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](https://www.postgresql.org/docs/current/sql-createindex.html) — 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](https://www.postgresql.org/docs/current/using-explain.html) — `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](https://www.postgresql.org/docs/current/indexes-multicolumn.html) — leading-column efficiency of multicolumn B-tree indexes.
- [PostgreSQL: Documentation: 18: 11.8. Partial Indexes](https://www.postgresql.org/docs/current/indexes-partial.html) — smaller, cheaper-to-maintain partial indexes; parameterised clauses cannot match them.
- [PostgreSQL: Documentation: 18: 11.9. Index-Only Scans and Covering Indexes](https://www.postgresql.org/docs/current/indexes-index-only-scans.html) — visibility map dependency and heap fetches for index-only scans.
- [PostgreSQL: Documentation: 18: 8.14. JSON Types](https://www.postgresql.org/docs/current/datatype-json.html) — `jsonb_path_ops` operators (`@>`, `@?`, `@@`) and its smaller index size.
- [PostgreSQL: Documentation: 18: 65.5. BRIN Indexes](https://www.postgresql.org/docs/current/brin.html) — 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](https://www.postgresql.org/docs/current/pgstatstatements.html) — the `total_exec_time` column.