Query optimisation

The database tells you exactly what it is going to do with your query, in a format almost nobody reads. EXPLAIN is the plan: which tables are scanned how, in what order they are joined, and how many rows the planner expects at each step. EXPLAIN ANALYZE runs it and adds what actually happened. Every slow-query investigation starts here, and most end with one of five fixes. This lesson is how to read a plan, what the operators mean, and the story of a ten-line query that read eleven million rows.

#Reading a plan

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.name, COUNT(*) AS n
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.placed_at >= '2026-09-01'
GROUP BY c.name;

HashAggregate  (cost=4812.30..4815.30 rows=300 width=40) (actual time=41.2..41.3 rows=287 loops=1)
  Group Key: c.name
  ->  Hash Join  (cost=32.50..4790.10 rows=4440 width=32) (actual time=0.6..38.9 rows=4412 loops=1)
        Hash Cond: (o.customer_id = c.id)
        ->  Index Scan using idx_orders_placed on orders o  (cost=0.43..4696.20 rows=4440 width=8) (actual time=0.03..35.1 rows=4412 loops=1)
              Index Cond: (placed_at >= '2026-09-01')
              Buffers: shared hit=4380
        ->  Hash  (cost=20.00..20.00 rows=1000 width=32) (actual time=0.5..0.5 rows=1000 loops=1)
              ->  Seq Scan on customers c  (cost=0.00..20.00 rows=1000 width=32)

Read it inside out and bottom up: the most indented nodes run first and feed their parents. Each node shows the planner's estimate (rows=4440) and, with ANALYZE, the truth (rows=4412). The number to compare is those two. A plan whose estimates match reality is a plan the planner chose well; a node estimating 10 rows that produced 2 million is where the plan went wrong, and everything above it was chosen on a false premise.

cost is in arbitrary units (roughly, sequential page reads), useful only relative to other nodes. actual time is milliseconds, and loops multiplies it: a node with actual time=0.05 loops=50000 took 2.5 seconds, not 0.05 ms. Buffers: shared hit versus read says whether the data came from cache or disk.

MySQL's equivalent is EXPLAIN FORMAT=JSON or EXPLAIN FORMAT=TREE, and EXPLAIN ANALYZE since 8.0.18 gives the same actual-versus-estimated view. The classic tabular EXPLAIN shows type (ALL is a full scan, ref and range use an index, const is a primary-key lookup), rows, and Extra, where Using filesort and Using temporary are the words to notice.

#Scan types

A seq scan is not a bug. On a 2,000-row table it is the fastest possible plan, and the planner knows it. It is a bug when it appears on a large table under a selective predicate, and then the reason is one of the four from the Indexes lesson: not a prefix, a function on the column, poor selectivity, stale statistics.

#Join algorithms

The planner has three ways to join, and it picks per join, per query:

• Nested loop. For each row of the outer input, look up matches in the inner. Cheap when the outer is small and the inner has an index on the join key; catastrophic when the outer is large and the inner is a scan, because that is rows × rows. loops= on the inner node tells you how many times it ran.
• Hash join. Build a hash table from the smaller input, probe it with the larger. Needs an equality join condition and memory (work_mem in PostgreSQL; a hash that spills to disk shows Batches: 2 or more). The default for two large sets. MySQL added hash joins in 8.0.18; before that everything was a nested loop, which is why old MySQL advice is so index-obsessed.
• Merge join. Both inputs sorted on the join key, walked together. Right when the inputs are already sorted, by an index or a previous step.

A nested loop chosen because the planner estimated ten outer rows and got a million is the single most common cause of a query that ran in 20 ms in staging and 20 minutes in production.

#Statistics and estimates

The planner does not look at the data; it looks at statistics: row counts, the number of distinct values per column, the most common values and their frequencies, a histogram of the rest. PostgreSQL collects them with ANALYZE, which autovacuum runs after enough changes; MySQL with ANALYZE TABLE and persistent InnoDB stats. When they are stale, after a bulk load, a big delete, or a table that changed character, the estimates are wrong and the plans follow.

Two more reasons estimates go wrong even with fresh statistics. Correlated columns: the planner assumes city = 'Pune' AND country = 'IN' is as selective as the product of the two, which undercounts badly; PostgreSQL's CREATE STATISTICS on the column pair fixes exactly this. Skew: a column where one value is 90% of rows gets a histogram that handles the common values but can mis-estimate the tail; raising default_statistics_target for that column helps.

#The ten-line query that read eleven million rows

SELECT o.id, o.total
FROM orders o
JOIN customers c ON LOWER(c.email) = LOWER(:email)
WHERE o.customer_id = c.id
  AND DATE(o.placed_at) = CURRENT_DATE;

The plan: a seq scan of customers (LOWER defeats the email index), a seq scan of orders (DATE defeats the placed_at index), a hash join of the two, eleven million rows read to return four. Both fixes are from the Indexes lesson:

SELECT o.id, o.total
FROM orders o
JOIN customers c ON c.email = :email                 -- store emails lower-cased; or an index on LOWER(email)
WHERE o.customer_id = c.id
  AND o.placed_at >= CURRENT_DATE AND o.placed_at < CURRENT_DATE + 1;

Index scan on customers(email), one row; nested loop into orders(customer_id, placed_at), four rows; forty pages read instead of eleven million. Nothing about the schema changed. The query was correct the whole time.

#The common fixes

1. The missing index, usually composite, in the order the Indexes lesson gives. Confirm with EXPLAIN before and after.
2. A function or cast on an indexed column in the predicate. Rewrite the predicate as a range, or add an expression index.
3. OFFSET pagination. OFFSET 100000 LIMIT 20 reads and discards 100,000 rows. Keyset pagination, WHERE (placed_at, id) < (:last_placed_at, :last_id) ORDER BY placed_at DESC, id DESC LIMIT 20, reads twenty.
4. SELECT * that pulls wide columns (JSON, text) the caller never uses, and prevents an index-only scan.
5. The N+1 from an ORM: a hundred queries of one row each, every one fast, the page slow. Not visible in any single EXPLAIN; visible in the query log as the same statement repeated. Fetch join or a batch IN (...).

And the one that is not a query fix: a plan that is right but slow because the working set no longer fits in memory. Buffers: read climbing where it used to say hit is the signal, and the fix is memory or a smaller working set, not SQL.

#Under the hood: what the planner does before you see the plan

Between parsing and execution there are four stages, and each is somewhere a plan can go wrong. The rewriter applies view definitions and rules, so a query against a view is planned as the view's query joined with yours. The planner first simplifies: constants are folded, subqueries flattened, IN lists turned into semi-joins, outer joins reduced to inner ones when a WHERE on the null side proves they can be. It then enumerates paths: for each base table, every access method (sequential scan, each usable index, a bitmap combination) is costed; for each pair, then triple, of tables, every join method and order is costed using a dynamic-programming search, which is exponential in the number of tables and is why PostgreSQL switches to a genetic search (geqo_threshold, default 12 tables) and why MySQL's optimizer_search_depth exists. The cheapest complete path becomes the plan. Nothing here reads a data page: every number comes from pg_class.reltuples, pg_stats histograms and most-common-value lists, and a handful of cost constants.

The cost model is a formula with five knobs: seq_page_cost (1.0), random_page_cost (4.0, the one to lower on SSD), cpu_tuple_cost, cpu_index_tuple_cost and cpu_operator_cost, plus effective_cache_size, which tells the planner how much of an index it can assume is cached. A plan's cost= is those knobs times estimated pages and rows. The estimate that matters most is the row count at each node, because join method and order are chosen from it: a nested loop is planned when the outer is small, and a hash join's build side is the input estimated to be smaller. Estimates compound. A 10× error at a base table becomes a 100× error after a join, which is how a plan that is "slightly wrong" at the bottom is catastrophically wrong at the top.

Once chosen, the plan is executed by a tree of nodes pulling rows from their children on demand (the volcano model), which is why EXPLAIN ANALYZE reports actual time as first-row and last-row and why a LIMIT above an index scan can finish before reading much. Prepared statements add one more stage: after five executions with different parameters, PostgreSQL compares a generic plan (planned without knowing the values) with the custom ones, and switches to the generic plan if it is not much worse on average. A parameter value with wildly different selectivity than the others then runs a plan built for the average, which is the "fast in psql, slow from the application" mystery; plan_cache_mode = force_custom_plan or SET LOCAL per query is the fix.

#Walkthrough: fast in staging, twenty minutes in production

A nightly reconciliation joined three tables and finished in seconds on staging and did not finish in production.

SELECT p.id, p.amount, o.total
FROM payments p
JOIN orders o ON o.id = p.order_id
JOIN refunds r ON r.payment_id = p.id
WHERE p.captured_at >= :from AND p.captured_at < :to AND r.status = 'PENDING';

1. Staging had 2 million payments and a plan of hash joins. Production had 900 million and the plan was Nested Loop → Nested Loop → Seq Scan on refunds: the planner estimated r.status = 'PENDING' at 12 rows (the most-common-values list said pending was 0.001% of refunds) and chose to drive the loops from refunds with an unindexed probe into payments per row.
2. The estimate was stale. A batch job had failed for a week and left 4 million refunds pending; autovacuum's analyze threshold on a 400-million-row table (10% by default) had not been crossed, so pg_stats still described last month. EXPLAIN alone looked fine; EXPLAIN ANALYZE on production showed rows=12 against actual rows=4,100,000 loops=1, and the inner seq scan with loops=4100000.
3. The immediate fix was ANALYZE refunds; the plan flipped to hash joins and the job took ninety seconds. The lasting fix was ALTER TABLE refunds SET (autovacuum_analyze_scale_factor = 0.01), so a 1% change triggers a re-analyze on a table that size, and an ANALYZE step at the end of the batch job that changes it.
4. A second issue surfaced in the same plan: p.captured_at between :from and :to was a prepared statement on its sixth execution, running the generic plan, which assumed a "typical" range and mis-sized the hash. SET LOCAL plan_cache_mode = force_custom_plan in that job's transaction made each night's plan match its window.
5. The alert added: pg_stat_user_tables.n_mod_since_analyze above a threshold for any table over 100 million rows, which catches stale statistics before a plan does.

The query did not change. The data's shape changed and the statistics did not follow, and the planner did exactly the right thing for a world that no longer existed.

#Try it yourself

Nested Loop  (rows=25) (actual rows=1,800,000 loops=1)
  ->  Index Scan on customers c  (rows=25) (actual rows=25 loops=1)
        Index Cond: (segment_id = 4)
  ->  Index Scan on orders o  (rows=1) (actual rows=72,000 loops=25)
        Index Cond: (customer_id = c.id)

#Misconceptions

• "The planner reads the data to decide." It reads statistics and cost constants. Stale statistics produce a confident, wrong plan.
• "A higher cost= means slower." Cost is a unit-less estimate; compare nodes within a plan, and compare estimated rows with actual, not costs with wall time.
• "Nested loops are bad, hash joins are good." Each is right for a shape: a small outer with an indexed inner wants a loop; two large sets want a hash. The wrong estimate, not the algorithm, is the bug.
• "The same query gets the same plan." Prepared statements may run a generic plan after five executions; parameter values change selectivity; statistics change over time.
• "EXPLAIN is enough." Without ANALYZE you see only estimates, and the whole question is where the estimates diverge from reality.

#Going deeper

• PostgreSQL manual, "Using EXPLAIN", "Statistics Used by the Planner" (including extended statistics) and "Planner Cost Constants".
• explain.depesz.com and explain.dalibo.com, which annotate plans with the estimate/actual ratio per node.
• MySQL reference, "Optimizing Queries with EXPLAIN", "EXPLAIN ANALYZE" and "Optimizer Statistics".
• PostgreSQL manual, PREPARE reference, the section on generic versus custom plans and plan_cache_mode.
• Bruce Momjian, "Explaining the Postgres Query Optimizer", and the pg_stat_statements documentation.