Why developers read query plans
basic EXPLAIN literacy.
the plan shows the operators the optimizer chose to run a query; developers read it to find why a query is slow; a common thing to look for is a full table scan where an index was expected.
What's really being asked
The interviewer checks baseline competence: do you know what a plan is, how to get it, and one useful thing it tells you.
The full answer
A query execution plan is the step-by-step strategy the database optimizer chose to satisfy a query, expressed as a tree of operators such as table scans, index scans, joins, filters, sorts, and aggregations, usually annotated with estimated cost and the estimated number of rows at each step. You obtain it by prefixing the query with EXPLAIN, or EXPLAIN ANALYZE to actually run the query and see real timings and row counts. It is useful because it reveals how the database will actually fetch data rather than how you wrote the SQL, so when a query is slow you can see the cause instead of guessing. A common thing to look for is a sequential or full table scan on a large table where you expected an index to be used, which often points to a missing index, a non-sargable predicate, or stale statistics. You also check that joins use sensible algorithms and that row estimates look believable.
The mistakes people make
Not knowing how to obtain a plan. Confusing the plan with the SQL text itself. Reading cost as milliseconds. Assuming every scan is a problem when a scan can be optimal for small tables.
What usually comes next
What is the difference between EXPLAIN and EXPLAIN ANALYZE; what does a sequential scan imply; how do you fix one; what are estimated versus actual rows.
A concrete example
A developer notices a user-lookup endpoint is slow, runs EXPLAIN ANALYZE on its query, and sees a sequential scan over a million-row users table for WHERE email equals a value. That tells them no usable index exists on email; adding one changes the plan to a fast index scan and resolves the latency.
Interview question
A developer runs EXPLAIN on a slow lookup and sees a sequential scan on a large table for an equality filter. What is the most likely takeaway?
- a.The database is corrupted and needs rebuilding
- b.The query is optimal because scans are always efficient
- c.A usable index on the filtered column is probably missingCorrect
- d.The plan is wrong and should be ignored
Why? this is the answer
A full scan for a selective equality filter usually signals a missing or unusable index, which adding one would fix. Scans are not always optimal, and the plan reflects a real optimizer decision rather than corruption.
Just read this? Test yourself on what you have been reading.
Read the original → en.wikipedia.org
- #databases
- #explain
- #query-plan
- #performance
- #indexing
Put your scrolling time to good use
Learn one idea, try a quiz and save useful cards for revision. Tezvyn makes it easy to learn and stay current in your tech field, a few minutes at a time.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on databases — each one lists the topics its interview covers.
See open roles