Walk me through diagnosing a slow query in production.
Reviewed by Gurusankar M.
⚡ Short Answer
Find it (slow-query log / pg_stat_statements / APM), reproduce with EXPLAIN ANALYZE, identify the costly node (full scan, bad join, sort/spill), check indexes + statistics + the estimated-vs-actual gap, then fix: add/fix index, rewrite SARGable predicates, update stats, or reduce returned data. Verify p95/p99 after.
☕Coffee Chat Question
Concept Made Simple
“Walk me through diagnosing a slow query in production.”
🧠Mind Map Answer
Remember It Faster
🔥What If?
Think Beyond the Expected
The query is fast in isolation but slow only in production — what else do you check?
Look beyond the plan: parameter-sniffing/plan-cache issues, lock contention/blocking (it's waiting, not running), connection-pool saturation, cold cache vs warm, and data volume/skew differences. 'Slow' in prod is often waiting, not executing.
😂Real World
pg_stat_statements / the slow-query log surfaces the worst offenders by total time; the fix is usually an index, a SARGable rewrite, or fresher statistics — validated by p99 latency, not a one-off run.
🎯Interviewer's Expectation
Keywords they're listening for:
⚠️Common Mistakes
- ✗Optimizing a single run, not aggregate impact
- ✗Ignoring lock waits / pool saturation
- ✗Not re-measuring p99 after the change
✅Best Practices
- ✓Prioritize by total time (pg_stat_statements)
- ✓Separate execution time from wait time
- ✓Validate with percentiles after the fix
🔁Follow-up Questions
- 1How do you find the worst queries by total impact?
- 2How do you tell 'slow executing' from 'slow waiting'?
- 3What is parameter sniffing and how do you handle it?
🧩Related Technologies
Continue Learning with AI
Take this question deeper with your favourite AI assistant. Pick a depth, copy the prompt, or open it directly — AI is your learning companion, not a shortcut.
Plain-language foundations
I'm preparing for a software engineering interview and want to understand this from scratch, as a beginner. Topic: Optimization (SQL) Interview question: "Walk me through diagnosing a slow query in production." Please: 1. Explain the core idea in simple, plain language, using an everyday analogy. 2. Define any technical terms you use. 3. Walk through one small, concrete example. 4. Finish with a single sentence I can easily remember. Keep the tone friendly and assume I'm new to this topic.
Was this answer helpful?
⭐ Featured Products
Support our platform by exploring our recommended products.
As an Amazon affiliate, purchases through these links may earn us a small commission — at no extra cost to you. It helps keep Full Stack Interview Guru free.