Reading a Postgres Query Plan Before You Add an Index

Run EXPLAIN ANALYZE on the actual slow query, not a simplified version of it, since query plans change based on the real data distribution and the real filter values. Look for a sequential scan on a large table where you expected an index scan, since that's the clearest sign a missing or unusable index is the problem.

Also check the gap between the planner's estimated row count and the actual row count at each step; a big gap usually means Postgres's statistics are stale, which can cause it to pick a bad plan even when the right index already exists.

A sequential scan on a small table is usually faster than an index scan would be, since the overhead of using an index exceeds just reading the whole table when the table's small enough to fit in memory. A query that filters on a low-selectivity column, like a boolean column where almost every row shares the same value, gets little benefit from an index on that column alone, since an index only helps when it meaningfully narrows the result set.

And a query that's slow because it's returning and processing an enormous result set has a query-shape problem, usually solved by pagination or a narrower filter, not an indexing problem at all.

A composite index on status and created-date supports a query filtering on status alone, or on status and created-date together, but it doesn't help a query filtering on created-date alone, because a composite index is only usable from its leftmost column inward.

Put the column your queries filter on most consistently first, and the one that narrows the result set most, usually an equality filter before a range filter, ahead of a less selective one. Getting the order wrong is a common reason a team adds an index and sees no improvement, then concludes indexing didn't help when the real issue was column order.

Every index speeds up the reads it covers and slows down every write to that table, since the database has to maintain the index on every insert, update, and delete. An over-indexed table with many overlapping or rarely-used indexes pays that write cost on every mutation for indexes that queries barely use.

Periodically check for unused indexes, most databases expose usage statistics for this, and drop them; an index that never gets used in a query plan is pure write overhead and storage cost with no offsetting benefit.

Before you add an index, confirm each of these:

• EXPLAIN ANALYZE on the real slow query shows a sequential scan on a large table, not a small table that fits in memory.
• The filtered column is selective enough that an index skips most rows, unlike a boolean column where nearly every row shares the same value.
• Planner row estimates match actual row counts at each step, because a big gap points to stale statistics rather than a missing index.
• Composite indexes lead with the column your queries filter on most consistently, since they only work from the leftmost column inward.
• Existing indexes are actually used, because each unused one still slows every insert, update, and delete.

Say a dashboard query filtering on an organization ID and a date range takes several seconds, and EXPLAIN ANALYZE shows a sequential scan on a multi-million-row table. Adding a composite index with the organization ID first, since every query filters on it and it's highly selective, turns that sequential scan into an index scan touching only the rows for one organization's date range.

If the plan still shows a sequential scan after adding the index, check whether the planner's row estimates are stale, since Postgres won't use an index it doesn't expect to help, even when it would.

If a query only ever filters for rows in one particular state, active orders, unread notifications, pending approvals, and that state describes a small fraction of the table, a full index on the column wastes space and write overhead maintaining entries for rows no query will ever ask for. A partial index, built with a WHERE clause matching that same filter, indexes only the relevant rows, staying smaller and faster to maintain while still fully supporting the specific query it was built for.

This is an easy win teams miss because a standard index already works, just less efficiently than a partial one would, so there's no error prompting anyone to look closer.

A large delete or bulk update leaves index bloat behind, dead entries that still occupy space and slow down scans until routine maintenance cleans them up. If a table has recently gone through a large deletion and query performance on it hasn't improved the way you'd expect, check whether index maintenance has actually run, rather than assuming the index itself needs rebuilding from scratch. Most databases handle this automatically on a schedule, but a sudden large one-off deletion can outpace that schedule and benefit from a manual pass.