When should I add an index?

Add an index when a column is frequently used in WHERE, JOIN, or ORDER BY on a table large enough that a sequential scan is slow. Indexes speed reads but cost write throughput and storage, so don't index everything. Use EXPLAIN ANALYZE to confirm a query is actually scanning sequentially before adding one, and prefer a composite index matching your query's filter+sort order over several single-column indexes.