Understanding SQL Indexes and Query Performance

Part 10 of 16 · ~3 min

Reading an Execution Plan

Every claim this reference has made up to this point has really just been a guess at what the optimizer might choose to do. Finding out what it actually did requires the execution plan — nothing else — and you've been reading them since chapter 3.

Guessing from the SQL text does not work, and the previous chapter is why: the optimizer's choice depends on statistics and data distribution that are invisible in the query. Two queries differing only in a literal — 'Sales' versus 'Facilities' — produce genuinely different plans.

EXPLAIN QUERY PLAN
SELECT name, dept, salary
FROM Employees
WHERE dept = 'Sales' AND salary BETWEEN 60000 AND 90000
ORDER BY salary;

The command varies: EXPLAIN QUERY PLAN in SQLite, EXPLAIN in Postgres and MySQL, EXPLAIN ANALYZE in Postgres and MySQL, which runs the query for real and comes back with measured timings and real row counts instead of estimates, and a graphical plan in SQL Server. Reach for the analyze variant whenever you can, with one caution — it genuinely executes the statement, so never point it at an UPDATE or DELETE outside a transaction you intend to roll back.

What to look for, roughly in descending order of how often it is the answer:

• The access path per table. In SQLite, SEARCH is a seek and SCAN is a full scan. There is one phrase that catches people out, and you have already seen it: SCAN Employees USING INDEX idx_emp_salary is not a table scan. It means the engine is walking the entire index in order — usually to get sorted output without a separate sort — which is a completely different thing from SCAN Employees with no index named. Read the whole line, not the first word.
• How the estimated row counts compare to the actual ones — a comparison only the analyze variant can show you. This is the highest-signal thing in any plan: a step that expected 200 rows and got back 400,000 means the optimizer built its strategy on a false premise, and the fix is almost always statistics, not the query itself.
• Sort and temp-structure steps. USE TEMP B-TREE FOR ORDER BY in SQLite, or a Sort node in Postgres, means the engine is ordering rows at query time. That is often removable with the right index column order — chapter 5 removed exactly this one — and it is expensive on a large result because it cannot emit the first row until it has seen the last.
• Key lookups appearing per row. In SQLite, the tell is the absence of the word COVERING on a SEARCH line. It points straight at a covering index as a candidate fix.
• Where the time is actually going, in an analyzed plan. Multi-step plans almost always have one step dominating. Optimizing any other step is wasted effort, no matter how inefficient it looks.

Treat EXPLAIN as the tie-breaker for every performance question in this reference. Leftmost-prefix rules, SARGability, selectivity thresholds — all of it is a model of what the optimizer will probably do. The plan is what it did, on your data, on your engine, at your data size. When the model and the plan disagree, the plan is right.