Skip to content
Tutorials Center
My learning

Topics

Lesson 60 of 90 Β· Performance Tuning

EXPLAIN and EXPLAIN ANALYZE

Learn to read PostgreSQL query plans: plan trees, cost versus actual time, rows, loops, buffers, scan nodes and join nodes, with EXPLAIN ANALYZE.

When a query is slow, guessing is the slowest way to fix it. PostgreSQL will tell you exactly how it intends to run any statement, and with one extra word it will tell you how the run actually went. That report is the query plan, and EXPLAIN is the command that prints it. Reading plans is the central skill of database performance work: every later lesson in this chapter builds on it.

In this lesson you will learn how a plan is structured as a tree, what the numbers in parentheses mean, how EXPLAIN differs from EXPLAIN ANALYZE, and how to recognise the scan and join nodes you will meet most often. The plan output from PostgreSQL is read-only here, and the numbers are representative rather than copied from a particular machine, so yours will differ. The live editors use SQLite's EXPLAIN QUERY PLAN to show the same ideas (scan versus index lookup, join order, sorting) and a small stand-in for the gap between estimated and actual row counts. For SQLite's own plan format, see Reading Query Plans.

Asking for a plan

Put EXPLAIN in front of a statement. PostgreSQL plans it but does not run it:

SQL
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
TEXT
Seq Scan on orders  (cost=0.00..20834.00 rows=100 width=36)
  Filter: (customer_id = 42)

Adding ANALYZE makes PostgreSQL run the statement for real and report what happened next to the estimates:

SQL
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
TEXT
Seq Scan on orders  (cost=0.00..20834.00 rows=100 width=36) (actual time=0.015..142.870 rows=98 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 999902
Planning Time: 0.090 ms
Execution Time: 142.912 ms

EXPLAIN ANALYZE really executes the query. An UPDATE, DELETE or INSERT will change your data. To look at the plan of a write safely, wrap it: BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK;. Also expect a slow query to stay slow while you analyze it.

You will usually want a few more options. They go in parentheses:

SQL
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) SELECT ...;
EXPLAIN (ANALYZE, FORMAT JSON) SELECT ...;     -- for plan visualisation tools

BUFFERS adds how many 8 KB pages were read from memory or from disk (more on that below), and VERBOSE lists the output columns of each node. PostgreSQL 18 switches BUFFERS on automatically with ANALYZE, but spelling it out is a harmless habit that also works on older servers. FORMAT JSON produces a machine-readable plan.

A plan is a tree

Every plan is a tree of nodes. Each node does one job (scan a table, join two inputs, sort, aggregate) and passes rows up to its parent. The indentation and the arrows (->) show the nesting. Data flows from the innermost, most indented nodes at the bottom up to the top node, which produces the final result.

Here is a join with a filter and a grouping, trimmed and with illustrative numbers:

SQL
EXPLAIN ANALYZE
SELECT c.name, count(*)
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE c.country = 'NZ'
GROUP BY c.name;
TEXT
HashAggregate  (cost=25061.00..25076.00 rows=1500 width=18) (actual time=211.40..211.80 rows=1500 loops=1)
  Group Key: c.name
  ->  Hash Join  (cost=1137.50..24761.00 rows=30000 width=10) (actual time=6.20..198.50 rows=30124 loops=1)
        Hash Cond: (o.customer_id = c.id)
        ->  Seq Scan on orders o  (cost=0.00..20834.00 rows=1000000 width=4) (actual time=0.02..95.30 rows=1000000 loops=1)
        ->  Hash  (cost=1125.00..1125.00 rows=1500 width=14) (actual time=6.10..6.10 rows=1500 loops=1)
              ->  Seq Scan on customers c  (cost=0.00..1125.00 rows=1500 width=14) (actual time=0.01..5.40 rows=1500 loops=1)
                    Filter: (country = 'NZ'::text)
                    Rows Removed by Filter: 48500
Planning Time: 0.350 ms
Execution Time: 212.100 ms

Read it from the bottom: PostgreSQL scans customers, keeps the 1,500 rows where country = 'NZ', and builds an in-memory hash table from them (Hash). It then scans all of orders, and for each order probes the hash table (Hash Join). Finally HashAggregate groups the 30,124 joined rows into 1,500 customer names.

How to read a plan

Start at the bottom The deepest node runs first.
Rows flow upward Each parent consumes its children.
Compare numbers Estimated against actual rows.
Find the slow node Look at actual time, times loops.

Do not stare at the top line only.

1 / 2

What the numbers mean

Each node shows two groups of numbers. With EXPLAIN ANALYZE you get both.

The estimate, (cost=0.00..20834.00 rows=100 width=36):

  • cost=A..B: A is the cost before the first row can be returned (the startup cost), B is the total cost to return all rows. The unit is arbitrary. It is not milliseconds. It is built from settings: reading one page sequentially costs 1.0, one random page 4.0, processing one row 0.01, and evaluating one operator 0.0025 (the defaults). A node's cost includes its children's cost.
  • rows: how many rows the planner expects this node to output.
  • width: the average size of an output row in bytes.

You can check the cost arithmetic. If orders has 1,000,000 rows stored in 8,334 pages, a sequential scan with a filter costs about 8334 Γ— 1.0 + 1,000,000 Γ— 0.01 + 1,000,000 Γ— 0.0025 = 20834, exactly what the plan above shows. Costs are only useful for comparing plans for the same query on the same data; the planner picks the cheapest one it finds.

The measurement, (actual time=0.015..142.870 rows=98 loops=1):

  • actual time=A..B: milliseconds until the first row and until the last row, measured per loop.
  • rows: the number of rows this node really returned (per loop).
  • loops: how many times the node was started. A node on the inside of a nested loop join runs once for each outer row.

To get a node's real total time, multiply B by loops. A node showing actual time=0.05..0.06 rows=1 loops=40000 took about 2.4 seconds in total, even though each single run looks instant. This is the most common way to misread a plan.

The most valuable comparison is estimated rows against actual rows. Here the planner expected 100 rows from the filter and got 98, which is excellent. When the two differ by factors of ten or more, the planner chose its strategy on wrong information, and that is usually the real cause of a slow query. Reading Slow Plans and Statistics and the Query Planner show how to deal with it.

The next editor shows how a naive guess goes wrong when values are not spread evenly. A planner without better statistics would assume each status is equally common; the table is skewed, so the guess is far off.

SQL Estimate versus actual rows

Press Run (or Ctrl + Enter) to see the result here.

For pending the naive estimate is 33 times too high; for shipped it is nearly three times too low. Real PostgreSQL does much better than this naive guess because it keeps per-column statistics, including the most common values and how often they occur, but the same kind of error appears whenever the statistics are stale or the columns are correlated.

Buffers: memory versus disk

With the BUFFERS option each node reports the 8 KB pages it touched:

TEXT
Index Scan using orders_customer_id_idx on orders  (cost=0.42..412.80 rows=100 width=36) (actual time=0.031..0.402 rows=98 loops=1)
  Index Cond: (customer_id = 42)
  Buffers: shared hit=95 read=3
Planning Time: 0.110 ms
Execution Time: 0.431 ms
  • shared hit: pages found in PostgreSQL's memory cache (shared_buffers).
  • shared read: pages that had to be fetched from the operating system, which may mean real disk reads.
  • dirtied and written: pages changed or flushed during the query.

A query that reads thousands of pages to return a handful of rows is doing too much work, no matter what its timing says on a warm cache. Run a slow query twice: the second run is often much faster because the pages are now cached, so look at read counts to understand a "cold" run.

Scan nodes: how rows are read from a table

The scans you will meet most

Seq Scan Reads every page of the table.
Index Scan Walks the index, then fetches rows.
Index Only Scan Answers from the index alone.
Bitmap Scan Collects matches, then reads pages in order.

None of these is always best. It depends on how many rows match.

1 / 2
  • Seq Scan reads the whole table in physical order. It is the right choice when you need a large fraction of the rows, and on small tables it beats any index. A Seq Scan on a big table that returns a few rows is the classic sign of a missing index.
  • Index Scan finds matching entries in an index and then visits the table for each row. It is best when few rows match.
  • Index Only Scan reads only the index, when the index holds every column the query needs. Its Heap Fetches: N line says how many times it still had to check the table to confirm a row is visible, which depends on how recently the table was vacuumed.
  • Bitmap Index Scan feeding a Bitmap Heap Scan is the middle ground. PostgreSQL first collects every matching row location from the index into a bitmap, then reads those table pages in physical order. It is chosen when too many rows match for a plain index scan, but too few for reading everything, and it can combine several indexes with BitmapAnd/BitmapOr.

How Indexes Work in PostgreSQL explains why the planner picks one over another. The SQLite plans in the next editor show the same contrast: a SCAN of the whole table, then a SEARCH once an index exists, and a covering index that never touches the table (the SQLite counterpart of an index-only scan).

SQL Scan versus index lookup (SQLite plans)

Press Run (or Ctrl + Enter) to see the result here.

Join nodes: how two inputs are combined

Three ways to join

Nested Loop For each outer row, look up the inner side.
Hash Join Hash one side, probe with the other.
Merge Join Zip two sorted inputs together.

The planner chooses by estimated cost.

1 / 2
  • Nested Loop: for every row from the outer (first) input, find matching rows in the inner input, ideally through an index. It is excellent when the outer side is small. It is a disaster when the outer side is large and the inner side is scanned again every time, which shows up as a huge loops count on a Seq Scan.
  • Hash Join: builds an in-memory hash table from one input (the node labelled Hash), then streams the other input past it. It works well for large unsorted inputs and equality joins. If the hash table does not fit in work_mem, the plan shows Batches greater than 1, meaning it spilled to disk.
  • Merge Join: sorts or reads both inputs in order and walks through them together. It shines when both sides are already sorted, for example by an index.

Other nodes worth knowing

Node What it does
Sort Sorts rows. Sort Method: quicksort Memory: 25kB is in memory; external merge Disk: ... spilled to disk.
Limit Stops after N rows. Its children may stop early too, which is why LIMIT can be very cheap.
HashAggregate / GroupAggregate Group rows with a hash table, or from sorted input.
Aggregate Computes one result, such as a plain count(*).
Materialize Stores an input so it can be rescanned.
Gather Collects rows from parallel worker processes.
Index Cond The condition used inside the index.
Filter A condition applied to rows after they are read. A large Rows Removed by Filter means wasted work.

A reading routine

When a plan lands in front of you, work through it in this order:

  1. Read Execution Time: is it really slow, or only slow once (cold cache)?
  2. Find the node with the biggest actual time Γ— loops among the deepest nodes. Most of the time is usually in one or two nodes.
  3. Compare estimated and actual rows on that node and its children. A big gap points at statistics.
  4. Look for Seq Scan with a big Rows Removed by Filter, Sort ... Disk, Batches above 1, and loops in the thousands.
  5. Check Buffers for lots of read.
  6. Change one thing (an index, a rewrite, ANALYZE) and run the plan again.

Remember the plan is a snapshot: it can change when the table grows, statistics refresh, or parameters change. Reading Slow Plans takes this routine further with real problem cases.

Check your understanding

Quick check Β· 5 questions

of 5 answered You scored / 5 Β· perfect!

  1. 1. How do you read the nodes of a PostgreSQL plan?

    Each node passes its rows to its parent, so execution starts at the deepest nodes and the top node produces the final result.
  2. 2. What is the unit of the cost= numbers?

    Costs are relative, built from settings like seq_page_cost and cpu_tuple_cost. They are useful for comparing plans, not for predicting time.
  3. 3. A node shows actual time=0.05..0.06 rows=1 loops=40000. About how much total time did it take?

    Actual time is reported per loop, so you multiply by loops. A tiny per-loop time repeated 40,000 times can dominate the whole query.
  4. 4. Why wrap EXPLAIN ANALYZE of an UPDATE in BEGIN ... ROLLBACK?

    EXPLAIN ANALYZE executes the statement. Rolling back undoes the changes while still letting you see the real plan and timings.
  5. 5. The planner estimated rows=12 but the node returned rows=480000. What does that most likely indicate?

    A large gap between estimated and actual rows means the planner had bad information, so it may have picked a poor plan. Refreshing statistics with ANALYZE is the usual first step.

Summary

  • EXPLAIN shows the planned strategy; EXPLAIN ANALYZE runs the query and adds real times and row counts. Wrap writes in a transaction you roll back.
  • A plan is a tree: read from the deepest nodes upward, and use BUFFERS to see memory hits versus disk reads.
  • cost=startup..total is in arbitrary units; actual time is milliseconds per loop, so multiply by loops.
  • The best signal is estimated rows versus actual rows: large gaps mean the planner was misled.
  • Scan nodes: Seq Scan, Index Scan, Index Only Scan, Bitmap Index and Heap Scan. Join nodes: Nested Loop, Hash Join, Merge Join.
  • Watch for big Rows Removed by Filter, sorts or hashes that spill to disk, and huge loops counts.

Next: Reading Slow Plans, where you diagnose misestimates, join choices and disk spills.