Module 08 · Database scaling · Lesson 6 of 9
Query optimization
How the planner turns SQL into an execution plan and how to read one. Scans, joins and sorts, statistics and cardinality estimates, the query patterns that defeat indexes, N+1 from ORMs, and a repeatable method for making a slow query fast.
Why it matters
Most database capacity is wasted by a handful of bad queries. A single query doing a sequential scan on a hot path can consume more CPU than the rest of the workload combined, and fixing it is worth more than any amount of hardware. Query optimisation is the skill of finding those queries and understanding why the planner chose what it chose, and it is the daily reality behind the phrase "we scaled the database".
The idea
The database does not execute SQL; it executes a plan: a tree of operators (scan this index, join these two inputs with a hash, sort, limit). The planner chooses among many possible plans using statistics about the data (row counts, value distributions, correlations) to estimate the cost of each. When the plan is good, the query is fast; when the estimates are wrong or no good plan exists, the query is slow.
Reading a plan
EXPLAIN ANALYZE runs the query and shows each operator with estimated and actual rows, time and loops. Read it bottom-up and inside-out. The usual culprits:
| Symptom in the plan | Likely cause | Fix |
|---|---|---|
| Seq Scan on a big table with a selective filter | No usable index, or a non-sargable predicate | Add the index; rewrite the predicate so the column stands alone |
| Estimated rows 100, actual rows 2,000,000 | Stale or missing statistics, correlated columns | ANALYZE the table; extended statistics on correlated columns |
| Nested Loop with a huge outer input | Planner expected few rows and got many | Fix the estimate; the planner would then pick a hash join |
| Sort with external merge on disk | Sorting more than work memory allows | Index that provides the order; raise work memory; limit earlier |
| Index Scan followed by many Heap Fetches | Index does not cover the columns read | Make the index covering |
| Many identical small queries in the log | N+1 from an ORM lazy-loading | Eager load or batch with IN |
Patterns that defeat indexes
A predicate is sargable when the indexed column appears bare on one side: created_at >= '2026-11-01' uses an index, date(created_at) = '2026-11-01' does not. Leading wildcards (LIKE '%foo'), implicit type casts (comparing a text column to an integer), OR across different columns, and functions on the column all push the planner to a scan. SELECT * on wide tables forces heap fetches that a covering index would avoid. OFFSET 100000 reads and discards a hundred thousand rows; cursors from the API module fix it.
N+1 and the ORM
Object-relational mappers make it easy to load a list and then touch a relation per item, producing one query for the list and one per row: fast in development with ten rows, catastrophic with ten thousand. The slow query log shows it as thousands of identical fast queries rather than one slow one. Fix with eager loading (a join or an IN list), data loaders that batch within a request, or a hand-written query for the hot path.
A method
- 1
Find the worst offenders by total time
Use the slow query log or a statistics view (pg_stat_statements) sorted by total time, not by single slowest: a 20 ms query run a million times matters more than a 5 s report. - 2
Explain with real parameters
Plans change with values; a plan for a small tenant can differ from a large one. Use EXPLAIN ANALYZE with representative inputs. - 3
Fix estimates before adding indexes
If estimated and actual rows diverge, run ANALYZE and consider extended statistics. A correct estimate often fixes the plan for free. - 4
Then index or rewrite
Add the index the plan needs, make it covering if heap fetches dominate, or rewrite the predicate to be sargable. Re-explain to confirm. - 5
Measure in production
Compare the statistics view before and after; a plan that looked better in isolation can regress under concurrency.
Beyond single queries
Connection limits, lock contention and long transactions can make fast queries slow by making them wait. Pooling and transaction hygiene (later in this module and in the ACID lesson) matter as much as plans. Read replicas take reporting queries off the primary; materialised views pre-compute the expensive ones.