CS Fundamentals Trainer · Guides

How a query planner works: plans, estimates and why they go wrong

Reading an execution plan and fixing a bad one: the four access paths and three join algorithms a planner chooses between, where cost numbers come from, how row counts are estimated and why the independence assumption and hidden expressions wreck them, join ordering and the search space, what the executor does with a plan, and an ordered ladder for diagnosis - with every plan, row count and timing produced by a PostgreSQL 16 instance you can reproduce from the appendix.

47 min read

SQL does not say how to answer a question. It says what the answer should contain, and something else decides whether to read a table from front to back, which index to use, which of three join algorithms to run, and in what order to combine five tables. That something is the query planner, and the thing it is actually optimising is not your query: it is an estimate of your data. Almost every mystery in database performance — the query that was fast for a year and fell over on Tuesday, the index that is ignored, the join that takes four minutes instead of four milliseconds — is the gap between that estimate and reality.

This guide is about closing that gap. It covers how to read a plan, the access paths and join algorithms a planner chooses between, where the cost numbers come from, how row counts are estimated and why they go wrong, how the join order is searched, what the executor does with the plan it is given, and an ordered procedure for diagnosing a bad one.

How to read it.

You wantRead
To fix a slow query todaySection 2 (reading a plan), then section 12's ladder, then the section it points at
The model, in half an hourSections 1 to 4, then the Quick recap
To understand why estimates failSections 6 and 7, which are the heart of the guide
The whole pictureIn order. Sections 8 (join ordering) and 10 (execution models) are the deep dives

About the measurements. Every plan, row count and timing quoted here was produced on PostgreSQL 16.15 on the machine that wrote this guide, against a data set the appendix builds: 100,000 customers in 20 cities, 500,000 orders with a deliberately skewed status column, and 1,500,000 order items (52 MB and 96 MB of table plus index). The data was cached, the server used default settings unless a line says otherwise, and the script is in the appendix so you can run it.

What kind of evidence is in this guide.

KindWhereHow far to trust it
Documented behaviourthe PostgreSQL documentation and the papers in Sourcesas far as the source; planner behaviour changes between major versions
Plans and row counts produced hereevery EXPLAIN output belowthey are real, and they are what this version chose for this data; yours will differ
Timings produced herethe millisecond figuresuseful as ratios between two plans on one machine, meaningless as absolute numbers
Arithmeticthe search-space table in section 8it follows from the definitions
A benchmark of enginesnonenothing here compares PostgreSQL's speed with another database's

Most of what follows is PostgreSQL, because it will show you its reasoning for free. The ideas — cost models, cardinality estimation, the three join algorithms, the plan search — are the same in MySQL, SQL Server, Oracle and the analytic engines; section 14 maps the differences.

1. What the planner is for

In short. The planner turns a declarative query into a program, by choosing an access path per table, a join algorithm per join and an order for the joins. It picks the combination with the lowest estimated cost, and the estimate is a model of your data, not a measurement of it.

A planner does three jobs, and every section below is one of them in detail:

JobThe choicesSection
Access path: how to get rows out of one tablesequential scan, index scan, index-only scan, bitmap scan3
Join algorithm: how to combine two inputsnested loop, hash join, merge join4
Join order: which pair to combine firstany order the rewrites allow, which grows factorially8

To choose, it needs two things: a cost model that says what work costs (section 5) and a cardinality estimate that says how many rows each step will produce (section 6). The cost model is the stable part — it is arithmetic over page reads and per-row CPU costs. The cardinality estimate is the fragile part, because it is extrapolated from statistics gathered the last time somebody ran ANALYZE.

That asymmetry is the single most useful thing to know about planners. When a plan is bad, the cost formula is almost never wrong; the row count fed into it is. The classic study of this is Leis and colleagues' "How good are query optimizers, really?", which took the join-ordering machinery of several systems apart and found that estimation errors, not the search or the cost model, are what produce bad plans — and that the errors grow as joins stack up.

A second thing worth internalising early: the planner optimises for the whole result unless you tell it otherwise. LIMIT changes the arithmetic, because a plan that returns the first row quickly may be the cheapest way to get ten rows and the most expensive way to get all of them. Section 5 has that measured.

2. Reading a plan

In short. EXPLAIN shows the plan; EXPLAIN ANALYZE runs it and shows what really happened. The one habit that matters is comparing the estimated row count with the actual one at every node, from the bottom up.

Here is the simplest possible plan, from the data set in the appendix:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE id = 424242;

 Index Scan using orders_pkey on orders  (cost=0.42..8.44 rows=1 width=27)
                                         (actual time=0.026..0.026 rows=1 loops=1)
   Index Cond: (id = 424242)
   Buffers: shared hit=4
 Planning Time: 0.378 ms
 Execution Time: 0.062 ms

Everything in that output is worth being able to name:

What you seeWhat it means
Index Scan using orders_pkeythe node type and, here, the index it chose
cost=0.42..8.44estimated startup cost, then estimated total cost, in arbitrary units (section 5)
rows=1 width=27the planner's estimate of rows produced, and their average size in bytes
actual time=0.026..0.026real milliseconds to the first row, then to the last
rows=1 loops=1rows the node really produced per loop, and how many times it ran
Index Condthe condition the index itself evaluated
Buffers: shared hit=48 kB pages found in cache; read= would mean fetched from the operating system
Planning Time / Execution Timehow long choosing the plan took, and running it

Four habits turn that into a diagnostic tool.

Read it inside out. The deepest, most indented node runs first, and each parent consumes its children's rows. The node where cost or time explodes is the one to understand, and it is usually below the one that looks slow.

Multiply by loops. A node that reports rows=1 loops=400000 produced 400,000 rows in total and ran 400,000 times. Average actual time is also per loop, so a 0.002 ms index lookup repeated 400,000 times is 0.8 seconds of work reported as a very fast node. Section 7 has exactly that plan.

Compare rows= with actual rows. This is the diagnosis. The ratio between estimate and reality, at the lowest node where it goes wrong, tells you whether the problem is statistics, a predicate the planner cannot estimate, or a correlation it does not know about. Everything above a bad estimate is chosen on bad information.

Ask ANALYZE for more. BUFFERS shows cache hits and reads; TIMING OFF removes the per-row clock overhead when you only need row counts; SETTINGS reports any non-default planner parameters; WAL shows write-ahead log volume for a write. And remember that EXPLAIN ANALYZE executes the statement, which for an UPDATE or DELETE means wrapping it in a transaction you roll back.

Three lines that appear in real plans and answer a question before you ask it:

3. Getting rows out of one table

In short. There are four ways in, and the planner chooses by how many rows it thinks it will keep. Counting rows and fetching rows flip at very different points, which is why "it uses the index for count(*) but not for select *" is normal rather than a bug.

Access pathHow it worksWins when
Sequential scanread every page in ordera large fraction of the table qualifies, or the table is small; sequential reads are cheap per page
Index scanwalk the index, fetch each matching heap rowfew rows qualify, or the rows are needed in index order
Index-only scananswer from the index alone, no heap accessthe index contains every column the query needs and the pages are visible
Bitmap heap scancollect matching row locations from one or more indexes, sort them, then read each heap page oncea middling fraction qualifies, or two indexes must be combined

The bitmap scan is the one people forget, and it exists for a precise reason: an index scan visits heap pages in index order, which is random I/O and may visit the same page repeatedly, while a bitmap scan visits each page once in physical order. (Why a B-tree gives you index order in the first place is the subject of storage engines.) That is why the planner's preference moves index scan → bitmap scan → sequential scan as the result set grows.

Here is that progression measured. The same query shape, SELECT * FROM orders WHERE created_at in a widening window, with an index on created_at:

WindowRowsShare of tablePlan chosen
1 day7140.1%Bitmap Heap Scan
7 days5,0041.0%Bitmap Heap Scan
30 days21,4494.3%Bitmap Heap Scan
120 days85,79917.2%Bitmap Heap Scan
240 days171,56034.3%Bitmap Heap Scan
700 days500,000100%Seq Scan

And the same windows, when the query only needs to count rows, so the index alone can answer it:

WindowRowsSharePlan chosen
1 day7140.1%Index Only Scan
180 days128,69925.7%Index Only Scan
360 days257,24051.4%Index Only Scan
700 days500,000100%Parallel Seq Scan

Two lessons. First, an index-only scan stays attractive at half the table, because there is no heap access to pay for — the index is smaller than the table and is read in order. Second, when rows must be fetched, the crossover is far earlier, and the exact point depends on how well the index order matches physical order. PostgreSQL measures that as the correlation statistic per column: for the skewed status column it is 0.67 here, for created_at essentially zero.

Covering indexes, and the heap fetch you avoid

EXPLAIN (ANALYZE) SELECT sum(total_cents) FROM orders WHERE status = 'cancelled';

 Index Only Scan using orders_status_total_idx on orders  (actual rows=9500 loops=1)
   Heap Fetches: 0

Adding total_cents to the index on status turned a scan that had to visit the table into one that did not: Heap Fetches: 0. That is the whole idea of a covering index. The cost is a wider index to maintain on every write, so it pays for queries you run constantly, not for every query you can think of.

One composite index beats two single-column indexes, usually

With separate indexes on status and created_at, a query with both predicates uses one index and filters the rest:

 Bitmap Heap Scan on orders  (actual rows=24276 loops=1)
   Recheck Cond: (status = 'open')
   Heap Blocks: exact=3677
   ->  Bitmap Index Scan on orders_status_idx  (actual rows=90000 loops=1)

Note the shape: the index produced 90,000 candidates, the heap visit cost 3,677 pages, and 24,276 rows survived. With one index on (status, created_at) the same query becomes:

 Index Only Scan using orders_status_created_idx on orders  (actual rows=24276 loops=1)
   Heap Fetches: 0

Measured over five runs each on this machine: 3.24 ms with the composite index against 27.62 ms with the two separate ones, and the composite index touched no table pages at all. PostgreSQL can combine two indexes with a BitmapAnd, and it will when neither index is selective on its own, but a composite index in the right column order is the stronger tool. The order matters: (status, created_at) supports a query on status alone, while (created_at, status) does not support one on status alone.

The companion guide database indexes covers index types and column order in detail; this guide is about why the planner reaches for them or ignores them.

4. Joining two inputs

In short. There are three algorithms, they have completely different cost shapes, and the planner picks by estimated row counts. A nested loop over a few hundred rows is excellent and over a few hundred thousand is a disaster, which is why a wrong estimate here is the most expensive kind.

AlgorithmHow it worksCost shapeChosen when
Nested loopfor each row of the outer input, look up matches in the inner oneouter rows × cost of one inner lookupthe outer side is small and the inner side has an index on the join key
Hash joinbuild a hash table on the smaller input, then probe it with the largerread both inputs once, plus memory for the tableboth sides are large and the join is on equality
Merge joinsort both inputs on the join key, then walk them togethersort cost, or free if both arrive sortedinputs are already ordered, or an ordered result is wanted

The same query shape at two selectivities shows the choice being made correctly. First, 500 disputed orders joined to their customers:

 Nested Loop  (actual time=0.032..2.756 rows=500 loops=1)
   ->  Index Scan using orders_status_idx on orders o  (actual rows=500 loops=1)
         Index Cond: (status = 'disputed')
   ->  Index Scan using customers_pkey on customers c  (actual rows=1 loops=500)
         Index Cond: (id = o.customer_id)
 Execution Time: 3.008 ms

Note loops=500 on the inner node: that is 500 primary-key lookups, each costing about 0.002 ms. Now the same join with status = 'shipped', which is 400,000 rows:

 Hash Join  (actual time=64.504..169.585 rows=133333 loops=3)
   Hash Cond: (o.customer_id = c.id)

Three loops because the plan went parallel with two workers. Averaged over three runs: 190.8 ms. Then the same query with the hash and merge joins switched off, so the planner has to use a nested loop:

 Nested Loop  (actual time=0.101..312.842 rows=133333 loops=3)
   ->  Parallel Seq Scan on orders o  (actual rows=133333 loops=3)
   ->  Index Scan using customers_pkey on customers c  (actual rows=1 loops=202532)
 Execution Time: 356.269 ms

Average over three runs: 397.1 ms, about 2.1 times slower. Two things are worth noticing. The nested loop is not catastrophic here, because the inner table is small and fully cached, so 400,000 index lookups cost 0.002 ms each. And that is exactly the trap: on a table too large to cache, those lookups become random reads, and the same plan is not twice as slow but a hundred times. Section 7 shows that failure happening for real.

work_mem decides whether a hash join stays in memory

A hash join builds its table in work_mem (4 MB by default, per node, per worker — not per query). When the build side does not fit, it is split into batches that are written to temporary files and read back:

work_memBatchesAverage execution time
64 kB64137.7 ms
1 MB1124.1 ms
4 MB1120.9 ms
64 MB1118.1 ms

A 14% penalty for 64 passes looks mild, and it is, for one reason worth being honest about: this data set is small and entirely in cache, so "writing to a temporary file" meant writing to the operating system's page cache. On data that does not fit in memory the same spill is the difference between a query and an incident. What transfers from this table is the diagnosis, not the number: Batches: 1 means the hash fit, anything larger means it did not, and Sort Method: external merge Disk: … is the same signal for a sort.

The companion risk is the opposite one: work_mem is per node per worker, so a plan with three hash joins and two workers can use nine times the setting. That is why raising it globally to a comfortable-sounding number is how servers run out of memory.

5. Where the cost numbers come from

In short. A cost is an arithmetic estimate in units of one sequential page read. The constants are settings you can inspect, two of them matter, and the most common mistake is reading a cost as milliseconds.

These are the defaults this guide ran with, straight out of pg_settings:

SettingValueWhat it prices
seq_page_cost1.0reading one 8 kB page sequentially — the unit everything else is measured in
random_page_cost4.0reading one page at a random offset
cpu_tuple_cost0.01processing one row
cpu_index_tuple_cost0.005processing one index entry
cpu_operator_cost0.0025one operator or function evaluation
effective_cache_size4 GBhow much cache the planner assumes exists, for index-scan arithmetic
work_mem4 MBmemory per sort or hash node, per worker
default_statistics_target100histogram buckets and most-common values per column

So a plan showing cost=0.42..8.44 is claiming the node costs about as much as reading eight sequential pages. It is not a prediction of milliseconds, it cannot be compared between servers, and two plans' costs are only comparable within one planning run.

Two constants deserve attention in production. random_page_cost = 4.0 encodes a spinning disk, where a random read cost roughly four times a sequential one. On SSDs and NVMe the real ratio is much closer to 1, and the usual advice is to lower it (1.1 to 2 is common), which makes the planner more willing to use indexes. effective_cache_size is not an allocation — nothing is reserved — it only tells the planner how likely repeated index lookups are to find pages in cache; setting it to roughly half to three quarters of system memory is the normal starting point.

Startup cost, total cost, and why LIMIT changes everything

Every node reports two costs: what it takes to produce the first row, and what it takes to produce all of them. The planner compares total costs, unless a LIMIT tells it that only the first few rows matter, at which point startup cost dominates. That flips plans:

SELECT * FROM orders WHERE status = 'open' ORDER BY created_at LIMIT 10;

 Limit  (actual time=4.139..4.144 rows=10 loops=1)
   ->  Index Scan using orders_created_idx on orders  (actual rows=10 loops=1)
         (cost=0.42..25158.01 rows=90667)
 Execution Time: ~4.1 ms

The index scan's total cost is 25,158 — enormous — but its startup cost is 0.42, and with a LIMIT 10 the plan stops after ten rows. Remove the LIMIT and the same query becomes:

 Sort  (actual time=36.757..44.479 rows=90000 loops=1)
   Sort Key: created_at
   Sort Method: external merge  Disk: 3712kB
   ->  Bitmap Heap Scan on orders  (actual rows=90000 loops=1)
 Execution Time: ~44 ms

Both plans are correct choices for the question that was asked. This is also the mechanism behind a familiar production surprise: a query with LIMIT picks the index-ordered plan and is instant while the matching rows are near the start of the index, and becomes very slow when they are not, because it has to walk further and further to find ten of them.

6. Estimating how many rows there will be

In short. The planner estimates selectivity from per-column statistics: a most-common-value list, a histogram, a distinct count. Within one column the estimates are good. Across two columns it assumes independence, and that assumption is where plans die.

This is what ANALYZE left behind for the skewed status column:

attname           | status
n_distinct        | 4
most_common_vals  | {shipped,open,cancelled,disputed}
most_common_freqs | {0.7980667,0.18133333,0.019433333,0.0011666666}
null_frac         | 0
correlation       | 0.668676

Four distinct values, each with a measured frequency, so an estimate for status = 'disputed' is 0.00117 × 500,000 = 583. The plan in section 3 estimated 467 and found 500. For a column with many values the list holds the 100 most common (that is default_statistics_target) and a histogram covers the rest; for customer_id the output above shows n_distinct = -0.199, where a negative number means "distinct values are 19.9% of the row count" — a ratio rather than an absolute, so it stays correct as the table grows.

Single-column estimates from this are usually within a few per cent. The problem is what happens with two.

The independence assumption

In the data set, every city belongs to exactly one state: Recife is always in PE. A human reads city = 'Recife' AND state = 'PE' and sees one condition. The planner multiplies two selectivities:

-- city = 'Recife' alone
Index Only Scan ... (cost=0.29..109.49 rows=5097) (actual rows=5000)

-- city = 'Recife' AND state = 'PE'
Bitmap Heap Scan on customers (cost=58.78..811.23 rows=1027) (actual rows=5000)

The first estimate is excellent: 5,097 against 5,000. The second is out by a factor of five — 1,027 against 5,000 — purely because 5,097/100,000 was multiplied by the selectivity of state = 'PE', which is 20%. Nothing is wrong with the statistics; the model is.

PostgreSQL 10 and later can be told about the relationship:

CREATE STATISTICS customers_city_state (dependencies, ndistinct)
  ON city, state FROM customers;
ANALYZE customers;
Bitmap Heap Scan on customers (cost=59.66..811.86 rows=5080) (actual rows=5000)

The estimate is now 5,080 against 5,000. The object itself records what it learned, readable in pg_stats_ext:

dependencies | {"2 => 3": 1.000000, "3 => 2": 0.049700}

Column 2 (city) determines column 3 (state) with coefficient 1.0 — perfectly — while the reverse holds only 5% of the time. This is one of the highest-value, least-used features in PostgreSQL: a one-line fix for a whole family of bad plans, on any pair of columns a reader would call "obviously related" (city and state, country and currency, model and manufacturer, status and status reason).

Errors compound upwards

A single 5× error at the bottom of a plan is survivable. The reason bad plans are usually deep plans is that errors multiply. Here is a three-table join where both filters are mildly misestimated:

 Nested Loop        (cost=... rows=22440) (actual rows=40000 loops=3)
   ->  Hash Join    (cost=... rows=7480)  (actual rows=13333 loops=3)
         ->  Parallel Bitmap Heap Scan on orders o  (rows=37778) (actual rows=30000 loops=3)
               Recheck Cond: (status = 'open')
         ->  Seq Scan on customers c  (rows=19800) (actual rows=20000 loops=3)
               Filter: (state = 'PE')
               Rows Removed by Filter: 80000

The two scans are close. The join above them is out by 1.8×, and the join above that inherits the error. In this plan nothing breaks, because the absolute numbers stay small. With five more joins the same drift is what turns an estimated 1,000 rows into an actual 10,000,000, and by then the planner has chosen a nested loop.

7. The estimates that go badly wrong

In short. Three situations make a planner guess instead of estimate: a predicate it cannot see through, a parameter it does not know yet, and a value outside the statistics it has. The first is the one you can fix in a minute, and it is the one that produces the worst plans.

A predicate hidden behind a function

Wrapping a column in a function throws the statistics away. The planner has frequencies for status, but not for upper(status), so it falls back to a hard-coded guess:

-- the planner knows this column
SELECT count(*) FROM orders o JOIN customers c ON c.id = o.customer_id
 WHERE o.status = 'shipped';

 Parallel Hash Join  (rows=166264) (actual rows=133333 loops=3)

-- the same rows, behind a function call
 WHERE upper(o.status) = 'SHIPPED';

 Nested Loop  (cost=0.29..7585.17 rows=1042) (actual time=0.084..376.922 rows=133333 loops=3)
   ->  Parallel Seq Scan on orders o  (rows=1042) (actual rows=133333 loops=3)
   ->  Index Only Scan using customers_pkey on customers c  (actual rows=1 loops=400000)
 Execution Time: 395.759 ms

This is the whole failure mode in one plan. The estimate is 1,042 rows against 400,000 actual, out by 384×. On that estimate a nested loop looks cheap, so the planner chose one, and the inner node ran 400,000 times (loops=400000). The query that took 190 ms as a hash join took 396 ms here — and only because the inner table is tiny and cached. This is the plan that becomes a four-minute query on production-sized data.

The fix is to give the planner statistics for the expression, which an index on the expression does as a side effect:

CREATE INDEX orders_upper_status_idx ON orders (upper(status));
ANALYZE orders;
 Parallel Hash Join  (rows=167104) (actual rows=133333 loops=3)
 Execution Time: 207.386 ms

The estimate is now 167,104 against 133,333 per worker, the plan is a hash join again, and the time is back in line. The same expression index also helps the selective case directly:

 WHERE upper(o.status) = 'DISPUTED'
 Index Scan using orders_upper_status_idx on orders o  (rows=417) (actual rows=500 loops=1)
 Execution Time: 4.000 ms

In PostgreSQL 14 and later you can get the statistics without the index, with CREATE STATISTICS … ON (upper(status)) FROM orders. Either way, the lesson is the rule every DBA repeats: keep columns bare on the left-hand side of a predicate. WHERE created_at >= '2025-06-01' is estimable; WHERE date_trunc('month', created_at) = '2025-06-01' is not, unless you have indexed or analysed that expression.

A value the statistics do not cover

Two near-relatives of the same problem:

Parameters: the generic plan

A prepared statement is planned before the values arrive. PostgreSQL plans the first five executions with the actual parameters (a custom plan), compares their cost with a generic plan built from average selectivities, and then, if the generic plan is not more expensive, keeps using it. That is the right trade in most cases — it saves planning time — and it is wrong in exactly one: when one parameter value is rare and another is 80% of the table, as status is here. A generic plan built for the average is then badly wrong for both.

Symptoms: the same statement is fast from psql and slow from the application, or fast for most inputs and slow for one tenant. EXPLAIN (ANALYZE) on the literal query will not reproduce it, because that is the custom plan. The controls are plan_cache_mode = force_custom_plan (per session, or per function), splitting the statement so each shape is planned separately, or not preparing it at all.

8. Choosing the join order

In short. The number of possible orders explodes factorially, so no planner examines them all. PostgreSQL uses Selinger-style dynamic programming up to a limit and a genetic search beyond it, and both limits are settings you can see and move.

Three tables can be joined in a handful of ways; twelve cannot be enumerated at all. The arithmetic:

TablesLeft-deep orders (n!)All tree shapesDynamic-programming sub-problems (2ⁿ)
2224
36128
42412016
51201,68032
840,32017,297,280256
103,628,80017,643,225,6001,024
12479,001,60028,158,588,057,6004,096

The third column is the insight from the 1979 System R paper by Selinger and colleagues, which is still the shape of every relational planner: instead of enumerating orders, work up through subsets of tables, keep only the cheapest plan for each subset (plus the cheapest plan that happens to produce a useful sort order — their "interesting orders"), and build larger subsets from smaller ones. That turns a factorial problem into 2ⁿ, which is tractable to about a dozen tables.

What that costs in practice, measured by planning a chain join over an increasing number of 1,000-row tables:

Tables in the joinPlanning time
20.5 ms
41.1 ms
63.6 ms
819.5 ms
10230.1 ms
12103.9 ms

Planning time grows steeply — and then falls at twelve tables. That is not noise: geqo_threshold is 12 by default, and at twelve relations PostgreSQL stops doing the exhaustive search and runs a genetic one instead, trading plan quality for planning time. Turning that off makes the cost visible:

12-table joinPlanning time (three runs)
geqo = on (default)69 ms, 71 ms, 88 ms
geqo = off1,647 ms, 1,252 ms, 1,350 ms

A second pair of limits comes first, though. join_collapse_limit and from_collapse_limit (both 8) control how many tables the planner will flatten into one join problem. Below the limit, explicit JOIN syntax is treated as a suggestion and the planner reorders freely; above it, it stops merging and plans the pieces separately in the order written. You can see the reordering by setting the limit to 1:

-- default: the planner joins orders to customers first, then items
 Aggregate
   ->  Nested Loop
         ->  Hash Join
               Hash Cond: (o.customer_id = c.id)
               ->  Index Scan using orders_status_idx on orders o
               ->  Hash
                     ->  Bitmap Heap Scan on customers c
         ->  Index Only Scan using items_order_idx on items i

-- join_collapse_limit = 1: the written order is kept
 Aggregate
   ->  Hash Join
         Hash Cond: (o.customer_id = c.id)
         ->  Nested Loop
               ->  Index Scan using orders_status_idx on orders o
               ->  Index Only Scan using items_order_idx on items i
         ->  Hash
               ->  Bitmap Heap Scan on customers c

Both plans answer the same query. The first one filters orders and customers before touching the 1.5-million-row items table, which is what you would have chosen by hand.

Three practical consequences:

9. What happens before costing: rewrites

In short. Before any plan is costed, the query is rewritten into an equivalent, usually simpler form. Knowing which rewrites happen tells you which hand-optimisations are pointless and which SQL shapes genuinely block the planner.

The rewrites you can count on in a modern planner:

RewriteWhat it doesWhy it matters to you
View and inline-function expansionthe view's body replaces its namepredicates from outside can then be pushed inside the view
Predicate pushdowna filter moves as close to the scan as possiblea WHERE on a subquery result can become an index condition
Subquery flatteningIN (SELECT …) becomes a semi-join; NOT EXISTS becomes an anti-jointhe join algorithms and orders of section 8 become available; IN and EXISTS usually plan identically
Constant foldingWHERE total_cents > 100 * 5 becomes > 500expressions on constants cost nothing at run time
Join strength reductionan outer join whose null rows are filtered away becomes an inner joinmore join orders become legal
CTE inlininga non-recursive CTE used once is treated like a subquery (PostgreSQL 12 and later)the famous "CTEs are optimisation fences" advice is obsolete

That last one is worth seeing, because the old behaviour shaped a generation of advice:

WITH recent AS (SELECT * FROM orders WHERE created_at >= '2025-06-01')
SELECT count(*) FROM recent WHERE status = 'open';

 Aggregate
   ->  Bitmap Heap Scan on orders
         Recheck Cond: (status = 'open')
         Filter: (created_at >= '2025-06-01')
         ->  Bitmap Index Scan on orders_status_idx

The CTE vanished: both predicates were applied to one scan, and the planner chose the index on status rather than the one the CTE's body suggested. Adding MATERIALIZED brings the fence back:

WITH recent AS MATERIALIZED (SELECT * FROM orders WHERE created_at >= '2025-06-01')
SELECT count(*) FROM recent WHERE status = 'open';

 Aggregate
   CTE recent
     ->  Bitmap Heap Scan on orders
           Recheck Cond: (created_at >= '2025-06-01')

Both are legitimate. Inlining is usually faster; MATERIALIZED is the deliberate choice when a CTE is expensive and used several times, or when you want to stop the planner pushing a predicate into something it should not — a hand-written fence, with the cost that the intermediate result is built in full.

10. What the executor does with the plan

In short. A plan is a tree of operators that pull rows from each other one at a time. That model is simple and general, and it costs per row, which is why analytic engines batch, why PostgreSQL compiles expressions for expensive queries, and why parallel workers help scans more than they help small lookups.

The iterator model. Every node in a PostgreSQL plan implements the same interface: open, produce the next row, close. A Sort asks its child for rows until there are none, sorts them, then hands them up one at a time. Graefe's Volcano work is where this design is laid out, and its virtue is that any operator composes with any other, which is how one engine supports arbitrary SQL. Its cost is per-row overhead: a few function calls and a tuple deformation for every row at every level, which barely matters for a 10-row lookup and matters a great deal for a 500-million-row aggregate.

Batched and vectorised execution is the answer analytic systems chose: pass a block of a few thousand values between operators instead of a row, keep columns contiguous so loops are tight and predictable, and let the CPU work on many values per branch. DuckDB, ClickHouse, Snowflake and the column-store engines are built this way; PostgreSQL is not, which is one honest reason a 100-million-row aggregate is faster in DuckDB than in PostgreSQL and a single-row primary-key lookup is not.

Compilation is the other answer: generate machine code for the expressions in this particular query instead of interpreting a tree. PostgreSQL does this with LLVM for expensive queries, and the defaults matter more than the feature. Forcing it on for a query that does not need it:

SET jit = on; SET jit_above_cost = 0; SET jit_optimize_above_cost = 0; SET jit_inline_above_cost = 0;
SELECT sum(total_cents), avg(total_cents), max(created_at) FROM orders WHERE total_cents > 1000;

 JIT:
   Functions: 17
   Timing: Generation 3.1 ms, Inlining 618.5 ms, Optimization 285.3 ms, Emission 288.5 ms, Total 1195.5 ms
 Execution Time: 2338.5 ms

SET jit = off;   -- the same query
 Execution Time: 66.6 ms

Compiling 17 functions took 1.2 seconds to speed up a query that runs in 67 milliseconds, and the result was 35 times slower. That is why jit_above_cost defaults to 100,000: compilation pays only when execution dominates. If you see JIT timings in a plan whose execution is short, that is a misconfiguration, not an optimisation — and on a workload of many small queries, jit = off is a defensible default.

Parallel query appears throughout this guide's plans as loops=3 with Workers Planned: 2: the leader and two workers each scan part of the table, and a Gather node merges their output. Parallelism helps sequential scans, hash joins and aggregates over many rows; it does nothing for a plan whose cost is one index lookup, and max_parallel_workers_per_gather (2 by default) caps it. Note that each worker gets its own work_mem, which is the memory-sizing trap from section 4.

11. Statistics in practice

In short. Everything in sections 6 and 7 depends on statistics being fresh and detailed enough. Three habits cover almost all of it: analyse after bulk changes, raise the target on the columns that matter, and create extended statistics for correlated pairs.

What to doWhenWhy
ANALYZE tableafter a bulk load, a large delete, or a new partitionautovacuum's default threshold is 10% of the table plus 50 rows, so a load that lands at 9% is invisible, and the histogram's upper bound is stale precisely for "the newest rows" queries
ALTER TABLE … ALTER COLUMN … SET STATISTICS 1000on skewed join keys and filter columns with a long tailthe default 100 most-common values cannot describe skew spread over thousands of values
CREATE STATISTICS … (dependencies, ndistinct)on column pairs a human would call relatedfixes the independence assumption, which is the most common cause of a 10× misestimate (section 6)
CREATE STATISTICS … ON (expression)when a predicate must wrap a column in a functiongives the expression its own statistics without an index (PostgreSQL 14 and later)
VACUUMregularly, via autovacuumdead rows inflate the table, and the visibility map is what makes index-only scans possible: Heap Fetches: 0 depends on it (MVCC and isolation)

Two numbers to keep in mind. default_statistics_target = 100 means 100 most-common values and 100 histogram buckets per column, sampled from roughly 30,000 rows — that sample size is why statistics on a billion-row table are approximate by design. And autovacuum's analyse threshold is proportional, so the bigger the table, the longer it takes to notice that a tenth of it changed.

A fast way to see whether statistics are your problem at all:

SELECT relname, n_live_tup, n_mod_since_analyze, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_mod_since_analyze DESC
LIMIT 10;

If n_mod_since_analyze is a large fraction of n_live_tup for the table in your slow query, stop reading plans and run ANALYZE first.

12. A ladder for diagnosing a bad plan

In short. Work from the cheapest, most reversible check to the most invasive. Nine times out of ten you stop in the first four rungs, and the one thing that tells you where to stop is the estimate-against-actual ratio.

RungCheckHowIf it is this
1Is this query really the problem?pg_stat_statements for total time, not just mean; is the time in planning, execution or waiting on a lock?fix the right thing; a lock wait is not a planner problem
2What is the real plan?EXPLAIN (ANALYZE, BUFFERS); for a prepared statement use auto_explain or prepare it yourself, because the literal query gives you a different planyou now have estimates and actuals side by side
3Where does the estimate first go wrong?read bottom-up, find the deepest node where rows= and actual rows differ by more than about 10×, remembering to multiply by loopseverything above that node was chosen on bad information
4Are the statistics stale?n_mod_since_analyze against n_live_tup in pg_stat_user_tablesANALYZE the table and re-check. Free, instant, no risk
5Two predicates on related columns?the misestimated node filters on two columns of one tableCREATE STATISTICS … (dependencies, ndistinct) and ANALYZE (section 6)
6Is a column wrapped in a function?look at the Filter and Index Cond lines for upper(…), date_trunc(…), a cast, a concatenationrewrite to keep the column bare, or index the expression (section 7)
7Is the index wrong rather than missing?does an index cover the predicate columns in a usable order; is the query fetching columns that force heap accessa composite or covering index (section 3); measure, do not add on faith
8Is it a parameter-sensitive statement?fast with literals, slow from the application, or slow for one inputplan_cache_mode = force_custom_plan, or split the statement (section 7)
9Is it memory?Batches: 64, Sort Method: external merge Disk: …raise work_mem for that session or that query, not globally (section 4)
10Is the planner out of room?many tables in one statement, large Planning Timebreak the query up, or pin the order with join_collapse_limit = 1 (section 8)
11Force itSET enable_nestloop = off and friends, or pg_hint_planlast resort: record why, and revisit after every major upgrade

Rungs 1 to 6 are free and reversible. Rung 7 adds write cost for every insert. Rungs 10 and 11 pin behaviour that the planner would otherwise improve on its own, and they age badly: a forced plan from 2019 is a liability on a 2026 server with different data.

The enable_* switches deserve a note of their own. They are diagnostic tools, not configuration: turning enable_hashjoin off in a session is how you ask "what would the alternative have cost?", which is exactly how the 190 ms against 397 ms comparison in section 4 was produced. Leaving one off in postgresql.conf removes an algorithm from every query on the server.

13. What you can control, and what you cannot

In short. In PostgreSQL you steer the planner by giving it better information, better indexes or a different query; you cannot give it a hint in core. That is a deliberate design position, and there is a widely used extension for people who disagree.

LeverWhat it doesWhen to reach for it
Statistics (ANALYZE, SET STATISTICS, CREATE STATISTICS)changes the estimate, so the planner chooses differently on its ownalways first: the fix survives upgrades and data growth
Indexes, including expression and covering indexeschanges what access paths existwhen the access path, not the estimate, is the problem
Rewriting the queryremoves an unestimatable predicate, a needless DISTINCT, a correlated subquery that cannot be flattenedwhen the SQL shape is blocking a rewrite from section 9
Optimisation fences (MATERIALIZED, OFFSET 0)stops the planner merging a subquery into its parentwhen you have proven that merging is the problem
Planner parameters (work_mem, random_page_cost, effective_cache_size, jit)changes the arithmetic for every query, or for one sessionrandom_page_cost and effective_cache_size once per server, on modern storage; the rest per session
join_collapse_limit, enable_*pins a join order or removes an algorithmdiagnosis, and as a last-resort fix in one function
pg_hint_planadds Oracle-style hints in a comment: /*+ HashJoin(a b) IndexScan(a idx) */when you need a plan pinned and cannot change the query; an extension, not core

PostgreSQL's refusal to put hints in core is worth understanding rather than just complaining about: a hint written today is applied forever, including after the data distribution changes, the statistics improve and the planner gets a better algorithm. The project's position is that the planner should be fixed or informed instead. The counter-argument — that a production outage at 02:00 is a bad time to fix a planner — is why pg_hint_plan exists and why every commercial engine has hints and plan management.

Tools worth having before you need them: pg_stat_statements (which queries cost the most in aggregate), auto_explain with log_min_duration and log_analyze (the real plan of the slow execution, including the generic plan a prepared statement actually used), EXPLAIN (FORMAT JSON) for machine processing, and a plan visualiser such as pev2 or explain.depesz.com for plans too large to read as text.

14. The same ideas in other engines

In short. Every mainstream relational engine is cost-based with per-column statistics and the same three join algorithms. What differs is how much control you are given and how much the system corrects itself at run time.

StatisticsHints and plan controlNotable
PostgreSQLper-column MCV plus histogram; extended statistics for correlation and expressionsnone in core; pg_hint_plan extension; enable_* switchesshows its reasoning freely; custom against generic plans for prepared statements
MySQL 8 / InnoDBindex statistics plus optional histograms; cost model constants in tablesoptimizer hints in /*+ … */, index hints (FORCE INDEX), optimizer_switchgained hash join and EXPLAIN ANALYZE in 8.0.18
SQL Serverauto-created and auto-updated statistics, multi-columnquery hints, plan guides, Query Store with forced plansadaptive joins and interleaved execution; parameter-sensitive plan optimisation in 2022
Oraclehistograms, extended statistics, dynamic samplinga large hint vocabulary, SQL Plan Management baselinesadaptive plans and statistics feedback: it can correct a plan on re-execution
DuckDB, ClickHouselightweight statistics; columnar layoutlittle to tunevectorised execution and column pruning do most of the work (section 10)
BigQuery, Snowflakemaintained by the serviceessentially nonedynamic re-optimisation and partition or micro-partition pruning instead of indexes

Which of these a workload belongs on is a different question, and one with its own guide: choosing a database.

Two generalisations hold across all of them. Estimation error, not search, is the usual cause of a bad plan — the Leis study tested that across systems. And the deeper the plan, the worse the estimates get, which is why every engine is adding some form of runtime correction: adaptive joins, statistics feedback, re-optimisation between stages.

15. Common mistakes

16. Things to mention in an interview

Quick recap

Appendix: the data set and the experiments

Everything measured in this guide came from one PostgreSQL 16.15 instance with default settings except where a line says otherwise, and from a data set built to have three properties a planner finds hard: a skewed column, a correlated pair of columns, and a column whose selectivity can be varied smoothly.

-- 100,000 customers across 20 cities; each city belongs to exactly one state,
-- so city and state are perfectly correlated (section 6).
CREATE TABLE customers (id int PRIMARY KEY, city text, state text, segment text);

-- 500,000 orders with a deliberately skewed status column:
--   shipped 80%, open 18%, cancelled 1.9%, disputed 0.1%   (section 3)
CREATE TABLE orders (id int PRIMARY KEY, customer_id int REFERENCES customers(id),
                     status text, total_cents int, created_at timestamptz);

-- 1,500,000 items, three per order, to give the join-order section something large
CREATE TABLE items (order_id int REFERENCES orders(id), sku text, qty int);

The experiments, in the order the guide quotes them:

  1. EXPLAIN (ANALYZE, BUFFERS) of a primary-key lookup, for the plan anatomy.
  2. The same column at four selectivities, estimate against actual.
  3. A widening date range, counting rows and then fetching them, to find where each access path wins.
  4. city = 'Recife' AND state = 'PE' before and after CREATE STATISTICS.
  5. The same join at two selectivities, for the nested loop and the hash join.
  6. The same query with enable_hashjoin = off, to price the wrong algorithm.
  7. The same join at four work_mem settings, for Batches.
  8. A covering index, for Heap Fetches: 0.
  9. One query with and without LIMIT 10.
  10. A three-table join with and without join_collapse_limit = 1.
  11. A three-table join with ANALYZE, to watch the estimate drift upwards.
  12. pg_stats for three columns of orders.
  13. A CTE, inlined and MATERIALIZED.
  14. An aggregate with JIT forced on and off.
  15. A chain join over 2 to 12 tables, for planning time, and the same 12-table join with geqo off.

Each one is a single EXPLAIN, so the way to use this appendix is to re-run it on your own data: the plans will differ, and the differences are the interesting part.

Sources

Practise this topic: Databases

More guides