Reading an EXPLAIN Plan

EXPLAIN prints the plan the planner chose and the arithmetic that chose it. The output is a tree, it is read from the inside out, and every node in it carries the same four numbers: a startup cost, a total cost, an estimated row count and an estimated row width. Nothing is hidden and nothing is approximate about the report itself — what you are looking at is exactly the argument the planner made.

Learning to read those four numbers is what turns "the database is slow" into a claim about one node. Every example in this chapter comes off Cartwheel's own schema, and the one that matters most is the query behind checkout's order-history panel — the reason checkout latency goes from 40 ms to about 6 seconds on the first Saturday of the month. That plan is unreadable until this page's vocabulary is in place, and completely legible afterwards. The example here is its close relative: the month-to-date summary the operations console renders, over the same table.

A Tree, Read From the Inside Out

Indentation is nesting. Each line beginning with an arrow is a child of the nearest less-indented line above it, and children produce rows for their parent. Execution therefore starts at the deepest, most-indented nodes, and the unindented line at the top is the last thing that happens, not the first. Reading a plan top-down is the single most common reason plans look incomprehensible, because it inverts the order of every event in the query.

EXPLAIN
SELECT s.label, count(*) AS orders, sum(o.total) AS revenue
  FROM orders o
  JOIN order_statuses s ON s.status = o.status
 WHERE o.placed_at >= date_trunc('month', now())
 GROUP BY s.label;

 HashAggregate  (cost=64650.98..64651.05 rows=7 width=48)
   Group Key: s.label
   ->  Hash Join  (cost=1.16..61644.71 rows=300615 width=26)
         Hash Cond: (o.status = s.status)
         ->  Bitmap Heap Scan on orders o  (cost=3390.55..57883.19 rows=300615 width=20)
               Recheck Cond: (placed_at >= date_trunc('month'::text, now()))
               ->  Bitmap Index Scan on orders_placed_at_idx  (cost=0.00..3315.40 rows=300615 width=0)
                     Index Cond: (placed_at >= date_trunc('month'::text, now()))
         ->  Hash  (cost=1.07..1.07 rows=7 width=14)
               ->  Seq Scan on order_statuses s  (cost=0.00..1.07 rows=7 width=14)

Narrated in execution order, the tree says this. The deepest node walks orders_placed_at_idx and collects the addresses of every row whose placed_at falls in the current month, building a bitmap rather than fetching anything. Its parent, the bitmap heap scan, sorts those addresses by page and reads each heap page once, producing an estimated 300,615 rows twenty bytes wide. In a separate branch, the seven-row order_statuses table is scanned and loaded into a hash table. The hash join then probes that table once per order row to attach the label. Finally the aggregate groups by label and returns seven rows. Seven rows out of a plan the planner prices at 64,651.

The Four Numbers

Take one node and read its parenthesis left to right. cost=3390.55..57883.19 is a pair: the startup cost is the work done before the node can emit its first row, and the total cost is the work done if the node runs to completion. For the bitmap heap scan, the startup cost of 3390.55 is the whole bitmap index scan underneath it, because no heap page can be read until the bitmap is complete. rows=300615 is an estimate, not a count — the planner's belief about how many rows this node will emit. width=20 is the estimated average size of one of those rows in bytes, which matters when a sort or a hash has to hold them in memory.

Costs are cumulative: a node's total includes everything its children cost. The hash join's 61,644 is the price of the whole subtree ending at the join, not the price of joining. To find what one node adds, subtract its children. And the unit is arbitrary. By convention it is anchored so that reading one page sequentially costs 1.0, which makes a cost of 57,883 roughly comparable to "reading fifty-eight thousand sequential pages' worth of effort". Costs exist to rank the alternatives for one query on one server. They are not milliseconds, they do not convert to milliseconds, and comparing them between two machines or between two unrelated queries means nothing.

Startup Cost, Total Cost, and the LIMIT Effect

The split between startup and total is what makes LIMIT interesting. A hash join has to build its entire hash table before it can emit anything, so its startup cost is large and its per-row cost afterwards is small. A nested loop can emit its first row almost immediately. When a query has no LIMIT, the planner compares total costs and the hash join usually wins on volume. Add LIMIT 20 and the planner switches to optimizing for the fraction of the work needed to produce twenty rows, which shifts the comparison toward whatever starts fastest.

That is normally what you want, and occasionally it is a trap. The planner assumes rows are found at a uniform rate, so a plan it believes will hit twenty matches in the first thousand rows may actually have to scan nineteen million to find them. Topic 49 comes back to this as one of the recurring shapes of a wrong plan. It is why adding a LIMIT to a slow query sometimes makes it dramatically slower rather than faster, and why a query that is fast with LIMIT 20 and slow with LIMIT 200 got two different plans rather than behaving strangely.

Index Cond, Filter, and the Rows Read for Nothing

There are two lines under a scan node that look similar and mean opposite things. An Index Cond is evaluated by the index itself: the scan descends to the matching range and never looks at anything outside it, so rows that fail the condition cost nothing at all. A Filter is evaluated after a row has been read from the heap. Every row it rejects was still fetched, deformed and examined. A Recheck Cond is a third thing again, specific to bitmap scans, where the original condition is re-applied to the rows on each fetched page.

EXPLAIN
SELECT o.public_id, o.total
  FROM orders o
 WHERE o.placed_at >= date_trunc('month', now())
   AND o.status = 'refunded';

 Bitmap Heap Scan on orders o  (cost=3390.55..58634.71 rows=4299 width=24)
   Recheck Cond: (placed_at >= date_trunc('month'::text, now()))
   Filter: (status = 'refunded'::text)
   ->  Bitmap Index Scan on orders_placed_at_idx  (cost=0.00..3315.40 rows=300615 width=0)
         Index Cond: (placed_at >= date_trunc('month'::text, now()))

The date predicate is an Index Cond, so 39.7 million of the table's 40 million rows are eliminated by the index without a single heap page being touched. The status predicate is a Filter, so all 300,615 rows in the month are read from disk and thrown away one at a time until roughly 4,300 refunds remain, an estimate that is simply the 1.43% of orders the statistics record as refunded applied to the month. That is the plan telling you which predicate has an index behind it and which does not. Whether an index is the right answer at all is Chapter 8's argument. Add ANALYZE, which the next topic covers, and Postgres counts the casualties on a Rows Removed by Filter line, the fastest pointer there is to a missing index or a composite index whose columns are in the wrong order.

The Cost Constants

The exchange rates behind every number in the output are ordinary settings, and they are worth reading once on your own server rather than trusting a blog post's table.

SELECT name, setting, unit FROM pg_settings
 WHERE name LIKE '%page_cost' OR name LIKE 'cpu_%'
    OR name IN ('parallel_setup_cost', 'parallel_tuple_cost');

         name         | setting
----------------------+---------
 cpu_index_tuple_cost | 0.005
 cpu_operator_cost    | 0.0025
 cpu_tuple_cost       | 0.01
 parallel_setup_cost  | 1000
 parallel_tuple_cost  | 0.1
 random_page_cost     | 4
 seq_page_cost        | 1

A sequential scan of orders is priced with those numbers and no others. The table occupies 455,000 pages and holds 40 million rows, so the estimate is 455,000 times seq_page_cost plus 40 million times cpu_tuple_cost — 455,000 plus 400,000, or 855,000. Every alternative is priced the same way, in the same units, and the cheapest one wins. The number that deserves suspicion is random_page_cost at 4.0: it says a randomly located page costs four times a sequential one, which described a spinning disk accurately and describes pg-primary's NVMe badly. Left at 4.0 it makes every index scan look several times more expensive than it is, which pushes the crossover between "use the index" and "scan the table" to the wrong place. That is a one-line, cluster-wide correction and it belongs in Chapter 10 with the rest of the configuration, not in a fight with individual queries.

What EXPLAIN Cannot Know

Plain EXPLAIN never executes the query. Every number in the output is a prediction, and predictions are printed with exactly the same confidence whether they are right or wrong by a factor of six orders of magnitude. A node reading rows=1 may be about to return 300,000 rows, and there is nothing in the plan text to distinguish that case from a genuine single-row lookup. That gap is the entire argument for the next topic.

There are two smaller limits worth knowing now. Plain EXPLAIN costs almost nothing to run: it plans, prints and stops, so there is no reason not to look at a plan before changing anything. And EXPLAIN plans the statement it is given, so running it with literal values substituted for parameters tells you about that query, not necessarily about the one your application prepares. A prepared statement can end up with a generic plan built without any parameter value at all, and EXPLAIN (GENERIC_PLAN) exists precisely so you can see that plan for a statement containing $1 placeholders.