Backend Development › Indexing & Query Performance

Query Optimization

Rewriting queries and adding indexes to make them fast.

Backend Engineer · Mid-level · core Data Engineer · Mid-level

Also known as: SQL optimization, query tuning, SQL tuning, slow query fix, database performance tuning

Query optimization is making a database query faster by changing the query, the indexes or the data layout. It’s a measure, change, measure process, not guesswork.

A reliable workflow

Find what’s actually slow. Use the slow query log, APM or monitoring to find the queries that cost the most in total (time × frequency). A 50 ms query run 10,000 times a minute can matter more than one 5-second report.
Look at the plan with EXPLAIN / EXPLAIN ANALYZE, on realistic data volume (EXPLAIN).
Find the expensive step and decide on a fix.
Change one thing, then measure again.

Common fixes, roughly in order

Add or fix an index for the WHERE, JOIN and ORDER BY columns, with the right column order (indexes, composite indexes).

Make the filter usable by the index. These defeat it:

WHERE LOWER(email) = 'a@x.com'            -- function on the column (use an expression index, or store it lowercased)
WHERE created_at::date = '2024-06-01'     -- cast on the column; use a range: >= '2024-06-01' AND < '2024-06-02'
WHERE name LIKE '%son'                    -- leading wildcard
WHERE customer_id = '42'                  -- type mismatch

Fetch less. Select only the needed columns instead of SELECT *, filter early, add LIMIT, and avoid pulling big result sets into the application (cursors, pagination).

Fix N+1 patterns: replace per-row queries with a join or a batched query (N+1).

Avoid unnecessary work: remove needless DISTINCT, ORDER BY on huge sets, repeated subqueries, and joins to tables you don’t use.

Keep statistics fresh so the planner makes good choices (table statistics).

Rewrite awkward forms: EXISTS instead of IN with large lists, a join instead of a correlated subquery, UNION ALL instead of UNION when duplicates don’t matter.

Precompute expensive aggregates (materialized views, summary tables), or cache results (caching).

Reduce data: archive or partition old data (partitioning).

Cautions

Optimize with real data and real queries. A plan on 100 rows says nothing about 100 million.
Indexes cost write speed and space. Don’t add one for every query. Drop unused ones.
Don’t optimize without measuring. The bottleneck might be the network, the application or lock contention, not the query.
Premature optimization of queries that run rarely wastes time (premature optimization).
Test after changes: a rewrite must return the same results.
Database-specific features (hints, settings) are a last resort, and need revisiting as data grows.