A missing index on a foreign key column can turn a 5ms query into a 5-second table scan. A correlated subquery in a WHERE clause can multiply your query time by the number of rows. An ORM that generates N+1 queries can bring down a production API under moderate load. SQL performance problems are almost always fixable — the hard part is knowing where to look. This guide is that map.
All examples use PostgreSQL 16 syntax. The concepts apply to MySQL, SQLite, and most relational databases.
How PostgreSQL Picks a Query Plan
Before optimizing, understand what the planner does. PostgreSQL maintains statistics about tables and uses a cost-based optimizer to choose among many possible query plans. It estimates row counts, considers available indexes, and picks the plan with the lowest estimated cost. The planner is usually right — when it is wrong, it is almost always because statistics are stale or misleading.
-- always run ANALYZE before investigating slow queries
