How do you approach SQL performance tuning?

Start with the actual query and its plan, not with guesses. Find the expensive node, compare estimated and actual rows, and check whether the query reads far more data than it returns. Fixing row estimates or removing a bad join often matters more than adding another index.

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total_cents
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.email = 'a@example.com'
  AND o.created_at >= now() - interval '30 days';

The tuning checklist

• Confirm the query is actually the bottleneck and measure with realistic parameters
• Read the plan for sequential scans, bad estimates, spills, and repeated inner loops
• Reduce rows early with selective predicates and avoid functions on indexed columns
• Create composite indexes that match equality, range, and ordering needs
• Avoid N plus one access patterns and unnecessary SELECT * columns
• Use keyset pagination instead of deep OFFSET
• Refresh statistics and consider partitioning for very large tables
• Cache or precompute results when the same expensive aggregation is requested often

SELECT id, total_cents
FROM orders
WHERE customer_id = 42
  AND created_at >= '2026-09-01'
  AND created_at < '2026-10-01';

Indexes are not the only lever. A query that returns a million rows to the application is a design problem, not an index problem. Sometimes the best fix is a narrower projection, a precomputed summary, or a different access pattern.