Indexes & Query Performance

Indexes & Query Performance

Indexes are the primary performance tool in PostgreSQL. EXPLAIN ANALYZE shows you where time is actually spent.

Why Indexes Matter

Without an index, PostgreSQL reads every row in the table to find matches — a "sequential scan." On a table with 1 million rows, finding one record might require reading all 1 million rows. An index is a sorted data structure (a B-tree) that lets Postgres jump directly to matching rows, like using a book's index instead of reading every page.

Creating Indexes

-- Single column (most common)
CREATE INDEX enrollments_user_id_idx ON enrollments (user_id);

-- Composite index (column order matters)
CREATE INDEX enrollments_user_course_idx ON enrollments (user_id, course_id);

-- Partial index (only index relevant rows — smaller and faster)
CREATE INDEX active_users_idx ON users (id)
WHERE deleted_at IS NULL;

-- Unique index (also enforces uniqueness)
CREATE UNIQUE INDEX enrollments_unique_idx ON enrollments (user_id, course_id);

-- Index on expression
CREATE INDEX course_track_idx ON enrollments (LEFT(course_id, 8));

EXPLAIN ANALYZE

-- Run before creating an index
EXPLAIN ANALYZE
SELECT * FROM enrollments WHERE user_id = 'uuid-here';

-- Without index output:
-- Seq Scan on enrollments  (cost=0.00..5432.00 rows=5 width=80)
--   Filter: (user_id = 'uuid-here')
-- Execution Time: 187 ms

-- After: CREATE INDEX enrollments_user_id_idx ON enrollments (user_id);
EXPLAIN ANALYZE
SELECT * FROM enrollments WHERE user_id = 'uuid-here';

-- With index output:
-- Index Scan using enrollments_user_id_idx on enrollments  (cost=0.29..8.31 rows=5 width=80)
--   Index Cond: (user_id = 'uuid-here')
-- Execution Time: 0.2 ms

The difference: 187ms → 0.2ms. That is a 935x speedup on a 100k-row table.

When NOT to Index

-- Do NOT index:
-- 1. Small tables (< 10k rows) — seq scan is faster than index overhead
-- 2. Low-cardinality columns (boolean, status with 3 options)
-- 3. Columns rarely used in WHERE / JOIN / ORDER BY
-- 4. Write-heavy tables — every index slows INSERT/UPDATE/DELETE

-- DO index:
-- 1. Foreign key columns (user_id, post_id) — always
-- 2. Frequently filtered columns
-- 3. Columns in ORDER BY on large tables
-- 4. Columns used in unique constraints

Reading EXPLAIN Output

Key terms to look for:

• Seq Scan — reading the whole table. Bad on large tables.
• Index Scan — using an index. Good.
• cost=X..Y — X is startup cost, Y is total cost (higher = slower)
• rows=N — estimated matching rows
• actual time=X..Y — real execution time in ms
• Execution Time — total query time at the bottom