How PostgreSQL plans a query
Statistics, scans, join methods and sargable conditions.
SQL says what you want, not how to get it. For each query, PostgreSQL's planner considers the ways it could run, estimates the cost of each, and picks the cheapest. EXPLAIN shows the chosen plan; EXPLAIN ANALYZE runs it and adds what really happened.
Statistics and estimates
The planner's estimates come from statistics about each column: how many distinct values, the most common ones, and their spread. ANALYZE refreshes them (autovacuum does it in the background). The key estimate is selectivity: the share of rows a condition keeps. A condition that keeps few rows favours an index; one that keeps most rows favours reading the whole table.
Reading a table
| Scan | How | Cheap when |
|---|---|---|
| Sequential scan | reads every page of the table in order | a large share of rows is wanted |
| Index scan | walks the index, fetches each row | few rows are wanted |
| Bitmap scan | collects matches from the index, then reads their pages in order | a moderate share |
| Index-only scan | answers from the index alone | the index holds every column needed |
Joining tables
| Join method | How | Good for |
|---|---|---|
| Nested loop | for each row on one side, look up matches on the other | a small outer side and an index on the inner |
| Hash join | build a hash table of the smaller side, probe it with the larger | large, unsorted inputs joined on equality |
| Merge join | walk two inputs sorted on the join key side by side | inputs already sorted, for example by an index |
Why a B-tree is fast
A B-tree keeps keys sorted in pages of a few hundred entries each, in levels. Each level narrows the search by a factor of a few hundred, so the number of pages read grows with the logarithm of the table's size:
Sargable conditions
An index can serve a condition only on the column as it is stored. WHERE loss_date >= '2025-01-01' can use an index on loss_date; WHERE EXTRACT(YEAR FROM loss_date) = 2025 cannot, because the index is not sorted by year. Rewrite conditions as ranges on the column (such conditions are called sargable), or index the expression itself.
EXPLAIN
SELECT *
FROM claims
WHERE claim_id = 42;EXPLAIN ANALYZE before and after a change, on realistic data.Check your understanding
Answer every question, then check.