A query planner, also called a query optimizer, is the database component that turns a declarative SQL statement into a concrete execution plan: which tables to read, whether to use an index or a full scan, in what order to join tables and which join algorithm to apply. In indexing, the planner is the part that decides whether an index is used at all.
SQL describes what result is wanted, not how to compute it. Most relational systems use a cost-based planner that estimates the cost of many alternative plans from table statistics and picks the cheapest. Creating an index only offers the planner a new option; the planner still judges whether that option is cheaper than the alternatives.
How a query planner builds an execution plan
Planning happens after parsing and before execution. The parser checks syntax, the rewriter expands views and applies rules, and the planner then explores equivalent ways to produce the same result. The chosen plan is a tree of operators, such as scans, joins, sorts and aggregations, that the executor runs.
- Parse and validate the statement against the system catalog.
- Rewrite it, for example by inlining views or unnesting subqueries.
- Generate candidate plans for access paths, join orders and join methods.
- Estimate the cost of each candidate from statistics and cost parameters.
- Select the cheapest plan and hand it to the executor.
The search space grows very quickly with the number of joined tables, because the number of possible join orders grows factorially. Planners therefore use dynamic programming for small joins and switch to heuristics or genetic search beyond a threshold; PostgreSQL, for example, uses its genetic optimizer from 12 tables by default.
Cost estimation and statistics
The planner predicts cost in abstract units that combine estimated I/O and CPU work. Its most important input is cardinality estimation: how many rows each step will produce. Errors in row estimates multiply through joins, which is why most bad plans trace back to bad estimates rather than bad cost formulas.
The statistics behind those estimates are collected by commands such as ANALYZE and are usually gathered automatically in the background. The table below lists the main inputs and their influence.
| Input | What it describes | Influence on the plan |
|---|---|---|
| Row count and pages | Size of each table | Scan cost and join order |
| Distinct values | Number of different values in a column | Selectivity of equality filters |
| Histograms | Distribution of values | Selectivity of range filters |
| Most common values | Frequent values and their shares | Estimates for skewed data |
| Correlation | Physical order versus index order | Cost of index range scans |
| Cost parameters | Relative price of random and sequential I/O | Index scan versus full scan |
Planners usually assume that filter conditions are independent. When columns are correlated, such as city and postal code, the estimate can be wrong by orders of magnitude. Some systems offer extended or multi-column statistics to correct this.
Access paths and the role of indexes
For each table the planner chooses an access path. An index is attractive when a filter is selective, typically returning a small fraction of rows; when a query needs a large share of the table, a sequential scan is often cheaper because it reads pages in order and avoids random I/O.
| Access path | When the planner prefers it | Index type involved |
|---|---|---|
| Sequential scan | Low selectivity or small tables | None |
| Index scan | Selective filter, few rows | B-tree or hash index |
| Index-only scan | All needed columns are in the index | Covering index |
| Bitmap index scan | Moderate selectivity or several indexes combined | One or more B-tree indexes |
| Index range scan with order | Query needs sorted output | B-tree or composite index |
A composite index helps only when the filter uses its leading columns, and a covering index lets the planner skip table lookups entirely. A hash index supports equality only, so the planner ignores it for ranges and sorting. Index design is therefore always a negotiation with the planner’s rules.
Join methods the planner chooses
When a query joins tables, the planner chooses both the join order and the join algorithm for each pair. Each algorithm suits different input sizes and index availability.
| Join method | Best case | Weak case |
|---|---|---|
| Nested loop join | Small outer input and indexed inner table | Two large unindexed inputs |
| Hash join | Large unsorted inputs with equality conditions | Hash table larger than working memory |
| Merge join | Inputs already sorted on the join key | Unsorted inputs needing expensive sorts |
A plan that chooses a nested loop because it expected ten rows but receives a million is the classic cause of a query that suddenly takes minutes. The fix is usually better statistics or a better index, not a different join syntax.
Reading plans and fixing planner mistakes
The EXPLAIN command shows the chosen plan, and EXPLAIN ANALYZE in PostgreSQL or MySQL 8 also runs the query and reports actual rows and timing next to the estimates. A large gap between estimated and actual rows marks the point where the planner went wrong.
| Symptom | Likely cause | Remedy |
|---|---|---|
| Index ignored | Function or type cast on the indexed column | Rewrite the predicate or add an expression index |
| Estimates far from actual | Stale or missing statistics | Run ANALYZE or raise the statistics target |
| Plan changes suddenly | Data growth or parameter-sensitive plan | Review statistics, plan caching and parameters |
| Slow join on large tables | Wrong join order from correlated filters | Add extended statistics or restructure the query |
Some databases accept optimizer hints or plan guides that force a choice. Hints can rescue an urgent problem, but they freeze a decision that may become wrong as data changes, so they are best treated as a last resort with documentation.
Explained without jargon
A query planner is like a navigation app for data. You state the destination, the result you want, and the app considers several routes: the highway, the side streets, or a shortcut through an index. It estimates each travel time and picks the fastest.
Its choice is only as good as its map. If the information about traffic, here the statistics about the data, is outdated, the app may send you into a jam. That is why keeping statistics current matters as much as building the roads.
A typical use case
An order history page in a web shop becomes slow after a large data import. The developer runs EXPLAIN ANALYZE and sees that the planner expected about 50 rows for one customer’s orders but read over 400,000, choosing a nested loop join that scanned far too much data.
The import had bypassed automatic statistics collection. Running ANALYZE on the orders table corrects the estimates, and the planner switches to an index scan on a composite index of customer and order date. Page load time falls from several seconds to under 50 milliseconds, without any change to the application code.