- Which change most directly targets the repeated work visible in this plan?
Answer: Create an index on devices.serial_no, allowing each inner lookup to avoid repeatedly scanning the full devices table.
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.
- Which index is most likely to let the optimizer satisfy both the equality filter and the requested ordering without a separate sort?
Answer: An index on (status, created_at), because rows for one status are then ordered by created_at within that status.
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.
- Which explanation and remedy best fit the plan?
Answer: Applying LOWER to the indexed column prevents a direct search on the stored index keys; an expression index on LOWER(email) could help.
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.
- Which change most directly enables an efficient range search for this query while preserving its current predicate?
Answer: Create an index whose leading column is order_date, because the current index is ordered first by customer_id.
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.
- What is the most appropriate first action based on this evidence?
Answer: Refresh the relevant optimizer statistics, then obtain a new plan and compare the revised row estimates.
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.
- Which interpretation of the table-row lookups is most accurate?
Answer: 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.
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.
- Which conclusion is best supported by this plan?
Answer: The sequential scan may be reasonable because fetching many qualifying rows through the index could require more random access.
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.
- Which approach best avoids unintentionally deleting rows while still obtaining the proposed execution strategy?
Answer: Run plain EXPLAIN on the DELETE, because it returns the proposed plan without executing the deletion.
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.
- Why can this plan be efficient even though the table is very large?
Answer: The backward index scan can obtain the newest entries in order and stop after enough rows satisfy the limit.
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.
- Which interpretation of the cost values is most accurate?
Answer: Plan Y is estimated cheaper under the optimizer's cost model, but the values are not necessarily execution times in milliseconds.
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.
