Read the query plan before you add another index

The claim

Adding an index because a query is slow, without first reading the plan the database uses to run it, is guessing — and it frequently makes things worse, because every index you add slows every write and consumes cache whether or not it helps the read. EXPLAIN ANALYZE tells you exactly what the database is doing and why, and learning to read three things in its output replaces guesswork with diagnosis.

EXPLAIN versus EXPLAIN ANALYZE

EXPLAIN alone shows the plan the database intends to use, with estimated costs. EXPLAIN ANALYZE actually runs the query and shows what really happened — real timings, real row counts. Use ANALYZE, because the gap between estimate and reality is often where the problem lives, and add BUFFERS to see how much data was read from cache versus disk:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 8812 AND status = 'shipped'
ORDER BY created_at DESC;

One warning: ANALYZE executes the query, so on an UPDATE or DELETE it will change data. Wrap those in a transaction you roll back.

The first thing to read: scan type

Near the bottom of the plan is how the database accessed each table, and this is usually the whole story:

• Seq Scan reads every row in the table. Correct for small tables and for queries that genuinely need most rows; a problem when a query filtering to a few rows out of millions is doing it, because it means no usable index exists for that filter.
• Index Scan uses an index to find matching rows directly. This is usually what you want for a selective filter.
• Bitmap Heap Scan sits in between — it uses an index to find many matching rows efficiently, common and healthy for filters that match a moderate fraction of the table.

A Seq Scan on a large table under a selective WHERE clause is the single most common finding, and the one an index actually fixes.

The second thing: the estimate-versus-actual gap

Every node shows rows= (the planner's estimate) and, under ANALYZE, the actual count it produced. When these diverge badly — the planner expected 10 rows and got 40,000 — the database is making bad decisions based on stale or missing statistics, and no index will fully fix that until the statistics are corrected:

ANALYZE orders;   -- refresh the planner's statistics

A large estimate error is a distinct diagnosis from a missing index, and it is frequently the real cause of a query that "suddenly" got slow after a big data change. Adding an index to compensate for bad statistics treats the symptom and leaves the disease.

The third thing: where the time actually goes

In a plan with several nodes, the time is not spread evenly. Read the actual time on each node and find the one that dominates — that is the only node worth optimising. A common trap is optimising a node that takes 3% of the total because it is easy to understand, while the 90% node sits untouched. The plan is a tree; the expensive branch is the one to cut.

Watch specifically for a Nested Loop whose inner side runs many times over a large table — this is how a join that looks innocent becomes quadratic, and it usually means an index is missing on the join column, not the filter column.

Then, and only then, add the index

Once the plan has told you which filter or join lacks an index, add the one the query needs — and make it match the query, including sort order and multiple columns where the query uses them:

CREATE INDEX CONCURRENTLY idx_orders_cust_status_created
  ON orders (customer_id, status, created_at DESC);

Column order matters: put the columns used for equality first, the range or sort column last. Then re-run EXPLAIN ANALYZE and confirm two things — that the scan changed from Seq Scan to Index Scan, and that the actual time dropped. If the plan did not change, the index is not being used, and adding a second guess on top of the first is how tables end up with a dozen indexes and no faster queries.

The discipline in one sentence

Read the plan, find the dominant node, identify why it is slow — missing index, stale statistics, or a genuinely large amount of necessary work — and act on that specific cause. An index added from the plan is a fix; an index added from a hunch is a liability that slows every insert for a read it may not even help. The plan is free, it is instant, and it turns database performance from folklore into something you can actually see.