Database Explain Plan — Vocabulary and Communication
Learn vocabulary for reading and discussing query execution plans: sequential scan, index scan, cost estimates, and joins.
0 / 18 completed
1 / 18
What is a query 'execution plan' (or explain plan) in database vocabulary?
An execution plan (EXPLAIN in PostgreSQL/MySQL, Execution Plan in SQL Server) shows how the database engine will execute a query: table access methods (seq scan vs. index scan), join algorithms, sort operations, and estimated row counts and costs at each step.
2 / 18
What is the difference between a 'sequential scan' and an 'index scan'?
Sequential scan: reads all table pages linearly — efficient for small tables or low-selectivity queries (returning >10–20% of rows). Index scan: traverses the B-tree index to find matching rows — efficient for high-selectivity queries. The optimizer chooses based on row count estimates and cost.
3 / 18
What does 'cost' mean in a database execution plan?
Cost in execution plans is an abstract optimizer unit (not seconds or dollars). A cost of 100 vs. 10,000 means the optimizer estimates the latter is 100x more expensive. Costs are based on statistics (row counts, selectivity) and cost parameters (sequential_page_cost, random_page_cost in PostgreSQL).
4 / 18
What is a 'nested loop join' in execution plan vocabulary?
Nested loop join: for each outer row, scan inner table for matches. Best when outer table is small and inner table has an index (index scan per outer row). For large unindexed tables, hash join or merge join is typically more efficient.
5 / 18
What does 'rows' estimate mean in an explain plan output?
The rows estimate is the optimizer's prediction (not actual) based on table statistics (ANALYZE output). A large discrepancy between estimated and actual rows (shown in EXPLAIN ANALYZE) indicates stale or inadequate statistics — often the root cause of poor plan choices.
6 / 18
Sarah: 'The explain plan shows a 'hash join' with an estimated cost of 10. This seems really slow! What does the 'cost' value actually represent here? I'm seeing a lot of rows predicted, but it's still taking a long time.
Reviewer (you):
The 'cost' value within an explain plan is not simply a measurement of time or resource usage. It's a *predicted* cost based on the planner's analysis of table statistics (cardinality estimates) and available indexes. A higher cost indicates that the database optimizer believes this particular execution strategy will require more resources to complete, often due to factors like full table scans or inefficient joins – it's a relative estimate for comparison.
7 / 18
PR Description:
"Running a query to retrieve all users with orders placed in the last week. The explain plan shows a 'full table scan' and a cost of 25. I'm concerned about performance. Can someone review this?"
A 'full table scan' in an explain plan means the database is reading every row in the table to find matching records. While sometimes acceptable for small tables, it's generally inefficient because the database doesn't use an index to speed up the search. The 'cost' value reflects the estimated resources (CPU, I/O) required to perform that scan; a high cost indicates a potentially slow operation. The incorrect options assume optimal performance or mistake 'full table scan' for something else, failing to grasp its implications.
8 / 18
Sarah: 'The explain plan shows a 'hash join' with an estimated cost of 10. This seems really slow! What does the 'cost' value actually represent here? I'm seeing a lot of rows predicted, but it's still taking a long time.
Reviewer (you):
The 'cost' value within an explain plan is not simply a measurement of time or resource usage. It's a *predicted* cost based on the planner's analysis of table statistics (cardinality estimates) and available indexes. A higher cost indicates that the database optimizer believes this particular execution strategy will require more resources to complete, often due to factors like full table scans or inefficient joins – it's a relative estimate for comparison.
9 / 18
PR Description:
"Running a query to retrieve all users with orders placed in the last week. The explain plan shows a 'full table scan' and a cost of 25. I'm concerned about performance. Can someone review this?"
A 'full table scan' in an explain plan means the database is reading every row in the table to find matching records. While sometimes acceptable for small tables, it's generally inefficient because the database doesn't use an index to speed up the search. The 'cost' value reflects the estimated resources (CPU, I/O) required to perform that scan; a high cost indicates a potentially slow operation. The incorrect options assume optimal performance or mistake 'full table scan' for something else, failing to grasp its implications.
10 / 18
Sarah: 'The explain plan shows a 'hash join' with an estimated cost of 10. This seems really slow! What does the 'cost' value actually represent here? I'm seeing a lot of rows predicted, but it's still taking a long time.
Reviewer (you):
The 'cost' value within an explain plan is not simply a measurement of time or resource usage. It's a *predicted* cost based on the planner's analysis of table statistics (cardinality estimates) and available indexes. A higher cost indicates that the database optimizer believes this particular execution strategy will require more resources to complete, often due to factors like full table scans or inefficient joins – it's a relative estimate for comparison.
11 / 18
PR Description:
"Running a query to retrieve all users with orders placed in the last week. The explain plan shows a 'full table scan' and a cost of 25. I'm concerned about performance. Can someone review this?"
A 'full table scan' in an explain plan means the database is reading every row in the table to find matching records. While sometimes acceptable for small tables, it's generally inefficient because the database doesn't use an index to speed up the search. The 'cost' value reflects the estimated resources (CPU, I/O) required to perform that scan; a high cost indicates a potentially slow operation. The incorrect options assume optimal performance or mistake 'full table scan' for something else, failing to grasp its implications.
12 / 18
Sarah: 'The explain plan shows a 'hash join' with an estimated cost of 10. This seems really slow! What does the 'cost' value actually represent here? I'm seeing a lot of rows predicted, but it's still taking a long time.
Reviewer (you):
The 'cost' value within an explain plan is not simply a measurement of time or resource usage. It's a *predicted* cost based on the planner's analysis of table statistics (cardinality estimates) and available indexes. A higher cost indicates that the database optimizer believes this particular execution strategy will require more resources to complete, often due to factors like full table scans or inefficient joins – it's a relative estimate for comparison.
13 / 18
PR Description:
"Running a query to retrieve all users with orders placed in the last week. The explain plan shows a 'full table scan' and a cost of 25. I'm concerned about performance. Can someone review this?"
A 'full table scan' in an explain plan means the database is reading every row in the table to find matching records. While sometimes acceptable for small tables, it's generally inefficient because the database doesn't use an index to speed up the search. The 'cost' value reflects the estimated resources (CPU, I/O) required to perform that scan; a high cost indicates a potentially slow operation. The incorrect options assume optimal performance or mistake 'full table scan' for something else, failing to grasp its implications.
14 / 18
Mark is reviewing a PR that includes a query to get all active users. The explain plan shows a 'full table scan' on the `users` table and a cost of 15. He asks you: 'What does this high 'cost' value likely indicate about the query's performance?'.
A high 'cost' value in an explain plan typically indicates that the database is using a less efficient strategy for retrieving data. Specifically, a full table scan implies the database is examining every row in the `users` table to find active users, which is inefficient compared to utilizing indexes. The cost is essentially a measure of how much the database believes this operation will take.
15 / 18
Code Review Comment:
`user@team: > SELECT * FROM orders WHERE customer_id = 123;`
The explain plan shows a 'hash join' with an estimated cost of 5 and 10,000 rows predicted. Liam says: 'This looks fast but I'm not sure if the estimate is accurate. What does this 'cost' value *really* mean in this context?'
The 'cost' in an explain plan is an *estimate* of how much time and resources the database thinks a particular operation will take. It's not a direct measure of memory or concurrency; instead, it combines factors like index usage, data size, and join algorithms to provide an overall performance prediction. This estimate drives the query optimizer's choices.
16 / 18
Alex (a junior developer) sends this message in a Slack channel:
'I'm getting a slow performance warning on this query: `SELECT * FROM products WHERE category_id = 42;`. The explain plan shows a 'nested loop join' with an estimated cost of 12. I don't understand what that means.'
A 'nested loop join' is a type of join algorithm where one table's rows are iterated through for each row of another table. This can be efficient when one table is small and indexed, but it becomes less performant as the tables grow larger. The cost reflects this complexity.
17 / 18
PR Description:
'Implemented a new query to retrieve recent orders for each customer. The explain plan shows a 'merge join' with an estimated cost of 8 and 50,000 rows predicted. I'm seeing this high cost. What does it mean?'.
A 'merge join' algorithm efficiently combines data from two sorted tables. However, in the context of an explain plan, a high cost with a large row estimate suggests it's performing a full scan – essentially sorting and merging the entire table rather than using indexes, which would be far more efficient for this scenario.
18 / 18
During a standup meeting, David says: 'I'm investigating a slow query that gets order details. The explain plan shows a 'bitmap index scan' with an estimated cost of 3 and 10,000 rows predicted. I'm not sure what this 'cost' value means.'
A 'bitmap index scan' is an efficient way to retrieve rows based on a predicate when the index contains bitmap representations of the values. The cost here represents how effectively this technique utilizes the indexed data for filtering – it's a relatively low-cost operation compared to a full table scan because the database only needs to examine the relevant bits in the bitmap index.
What does the "Database Explain Plan — Vocabulary and Communication" exercise practise?
Learn vocabulary for reading and discussing query execution plans: sequential scan, index scan, cost estimates, and joins.
How many questions are in this exercise?
This exercise has 18 questions, each multiple-choice with a full explanation shown after you answer.
What English level is this exercise for?
This exercise is tagged Intermediate. If the vocabulary feels difficult, browse the Database Optimization category page for an easier module to start with.
Is this exercise free to use?
Yes. Every exercise on CoderSlingo, including this one, is free with no account, sign-up, or paywall.
Do I get feedback if I answer incorrectly?
Yes — whichever option you choose, right or wrong, you'll immediately see an explanation clarifying the correct term and why the other options don't fit.
Can I retry this exercise?
Yes — once you finish all the questions, a "Try again" button on the results screen resets the exercise so you can practise as many times as you like.
Do I need an account to track my progress?
No account is required. Your progress bar and score for this session are tracked in the browser as you go, but nothing is saved once you leave the page.
Is "Database Explain Plan — Vocabulary and Communication" part of a larger series?
Yes — it's one exercise in the Database Optimization category on CoderSlingo. See the category page for the full list of related exercises on similar terminology.
Can I link directly to this exercise?
Yes — this exercise has its own permanent URL, so you can bookmark it or share the link directly with a colleague or study partner.
Where can I find more exercises like this one?
See the Database Optimization category page for related exercises, or browse the main Exercises hub for other IT English topics.