Indexes & Query Performance (Intro)

What an index is

An index is a separate data structure (typically a B-tree) that lets the DBMS find rows by a column’s value without scanning the whole table — like a book’s index instead of reading every page. You create one explicitly:

CREATE INDEX idx_students_major ON students (major_dept_id);

The primary key already has a unique index (Postgres builds it automatically), which is why PK lookups are fast for free.

The read/write trade-off

Indexes are not free. They speed up reads — row selection in WHERE, table matching in JOIN, and ordering for ORDER BY / GROUP BY — but they cost on every write, because each INSERT, UPDATE, or DELETE on an indexed column must also update the index, and each index consumes storage. So index selection is a balance:

- Index the primary key (automatic), the foreign keys (critical for join performance), and columns frequently used in WHERE / JOIN / ORDER BY with high selectivity — meaning the predicate narrows to a small fraction of rows (e.g. a unique-ish email, not a two-value flag). - Avoid indexing low-selectivity columns (few distinct values, so the index barely narrows the search) and heavily-updated columns, where the per-write maintenance cost outweighs the read gain. Too many indexes can make a write-heavy table *slower* overall.

Reading a plan with EXPLAIN

EXPLAIN shows the optimizer’s chosen query plan *without running* the query — crucially, whether it will read the whole table (Seq Scan) or jump straight to matching rows via an index (Index Scan):

EXPLAIN SELECT * FROM students WHERE major_dept_id = 1;

Worked example: inspect, then index

The workflow is *measure, then change*. First read the current plan for a filtered query, then add an index on the filtered column so a future planner can use it:

EXPLAIN SELECT * FROM students WHERE major_dept_id = 1;
CREATE INDEX idx_students_major ON students (major_dept_id);

On a large table the index lets the planner switch from a sequential scan to an index scan, often a huge win. On a tiny table (like this 12-row sample) the planner may rationally *still* choose a seq scan — reading a couple of pages is cheaper than the index hop — so don’t be surprised if the plan looks unchanged here. The lesson is that the plan reflects cost, not dogma: an index is an option the optimizer uses *when it pays*. Module 8 takes file organizations, selectivity, and EXPLAIN further.