HardExperience level: 8-15 years1 min read

Walk me through diagnosing a slow query in production.

Reviewed by Gurusankar M.

Asked inAmazonMicrosoftGoogleDeloitte
#slow query#diagnosis#explain analyze#slow query log#production
Report issue

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.

On this page

Concept Made Simple

Coffee Chat Question

“Walk me through diagnosing a slow query in production.”

Remember It Faster

Mind Map Answer

1. Find
slow log / pg_stat_statements / APM
2. Explain
EXPLAIN ANALYZE the real query
3. Diagnose
scan? bad join? sort spill? stats?
4. Fix + verify
index/rewrite/stats → re-measure

Think Beyond the Expected

What If?

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:

  • find via slow log/pg_stat_statements
  • EXPLAIN ANALYZE
  • scan/join/sort diagnosis
  • stats + estimate gap
  • consider waiting/locking/pool
  • verify p99

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

  1. How do you find the worst queries by total impact?
  2. How do you tell 'slow executing' from 'slow waiting'?
  3. What is parameter sniffing and how do you handle it?

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.
Open inChatGPTGeminiClaude

Was this answer helpful?

Affiliate links

Tools for Your Developer Workspace

Equipment for a comfortable, productive setup.

As an Amazon Associate I earn from qualifying purchases.