SQL Quiz: Explain Query Plan
10 questions · exam conditions
0:00
Explain Query PlanQuestion 1 of 10

An orders table has a composite index on (customer_id, order_date). The query SELECT order_id FROM orders WHERE order_date >= '2026-01-01'; produces a plan that scans the entire composite index and then applies the date condition.

Which change most directly enables an efficient range search for this query while preserving its current predicate?

Create an index whose leading column is order_date, because the current index is ordered first by customer_id.
Reverse the comparison to write '2026-01-01' <= order_date, because indexes support constants only on the left.
Add customer_id IS NOT NULL to the predicate, because that activates the first column of the composite index.
Select customer_id as well as order_id, because projecting the leading index column enables a date range search.
← Back to quizzes

SQL Quiz

SQL Quiz: Explain Query Plan

Practice Explain Query Plan in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Explain Query Plan, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

An orders table has a composite index on (customer_id, order_date). The query SELECT order_id FROM orders WHERE order_date >= '2026-01-01'; produces a plan that scans the entire composite index and then applies the date condition.

Which change most directly enables an efficient range search for this query while preserving its current predicate?

  1. Create an index whose leading column is order_date, because the current index is ordered first by customer_id. (correct answer)
  2. Reverse the comparison to write '2026-01-01' <= order_date, because indexes support constants only on the left.
  3. Add customer_id IS NOT NULL to the predicate, because that activates the first column of the composite index.
  4. Select customer_id as well as order_id, because projecting the leading index column enables a date range search.
Explanation: Whenever you see a question about index performance, think about the leftmost prefix rule: a composite index (col_A, col_B) is ordered first by col_A, then by col_B within each col_A group. This means the database can only "jump into" the index efficiently if you filter on the leading column. Without a condition on customer_id, the engine has no choice but to scan every entry in the index and apply the order_date filter afterward — exactly what the passage describes. The fix is A: create a new index with order_date as the leading column. When order_date comes first, all rows are physically sorted by date, so a range condition like >= '2026-01-01' lets the database seek directly to that cutoff and read forward — a true index range scan rather than a full scan. B is wrong because SQL indexes don't care which side of a comparison holds the constant. Writing '2026-01-01' <= order_date is semantically identical to the original predicate and changes nothing about execution. C is wrong because adding customer_id IS NOT NULL doesn't supply a selective value for the leading column — it's nearly always true, so the optimizer gains no useful boundary to seek on and still must scan the entire index. D is wrong because projecting a column (adding it to SELECT) never changes how the optimizer uses an index for filtering. Index selection is driven by WHERE, JOIN, and ORDER BY clauses, not the column list. Study tip: Always ask "what is the leftmost column of my index, and does my query filter on it?" If not, the index won't help your range conditions.

Question 2

An EXPLAIN ANALYZE plan for a join shows a nested-loop operation. Its outer input produces 30,000 rows. The inner operation performs a full scan of devices and reports approximately 30,000 loops while applying devices.serial_no = events.device_serial. No index exists on devices.serial_no.

Which change most directly targets the repeated work visible in this plan?

  1. Replace the equality condition with LIKE, encouraging the optimizer to compare fewer device rows per loop.
  2. Create an index on the outer table's selected output columns, ensuring the nested loop executes only once.
  3. Add ORDER BY devices.serial_no, allowing the final output sort to eliminate repeated inner-table scans.
  4. Create an index on devices.serial_no, allowing each inner lookup to avoid repeatedly scanning the full devices table. (correct answer)
Explanation: When reading an EXPLAIN ANALYZE output, your goal is to identify where repeated work is happening and why. Here, the plan shows a nested-loop join where the inner side scans the entire devices table 30,000 times — once per outer row. That's the bottleneck. The fix must eliminate those redundant full scans on the inner table. Adding an index on devices.serial_no (answer D) directly solves this. With an index, each inner loop no longer reads every row in devices — it performs a fast index lookup for the matching serial_no. What was 30,000 full scans becomes 30,000 efficient point lookups, dramatically reducing I/O and execution time. The index exists on precisely the column being compared in the join condition, making it the most targeted fix possible. Answer A is wrong because switching to LIKE doesn't reduce loops — it actually makes each comparison more expensive and prevents index usage entirely, worsening performance. Answer B is a trap. Indexing the outer table's output columns doesn't help the inner scan at all. Nested-loop performance is dominated by inner-table access patterns, not outer-table column retrieval. Answer C is irrelevant to the repeated-scan problem. Adding ORDER BY affects how the final result is sorted, not how many times the inner table is scanned. It introduces a sort step and does nothing to reduce inner-loop iterations. Study tip: When you see "N loops" on an inner join node in an execution plan, immediately look for a missing index on the join column of the inner table — that's the classic nested-loop performance culprit.

Question 3

An orders table has an index on customer_id. For SELECT order_date, total FROM orders WHERE customer_id = 42;, the plan shows an index search on customer_id followed by table-row lookups. The query returns thousands of orders for that customer.

Which interpretation of the table-row lookups is most accurate?

  1. The index locates matching row references, but the requested columns are not stored in the index, so the executor must fetch those values from the table rows. (correct answer)
  2. The index search did not apply the customer predicate, so the table lookups must re-test every row in the table to find matching orders.
  3. The table lookups confirm that the result rows are being sorted by order_date, even though no ordering was specified in the query.
  4. The table lookups occur because customer_id is a numeric column; storing it as text would automatically make the index cover all requested columns.
Explanation: When reading a query execution plan, the key question to ask is: why is the database going back to the table after using the index? This concept is called a table row lookup (sometimes called a "key lookup" or "RID lookup"), and it almost always comes down to one thing — the index doesn't contain all the columns the query needs. Here's the logic: an index on customer_id stores that column's values along with pointers to the actual table rows. When the executor finds all rows where customer_id = 42, it has the locations of matching rows but not the data in order_date or total. Those columns live in the table itself, so the executor must follow each pointer back to retrieve them. That's precisely what A describes, making it the correct answer. B is wrong because the predicate customer_id = 42 is applied during the index scan — that's the entire purpose of using the index. The database doesn't throw it away and recheck the whole table. C invents a sorting behavior; no ORDER BY was specified, and table lookups have nothing to do with sorting. D is a nonsense distractor — a column's data type doesn't determine whether an index "covers" other columns. Coverage depends on which columns are included in the index definition, not on numeric vs. text types. A practical tip: when you see an index scan followed by table lookups in a plan, immediately think "the index is non-covering." The fix is usually a covering index — one that includes all columns referenced in the SELECT and WHERE clauses.

Question 4

Two alternative plans are generated by the same database instance for the same query and parameter values. Plan X has an estimated total cost of 9,200, while Plan Y has an estimated total cost of 7,600. A developer interprets these values as predicted milliseconds.

Which interpretation of the cost values is most accurate?

  1. Plan X is preferred because a higher cost value means the optimizer has allocated more computational resources to the query.
  2. Plan X will finish exactly 1,600 milliseconds later than Plan Y, because total plan cost is a calibrated wall-clock measurement.
  3. Plan Y will be faster in every execution, because a lower estimated cost guarantees a lower observed runtime regardless of data conditions.
  4. Plan Y is estimated cheaper under the optimizer's cost model, but the values are not necessarily execution times in milliseconds. (correct answer)
Explanation: Whenever you see a question about query optimizer cost estimates, remember the key principle: cost numbers are unitless, model-internal values, not real-world time measurements. The optimizer uses a mathematical model that weighs factors like I/O operations, CPU cycles, and memory usage — but it translates those factors into an abstract scoring system, not milliseconds or seconds. That's exactly what makes D correct. Plan Y receives a lower estimated cost (7,600 vs. 9,200), meaning the optimizer's model predicts it will consume fewer resources. However, "7,600" doesn't represent 7,600 milliseconds — it's a relative score within the optimizer's internal framework. Different database engines (PostgreSQL, SQL Server, Oracle) each have their own cost formulas, and none of them produce wall-clock time directly. A is backwards and fabricated — a higher cost value means the optimizer expects the plan to be more expensive, not that more resources have been "allocated" to help it. B makes a precise arithmetic claim (exactly 1,600 ms difference) that would only be valid if cost mapped directly to wall-clock time, which it never does. C is a tempting trap: while a lower estimated cost generally suggests better performance, it is not a guarantee. Actual runtime depends on real data distribution, caching effects, hardware conditions, and parameter sniffing — all things the model may not perfectly predict. As a study tip, watch for answer choices that treat optimizer cost estimates as literal time units — that's almost always a distractor. Remember: cost values are relative and model-dependent, useful for comparison but not for predicting exact execution time.

Question 5

An EXPLAIN ANALYZE plan for a query shows that a filter was estimated to return about 100 rows but actually returned about 400,000 rows. A join chosen above that filter performs poorly because it was planned for a very small input. The data distribution changed substantially after a recent bulk load.

What is the most appropriate first action based on this evidence?

  1. Refresh the relevant optimizer statistics, then obtain a new plan and compare the revised row estimates. (correct answer)
  2. Force the current join order permanently, because actual row counts do not affect optimizer join selection.
  3. Add an index to every filtered column, because a large estimate error proves those indexes are missing.
  4. Remove EXPLAIN ANALYZE from testing, because collecting actual rows causes the optimizer's estimate error.
Explanation: When a query plan shows a large gap between estimated and actual row counts — like 100 estimated versus 400,000 actual — you're looking at a stale statistics problem. The optimizer relies on table statistics (row counts, value distributions, histograms) to choose join strategies, so outdated statistics after a bulk load will cause poor plan choices. Your instinct should be: "The planner was working with bad information — fix the information first." That makes A correct. Running ANALYZE (or equivalent) refreshes the statistics so the optimizer accurately understands the new data distribution. Once stats are updated, you re-run EXPLAIN ANALYZE to confirm the row estimates now align with reality. Only then can you evaluate whether a different join strategy or index would actually help. This is always the right first step before making structural changes. B is wrong because actual row counts absolutely affect join selection. The optimizer chooses between nested loops, hash joins, and merge joins based on estimated input sizes. Locking in a bad join order would permanently bake in poor performance. C is wrong because the estimate error doesn't prove indexes are missing — it proves statistics are stale. Adding indexes blindly wastes resources and may not address the root cause at all. Index decisions should follow accurate planning, not precede it. D is completely backwards. EXPLAIN ANALYZE reveals estimate errors by showing actual rows alongside estimates — it doesn't cause them. Removing it would leave you blind to the problem. Study tip: On exam questions about query performance, always ask "what does the optimizer need first?" The answer is almost always accurate statistics before anything else.

Question 6

A messages table contains many millions of rows and has an index on sent_at. The query SELECT message_id, sent_at FROM messages ORDER BY sent_at DESC LIMIT 20; produces a plan that scans the sent_at index backward and estimates only a small number of rows will be read.

Why can this plan be efficient even though the table is very large?

  1. The LIMIT clause causes the database to delete older index entries temporarily while this statement is executing.
  2. A descending scan physically reverses the entire table once, after which every future query reads only 20 rows.
  3. The backward index scan can obtain the newest entries in order and stop after enough rows satisfy the limit. (correct answer)
  4. The optimizer must first sort all table rows by sent_at, but the limit makes that full sort cost-free.
Explanation: When a query includes both an index-friendly ORDER BY and a LIMIT, you should think about whether the database can stream results from the index rather than processing the entire table first. This is the core concept being tested here. A B-tree index on sent_at stores values in sorted order, and most database engines can traverse that index in either direction — forward (ascending) or backward (descending). Because the index already has the data sorted, the planner can walk it from the largest sent_at value downward, retrieving matching rows one by one. Once it has collected 20 rows, it stops entirely. This is why C is correct: the backward index scan delivers the newest entries in the exact order needed and exits early, meaning the engine reads only a tiny fraction of a multi-million-row table. A is simply fictional — SQL statements never temporarily delete index entries as part of query execution. B misrepresents how indexes work; no "physical reversal" of the table occurs, and even if it did, such a one-time operation wouldn't persist across queries. D contains a subtle trap: it implies a full sort still happens but is somehow made free by LIMIT. In reality, the whole point is that no sort happens at all — the index replaces the need to sort. As a study tip, remember the pattern: index + ORDER BY matching index order + LIMIT = early stop. Whenever you see these three elements together in a query, the optimizer can avoid both a full table scan and an explicit sort step, making even huge tables manageable.

Question 7

A table named employees contains several million rows. An index exists on department. For the query SELECT employee_id FROM employees WHERE department = 'Sales' AND active = TRUE;, the plan shows Sequential Scan on employees with both predicates applied as a filter. Approximately one-third of the table is estimated to qualify.

Which conclusion is best supported by this plan?

  1. The optimizer ignored a useful index, so forcing the department index will necessarily reduce execution time.
  2. The sequential scan may be reasonable because fetching many qualifying rows through the index could require more random access. (correct answer)
  3. The active predicate prevents every index on department from being considered by the optimizer.
  4. The plan proves that the index on department is invalid and must be rebuilt before the query runs.
Explanation: When a query plan shows a sequential scan despite an available index, your first instinct shouldn't be "the optimizer made a mistake" — it should be "why did the optimizer choose this?" Query optimizers are cost-based: they estimate whether using an index actually saves work compared to reading pages sequentially. Here's the key insight: indexes shine when you're retrieving a small fraction of rows. When you fetch a row through a B-tree index, the database must perform a random I/O to jump to that row's heap page. If one-third of a multi-million-row table qualifies, you could be making millions of random reads — which is frequently slower than a single sequential pass through the table. The optimizer correctly recognizes this, making B the best-supported conclusion: a sequential scan is plausible, even smart, when a large fraction of rows qualify. A is wrong because "forcing the index will necessarily reduce execution time" is too strong a claim. With ~33% of rows qualifying, forcing the index could easily make performance worse due to random access overhead. C is wrong because the active predicate doesn't block the department index from being considered — the optimizer evaluated that index and rejected it on cost grounds, not because of a compatibility issue. D is wrong because a sequential scan is a legitimate execution strategy; it carries no implication that the index is corrupted or invalid. The optimizer simply didn't use it. As a study tip: whenever a question asks you to interpret a query plan, ask yourself what fraction of rows qualifies. High selectivity (few rows) favors index scans; low selectivity (many rows) often favors sequential scans — and that's by design, not error.

Question 8

In a database where plain EXPLAIN plans a statement without executing it, while EXPLAIN ANALYZE executes the statement and reports observed runtime information, a developer wants to inspect DELETE FROM sessions WHERE expires_at < CURRENT_TIMESTAMP; on production data.

Which approach best avoids unintentionally deleting rows while still obtaining the proposed execution strategy?

  1. Run plain EXPLAIN on the DELETE, because it returns the proposed plan without executing the deletion. (correct answer)
  2. Run EXPLAIN ANALYZE on the DELETE, because analysis always substitutes a read-only simulation for data changes.
  3. Run the DELETE with a row limit, because limiting affected rows makes execution equivalent to plan inspection.
  4. Run the DELETE after disabling indexes, because the optimizer then explains it without touching table rows.
Explanation: When working with EXPLAIN in SQL, the critical distinction to understand is the difference between planning a statement and executing it. These are two separate phases, and only one of them touches your data. EXPLAIN (without ANALYZE) asks the query optimizer to generate and display its execution plan — the strategy it would use — without actually running the statement. This means no rows are read, modified, or deleted. For a destructive operation like DELETE, this is exactly what you want in a production environment: you get full visibility into the optimizer's intended approach (index usage, join methods, estimated costs) with zero risk to your data. A is correct for precisely this reason. Running plain EXPLAIN on the DELETE reveals the proposed execution strategy safely, making it the ideal choice for inspecting a destructive query on production data. B is wrong because EXPLAIN ANALYZE actually executes the statement to collect runtime metrics. It does not simulate data changes — it performs them. Running EXPLAIN ANALYZE on a DELETE would genuinely delete the matching rows. C is wrong because adding a LIMIT still executes the deletion — it just deletes fewer rows. You still risk data loss, and a partial delete is not equivalent to plan inspection. D is wrong because disabling indexes changes the optimizer's decision-making environment, producing an artificial plan that doesn't reflect real production behavior. It also doesn't prevent execution of the statement. A useful rule of thumb: whenever you need to safely inspect a write operation (INSERT, UPDATE, DELETE), reach for plain EXPLAIN — it's the only option that is purely read-only by design.

Question 9

A users table has a normal B-tree index on email. The query SELECT user_id FROM users WHERE LOWER(email) = 'alex@example.com'; produces a full table scan in an engine that does not automatically transform this expression into a search on the original indexed value.

Which explanation and remedy best fit the plan?

  1. The index is unusable because SELECT returns only user_id; adding email to the projection would enable an index seek.
  2. String literals cannot participate in B-tree searches; converting the email literal to an integer could enable the existing index.
  3. Applying LOWER to the indexed column prevents a direct search on the stored index keys; an expression index on LOWER(email) could help. (correct answer)
  4. Functions in predicates always force full scans; moving LOWER(email) into a subquery would make the original index searchable.
Explanation: Whenever you see a query that wraps an indexed column inside a function, ask yourself: what values are actually stored in the index? A B-tree index stores the raw, as-written values from the column. When your query applies LOWER(email) in the WHERE clause, the database engine needs to evaluate that function for every row before it can compare — it can no longer jump directly to a matching key in the index. The result is a full table scan, exactly as described in the passage. This is why C is correct. The LOWER() function is applied to the indexed column, which breaks the direct mapping between the query predicate and the stored index keys. The clean remedy is creating an expression index (also called a functional index) on LOWER(email), so the index stores pre-computed lowercase values that match the predicate exactly. The other options contain common misconceptions worth recognizing. A is wrong because the columns in your SELECT list have no bearing on whether an index can be used for a lookup — index usability is determined by the WHERE clause, not the projection. B is wrong because string literals participate in B-tree searches all the time; casting a string to an integer would be semantically nonsensical for email addresses and wouldn't fix the underlying issue. D is wrong because moving LOWER(email) into a subquery doesn't change anything — the function is still applied to the column, so the same scan problem persists. The premise that any function placement can restore the original index is false. As a study tip, remember the pattern: function on a column = index ignored. The fix is either remove the function, ensure the data is stored consistently (e.g., always lowercase), or create an expression index that mirrors the function.

Question 10

For SELECT id, created_at FROM tickets WHERE status = 'OPEN' ORDER BY created_at;, a plan uses an index to locate rows having status = 'OPEN', fetches those rows, and then performs a separate sort. The table currently has separate indexes on status and created_at.

Which index is most likely to let the optimizer satisfy both the equality filter and the requested ordering without a separate sort?

  1. An index on (created_at, status), because every ORDER BY column should precede every filtering column.
  2. An index on (status, created_at), because rows for one status are then ordered by created_at within that status. (correct answer)
  3. An index on (id, status), because including the selected identifier automatically preserves creation-time order.
  4. An index on (status) only, because equality searches always return matching rows in insertion order.
Explanation: When thinking about composite indexes and query optimization, ask yourself two things: what does the WHERE clause filter on, and what does the ORDER BY sort on? The goal is an index that handles both without extra work. For the query WHERE status = 'OPEN' ORDER BY created_at, the optimizer needs to first isolate rows matching a specific status, then return them in created_at order. Option B, an index on (status, created_at), does exactly this. Because the index groups all rows by status first, the optimizer jumps directly to the 'OPEN' section. Within that section, rows are already physically sorted by created_at, so no separate sort step is needed — the optimizer reads them in order naturally. Option A gets the column order backwards. Placing created_at first means the index is sorted by timestamp globally, not grouped by status. The optimizer can't efficiently find all 'OPEN' rows without scanning most of the index, and eliminating the sort doesn't help if the filter becomes expensive. Option C is a distractor that sounds logical but isn't. Including id in the index has no relationship to created_at ordering. Index column order determines sort order, not which columns you SELECT. Option D misunderstands how databases work. Indexes (and heap storage) do not guarantee rows are returned in insertion order. An equality search on a single-column status index returns matching rows in no predictable order, requiring an explicit sort step. The study tip to remember: for combined filter-and-sort queries, put the equality column first, then the sort column — this is the classic composite index pattern for avoiding sorts.