SQLQuery PlanningIndexingPerformancemiddle level

Diagnosing and Fixing a Slow Database Query

the question

A database query has become a production bottleneck. How would you diagnose and optimize it?

what they're testing: The interviewer is probing whether you can turn performance evidence into a targeted fix while accounting for workload trade-offs.

a strong answer

Reproduce the slow case with representative parameters and data, then record a baseline. Capture latency, returned rows, and resource counters such as buffer reads when the engine exposes them. This separates a consistently expensive query from a one-off wait.

Inspect the estimated plan first, then use the engine's measured-plan option when it is safe. In PostgreSQL, for example, EXPLAIN ANALYZE executes the statement and reports actual rows and timing, so writes have their normal side effects. Compare estimated and actual row counts, account for repeated loops, and look for excessive scanning, filtered rows, or sorts that spill to disk.

Fix the cause the plan reveals. That may mean an index whose columns and order fit the predicates and the engine's B-tree rules, fewer rows or columns, or a simpler join. If row estimates are poor, update planner statistics and check the plan again. Re-run the benchmark after each change. Include storage and write cost when adding an index, and remember that a sequential scan can be right for a small table or a query that reads much of it. If the plan's work looks reasonable but latency is still high, check lock and storage waits.

Where people slip

the tempting wrong answer, and what's actually true

Add an index to every column used by the query and it will get faster.
Indexing every referenced column is not a reliable fix. Build indexes for useful access paths, then verify the plan; unnecessary indexes consume space and add work to inserts, updates, and deletes.
A sequential scan in the plan always means an index is missing.
A sequential scan does not prove an index is missing. It can be cheaper when the table is small or the query needs a large share of its rows.
`EXPLAIN ANALYZE` only estimates a plan, so it is safe to run on any statement.
In PostgreSQL, `EXPLAIN ANALYZE` executes the statement and reports actual timing and row counts. Data-changing statements have their normal side effects unless they are contained and rolled back.

If they push further

What do you look for in an execution plan?

Compare estimated with actual rows and inspect scan volume, rows removed by filters, buffer reads, and sort or hash spills. For nodes executed repeatedly, account for the loop count rather than reading one execution in isolation.

How would you choose a composite index for this query?

Map equality, range, and ordering conditions to the engine's B-tree rules. MySQL lookups use leftmost prefixes; PostgreSQL is most efficient with constraints on leading columns and can sometimes use skip scan. Verify the resulting plan.

What trade-off does adding an index introduce?

Reads may touch fewer rows, but every relevant insert, update, or delete must maintain the index. Include write latency and storage in the before-and-after measurement.

Sources

Now answer it yourself.

Reading a strong answer is easy. Producing one under pressure is the skill the interview tests. Gapmap grades your answer against the same bar an interviewer would.

was this useful?

beta

The interviewer part is in the works.

The diagnostic, personal maps, and AI mock interviews are being finished right now. The notes stay free either way. Leave an email and you'll get the first-cohort invite, plus a month of Pro when it opens.