Read a Query Plan Like a Senior Engineer: A 15-Minute Interview Drill

A query-plan question is not a test of whether you can recite PostgreSQL internals. It is a test of whether you can turn a messy performance symptom into a safe sequence of checks. In an interview, start with actual rows × loops, compare that with the estimate, then explain what you would change only after proving the bottleneck.

Most candidates see EXPLAIN ANALYZE output, spot a Seq Scan, and immediately say “add an index.” That is a risky answer. Sequential scans are often correct. The stronger answer explains why a node is expensive in this execution, what evidence supports a change, and what trade-off the change introduces.

This post gives you a compact drill you can run before a backend, data-engineering, or database interview. It deliberately uses a small fake plan: the goal is to practice the reasoning voice, not to pretend the sample replaces a real database.

What should you say before proposing an index?

Use this four-pass order:

Pass	What to inspect	The interview signal
1. Scale	actual rows × loops and elapsed time	You understand repeated work
2. Estimate	planned rows versus actual rows	You can spot stale statistics or skew
3. Access path	scan, join, sort, aggregate	You diagnose before tuning
4. Safety	selectivity, write cost, query shape	You know tuning has trade-offs

That order is more memorable than a list of database buzzwords. It also prevents a common mistake: confusing the node with the problem. A nested loop can be excellent for ten rows and terrible for ten thousand. A sequential scan can be the cheapest choice when a query needs most of a small table.

Build a tiny plan-review drill

Save this as plan-drill.mjs and run node plan-drill.mjs. It has no dependencies.

const plan = [
  { node: "Nested Loop", planRows: 120, actualRows: 2400, loops: 1, actualMs: 86 },
  { node: "Index Scan: orders_customer_id_idx", planRows: 1, actualRows: 3, loops: 2400, actualMs: 41 },
  { node: "Seq Scan: customers", planRows: 50000, actualRows: 50000, loops: 1, actualMs: 18 },
  { node: "Sort: created_at DESC", planRows: 120, actualRows: 2400, loops: 1, actualMs: 22 },
];

const ratio = (actual, planned) =>
  planned === 0 ? Infinity : actual / planned;

const findings = plan
  .map((step) => ({
    ...step,
    totalRows: step.actualRows * step.loops,
    estimateRatio: ratio(step.actualRows, step.planRows),
  }))
  .sort((a, b) => b.actualMs - a.actualMs);

for (const step of findings) {
  const flags = [];
  if (step.loops > 100) flags.push("repeated work");
  if (step.estimateRatio > 10) flags.push("estimate mismatch");
  if (step.node.startsWith("Sort")) flags.push("check sort memory or an ordering index");

  console.log({
    node: step.node,
    time_ms: step.actualMs,
    rows_processed: step.totalRows,
    estimate_ratio: step.estimateRatio.toFixed(1),
    next_question: flags.join("; ") || "inspect in context",
  });
}

The point is not the scoring formula. It is what the output forces you to notice:

1. The index scan looks cheap at 41 ms, but it executes 2,400 times. That makes it a join-shape question, not automatically an index question.
2. The nested loop expected 120 rows and saw 2,400. A 20× mismatch is a reason to ask about statistics, correlated predicates, or an uneven customer distribution.
3. The sequential scan processes every customer row once. It may be fine; you need table size, filter selectivity, and cache context before judging it.
4. The sort appears after the join. If the real query has ORDER BY created_at DESC LIMIT 20, an index matching the filtering and ordering columns could remove work. If it returns thousands of rows, that same index may not be enough.

How do you turn those observations into an interview answer?

Practice saying this out loud, without reading it word for word:

“I would first confirm where time and repeated work are accumulating. Here the inner index scan runs 2,400 times, so I would inspect the join cardinality and the join condition before adding another index. The estimate is low by about 20×, which makes me check statistics and data skew. Then I would compare an alternative join strategy with EXPLAIN ANALYZE, using production-like parameters. I would only keep an index if the reduced read cost justifies its write and storage cost.”

That answer has a useful property: every claim leads to a next observation. It does not overpromise a fix from a partial plan.

What follow-up questions should you expect?

Interviewers often change one detail and watch whether your approach changes with it.

• “The query is fast locally but slow in production.” Ask for data volume, parameter values, cache state, concurrent load, and whether the production plan differs.
• “There is already an index.” Check column order, predicate selectivity, whether a function or cast prevents its use, and whether the query is retrieving too many rows.
• “Can we force a hash join?” Treat a forced planner setting as an experiment, not the default fix. Explain that you would compare plans and address the reason the planner chose poorly.
• “The report is slow only at month end.” Look for skew, changing cardinality, stale statistics, lock contention, or an input range that changed the access pattern.

A good candidate asks for the missing signal. A great candidate names the decision that the signal will influence.

A 15-minute practice routine

Use one real plan from a side project, a sanitized work example you are allowed to discuss, or the sample above.

1. Spend three minutes marking the slowest node, the highest loop count, and the biggest estimate mismatch.
2. Spend four minutes writing one hypothesis for each mark. Keep hypotheses conditional: “If this predicate is selective, then…”
3. Spend four minutes explaining the plan aloud as if the interviewer cannot see it.
4. Spend two minutes naming one safe experiment, such as updating statistics in a staging copy or comparing an index with EXPLAIN ANALYZE.
5. Spend two minutes naming the cost of your proposed change.

Record yourself once. The gap is usually not SQL syntax; it is skipping from a symptom to a fix without narrating the evidence. For mock practice, aceround.app — an AI interview assistant can be useful for pressure-testing that explanation with follow-up questions, but it should not replace running and understanding the plan yourself.

FAQ

Is a sequential scan always bad?

No. If a query needs a large fraction of a small table, reading it sequentially can be cheaper than bouncing through an index and then fetching rows. The plan, table size, selectivity, and cache state matter together.

What is the fastest way to spot a cardinality problem?

Compare estimated rows with actual rows at important nodes, then include loop counts. A mismatch that is multiplied inside a join is usually more consequential than an isolated mismatch.

Should I memorize PostgreSQL planner settings for interviews?

Know that join strategies and statistics exist, but do not lead with a configuration toggle. A clear evidence-first investigation is more credible and transfers to other databases.

References

• PostgreSQL documentation: Using EXPLAIN

Disclosure: This article was drafted with AI assistance and reviewed for technical accuracy and originality by the author.