Diagnosing and optimizing a slow production query

WHAT THIS TESTS The interviewer wants a repeatable, evidence-driven method, not a list of tricks. They are checking that you measure before you change anything and that you understand how the planner executes a query.

A GOOD ANSWER COVERS Start by confirming the query is actually the problem using monitoring such as pg_stat_statements, the slow query log, or APM traces to see frequency and total time, since a moderately slow query run millions of times hurts more than one rare slow one. Then run EXPLAIN ANALYZE BUFFERS to get the real plan with actual rows, timings, and cache behavior. Read it bottom up, looking for sequential scans on large tables, nested loops over big row sets, expensive sorts that spill to disk, or large gaps between estimated and actual rows that signal stale statistics. Fix the root cause: add a selective or covering index, rewrite the query to be sargable, refresh statistics with ANALYZE, adjust join order, or denormalize a hot path. Finally re-run EXPLAIN ANALYZE to confirm improvement under realistic data volumes.

COMMON WRONG ANSWERS Adding indexes by guesswork without reading the plan, which can slow writes and still not be used. Optimizing in a tiny dev database where every plan is a fast in-memory scan. Ignoring that estimate-versus-actual row mismatches usually mean stale statistics.

LIKELY FOLLOW-UPS How do you handle a query that is fast in isolation but slow under concurrency? When does a covering index help versus hurt? How do parameter sniffing or cached plans cause inconsistent performance?

ONE CONCRETE EXAMPLE A report query takes eight seconds. EXPLAIN ANALYZE shows a sequential scan on a ten-million-row orders table filtered by customer_id and a date range. Adding a composite index on customer_id and order_date turns the scan into an index range scan, and the query drops to forty milliseconds, confirmed by re-running the plan.