How would you analyse and optimise a slow query?
Interviewer Expectations for this Question
01
Core Competency
Assesses fundamental understanding of DBMS conventions, runtime behavior, and memory/performance considerations.
02
Evaluation Criteria
Hiring managers look for precision, avoidance of ambiguous jargon, and ability to explain trade-offs under real production conditions.
Comprehensive Model Answer
Verified Solution
Query optimisation starts by measuring. EXPLAIN shows the plan; EXPLAIN ANALYZE runs it and reports actual rows and time.
Things to look for: sequential scans on large tables, nested loops over big inputs, misestimated row counts, sorts spilling to disk and repeated subplans.
Common improvements:
- Add the right index for filtering and join columns, and consider covering indexes with included columns.
- Keep statistics fresh so the planner estimates well, and avoid functions on indexed columns in predicates.
- Select only needed columns, and avoid
SELECT *. - Rewrite correlated subqueries as joins where appropriate, and reduce the number of joins per query.
- Verify the join order and join types suit the data distribution.
EXPLAIN ANALYZE
SELECT id FROM orders WHERE customer_id = 42 ORDER BY created_at DESC;
Indexes speed reads but slow writes and consume space, so balance them. Always verify with real data volumes, because plans on tiny test tables are misleading.
Candidate Response Strategy & Interview Tips
- Start with a concise one-sentence summary: Deliver a direct, confident answer first before expanding into nuances.
- Demonstrate real-world trade-offs: Discuss where this approach excels and when you would avoid it in production systems.
- Discuss complexity & edge cases: Proactively explain time/space complexity or boundary conditions (null values, scale limits).
- Prepare for interviewer follow-ups: Technical hiring panels frequently probe deeper into concurrency, backward compatibility, or alternative libraries.