Without an index, the database scans every row to find matches (full table scan). With an index on the filter column(s), it can seek to matching rows much faster. Indexes are data structures (usually B-trees) that map column values to row locations. CREATE INDEX builds one; the optimizer uses it when the query matches (e.g. WHERE email = $1 uses an index on email).

Trade-off: indexes speed up reads but slow down writes (every INSERT/UPDATE/DELETE must update the index). Use indexes on columns you filter or sort by; avoid indexing every column.

A single-column index (e.g. on email) helps WHERE email = $1 and ORDER BY email. A composite index on (user_id, created_at) helps WHERE user_id = $1 ORDER BY created_at DESC. Order of columns matters: the index is useful if the query uses a left prefix of the columns (e.g. user_id only, or user_id and created_at).

Put the most selective column first in equality filters; put sort columns after. Covering indexes (index includes all columns needed) can avoid touching the table at all (index-only scan).

Use EXPLAIN (or EXPLAIN ANALYZE) to see how the database executes a query: which indexes are used, whether it scans or seeks, and estimated cost. If the plan shows "Seq Scan" on a large table, consider adding an index or rewriting the query.

N+1 problem: the app runs one query to get a list (e.g. orders), then N queries to get related data (e.g. user for each order). Fix: JOIN in one query, or batch load (e.g. get all user IDs, then one query WHERE id IN (...)). ORMs often have eager loading or select_related to avoid N+1.

Too many indexes on a table slow down writes and use space. Index only what you query. Avoid indexing low-cardinality columns (e.g. boolean) unless combined with others. Monitor index usage (e.g. pg_stat_user_indexes); drop unused indexes. For OLAP or reporting, consider a separate read replica or data warehouse rather than over-indexing the main OLTP table.