Query Execution Plans
Give a real production-style example of using Query Execution Plans. Walk through the scenario end-to-end.
Answers use simple, clear English.
Audio N/AQuick interview answer
Scenario: Sudden seq scan on million-row table traced to outdated stats after bulk load. Implementation notes: ANALYZE/UPDATE STATISTICS after bulk changes; compare before/after cost.
Detailed answer
Scenario: Sudden seq scan on million-row table traced to outdated stats after bulk load. Implementation notes: ANALYZE/UPDATE STATISTICS after bulk changes; compare before/after cost. 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
Open one as its own read / solve / listen card