SQL Query Optimization & Indexing
Reading execution plans, choosing and ordering indexes, sargability, cardinality estimation, and knowing when an index is the wrong answer
A practitioner's tour of SQL performance as a data engineering interview topic: how to read an EXPLAIN / EXPLAIN ANALYZE plan and tell a sequential scan from an index scan from an index-only scan, how B-tree, hash, GIN, and GiST indexes differ and when each applies, why composite index column order determines whether an index is even usable, why wrapping a column in a function or relying on an implicit type cast silently disables an index, how the planner's cardinality estimates drive its join and scan choices — and go wrong when statistics are stale — the anti-patterns (SELECT *, OR instead of UNION, N+1 queries) that quietly cost the most in production, and the disciplined answer to 'should I just add an index here?'
Practice questions (5)
-
View →
Diagnosing and Fixing a Production Seq Scan
Intermediate · Free -
View →
Auditing a Query for Sargability Failures
Intermediate -
View →
Designing Composite Indexes for Competing Query Patterns
Intermediate -
View →
A Bulk Load Broke a Join's Plan
Intermediate -
View →
Code Review: Spotting Anti-Patterns Before They Hit Production
Intermediate