SQL: indexes & performance
This course explains how PostgreSQL indexes work and how they change query plans. It starts with the structure of a B-tree index: a sorted, balanced tree where every leaf sits at the same depth. It then compares EXPLAIN ANALYZE output on a 60,000-row table before and after add_index, and shows how Rails migrations and t.references create indexes. Later lessons cover composite indexes and column order, unique indexes that reject duplicates, GIN indexes for jsonb containment queries, and the write cost every index adds.
What you'll learn
- ✓How a B-tree index keeps values in sorted, balanced order
- ✓Reading EXPLAIN ANALYZE output to compare Seq Scan and Index Scan
- ✓Adding indexes in Rails migrations with add_index and t.references
- ✓How column order changes the cost of a composite index
- ✓Using unique indexes to reject duplicates at the database layer
- ✓GIN indexes for jsonb containment and the write cost of every index
What a b-tree index actually is
SQL: where & operators closed with a lesson naming B-tree indexes and showing EXPLAIN ANALYZE output, enough to tell Index Scan from Seq Scan. This course starts from that name and builds the structure underneath it. The next lesson then watches an index change a real query's plan.
Read the full lesson free →Curriculum
7 lessonsWhat a b-tree index actually is
Describes a B-tree as a sorted, balanced tree and explains why lookups grow far slower than rows.
Seq scan vs index scan, verified live
Compares EXPLAIN ANALYZE on 60,000 rows before and after add_index, showing Seq Scan turning into Index Scan.
add_index in a real Rails migration
Shows add_index inside this project's Rails migrations, the automatic index from t.references, and how schema.rb records indexes.
Composite indexes & column order
Measures how a composite index's column order changes query cost, with the leading column giving the cheapest lookups.
Unique indexes: enforcing correctness at the DB layer
Shows a unique index rejecting a duplicate insert and why model validations alone can be raced.
GIN indexes: making JSONB columns searchable
Explains why jsonb containment queries with @> need a GIN index, and why this app's original GIN indexes were dropped.
Putting it together: choosing the right index
Reviews the four index shapes and explains the write cost each index adds on INSERT, UPDATE and DELETE.