Back

Indexes and performance

Indexes make lookups fast, so the database doesn't have to scan every row. Add them on columns used in WHERE, JOIN, and ORDER BY. Use EXPLAIN ANALYZE to see if a query is slow and whether it uses an index.

-- Check how a query runs
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42;
-- "Seq Scan" = reading every row (slow on big tables)

CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Run again: now "Index Scan" (fast)
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42;

-- Tips: avoid SELECT *, use LIMIT, index foreign keys

Roadmap