Database Query Optimization: From Slow Queries to Sub-Millisecond

Database performance problems follow a predictable pattern: they are invisible during development, obvious in production, and expensive by the time they are urgent. A systematic approach to query optimization — one that starts with measurement rather than assumption — resolves most problems faster and more durably than ad-hoc tuning.

Start with the Query Plan, Not the Query

The query plan tells you what the database engine decided to do with your query, and why. In PostgreSQL, EXPLAIN ANALYZE shows the actual row counts, execution time per node, and whether indexes were used. The most common finding: a sequential scan on a large table where an index scan was expected. This usually means the index does not exist, the query planner chose not to use it due to statistics being stale, or the query is written in a way that prevents index utilisation.

The Index Strategy

Indexes are not free. Each index adds write overhead and storage cost. The correct strategy is to index for your actual query patterns, not for every column that appears in a WHERE clause. Composite indexes must be ordered by selectivity: the most selective column first. Partial indexes — which index only a subset of rows matching a condition — are dramatically more efficient for queries on filtered subsets like active records or recent events.

An index on status where 90% of rows have the same status value is nearly useless. A partial index on status = 'pending' where 2% of rows are pending is extremely efficient.

N+1 Queries Are a Backend Problem, Not a Database Problem

N+1 query patterns — fetching a list of N records and then making one additional query per record — are the most common source of database performance problems in application servers. They are invisible in development with small datasets and catastrophic in production with large ones. The fix is almost always to use a JOIN or a batch query, not to optimise the individual queries. ORMs hide this problem; query logging reveals it.

Connection Pool Sizing

Connection pool exhaustion looks like query latency but is not a query problem. If your pool is sized at 10 connections and 50 threads compete for them, 40 threads wait. The wait time shows up in your application metrics as database latency. The actual queries may be fast. Pool sizing is not a matter of setting a large number — it requires understanding your database server's connection limit, your application's concurrency, and the ratio between them.