Reviewed by Aditya Kumar · Last reviewed 2026-03-24
**Indexing**: B-tree/hash/bitmap structures for fast lookup; trades write cost for read speed. Use on high-selectivity filter/join columns. **Partitioning**: Physical segmentation (range/list/hash); partition pruning skips irrelevant data. **Execution plan**: Optimizer's chosen...
Indexing: B-tree/hash/bitmap structures for fast lookup; trades write cost for read speed. Use on high-selectivity filter/join columns. Partitioning: Physical segmentation (range/list/hash); partition pruning skips irrelevant data. Execution plan: Optimizer's chosen path—scan type, join algorithm, sort. EXPLAIN ANALYZE shows actual vs. estimated rows. Why together: Index within partition; partition reduces scope; plan reveals if both used. Trade-off: Over-indexing slows writes; over-partitioning = small-file problem. Cost: Composite index (a,b) helps (a) and (a,b) but not (b) alone.
Red Flag: Ignoring execution plan before adding indexes. Pro-Move: 'We saw plan estimated 100 rows, actual 10M—stats were stale; ANALYZE fixed it and planner chose index join instead of seq scan.'
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.