Query Execution Plans
As a Tech Lead engineer, explain Query Execution Plans. What problem does it solve and how would you describe it in an interview?
Answers use simple, clear English.
Audio N/AQuick interview answer
At Tech Lead depth: start with the problem, then mechanism, then a short example. Optimizer chooses join order, access paths (seek vs scan), and algorithms (hash vs merge join).
Detailed answer
At Tech Lead depth: start with the problem, then mechanism, then a short example. Optimizer chooses join order, access paths (seek vs scan), and algorithms (hash vs merge join). EXPLAIN/EXPLAIN ANALYZE shows estimated vs actual rows — key for tuning. Core: Optimizer chooses join order, access paths (seek vs scan), and algorithms (hash vs merge join). EXPLAIN/EXPLAIN ANALYZE shows estimated vs actual rows — key for tuning. Real-time example: Sudden seq scan on million-row table traced to outdated stats after bulk load. Pros: Visual plan reveals missing indexes and bad estimates. Cons: Plans vary by engine; hints are last resort. Common mistakes: Tuning without measuring; ignoring rows mismatch (estimate 10, actual 1M). Best practices: ANALYZE/UPDATE STATISTICS after bulk changes; compare before/after cost. Audience level: Tech Lead.
Full explanation
Optimizer chooses join order, access paths (seek vs scan), and algorithms (hash vs merge join). EXPLAIN/EXPLAIN ANALYZE shows estimated vs actual rows — key for tuning.
Real example & use case
Sudden seq scan on million-row table traced to outdated stats after bulk load.
Pros & cons
Pros: Visual plan reveals missing indexes and bad estimates. Cons: Plans vary by engine; hints are last resort.
Common mistakes
Tuning without measuring; ignoring rows mismatch (estimate 10, actual 1M).
Best practices
ANALYZE/UPDATE STATISTICS after bulk changes; compare before/after cost.
Follow-up questions
- How would you test Query Execution Plans?
- What metrics prove Query Execution Plans is healthy in prod?
- How does Query Execution Plans change at 10× traffic?