Reviewed by Aditya Kumar · Last reviewed 2026-03-24
**Diagnose**: (1) EXPLAIN ANALYZE—actual vs. estimated rows; large gap = stale stats. (2) Identify: Seq Scan on large table, nested loop with high cost, sort spill. (3) Check join order—small table should drive when possible. **Improve**: Add index on filter/join columns;...
Diagnose: (1) EXPLAIN ANALYZE—actual vs. estimated rows; large gap = stale stats. (2) Identify: Seq Scan on large table, nested loop with high cost, sort spill. (3) Check join order—small table should drive when possible. Improve: Add index on filter/join columns; ANALYZE; rewrite (join vs. subquery); partition prune; materialized view. Benchmark: Before/after; A/B in staging.
Red Flag: Adding index without verifying plan change. Pro-Move: 'We found planner chose nested loop (bad estimate); ANALYZE + composite index changed to hash join—20× faster.'
According to DataEngPrep.tech, this is one of the most frequently asked SQL interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.