Indexes and Query Performance

After this lesson, you will be able to: Reason about B-tree indexes: when to add one, when not to, covering indexes, and the write-cost trade-off.

Indexes are how databases stay fast at scale. The #1 reason a query is slow is a missing or unused index. This lesson teaches the mental model.

Prerequisites:Window Functions

What an index is

A separate sorted data structure (usually B-tree) that lets the DB find rows in O(log n) instead of scanning every row. Like the index of a book: instead of reading every page to find a topic, jump to the page number. The O(log n) lookup is binary search: because a B-tree keeps keys sorted, the database can halve the search space at every node, the same algorithm covered in the Computer Science Fundamentals > Algorithms binary search lesson. Default index type in Postgres is B-tree. Others exist (GIN, GiST, BRIN, Hash) for specific use cases.

Create + use indexes

Standard CREATE INDEX syntax.

tsx

-- Single-column index
CREATE INDEX idx_posts_user_id ON posts(user_id);

-- Composite index, order matters!
CREATE INDEX idx_posts_user_published ON posts(user_id, published);

-- Unique index (constraint + speed)
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Partial index (only some rows)
CREATE INDEX idx_posts_published ON posts(created_at) WHERE published = TRUE;

-- Drop
DROP INDEX idx_posts_user_id;

-- Concurrently, don't lock the table (use in prod)
CREATE INDEX CONCURRENTLY idx_users_name ON users(name);

Check if an index is used

EXPLAIN ANALYZE shows the query plan + timing.

sql

EXPLAIN ANALYZE
SELECT * FROM posts WHERE user_id = 42;

-- Output looks like:
--   Index Scan using idx_posts_user_id on posts (cost=0.29..8.31 rows=1 width=...) (actual time=0.012..0.014 rows=1 loops=1)
--     Index Cond: (user_id = 42)
--   Planning Time: 0.078 ms
--   Execution Time: 0.027 ms

-- If you see 'Seq Scan' on a big table where you expected Index Scan, your index isn't being used.
-- Reasons: column not indexed, OR query rewrites the column (WHERE LOWER(email) = ...).

Composite index column order

Order = leftmost prefix rule.

sql

CREATE INDEX idx_posts_user_pub ON posts(user_id, published);

-- Uses the index:
SELECT * FROM posts WHERE user_id = 1;
SELECT * FROM posts WHERE user_id = 1 AND published = TRUE;

-- Does NOT use the index (no leftmost prefix):
SELECT * FROM posts WHERE published = TRUE;

-- Rule: query must include columns starting from the LEFT of the index.

Common mistakes

Indexing every column 'just in case' (write penalty + bloat). Wrapping indexed column in a function: `WHERE LOWER(email) = 'x'`, bypasses index. Fix: functional index `CREATE INDEX ON users (LOWER(email))`. Composite index in wrong order. Not running ANALYZE after big data changes, planner uses stale stats.

←Window Functions

Back to SQL

Transactions and ACID→