Postgres performance: the indexes you're missing

When a Rails app gets slow, the cause is usually the database, and the cause within the database is usually a missing index. An index turns a query that scans every row of a million-row table into one that jumps straight to the handful it needs — the difference between seconds and milliseconds. Yet indexing is widely misunderstood: people under-index (and suffer slow queries), over-index (and slow down writes), or index the wrong way (and the planner ignores their index entirely). Here is how to index Postgres properly.

What an index is, briefly

A B-tree index — the default and the one you want 95% of the time — is a sorted data structure that lets Postgres find rows matching a value (or a range) without scanning the whole table. The trade is the thing to keep in mind: an index makes reads on the indexed columns dramatically faster, but it makes writes slightly slower (every insert/update must maintain the index) and takes disk space. So indexing is not “more is better” — it is “index what you query, and nothing you don’t”.

What to index

The high-value targets, in order:

• Foreign keys. This is the most commonly missed index and the highest-impact one. Rails creates a belongs_to association and you query post.comments, which runs WHERE post_id = ? — and if comments.post_id is not indexed, that is a full table scan every time. Postgres does not index foreign keys automatically, and Rails historically did not either, so an enormous number of apps have unindexed FKs causing slow association lookups and slow JOINs. Index every foreign key.
• Columns in WHERE clauses. Anything you regularly filter on — status, email, published_at — wants an index if the table is large.
• Columns in ORDER BY. Sorting a large result set without an index means Postgres sorts in memory (or on disk); an index that matches your sort order lets it read rows already sorted.
• Columns with a uniqueness requirement. A unique index enforces the constraint and speeds lookups.

The guiding question for any column: does a query filter, join, or sort on this, on a table big enough for it to matter? If yes, index it.

Composite indexes and the column-order rule

When queries filter on several columns together, a composite index (on multiple columns) beats separate single-column indexes — but only if the column order is right, and this is where people go wrong. A composite index on (a, b, c) can serve queries filtering on a, on (a, b), or on (a, b, c) — a left-to-right prefix — but not a query filtering on b alone, or c alone. The leftmost columns must be used:

-- serves WHERE status = ? , and WHERE status = ? AND created_at > ?
CREATE INDEX idx_orders_status_created ON orders (status, created_at);

-- does NOT efficiently serve WHERE created_at > ?  (created_at isn't a prefix)

So order composite-index columns by how you query: equality-filtered columns first, then range or sort columns. Getting the order wrong gives you an index the planner cannot use for your actual query — an index you pay for on every write and gain nothing from on reads.

Partial and covering indexes

Two more advanced kinds earn their keep in specific situations:

• Partial indexes index only the rows matching a condition, which makes them smaller and faster when you only ever query a subset. The classic case is a soft-delete or status flag — if you almost always query active records, index only those:

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

  The index is a fraction of the size of a full one and serves the query you actually run.

• Covering indexes include extra columns so the query can be answered from the index alone, without visiting the table at all (an “index-only scan”). Postgres 11 adds INCLUDE for exactly this:

  CREATE INDEX idx_orders_status ON orders (status) INCLUDE (total);
  -- a query selecting `total` filtered by `status` never touches the heap

  When a hot query reads only a couple of columns, a covering index can make it dramatically faster by avoiding the table lookup entirely.

Read EXPLAIN — the planner tells you the truth

You should not guess whether an index helps; Postgres will tell you. EXPLAIN ANALYZE shows the query plan and the actual time, and the single most important thing to look for is Seq Scan vs Index Scan:

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'paid';

A Seq Scan on a large table means Postgres is reading every row — usually a missing or unusable index. An Index Scan (or Index Only Scan) means it is using the index. If you added an index and still see a Seq Scan, the index is wrong for the query (often the composite column-order mistake), or the table is small enough that a scan is genuinely cheaper (the planner is smart about this). Learning to read EXPLAIN turns indexing from superstition into a measurable exercise — you make a change and see the plan change.

Find the indexes you’re missing — and remove the ones you don’t use

Postgres tracks statistics that point you straight at problems:

• Missing indexes: pg_stat_user_tables records seq_scan (sequential scans) per table. A large table with a high and growing seq_scan count is a table being scanned a lot — a strong candidate for a missing index. Find the queries hitting it (your APM or pg_stat_statements) and index what they filter on.
• Unused indexes: pg_stat_user_indexes records idx_scan (how often each index is used). An index with idx_scan near zero on a busy database is dead weight — it costs you write performance and disk for no read benefit. Drop it. Over-indexing is as real a problem as under-indexing, and unused indexes accumulate silently as queries change over the years.

This is the empirical loop: the database’s own statistics tell you which tables lack indexes and which indexes earn their keep, so you index by evidence rather than guesswork.

Verdict

The large majority of slow queries are a missing or wrong index away from fast, and indexing well is a small, learnable discipline. Index your foreign keys (the most-missed, highest-impact ones), your WHERE and ORDER BY columns on large tables, and your uniqueness constraints. Order composite-index columns by your query shape (equality first, then range/sort), reach for partial indexes when you query a subset and covering indexes for hot read-only queries, and verify with EXPLAIN ANALYZE rather than guessing. Then let the database’s statistics guide you — seq_scan counts to find missing indexes, idx_scan counts to remove unused ones — so your indexing stays matched to how the app actually queries. Done this way, the database stops being the bottleneck, and most of your “the app is slow” incidents simply stop happening.