Part 5 of 5 · 4 min read
Exercises
Easy
Easy 1: Name what EXPLAIN reports (15 minutes)
State what EXPLAIN, placed before a query, reports about that query.
Done when: the database's actual chosen execution plan, including scan type, is correctly named.
Hint
The database's actual chosen execution plan for that query, including which scan type it used.
Easy 2: Name the two scan types (15 minutes)
Name the two scan types a table's row in an execution plan might report.
Done when: full table scan and index scan are both correctly named.
Hint
Full table scan and index scan.
Easy 3: Name the missing-index signal (15 minutes)
State what signal in an execution plan suggests a missing index on a filtered, large table.
Done when: a full table scan reported for that filtered query is correctly named.
Hint
A full table scan reported for a query that filters a specific column on a large table.
Medium
Medium 1: Interpret a plan's fields (25 minutes)
An execution plan reports Seq Scan on orders, estimated rows=50000000, cost=0.00..1200000. State the scan type, and what the estimated rows field suggests about this query.
Done when: a full (sequential) table scan is correctly named, with the explanation correctly noting the high estimated row count suggests most or all of the table is being read.
Hint
A full (sequential) table scan; the very high estimated row count suggests nearly the entire table is being read.
Medium 2: Decide if an index candidate helped (25 minutes)
Before adding an index, a query's plan reports a full scan. After adding an index on the filtered column, the plan reports an index scan with a much lower cost. Decide whether the index helped this query.
Done when: yes is correctly concluded, citing the change from full scan to index scan and the lower cost as confirmation.
Hint
Yes, the change from a full scan to a cheaper index scan directly confirms the index helped this query.
Medium 3: Explain why cost is not comparable across databases (30 minutes)
Explain why a cost value of 8.31 from one database's execution plan cannot be directly compared to a cost value of 8.31 from a different database's plan.
Done when: the explanation correctly notes cost is a relative number meaningful only for comparing plans within the same database's own optimizer, not an absolute, universal unit.
Hint
Cost is a relative number each database's optimizer uses internally to compare its own plans; it carries no standardized, cross-database unit of measurement.
Hard
Hard 1: Diagnose a full scan despite an existing index (30 minutes)
A query filters WHERE placed_on = '2026-01-01', and an index exists on (customer_id, placed_on), but the plan still reports a full scan. Explain why.
Done when: the explanation correctly ties this to the composite-index page's leading-column rule: placed_on is not the leading column of this index, so the index gives little help to a query filtering only on placed_on.
Hint
placed_on is the second, non-leading column of the (customer_id, placed_on) index, so filtering on it alone cannot use the index efficiently, per the composite-index rule.
Hard 2: Use EXPLAIN to evaluate a candidate index before committing to it (35 minutes)
Describe a safe, direct process for deciding whether a proposed new index is actually worth adding to a production table.
Done when: the described process correctly includes running EXPLAIN before adding the index, adding it, running EXPLAIN again, and comparing scan type and cost to confirm an actual improvement, rather than assuming one.
Hint
Run EXPLAIN before adding the index to record the baseline plan, add the index, run EXPLAIN again, and compare scan type and cost directly to confirm the change actually helps.
Hard 3: Weigh a confirmed index against its write cost (40 minutes)
An EXPLAIN comparison confirms a candidate index would change a frequent query from a full scan to a cheap index scan, but the table receives very heavy write traffic. Explain how this page's confirmation should be combined with the previous page's write-cost concerns before deciding to add the index.
Done when: the explanation correctly states that confirming a read benefit via EXPLAIN is necessary but not sufficient; the index's write-maintenance cost on the heavily written table must still be weighed against that confirmed benefit before deciding.
Hint
A confirmed read benefit from EXPLAIN only addresses one side of the trade; the index's ongoing write-maintenance cost on a heavily written table must still be weighed against that benefit, as covered on the "When indexes hurt" page, before deciding to add it.