Postgres Index Optimization: A Practical Guide

Most Postgres performance problems are index problems, and most index problems are not "we have too few indexes" — they are "we have the wrong indexes." A missing index turns a 5 ms lookup into a 5-second scan. A redundant index costs you on every write and nobody notices. This guide is about designing indexes that earn their keep.

How a B-tree index actually works

Postgres's default index type, the B-tree, stores sorted key values with pointers to heap rows. A lookup walks the tree instead of the whole table — roughly O(log n) instead of O(n). That is the entire trick, and it explains every design rule below: an index helps when it lets the planner skip most of the table.

If the planner decides it has to read most of the table anyway, it will ignore your index — correctly. A 10% table is often the cutoff where a scan beats an index. That is why the first question is not "which column should I index" but "how selective is this filter?".

Three questions before adding any index

• What query is this for? Name the exact query pattern — the filter, join, and sort — not a column in the abstract.
• How selective is it? Roughly how many rows match? Below ~10% of the table, an index usually wins. Above that, the planner will rightly ignore it.
• What does it cost to maintain? Every insert, update, and delete now touches the index. A write-heavy table pays for every index you add.

If you cannot answer all three, you are not ready to create the index.

Composite indexes: column order is the design

A composite index on (a, b) serves queries that filter on a alone or on a and b together — but not on b alone. That is the leftmost-prefix rule, and it dictates the order:

• Put equality filters first. WHERE tenant_id = 1 AND created_at > now() - interval '30 days' wants (tenant_id, created_at), because tenant_id narrows instantly and the index then scans a small date range.
• Put the range filter last. Reversed — (created_at, tenant_id) — the index would have to visit every tenant's rows for each day. Same two columns, very different cost.
• Watch out for redundant indexes. If you have (a, b), a separate index on (a) is pure overhead — the composite already covers it.

CREATE INDEX idx_orders_tenant_created
  ON orders (tenant_id, created_at);

Partial indexes: index only what you query

If a hot query always filters on a constant, index only those rows:

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

The index is smaller, updates to rows outside the predicate never touch it, and lookups on pending orders get faster. The tradeoff: the planner will only use it when the query's WHERE implies the same predicate — which is exactly when you want it.

Covering indexes: skip the table round-trip

When a query needs a few columns back, a covering index stores them inside the index itself, so Postgres can answer without visiting the heap:

CREATE INDEX idx_orders_tenant_created_inc
  ON orders (tenant_id, created_at)
  INCLUDE (total, status);

For reporting queries that read the same five columns over and over, this is often the single cheapest win available — at the cost of a fatter index. Use it sparingly, on the columns your slowest read path actually returns.

When NOT to index

• Low-cardinality columns. A status column with three values will be ignored by the planner on most tables. A partial index on the rare value is the exception that earns its place.
• Write-heavy tables. Every additional index adds latency to every write. On an append-only event stream with no read problem, zero new indexes may be the right answer.
• Columns you never filter or sort. An index exists to serve a query pattern. If there is no pattern, there is no index.

Verify the win with EXPLAIN ANALYZE

Never deploy an index on faith. Measure before and after:

EXPLAIN ANALYZE
SELECT id, total
FROM orders
WHERE tenant_id = 7
  AND created_at > now() - interval '30 days'
ORDER BY created_at DESC;

Compare the plan node: a Seq Scan that becomes an Index Scan is a win; a scan that stays a scan means the index is being ignored — usually selectivity or column order. Keep the numbers in the commit message so the next reviewer knows the index was bought, not assumed.