The short answer
Quick answer: SQL is declarative: you describe the result you want, not how to compute it. The query planner (or optimiser) works out the "how". It parses the query, generates many possible execution plans (which indexes to use, which join algorithm, in what order), estimates the cost of each one using statistics about your data, and runs the cheapest. When a query is unexpectedly slow, the planner usually picked a poor plan because its estimates were wrong or because no good option, such as a suitable index, existed.
Why a planner is needed
Consider:
SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.country = 'NZ' AND o.created_at > '2026-01-01';
There are many ways to produce this result:
- Start with customers or start with orders?
- Use an index on
country, or scan the whole table? - Use an index on
created_at? - Join with nested loops, a hash table or a merge?
All give the same answer. One might take 5 ms and another 5 minutes. With more tables, the number of possible plans grows explosively. The planner's job is to find a good one quickly.
The pipeline
- Parse. Turn the SQL text into a tree and check that tables and columns exist. This is the same idea as the front end of a compiler.
- Rewrite. Expand views, simplify expressions, flatten subqueries where possible.
- Plan. Enumerate candidate plans and estimate each one's cost.
- Execute. Run the chosen plan. Each node in the plan pulls rows from the nodes below it.
How costs are estimated
The planner cannot run every plan to see which is fastest. It predicts, using statistics the database collects about each table:
- Number of rows and pages.
- Number of distinct values per column.
- The most common values and how frequent they are.
- A histogram of the value distribution.
From these it estimates selectivity: what fraction of rows a condition will match. If statistics say 0.1% of customers are in New Zealand, an index on country looks attractive. If 60% were, a full scan would be cheaper.
Each operation is assigned a cost based on expected page reads (sequential reads being cheaper than random ones) and CPU work per row. Costs are added up through the plan, and the lowest total wins. This is a cost-based optimiser. SQLite's query planning documentation explains the reasoning with simple examples.
Statistics are refreshed by ANALYZE (often automatically). Stale statistics are a leading cause of bad plans.
The choices: scans
| Access method | How it works | Best when |
|---|---|---|
| Sequential scan | Read every row | A large share of rows match, or the table is small |
| Index scan | Walk the index, fetch each matching row | Few rows match |
| Index-only scan | Answer entirely from the index | The index covers all needed columns |
| Bitmap scan | Collect matches from one or more indexes, then read pages in order | A moderate number of rows match |
A sequential scan is not automatically bad. Reading the whole table in order is faster than thousands of random lookups. How indexes fit in is covered in how database indexes work.
The choices: joins
| Join algorithm | How it works | Best when |
|---|---|---|
| Nested loop | For each row in A, look up matches in B | A is small and B has an index on the join column |
| Hash join | Build a hash table from the smaller input, probe it with the larger | Large inputs joined on equality, no useful index |
| Merge join | Sort both inputs (or read them pre-sorted from indexes), then walk in step | Both inputs are already sorted or very large |
Join order matters just as much. Joining the two tables that reduce the row count the most first keeps every later step small. With many tables, planners use dynamic programming and heuristics because trying every order is impossible.
Reading EXPLAIN
EXPLAIN shows the chosen plan. EXPLAIN ANALYZE actually runs the query and adds real measurements. The PostgreSQL guide to EXPLAIN is the reference.
Hash Join (cost=12.50..840.20 rows=95 width=40) (actual time=0.4..6.1 rows=4200 loops=1)
Hash Cond: (o.customer_id = c.id)
-> Seq Scan on orders o (cost=0.00..720.00 rows=9800 ...) (actual ... rows=9650 ...)
Filter: (created_at > '2026-01-01')
-> Hash (cost=12.00..12.00 rows=40 ...) (actual ... rows=1800 ...)
-> Index Scan using idx_customers_country on customers c ...
How to read it:
- It is a tree. Indented nodes feed the node above. Execution starts at the innermost nodes.
costis the planner's estimate in arbitrary units, not milliseconds.rowsin the first bracket is the estimate; in the second bracket it is the actual count.
The most valuable habit: compare estimated rows with actual rows. In the example, the planner expected 40 customers and found 1,800. An estimate that is wrong by a large factor tells you where the plan went off course.
Things to look for:
- A sequential scan on a large table that returns few rows: probably a missing index.
- A nested loop with a huge
loopscount. - Sorts or hashes that spill to disk.
- Rows removed by a filter after being read: an index could have avoided reading them.
Why planners get it wrong
- Stale statistics after a big data load.
- Correlated columns. The planner assumes conditions are independent.
city = 'Paris' AND country = 'France'is estimated as far rarer than it is. - Skewed data. One customer with a million orders behaves nothing like the average.
- Functions and expressions the planner cannot see through.
- Parameterised queries. A plan cached for one parameter value may be poor for another.
- Large joins, where estimation errors multiply at each step.
How to help the planner
- Keep statistics fresh. Make sure automatic analysis runs; run
ANALYZEafter bulk changes. - Create the right indexes for your filters, joins and sort orders.
- Write conditions that can use indexes. Avoid wrapping indexed columns in functions.
- Select only what you need.
SELECT *prevents index-only scans and moves more data. - Add extended statistics on correlated columns where your database supports them.
- Restructure the query if needed; sometimes splitting it or rewriting a subquery helps.
- Use hints sparingly. Some databases allow you to force a plan. It is a last resort, because data changes and the forced plan may become wrong.
Also check that the query is the real problem. An application that sends thousands of fast queries has an N+1 problem, not a planning problem, and one that opens too many connections needs connection pooling.
Frequently asked questions
What is a query execution plan?
The step-by-step strategy the database chose for running a query: which access methods, join algorithms and order of operations.
What is the difference between EXPLAIN and EXPLAIN ANALYZE?
EXPLAIN shows the plan and estimates without running the query. EXPLAIN ANALYZE runs it and reports actual times and row counts.
Why does the database ignore my index?
It estimated that another plan is cheaper, often because the condition matches many rows, the column is wrapped in a function, or statistics are out of date.
Is the query planner always right?
No. It works from estimates. It is usually good, and when it is wrong the estimated-versus-actual row counts usually show why.
Conclusion
The query planner is why you can write what you want and let the database work out how. It is only as good as its statistics and the options you give it. Learn to read EXPLAIN ANALYZE, look for the place where estimates and reality diverge, and you can fix most slow queries without guesswork.
Related articles
- How a Database Index Makes Queries 1000x Faster
- What Is the N+1 Query Problem and How to Fix It
- B-Trees vs LSM Trees: Why Databases Store Data Differently
- Why Your Database Needs Connection Pooling
Sources and further reading
