Query Execution Plan: The Database's Road Map
A query execution plan is the database's internal strategy for fetching your data. It's the recipe it creates before running your SQL. This plan determines whether to use an index or scan a whole table, directly impacting performance.
Why it exists
A single SQL query can be executed in many different ways. For example, joining two tables could be done by scanning one and looking up matches in the other, or by sorting both and merging them. The database needs a systematic way to choose the most efficient method to avoid disastrously slow performance.
The mental model
Think of a query plan as a GPS route for your data. You tell the GPS (the database) your destination (the SELECT statement) and your starting points (the FROM tables). The GPS (the query optimizer) then calculates the fastest route (the execution plan) based on traffic conditions (table statistics), available roads (indexes), and route options (join algorithms). It usually picks a great route, but sometimes it gets it wrong.
How it works
When you submit a SQL query, it first goes to a parser, which checks syntax. Then, the query optimizer generates multiple potential execution plans. It uses statistics about the data—like table sizes, column value distribution, and available indexes—to estimate the "cost" (usually I/O and CPU time) of each plan. It then selects the plan with the lowest estimated cost. This final plan is a tree of operations (like SCAN, JOIN, SORT) that the database execution engine follows.
When to use it
You don't "use" a query plan directly; the database creates one for every query. You interact with it when debugging a slow query. Tools like EXPLAIN (in PostgreSQL and MySQL) or EXPLAIN PLAN (in Oracle) show you the chosen plan. Analyzing the plan helps you identify bottlenecks, such as a full table scan where an index scan was expected, or a bad join order.
When not to use it
This isn't a tool you turn on or off. For extremely simple queries on tiny tables, the plan is trivial and not worth analyzing. The focus on analyzing plans is for queries that are complex or run against large datasets where performance matters and you've identified a problem.
One canonical example
You run EXPLAIN SELECT * FROM users WHERE country = 'Canada';. The plan shows a "Seq Scan" (Sequential Scan). This means the database is reading every single row to find the matching users. After seeing this, you add an index on the country column. Running EXPLAIN again now shows an "Index Scan," which is much faster because it uses the index to jump directly to the relevant rows.
Interview question
What is the primary purpose of a database's query execution plan?
- a.To identify the most cost-effective sequence of operations for data retrieval.Correct
- b.To validate the SQL query's syntax and structure before processing.
- c.To convert the SQL query into a low-level machine code for direct execution.
- d.To cache the results of frequently run queries for faster subsequent access.
Why? this is the answer
The query optimizer generates the plan to choose the most efficient method for data retrieval, estimating the 'cost' of various options and selecting the one with the lowest cost. Syntax validation is handled by the parser before plan generation.
Just read this? Test yourself on what you have been reading.
Read the original → en.wikipedia.org
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 database — each one lists the topics its interview covers.
See open roles