Query Execution Plans
As a Architect engineer, explain Query Execution Plans. What problem does it solve and how would you describe it in an interview?
Answers use simple, clear English.
Quick interview answer
At Architect 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 Architect 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: Architect.
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?