Query optimization is the process of finding the least expensive way to execute a database query, both automatically, when the query optimizer chooses an execution plan, and deliberately, when engineers rewrite SQL, add indexes or adjust the schema so that the optimizer has better options. In database performance, it is usually the change with the largest effect per hour of effort: a single fix to a slow query can cut its runtime by orders of magnitude, while hardware upgrades rarely deliver more than a small multiple.
SQL is declarative: a query states what result is wanted, not how to compute it. The same query can be executed through thousands of different plans whose costs differ by a factor of a million, so choosing among them is the central job of the optimizer and the main subject of performance tuning.
How a query optimizer chooses a plan
A query optimizer chooses a plan by generating alternative execution strategies, estimating the cost of each from statistics about the data, and keeping the cheapest. Almost all modern relational databases use cost-based optimization, an approach that goes back to IBM’s System R in the late 1970s.
| Stage | What happens | Typical output |
|---|---|---|
| Parsing | Syntax check and name resolution | Parse tree |
| Rewriting | View expansion, subquery flattening, predicate pushdown | Simplified logical plan |
| Plan enumeration | Alternative access paths, join orders and join methods | Candidate physical plans |
| Cost estimation | Row counts and I/O, CPU and memory costs | Estimated cost per plan |
| Execution | Runs the chosen plan, often cached for reuse | Result rows |
The heart of costing is cardinality estimation: predicting how many rows each step produces. The optimizer derives these numbers from table statistics, such as row counts, distinct values, most common values and histograms. When statistics are stale or the data is correlated in ways the optimizer does not model, estimates can be off by several orders of magnitude, which is the most common root cause of a bad plan.
Join order makes the search space enormous. A query joining 10 tables has 3,628,800 left-deep join orders, and the count grows factorially. Optimizers therefore use dynamic programming with pruning for moderate joins and heuristics beyond a limit; PostgreSQL, for example, switches to a genetic search at 12 or more relations by default.
Access paths and join algorithms
For each table the optimizer chooses an access path, such as a sequential scan, an index scan, an index-only scan or a bitmap scan, and for each join it chooses an algorithm. Reading a plan means recognizing these building blocks and knowing when each is appropriate.
| Join algorithm | How it works | Best when | Weakness |
|---|---|---|---|
| Nested loop | For each outer row, probe the inner input | Small outer input, indexed inner side | Explodes if the outer estimate is too low |
| Hash join | Build a hash table on one input, probe with the other | Large unsorted inputs, equality joins | Needs memory; spills to disk if it overflows |
| Merge join | Walk two sorted inputs in step | Inputs already sorted, for example by an index | Sorting cost if inputs are unsorted |
Index scans are not always better. An index lookup costs a random read per matching row, while a sequential scan reads pages in bulk. Once a predicate matches more than a few percent of a table, a full scan is often cheaper, and a good optimizer chooses it deliberately.
Practical query tuning workflow
Practical query optimization starts with measurement, not guessing. Engineers first identify the queries that consume the most total time, typically from the slow query log or statement statistics such as pg_stat_statements, because a 50 ms query executed 2 million times a day costs more than a 20 second report run once.
- Rank queries by total time, meaning frequency multiplied by mean duration, not by the slowest single run.
- Read the actual execution plan with
EXPLAIN ANALYZEor the engine’s equivalent and compare estimated with actual row counts. - Find the dominant step, usually a large scan, a nested loop with millions of iterations, or a sort spilling to disk.
- Apply the smallest effective fix: refresh statistics, add or change an index, or rewrite the predicate.
- Verify under realistic data and concurrency, then keep monitoring, because plans change as data grows.
The table lists frequent anti-patterns and their standard remedies.
| Anti-pattern | Why it is slow | Typical fix |
|---|---|---|
| Function on indexed column | WHERE date(created_at) = ... cannot use the index | Rewrite as a range or add an expression index |
| N+1 queries | One query per row from application code | Single join or batched IN query |
| Large OFFSET pagination | Skipped rows are still read | Keyset pagination on an indexed column |
| SELECT * | Reads unneeded columns, blocks index-only scans | List only required columns |
| Implicit type conversion | Comparing text to a number disables the index | Match parameter and column types |
| Stale statistics | Row estimates far from reality | Run ANALYZE or raise the statistics target |
Optimization levers beyond the query text
When rewriting a single query is not enough, optimization moves to the schema and the system. Each lever has a cost, so the choice depends on the bottleneck the plan reveals.
| Lever | Helps when | Cost or risk |
|---|---|---|
| Indexes | Selective filters, joins, sorting | Slower writes, more storage |
| Partitioning | Queries target a time or key range | More complex maintenance |
| Materialized views | Repeated heavy aggregations | Stale data between refreshes |
| Denormalization | Joins dominate read cost | Update anomalies, duplicate data |
| Memory settings | Sorts and hashes spill to disk | Less memory for concurrent sessions |
| Caching layer | Same results requested repeatedly | Invalidation complexity |
Optimizer hints and forced plans exist in Oracle, SQL Server and MySQL, but they freeze one decision while the data keeps changing. Experienced engineers treat them as a last resort, preferring better statistics or indexes that let the optimizer find the right plan on its own.
Limits and common mistakes
- Testing on small data: a plan that is fast on 10,000 rows can be disastrous on 100 million, because the optimizer chooses differently.
- Optimizing average latency only: the 99th percentile often reveals parameter-sensitive plans that are fast for most values and slow for a few.
- Adding indexes without removing old ones, which steadily slows writes.
- Ignoring plan regressions after upgrades or statistics refreshes; plan baselines and query stores help detect them.
- Tuning a query that should not run at all, such as a dashboard refreshing every second when once a minute would do.
A plain-language summary
Query optimization is like planning the fastest route before a delivery trip. A database question can be answered in many ways, just as a city can be crossed by many routes. The database looks at what it knows about traffic, here the size and shape of the data, and picks the route it expects to be quickest.
When its map is outdated or the roads are poorly built, it picks badly. Engineers then update the map, build better roads such as indexes, or rephrase the question so the good route becomes obvious.
An example from practice
A logistics company notices that its shipment tracking page has become slow. The statement statistics show that one query, executed about 400,000 times a day with a mean time of 850 ms, accounts for roughly 94 hours of database time daily. The query filters shipments by customer and status and joins three tables.
The execution plan reveals the cause: the optimizer estimated 12 rows for one status filter but actually found 180,000, so it chose a nested loop that ran 180,000 index probes. A statistics refresh with a higher target on the skewed column, plus a composite index on (customer_id, status, updated_at), lets the optimizer pick a hash join with an accurate estimate. Mean time falls to 14 ms, and the query’s daily load drops from about 94 hours to about 1.6 hours of database time.