Making slow queries fast

Most slow queries have one of three causes: missing indexes, fetching too many rows, or N+1 query patterns.

Step 1: EXPLAIN ANALYZE

Always start here. Never guess.

EXPLAIN ANALYZE
SELECT * FROM blogs
WHERE user_id = 42
ORDER BY created_at DESC;

Look for Seq Scan on large tables — that's your first target.

Step 2: Add the right indexes

CREATE INDEX idx_blogs_user_id ON blogs(user_id);
CREATE INDEX idx_blogs_created_at ON blogs(created_at DESC);

Step 3: Avoid SELECT *

Fetch only the columns you need. On wide tables this makes a significant difference.

N+1 is the silent killer

If you're running one query per item in a list, you have an N+1 problem. Use JOINs or batch fetching instead.