Query Execution Plans
Design a small subsystem that relies on Query Execution Plans. Outline components, data flow, failure modes, and metrics.
Answers use simple, clear English.
Quick interview answer
Use Query Execution Plans as the core idea. Example shape: Sudden seq scan on million-row table traced to outdated stats after bulk load..
Detailed answer
Use Query Execution Plans as the core idea. Example shape: Sudden seq scan on million-row table traced to outdated stats after bulk load.. Watch for: Plans vary by engine; hints are last resort.. Measure success via latency/error/saturation. 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: Mid-level.
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
Open one as its own read / solve / listen card