Indexes & Query Performance

Indexes & Query Performance

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

Creating Indexes

-- Single column (most common)
CREATE INDEX progress_user_id_idx ON progress (user_id);

-- Composite index (column order matters)
CREATE INDEX progress_user_course_idx ON progress (user_id, course_id);

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

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

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

EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT * FROM progress WHERE user_id = 'uuid-here';

-- Output:
-- Index Scan using progress_user_id_idx on progress  (cost=0.29..8.31 rows=5)
--   Index Cond: (user_id = 'uuid-here'::uuid)
-- Planning Time: 0.1 ms
-- Execution Time: 0.2 ms

-- Without index (seq scan on large table):
-- Seq Scan on progress  (cost=0.00..5432.00 rows=5)
--   Filter: (user_id = 'uuid-here'::uuid)
-- Execution Time: 187 ms

When NOT to Index

-- Don't index:
-- 1. Small tables (seq scan is faster)
-- 2. Low-cardinality columns (boolean, status with 3 values)
-- 3. Columns rarely used in WHERE/JOIN/ORDER BY
-- 4. Tables with very high write volume (indexes slow down writes)

-- Do index:
-- 1. Foreign keys (user_id, order_id)
-- 2. Frequently filtered columns
-- 3. Columns used in ORDER BY on large tables
-- 4. Unique constraints