Reading a Query Plan
The database chooses how to run a query: which index to use, which join method, in what order. The plan is that choice written down with estimated row counts, and reading it is how a slow query stops being a mystery.
What a learner can do afterwards
- Read a plan and name the access method chosen for each table
- Find the step where the estimated row count disagrees with reality
- Explain why a nested loop join beats a hash join on small inputs
1 · Read
A query plan is the database choice written down. It names the access method for each table, the join method, and the join order. Each step carries an estimated row count.
The access method is how one table is read. An index scan follows an index to a few rows. A full scan reads the whole table, which wins when most rows qualify.
A plan shows a full scan feeding a join, estimating 10 rows while 10,000 rows really arrive. The wrong estimate picked the wrong method, and that step costs the most.
A nested loop join wins on small inputs because it needs almost no setup. On big inputs a hash join wins instead. To fix the costly step, correct the estimate, add the missing index, or change the join order.
Read the plan, check each estimate against reality, and fix the step that lies.
2 · Watch
Take it off screen
Where it sits
8 questions wait behind this lesson, each with its answer explained. Every answer feeds the sky: stars light as they are learned, and dim when it is time to come back.