The Staff Engineer Query Optimization Interview: Plans, Indexes, and Evidence

Your query returns 50 rows. In this hypothetical interview exercise, the database performs 50,000 customer lookups before choosing them. Explaining where those lookups came from gives you a much stronger starting point than immediately proposing another index.

For a staff engineer query optimization interview, build an argument that connects the requested result, the execution plan, the data distribution, and a controlled experiment. This guide develops that argument through one orders screen. The workload and every numerical example are illustrative; the database behaviors come from primary documentation and research. PostgreSQL 16 supplies the SQL examples, with a MySQL comparison where measurement semantics matter.

1. Define the Query and the Workload

Suppose a multitenant application stores 30 million orders. An operator opens the latest 50 orders for one tenant, one status, and a starting timestamp. Each order has exactly one matching customer in the same tenant, enforced through keys and a foreign key. Order identifiers are unique, timestamps are non-null, and the response includes the customer's display name.

SELECT o.id, o.created_at, o.total_cents, c.display_name
FROM orders AS o
JOIN customers AS c
  ON c.id = o.customer_id AND c.tenant_id = o.tenant_id
WHERE o.tenant_id = $1
  AND o.status = $2
  AND o.created_at >= $3
ORDER BY o.created_at DESC, o.id DESC
LIMIT 50;

Establish the result contract before changing this query. Preserve tenant scope, customer matching, the timestamp boundary, and both sort keys. If you propose fetching 50 orders before joining customers, explain why every selected order still produces exactly one result. Removing a join condition or changing an inner join to a left join requires a separate correctness argument.

Ask for a baseline you can compare

Request the existing indexes, database version, recent data changes, parameter values from slow requests, and timings for small and large tenants. Decide whether the complaint concerns database execution, application response time, or both. Specify an acceptable read latency and a write-performance budget as constraints for the exercise.

Where available, PostgreSQL's pg_stat_statements reports calls, cumulative execution time, mean execution time, rows, and buffer activity. It normally groups statements that differ only in literal values. Consequently, a useful aggregate can also hide a parameter-specific problem: pair statement statistics with representative tenant and status examples. [1]

Prioritize by the workload you are responsible for. In an illustrative interval, a 200-millisecond query executed 1,000 times consumes 200 seconds of accumulated execution time; a two-second query executed 20 times consumes 40 seconds. These sums describe accumulated statement time, not CPU utilization or request latency. Use them alongside user impact when choosing your investigation.

2. Read the Plan as a Chain of Work

Start with an estimated plan, then collect actual execution evidence in a suitable test environment. PostgreSQL's estimated cost is expressed in planner units; actual execution time is expressed in milliseconds. A node's rows describes rows it emits, rather than every row it examines. [2]

EXPLAIN ANALYZE executes the statement, including writes and other side effects. For this exercise, use the read query on a disposable dataset with an execution budget. After preparing the query above as recent_orders, inspect a representative execution:

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SETTINGS)
EXECUTE recent_orders(
  42, 'open', TIMESTAMPTZ '2026-10-01 00:00:00+00'
);

TIMING OFF avoids repeated node-level clock measurements while retaining row counts and total statement timing. BUFFERS distinguishes cache hits, reads, and temporary-block activity; an upper node's counts include its children. Do not sum those counts across the tree as independent I/O. [3]

Find the first consequential mismatch

Imagine this abbreviated plan shape. These are invented row counts, not captured PostgreSQL output:

Limit: 50 rows
  Sort: newest orders first
    Nested Loop: estimated 500, actual 50,000 rows
      Orders scan: estimated 500, actual 50,000 rows
      Customer index lookup: actual 1 row, loops 50,000

The orders scan emits 100 times its estimate. The inner customer lookup returns one row per execution, but executes 50,000 times. PostgreSQL reports actual rows and node times as averages per execution when a node repeats; multiply by loops to understand its total contribution. Also check whether a parent LIMIT stopped a node early before declaring an estimate inaccurate. [2]

Your hypothesis is now specific: too many qualifying orders reach the customer lookup and sort before the query can finish. Inspect the scan's index conditions, residual filters, rejected rows, and actual buffer activity. Decide whether the first experiment should improve the estimate, reduce scanned candidates, or provide the requested order earlier.

Give your hypothesis a counterfactual. If you keep the same 50,000 qualifying orders but make their estimate accurate, does the planner choose a different join? If you add an ordered access path, do fewer orders reach the customer lookup? These experiments answer different questions. Predicting their different outcomes helps you distinguish a misleading estimate from an expensive access path.

Carry this discipline across engines. MySQL 8.4's EXPLAIN ANALYZE reports iterator execution times that include child iterators and uses average timing when there are multiple loops. Adding every displayed node time would count some work repeatedly. Read the engine's measurement definition before assigning blame to the largest number. [4]

3. Explain Why the Estimate Was Wrong

Cardinality reasoning deserves its own step. The 2015 Join Order Benchmark study tested complex, realistic multi-join queries and found that cardinality errors influenced performance more than cost-model errors in those experiments. Treat that as a reason to investigate row counts, with its workload and historical scope intact. It does not establish that every present-day optimizer makes the same mistakes. [5]

Separate stale statistics from correlated data

First ask whether statistics describe the current table. PostgreSQL's ANALYZE samples large tables, collecting approximate distributions, common values, and distinct-value estimates. A higher statistics target can improve detail at additional analysis cost. Refreshing statistics after a substantial data change is a testable proposal; repeatedly running ANALYZE without examining the estimate provides little explanation. [6]

Then investigate correlation. In our fictional application, one tenant might retain mostly open orders while another closes almost everything. Global frequencies for tenant and status do not necessarily describe their joint frequency. PostgreSQL normally treats multiple conditions as independent unless available statistics establish a relationship; single-column statistics cannot capture cross-column correlation. [7]

If the observed mismatch involves tenant and status together, test a targeted statistics object on those columns, followed by ANALYZE. Multivariate most-common-value statistics are a candidate for their joint distribution. PostgreSQL 16 supports several extended-statistics kinds, but does not use extended statistics for join-selectivity estimates. A better orders-filter estimate therefore needs separate verification from the customer join's estimate. [8]

State what success would look like before making the change: the orders estimate should approach the observed qualifying population, and the resulting plan should reduce measured work or execution time. If estimates improve while performance stays flat, report that result. You have corrected one explanation without yet solving the complaint.

Reproduce the application's prepared plan

A literal query in your console may exercise a different planning path from the application. PostgreSQL can choose a custom prepared-statement plan using supplied values or a generic plan shared across executions. In version 16's automatic mode, the first five executions use custom plans before the generic-plan cost comparison. Inspect EXPLAIN EXECUTE after representative repeated executions. [9]

For a large and a small tenant, compare generic and custom plans as controlled experiments. Keep the data and settings fixed, and account for planning time as well as execution time. If forcing custom plans helps only the exceptional tenant while adding cost elsewhere, describe that tradeoff before proposing a broader configuration change.

4. Design an Index for the Requested Access Path

For this query, test a B-tree candidate on (tenant_id, status, created_at DESC, id DESC). The first two fields have equality conditions; the timestamp has a range condition. PostgreSQL 16's multicolumn B-tree rule uses leading equalities and the first following inequality to restrict the scanned portion. Explain the ordering through this query's predicates and neighboring queries. [10]

Now challenge the candidate with a neighboring query that omits status. Tenant equality would still narrow the index, but status would separate groups before timestamp ordering. Ask whether that screen deserves a different index or can tolerate a sort. Choose between index candidates using the actual query mix, rather than treating one column order as universally correct. [10]

The remaining order matches the requested timestamp and identifier order. PostgreSQL can use a matching B-tree to satisfy ORDER BY, and an ordered access path combined with LIMIT can avoid processing the entire qualifying population before finding the first results. Predict that possibility, then confirm whether the complete join plan actually preserves it. [11]

Separate indexes on tenant and status offer another access path, but PostgreSQL's bitmap combination visits heap rows in physical order and loses the indexes' original ordering. An additional sort may remain necessary. Compare this existing path with the composite candidate using the same parameters; the presence of several indexes alone does not settle the decision. [12]

Make covering columns earn their space

Adding INCLUDE (customer_id, total_cents) would store the remaining order fields needed for this projection and customer lookup. PostgreSQL's index-only scans also depend on visibility-map information: when a heap page is not all-visible, heap checks remain necessary. Included values enlarge the index, so inspect actual heap fetches and index size before paying for a wider candidate. [13]

The customer name still comes from the customer relation. Distinguish covering the order access from eliminating the whole join. For a frequently edited orders table, test the narrow index first, then test the covering variant separately. This gives you evidence about which part of the design produced the benefit.

A partial index containing only open orders is another candidate if that subset serves a specific workload. PostgreSQL must prove that the query condition implies the index predicate during planning. A generic plan containing status = $2 cannot establish status = 'open' for every parameter value. Custom plans substitute supplied values, so inspect the actual prepared plan instead of assuming identical behavior across parameterized executions. [14] [9]

5. Prove the Change Across Representative Cases

Write the experiment before announcing a fix. Use the same dataset, database version, settings, and parameter set for baseline and candidate. Change one factor at a time: statistics, query shape, narrow index, covering index, or planning mode. Record which hypothesis each experiment tests and which result would make you reject it.

For this orders screen, include a small tenant, a large tenant, common and rare statuses, short and long timestamp ranges, and a case with fewer than 50 matches. Check the returned identifiers, their order, and customer names against the baseline on unchanged data. If a rewrite changes join placement or limiting, add missing-customer and duplicate-match fixtures appropriate to the proposed schema constraints.

Keep an experiment log that distinguishes observation from interpretation. For example: “The customer lookup loops fell after the ordered index” is an observation; “the plan stopped after finding enough eligible orders” is an explanation to verify in the plan. If latency improves while lookup counts stay constant, examine which other measured activity changed. Preserve an unsuccessful candidate in the comparison so your final recommendation explains why you rejected it.

Repeat comparisons under a declared cache condition and representative write activity. Report run-to-run variation, planning and execution time, buffer activity, qualifying rows, lookup loops, heap fetches, and index size. Measure ordinary application requests separately from instrumented plans. PostgreSQL documents profiling overhead for EXPLAIN ANALYZE, which can make its execution slower than the uninstrumented statement. [3]

Turn the experiment into an operating decision

Define acceptance across reads and writes. An illustrative gate might require the affected large-tenant cases to improve without exceeding an agreed write-latency regression budget, while preserving results and keeping storage growth within a stated limit. Choose those thresholds for the workload; they are practice constraints, not a published hiring rubric.

Explain how the index reaches production. PostgreSQL's ordinary index build blocks writes to its target table. CREATE INDEX CONCURRENTLY permits writes, but performs more work, waits for transactions, and can leave an invalid index after failure. It also cannot run inside a transaction block. Describe monitoring, completion checks, and cleanup for a failed build as part of the proposal. [15]

Keep any query rewrite behind a reversible release choice. Compare the affected parameter cohorts after release, watch write performance and build resource use, and identify who will stop or reverse the change when the acceptance conditions fail. State which evidence you still lack instead of turning a faster local execution into a production conclusion.

Rehearse a 25-minute explanation

Use this suggested practice allocation: spend five minutes establishing semantics and workload, seven reading a plan, six explaining statistics and index candidates, and seven designing validation and rollout. Ask a practice partner to change one assumption halfway through, such as tenant skew, frequent updates, or generic-plan reuse.

Finish with a short decision record: the observed mismatch, your strongest hypothesis, one proposed experiment, the expected plan change, and the evidence required for adoption. If a new assumption invalidates your index choice, revise it aloud. Your explanation should make clear why the recommendation fits the current facts and where it would stop fitting.

The durable preparation skill is building a causal explanation you can test. Take one unfamiliar execution plan this week, locate the work that produces the result, and defend a change with a representative experiment. That practice gives you something concrete to discuss when the next query has different data, different indexes, and a different failure.