Reading a query plan without fear

Most people run EXPLAIN, see a wall of text, and go back to guessing. The output is dense, but you can get most of the value from three questions.

Did it use the index you expected?

A sequential scan on a large table where you have an index is the headline finding. Common causes: the column is wrapped in a function, the query uses a leading wildcard, or the condition is not selective enough to be worth it.

Note that a sequential scan is not automatically wrong. On a small table, or a query returning most rows, scanning is genuinely faster than an index lookup plus thousands of row fetches. The planner is often right; the point is to know whether it made the choice you assumed.

Are the row estimates close to reality?

Run EXPLAIN ANALYZE and you get estimated *and* actual row counts. The gap between them is the most useful signal in the whole output.

When the planner expects fifty rows and gets fifty thousand, every downstream decision was made for a query that does not exist — it will pick a join strategy suited to tiny inputs and then feed it enormous ones.

That is usually stale statistics, and running ANALYZE on the table fixes it. It is a remarkably common cause of "this query got slow and nothing changed".

The gap between estimated and actual rows explains more bad plans than any other single number.

Which join strategy, and is it appropriate?

Nested loops are excellent when one side is tiny and terrible when both are large. Hash joins suit large unsorted inputs. Merge joins want sorted inputs and pair well with indexes that already provide the order.

A nested loop over two large tables is the classic pathological plan, and it almost always traces back to the row estimate problem above rather than to the join itself.

The things that quietly cost

A sort appearing in the plan means the database is ordering in memory — or on disk if the set is large enough, which is dramatically slower. An index providing the order removes the step entirely.

Filters applied late mean rows were read and then discarded. Pushing the condition down so fewer rows are read in the first place is usually a bigger win than speeding up what happens after.

Getting the plan from Rails

.explain on any relation prints it, and EXPLAIN ANALYZE needs the real thing in a console against production-shaped data.

The habit worth forming: when you write a query that will run on a large table, read its plan once before shipping it. It takes a minute and it is the difference between knowing and hoping.