Indexing & Query Performance
Why some queries are instant and others scan the whole table — and how an index (usually a B-tree) changes that.
What an index does
Without an index, finding rows matching a condition requires a full table scan — O(n). An index (typically a B-tree) maintains a sorted structure over one or more columns, turning that lookup into O(log n).
The trade-off
Indexes speed up reads but slow down writes — every INSERT/UPDATE/DELETE must also update every index on the table, and each index consumes additional storage. Indexing every column isn't "more optimization," it's usually a net loss.
What to index
Columns frequently used in WHERE, JOIN ON, and ORDER BY clauses are the best candidates. A composite index on (a, b) speeds up queries filtering on a alone or on a and b together, but not on b alone — column order matters.
Reading a query plan
EXPLAIN (or EXPLAIN ANALYZE) shows whether the database used an index scan or fell back to a sequential scan — the starting point for diagnosing a slow query, rather than guessing.
Prerequisite
Understanding schema design first matters because which columns are worth indexing depends on how the schema is normalized and queried.