How to read a query plan without fear

A query plan is the database explaining how it intends to answer your query. It looks intimidating, but a few ideas get you most of the way.

Read it from the inside out

A plan is a tree. The most indented steps run first, usually reading tables, and their results flow upward into joins, sorts and aggregates. The top of the tree is the final answer.

Look for the expensive step

Every step has a cost estimate (and, with an analyzed plan, a real time). Find the step that dominates. In DB Studio Pro, Explain draws the plan as a tree with a bar on each step scaled to its cost, so the heavy step stands out immediately. It works for PostgreSQL, MySQL, SQL Server, Oracle and SQLite.

Common things to spot

• A sequential (full) scan of a large table where you expected an index. Check whether the filter matches an index, and whether the column is wrapped in a function that prevents its use.
• A sort that spills to disk. Often fixed by an index that returns rows already in order, or by selecting fewer columns.
• A join that multiplies rows. If a step returns far more rows than the tables hold, a join condition is probably missing or too loose.
• Estimates far from reality. When the planner expects 10 rows and finds 100,000, its statistics are stale. Refreshing them (for example ANALYZE on PostgreSQL) often fixes the plan.

Change one thing at a time

Add an index, run Explain again, compare. Changing three things at once tells you nothing.

Plans differ between databases, and a plan on a small development copy may not match production. Treat them as evidence, not as a verdict.