Indexes, Composite Indexes, and Reading EXPLAIN

This lesson's DDL and EXPLAIN output are real PostgreSQL, shown for reading — SQLite's query planner and EXPLAIN output format differ enough from PostgreSQL's that reading real PostgreSQL output here is what this module's guided local lab has you verify directly.

Without an index, finding rows matching a condition (WHERE course_id = 5) requires PostgreSQL to check every single row in the table — a sequential scan, O(n) in the table's size, using this course's earlier terminology exactly. An index on course_id is a separate, ordered structure (by default, a B-tree — conceptually similar to the balanced search trees from the earlier DSA-adjacent reasoning this course assumes, though implemented very differently) that lets PostgreSQL locate matching rows in roughly O(log n), the same fundamental speedup a sorted structure provides over an unsorted scan. CREATE INDEX idx_enrollment_course_id ON enrollment(course_id); builds exactly this structure.

Indexes are not free — this is the honest half of the story too often left out. Every index must be updated on every INSERT, UPDATE, or DELETE touching the indexed column, which means indexes trade faster reads for slower writes, plus real, ongoing storage space. A table that's written to constantly but rarely queried by a given column is a genuinely poor candidate for indexing that column — the write-side cost is paid on every single write, whether or not the read-side benefit is ever actually used. This is precisely why "index every column, just in case" is bad advice: it's a real cost paid unconditionally, for a benefit that only exists if that column is actually queried often enough to matter.

A composite index covers multiple columns together, and column order matters — an index on (learner_id, course_id) efficiently serves a query filtering on learner_id alone, or on both learner_id AND course_id together, but does not efficiently serve a query filtering on course_id alone, because the index's ordering is by learner_id first — exactly the way a phone book sorted by last-name-then-first-name doesn't help you find everyone with a given first name. The general rule: put the column used in equality filters (WHERE learner_id = ...) before columns used in range filters or sorting, and match the column order to your most common, performance-critical query pattern.

EXPLAIN shows the query planner's chosen execution plan without actually running the query; EXPLAIN ANALYZE actually runs it and reports real timing alongside the plan. The single most important thing to look for in a plan's output is whether a query used a Seq Scan (every row checked — the O(n) case) or an Index Scan (the index was used — the O(log n) case) on the table and column in question. Genuinely worth stating plainly, since it's a common overclaim: an index does not automatically guarantee better performance for every query — for a very small table, or a query expected to match a large fraction of the table's rows, PostgreSQL's planner can correctly choose a sequential scan over an available index, because scanning sequentially is sometimes actually faster than the overhead of consulting the index structure for many matches; EXPLAIN is how you find out what the planner actually decided, rather than assuming.