Databases & SQL interview questions: query planning & performance
Reviewed by Mark Dickie · Last updated
Query planning is the process a database engine uses to decide how to execute a SQL statement: which indexes to read, which join order to use, and whether to sort or hash intermediate results. For interview purposes, the core areas are index selection and access paths, join strategies (nested loop vs. hash vs. merge), predicate selectivity, and reading EXPLAIN / EXPLAIN ANALYZE output to spot full table scans or expensive sorts. You should also be comfortable with common pitfalls: function-wrapped columns that defeat indexes, implicit type conversions that do the same, and the difference between a logical plan and the physical plan the engine actually runs.
Most performance questions boil down to a few concrete patterns. Here is a quick map of what interviewers target and the concept behind each:
| Interview area | What they probe | Key concept to explain |
|---|---|---|
| Index usage | Why a query ignores an index | B-tree range limits, composite index column order, covering indexes |
| Join strategy | When a hash join beats a nested loop | Build/probe cost, memory vs. disk spill, equi-join requirement |
| Selectivity | How the optimizer estimates cost | Cardinality, histogram stats, and the 10–30% heuristic |
| Execution plans | Reading EXPLAIN ANALYZE | Sequential scan flags, actual vs. estimated rows, sort/merge overhead |
| Concurrency locks | Why a slow query blocks others | Row vs. table locks, MVCC snapshot cost, lock escalation |
What does a database performance interview typically test?
- Diagnosing a slow query from its plan. You are handed an
EXPLAINoutput and asked to find the bottleneck, usually a sequential scan on a large table or a nested-loop join with high row estimates. - Choosing the right index. Expect a scenario query where a composite index
(a, b)exists but aWHERE b = ?predicate still scans, because the leading column is absent from the filter. - Explaining join order and join algorithms. You may need to say why the optimizer reorders joins, and when a hash join is cheaper than repeated index probes for a large inner relation.
- Discussing statistics and cost estimation. Interviewers often ask what happens when table statistics are stale: the optimizer picks a bad plan because cardinality estimates are off.
- Tracing query rewrites. Some engines transform correlated subqueries into joins, push predicates down through views, or collapse CTEs. Knowing these transformations helps you explain why a plan looks different from the SQL you wrote.
How should you prepare for SQL performance questions?
Run EXPLAIN ANALYZE on your own queries against a realistic data set, not a toy table with ten rows. Small tables hide every performance problem because the optimizer will pick a sequential scan regardless. Load at least a few hundred thousand rows so index access and join strategy choices become visible in the plan. Pay attention to the gap between estimated and actual row counts, since that gap is the single most common reason a plan goes wrong in production.
Key facts
- Tarmac has 31 Databases & SQL interview questions on this topic, 10 of them on this page, at difficulty 1–5 of 5.
- Tarmac tracked 8,648 job postings asking for Databases & SQL in August 2026.
- Roles asking for Databases & SQL advertise a median base salary of US$166,400, across 1,383 job postings as of August 2026.
- Tarmac last reviewed these Databases & SQL interview questions on 31 August 2026.
At a glance
| Questions | 10 shown · 31 in the bank |
|---|---|
| Difficulty | 1–5 of 5 |
| Formats | True / false, Fill in the blank, Ordering, Multiple choice, Multiple answer, Short answer |
What you'll review
- query planning
Practice questions
Try one before you open the answer. Pick an option and press Check; it's marked on the spot.
Databases & SQL/db-performance/query-planning
In most relational databases (e.g., PostgreSQL, MySQL InnoDB), adding an index on a column used only in the SELECT list (but never in WHERE, JOIN, ORDER BY, or GROUP BY clauses) will improve the query planner's ability to speed up that query.#
Options
Show answer
False. An index on a column that only appears in the SELECT list provides no speed benefit to the query planner. Indexes help by narrowing down which rows to read (via WHERE, JOIN) or eliminating sorts (ORDER BY/GROUP BY). If the column is never used for filtering or ordering, the planner won't use that index and it simply adds overhead to write operations.
Indexes help the query planner speed up row lookups by filtering (WHERE), joining (JOIN), sorting (ORDER BY/GROUP BY), or enabling index-only scans. If a column appears only in the SELECT list and not in any filtering or ordering clause, an index on that column alone provides no benefit to the planner — the database still needs to access the table rows by another means and then simply read the column value. The index would be unused and just add overhead to writes.
Databases & SQL/db-performance/query-planning
Most SQL databases provide the _____ command (or _____ _____) to display the execution plan chosen by the query planner for a given SQL statement, without actually running the query.#
Show answer
Most SQL databases provide the **EXPLAIN** command (or **EXPLAIN** **EXPLAIN**) to display the execution plan chosen by the query planner for a given SQL statement, without actually running the query.
The EXPLAIN keyword (used alone or as EXPLAIN ANALYZE in PostgreSQL/MySQL) instructs the database engine to output the query execution plan — including join strategies, index usage, and estimated costs — without executing the query. This is the primary tool developers use to understand and optimize query performance.
Databases & SQL/db-performance/query-planning
The query planner follows a general pipeline when processing a SQL query. Arrange these steps in the correct order from first to last:#
Put these in order
Show answer
The correct order is: Parse → Rewrite → Plan/Cost → Execute. First the SQL text is parsed into a parse tree. Then the tree is rewritten (e.g., views expanded). Next the planner generates candidate plans and estimates costs using table statistics. Finally, the cheapest plan is selected and executed. This pipeline is standard across major relational databases like PostgreSQL and MySQL.
A relational query planner works in four major stages: (1) Parsing — the SQL string is lexed and parsed into a structured parse tree, catching syntax errors early. (2) Rewriting — the parse tree is transformed by applying rewrite rules such as view expansion, subquery flattening, and constant folding. (3) Planning / Optimization — the planner enumerates candidate execution plans (e.g., which indexes to use, which join algorithm to apply) and estimates the cost of each using table statistics (row counts, histograms). (4) Execution — the chosen lowest-cost plan is executed by the storage engine. This pipeline is consistent across PostgreSQL, MySQL, and most other RDBMSs.
Databases & SQL/db-performance/query-planning
A table employees has a composite B-tree index defined as:#
Options
Show answer
A query filtering only on first_name (the second column of the index) cannot use the composite index (last_name, first_name, dob) because it skips the leading column last_name. Database query planners require the leftmost prefix of a composite index to be present in the WHERE clause to perform an efficient index range scan; without it, the engine resorts to a full sequential table scan.
When a query planner encounters a predicate on a column that has a composite (multi-column) index, the leading-column rule applies: the index can only be used efficiently if the WHERE clause includes the leftmost prefix of the index columns. For an index on (last_name, first_name, dob), a filter solely on first_name skips the leading column last_name, so the planner cannot perform an index range scan and must fall back to a full sequential scan of the table. Options A and C both include last_name (the leading column) so the index is usable; option D uses the full key and is also fine.
Databases & SQL/db-performance/query-planning
You run EXPLAIN ANALYZE on a slow query in PostgreSQL. Which of the following are valid and actionable insights you can directly obtain from its output? Select all that apply.#
Options
Pick every one that applies.
Show answer
From EXPLAIN ANALYZE output you can directly learn: (a) the access method chosen for each table (Sequential Scan vs. Index Scan), (b) estimated vs. actual row counts at every plan node — crucial for spotting stale statistics — and (d) the total wall-clock execution time. Network latency between client and server and specific OS page-cache blocks read are not reported in EXPLAIN ANALYZE output.
EXPLAIN ANALYZE executes the query and annotates each plan node with estimated cost/rows, actual rows returned, and actual timing (wall-clock time per node and total). This lets you identify bad row-count estimates (a vs. b) and expensive nodes (d). Network latency (c) is outside the scope of the query planner output entirely. Buffer/block-level cache statistics are only visible with EXPLAIN (ANALYZE, BUFFERS) and even then show buffer hit counts, not specific block addresses (e).
Databases & SQL/db-performance/query-planning
After loading millions of new rows into a table, the query planner may produce poor plans because its internal _____ are stale. Running the _____ command updates them. Separately, when a WHERE clause matches a very large percentage of rows, the planner considers the predicate to have low _____ and may prefer a sequential scan over an index scan.#
Show answer
After loading millions of new rows into a table, the query planner may produce poor plans because its internal statistics are stale. Running the **ANALYZE** command updates them. Separately, when a WHERE clause matches a very large percentage of rows, the planner considers the predicate to have low selectivity and may prefer a sequential scan over an index scan.
The FILL_BLANK tests understanding of two fundamental query-planning concepts: (1) after bulk data changes the query planner relies on table statistics, which must be refreshed via ANALYZE (or VACUUM ANALYZE) so that cardinality estimates remain accurate; and (2) an index is not used when the planner estimates the query will touch a large fraction of the table — this threshold is called the 'selectivity' of the predicate. Low selectivity (many matching rows) typically leads the planner to prefer a sequential scan over an index scan because random I/O for most rows is more expensive.
Databases & SQL/db-performance/query-planning
A PostgreSQL table orders has 10 million rows. A B-tree index exists on the status column. The following query ignores the index and performs a sequential scan:#
Options
Show answer
The planner correctly chooses a sequential scan because fetching ~9.4 million rows via random index lookups would be far more expensive than one sequential pass over the heap. PostgreSQL's cost model weights random I/O (random_page_cost) higher than sequential I/O (seq_page_cost), so when a predicate matches a large fraction of rows the index offers no benefit.
PostgreSQL's query planner chooses between a sequential scan and an index scan based on cost estimates derived from statistics (pg_statistic). When a predicate matches a large fraction of rows (e.g., a low-selectivity condition like status = 'active' on a table where 95% of rows are active), the planner correctly decides that reading the entire heap sequentially is cheaper than random index lookups plus heap fetches. The planner uses the page-level cost model: random I/O has a higher cost constant (random_page_cost) than sequential I/O (seq_page_cost). An index is only beneficial when selectivity is high (few rows returned). Options about outdated statistics (ANALYZE not run) and fillfactor are distractors — the described behavior is correct planner behavior, not a bug.
Databases & SQL/db-performance/query-planning
Place the following PostgreSQL query-processing stages in the correct order, from first to last, for a standard SELECT query.#
Put these in order
Show answer
The correct order is: (1) Parser – converts raw SQL into a parse tree; (2) Analyzer & Rewriter – resolves names and expands views; (3) Planner/Optimizer – costs candidate plans and picks the cheapest; (4) Executor – walks the plan tree to produce result rows. Each stage transforms the query representation before handing it to the next.
The correct sequence for how PostgreSQL processes a query through the planner/optimizer is: (1) Parser – turns SQL text into a parse tree; (2) Analyzer/Rewriter – resolves names, applies rules/views to produce a query tree; (3) Planner/Optimizer – generates and costs candidate plans using statistics, then selects the cheapest; (4) Executor – executes the chosen plan. Understanding this pipeline is essential for diagnosing why plans are chosen and how statistics feed into cost estimation.
Databases & SQL/db-performance/query-planning
A table events has ~500 million rows. A DBA creates the following composite B-tree index:#
Show answer
A B-tree index is traversed by walking down the tree using the leading column first. With (status, created_at), the planner uses the high-selectivity equality predicate status = 'PENDING' to jump directly to the subtree for that status value, then applies the range scan on created_at within that narrow band. Because status may have only a handful of distinct values (e.g., PENDING, PROCESSED, FAILED), the equality predicate is very selective — it eliminates most rows immediately. The index then delivers the matching rows in created_at order, so the ORDER BY created_at DESC LIMIT 100 can be satisfied with a single backward index scan without a sort step. With (created_at, status), the leading column is a timestamp with extremely high cardinality; the planner scans the 7-day range (potentially tens of millions of rows) and applies status = 'PENDING' as a residual filter on each fetched row. EXPLAIN ANALYZE would show a large 'rows removed by filter' count, high actual rows, and likely a sort node for the ORDER BY. With (status, created_at), EXPLAIN ANALYZE would show a very small 'rows removed by index recheck', low actual rows fetched, and no extra sort node because the index already provides the required order.
This question tests deep knowledge of multi-column index column ordering and query planner selectivity. The composite index on (status, created_at) is defined with status as the leading column. Because the query uses an equality predicate on status AND a range predicate on created_at, the planner can use both columns of the index efficiently (equality on the prefix, then range on the second column). Reversing the order to (created_at, status) would allow only the range prefix to be used — the status equality filter would have to be applied as a residual filter on the rows fetched by the range scan, which is less selective. The index on (status, created_at) therefore narrows the result set far more tightly using the B-tree structure, leading to fewer index pages read. EXPLAIN ANALYZE would show a much lower 'rows removed by index recheck' count for the correctly ordered index.
Databases & SQL/db-performance/query-planning
A DBA runs EXPLAIN (ANALYZE, BUFFERS) on two variants of the same query against PostgreSQL 15. Variant A (no LIMIT) uses a Hash Join with cost 1200..95000. Variant B (with LIMIT 1) uses a Nested Loop with cost 0.00..8.50.#
Options
Pick every one that applies.
Show answer
The correct explanations are (a), (b), and (d). PostgreSQL's planner estimates the cost of a LIMIT N node as startup + (N / total_rows) × (total_cost − startup). A Hash Join has a high startup cost (it must build the entire hash table before emitting row one), so even though its total cost beats a Nested Loop for full scans, the scaled cost for LIMIT 1 favours the Nested Loop, which can return its first row almost immediately. There is no hard threshold of 10, and Hash Joins are not categorically disabled by LIMIT.
PostgreSQL's EXPLAIN output uses 'cost' units that are dimensionless and relative. The startup cost (first number) is the estimated cost to return the first row, while the total cost (second number) is the cost to return all rows. For a LIMIT node, the planner scales the total cost of the child node proportionally — it does NOT simply run the child to completion. A Hash Join always has a non-zero startup cost (it must build the hash table before returning any row), whereas a Nested Loop can start emitting rows immediately. When a LIMIT 1 is applied, the planner may switch from a Hash Join to a Nested Loop because the scaled total cost of the Nested Loop (almost equal to just its startup cost) beats the Hash Join's startup cost. This is a classic plan-flip scenario caused by the LIMIT hint to the planner, and it explains why adding LIMIT to a query can paradoxically make it slower if the plan flip is incorrect (e.g., when the nested loop's inner side has no index).
Sources
The official documentation these questions are checked against:
Related interview questions
Job market
See databases-sql salaries and hiring demand from live job postings.
The other 21 questions
This page shows 10 and marks what you pick. That's as far as a page can go. A free account opens the other 21 and keeps every answer. What you miss comes back until it's right: after a day, then at longer gaps.
Free · the whole bank · 100 marked answers per 30 days · written feedback on the paid plan