# Reading query plans > Read EXPLAIN and EXPLAIN ANALYZE output node by node, spot bad estimates and expensive steps, and fix slow queries systematically. SQL & Databases roadmap · Design & performance · 19 min read · https://levelcode.org/roadmaps/sql/query-plans When a query is slow, guessing is expensive: you add an index that is never used, or rewrite a query that was never the problem. PostgreSQL will tell you exactly how it runs a query and where the time goes — the **query plan**. This lesson teaches you what each number in a plan means, how to read the tree, why estimates go wrong, and a repeatable workflow for finding the slow spot. ## EXPLAIN and EXPLAIN ANALYZE This lesson uses the large dataset from the indexes lesson, so load it first if you have not. Then make sure exactly these extra indexes exist, and turn off parallel query for your session so plans stay small enough to read: ```sql DROP INDEX IF EXISTS orders_paid_idx, customers_email_lower_idx, orders_status_idx; CREATE INDEX IF NOT EXISTS orders_customer_date_idx ON orders (customer_id, ordered_on); CREATE INDEX IF NOT EXISTS order_items_book_id_idx ON order_items (book_id); SET max_parallel_workers_per_gather = 0; ``` `SET` only affects your current session. Timings below come from one machine; yours will differ, but the shapes and row counts will match. ### EXPLAIN: the plan, not the run `EXPLAIN` shows the plan the planner chose, without running the query: ```sql EXPLAIN SELECT * FROM orders WHERE order_id = 500000; ``` ```text QUERY PLAN --------------------------------------------------------------------------- Index Scan using orders_pkey on orders (cost=0.42..8.44 rows=1 width=19) Index Cond: (order_id = 500000) (2 rows) ``` Each line starting with a node name (here `Index Scan`) is a **plan node**: one step of the execution. Indented lines below it, such as `Index Cond`, are details. The numbers in parentheses are estimates: | Field | Meaning | | --- | --- | | `cost=0.42..8.44` | **startup cost** .. **total cost**, in arbitrary planner units (by default, reading one page sequentially costs 1.0). Startup is the cost before the first row can be returned; total is the cost to return all rows. | | `rows=1` | estimated number of rows this node will output | | `width=19` | estimated average row size in bytes | Costs are only meaningful relative to each other: the planner prices many candidate plans and picks the cheapest. Startup cost matters with `LIMIT`, where a plan that produces its first rows immediately can win despite a higher total. ### EXPLAIN ANALYZE: what actually happened `EXPLAIN ANALYZE` runs the query for real and adds measurements next to the estimates: ```sql EXPLAIN ANALYZE SELECT * FROM orders WHERE order_id = 500000; ``` ```text QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Index Scan using orders_pkey on orders (cost=0.42..8.44 rows=1 width=19) (actual time=0.033..0.034 rows=1 loops=1) Index Cond: (order_id = 500000) Planning Time: 0.018 ms Execution Time: 0.038 ms (4 rows) ``` | Field | Meaning | | --- | --- | | `actual time=0.033..0.034` | milliseconds until the first row, and until the last row, **per loop** | | `rows=1` | rows actually output, **averaged per loop** | | `loops=1` | how many times this node was executed | | `Planning Time` / `Execution Time` | time spent choosing the plan, and running it | Comparing estimated `rows` with actual `rows` on each node is the single most useful habit in plan reading: when they differ by orders of magnitude, the planner's choices downstream may be wrong. `EXPLAIN (ANALYZE, BUFFERS)` adds a line such as `Buffers: shared hit=453 read=9102` to each node: how many 8 kB pages it found in PostgreSQL's cache (`hit`) and how many it had to fetch from the operating system or disk (`read`). New in PostgreSQL 17, the `SERIALIZE` option also measures converting result rows for the client, and `MEMORY` reports planner memory use. > [!WARNING] > `EXPLAIN ANALYZE` really executes the statement: `EXPLAIN ANALYZE DELETE ...` deletes the rows. Wrap data changes in `BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;`, and start with plain `EXPLAIN` for queries that may run for minutes. ## Reading the plan tree A plan is a tree. Each node pulls rows from its children (the indented `->` lines beneath it) and passes results up to its parent, so you read a plan **from the most indented nodes outward**. Here is one customer's order lines with book titles: ```sql EXPLAIN ANALYZE SELECT o.order_id, o.ordered_on, b.title, oi.quantity FROM orders AS o JOIN order_items AS oi ON oi.order_id = o.order_id JOIN books AS b ON b.book_id = oi.book_id WHERE o.customer_id = 4242; ``` ```text Nested Loop (cost=5.03..76.48 rows=8 width=28) (actual time=0.071..0.152 rows=5 loops=1) -> Nested Loop (cost=4.89..75.27 rows=8 width=16) (actual time=0.060..0.136 rows=5 loops=1) -> Bitmap Heap Scan on orders o (cost=4.46..24.11 rows=5 width=8) (actual time=0.037..0.050 rows=5 loops=1) Recheck Cond: (customer_id = 4242) Heap Blocks: exact=5 -> Bitmap Index Scan on orders_customer_date_idx (cost=0.00..4.46 rows=5 width=0) (actual time=0.027..0.027 rows=5 loops=1) Index Cond: (customer_id = 4242) -> Index Scan using order_items_pkey on order_items oi (cost=0.43..10.21 rows=2 width=12) (actual time=0.016..0.016 rows=1 loops=5) Index Cond: (order_id = o.order_id) -> Index Scan using books_pkey on books b (cost=0.14..0.15 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=5) Index Cond: (book_id = oi.book_id) Planning Time: 0.945 ms Execution Time: 0.190 ms ``` From the inside out: the **Bitmap Index Scan** finds the locations of customer 4242's five orders, and the **Bitmap Heap Scan** fetches them from five table pages. The inner **Nested Loop** then runs its second child, an **Index Scan** on `order_items_pkey`, once for each of those orders — hence `loops=5`. The outer **Nested Loop** does the same with `books_pkey` to fetch each title. Time and rows are **per loop**: the order-items scan took about 0.016 ms × 5 = 0.08 ms in total and produced 1 × 5 = 5 rows. A node showing `actual time=0.05 rows=1 loops=200000` is a ten-second node in disguise. ## Node types you will meet ### Scans: getting rows out of a table | Node | What it does | Typical when | | --- | --- | --- | | `Seq Scan` | reads every page of the table, applying `Filter` | no useful index, or a large fraction of rows needed | | `Index Scan` | walks the index, fetching each matching row from the table | few rows, or rows needed in index order | | `Index Only Scan` | answers from the index alone; `Heap Fetches` counts table visits it could not avoid | all needed columns are in the index | | `Bitmap Index Scan` + `Bitmap Heap Scan` | collects matching row locations from one or more indexes, sorts them by page, then reads each page once | a moderate number of rows, or combining indexes with `AND`/`OR` | An `Index Cond:` was used to search the index itself; a `Filter:` was applied to rows after reading them, and a large `Rows Removed by Filter` is often the first sign of a missing index. ### Joins: combining two inputs | Node | How it works | Good when | | --- | --- | --- | | `Nested Loop` | for each outer row, scan the inner input (ideally via an index) | the outer side is small and the inner side has an index on the join key | | `Hash Join` | build a hash table from the smaller input (the `Hash` child), then probe it with each row of the other | large inputs, equality joins, no useful order | | `Merge Join` | walk two inputs sorted on the join key in step | both inputs already sorted (for example, from indexes) | ### Everything else `Sort` (check its `Sort Method`), `HashAggregate` and `GroupAggregate` for `GROUP BY`, `Limit`, `Unique`, `Append` for `UNION ALL`, `Memoize` (caches a nested loop's inner results), and `SubPlan`/`InitPlan` for subqueries. With parallel query on, `Gather` or `Gather Merge` collects rows from worker processes; below it, figures are averaged per process, so `loops=3` means a leader plus two workers. ## A bigger plan Revenue per city for 2024, excluding cancelled orders: ```sql EXPLAIN ANALYZE SELECT c.city, sum(oi.quantity * oi.unit_price) AS revenue FROM customers AS c JOIN orders AS o ON o.customer_id = c.customer_id JOIN order_items AS oi ON oi.order_id = o.order_id WHERE o.ordered_on >= '2024-01-01' AND o.ordered_on < '2025-01-01' AND o.status <> 'cancelled' GROUP BY c.city ORDER BY revenue DESC; ``` ```text Sort (cost=87004.58..87004.59 rows=5 width=39) (actual time=793.302..793.310 rows=5 loops=1) Sort Key: (sum(((oi.quantity)::numeric * oi.unit_price))) DESC Sort Method: quicksort Memory: 25kB -> HashAggregate (cost=87004.46..87004.52 rows=5 width=39) (actual time=793.283..793.291 rows=5 loops=1) Group Key: c.city Batches: 1 Memory Usage: 24kB -> Hash Join (cost=34987.07..83845.14 rows=315932 width=17) (actual time=339.896..699.972 rows=311931 loops=1) Hash Cond: (o.customer_id = c.customer_id) -> Hash Join (cost=27325.94..71291.66 rows=315932 width=14) (actual time=297.216..539.404 rows=311931 loops=1) Hash Cond: (oi.order_id = o.order_id) -> Seq Scan on order_items oi (cost=0.00..24555.16 rows=1500016 width=14) (actual time=0.026..112.458 rows=1500016 loops=1) -> Hash (cost=23870.18..23870.18 rows=210621 width=8) (actual time=92.880..92.882 rows=207954 loops=1) Buckets: 262144 Batches: 2 Memory Usage: 6102kB -> Seq Scan on orders o (cost=0.00..23870.18 rows=210621 width=8) (actual time=36.821..66.272 rows=207954 loops=1) Filter: ((ordered_on >= '2024-01-01'::date) AND (ordered_on < '2025-01-01'::date) AND (status <> 'cancelled'::text)) Rows Removed by Filter: 792056 -> Hash (cost=4184.06..4184.06 rows=200006 width=11) (actual time=42.451..42.452 rows=200006 loops=1) Buckets: 262144 Batches: 2 Memory Usage: 6436kB -> Seq Scan on customers c (cost=0.00..4184.06 rows=200006 width=11) (actual time=0.010..17.111 rows=200006 loops=1) Planning Time: 0.440 ms Execution Time: 793.410 ms ``` Read it inside out: 1. `Seq Scan on orders` keeps the 207,954 orders from 2024 that are not cancelled — about 21% of the table, too many for an index to pay off. 2. `Hash` builds a hash table of them keyed by `order_id`. `Batches: 2` means it did not fit in `work_mem` (4 MB by default) and spilled to temporary files. 3. The first `Hash Join` probes it with all 1.5 million order lines, keeping 311,931; the second attaches each line's customer. 4. `HashAggregate` groups by city into 5 rows, and `Sort` orders them by revenue. Times are **cumulative**: a node's `actual time` includes its children, so subtract children from parents to see where time goes. Scanning `order_items` took about 112 ms, the first join finished at 539 ms, the second at 700 ms, and aggregation added about 93 ms. Estimates match actual row counts at every level, so this query is slow because it genuinely processes a lot of rows, not because of a bad plan. The options are raising `work_mem` for this report, pre-aggregating into a summary table, or letting parallel query split the work. ## Estimates versus reality The planner estimates row counts from per-column **statistics** — fraction of `NULL`s, number of distinct values, most common values, a histogram — that `ANALYZE` collects (autovacuum runs it automatically). Estimates go wrong when statistics are stale after a bulk change (run `ANALYZE table_name;`), when a condition wraps a column in an expression, and when columns are **correlated**, because the planner assumes conditions are independent and multiplies their selectivities. In the generated customers, `joined_on` completely determines `city`. Watch the planner miss it: ```sql EXPLAIN ANALYZE SELECT count(*) FROM customers WHERE city = 'Leeds' AND joined_on = '2020-01-06'; ``` ```text Aggregate (cost=5184.15..5184.16 rows=1 width=8) (actual time=12.848..12.849 rows=1 loops=1) -> Seq Scan on customers (cost=0.00..5184.09 rows=22 width=0) (actual time=0.006..12.834 rows=112 loops=1) Filter: ((city = 'Leeds'::text) AND (joined_on = '2020-01-06'::date)) Rows Removed by Filter: 199894 ``` It estimated 200,006 × 1/5 (one city in five) × 1/1800 (one date in 1,800) ≈ 22 rows; the real answer is 112. Had this node fed a nested loop, the inner side would run five times more often than planned — and with bigger misestimates, a plan that looked cheap becomes the slowest choice. **Extended statistics** teach the planner about the dependency: ```sql CREATE STATISTICS customers_city_joined (dependencies) ON city, joined_on FROM customers; ANALYZE customers; ``` ```text -> Seq Scan on customers (cost=0.00..5184.09 rows=111 width=0) (actual time=0.009..13.087 rows=112 loops=1) ``` > [!TIP] > When you find a misestimate, trace it to the lowest node where estimated and actual rows diverge. Everything above that node was planned using a wrong number, so fixing it there (with `ANALYZE`, extended statistics or a rewritten condition) often fixes the whole plan. ## A workflow for slow queries 1. **Get the real plan**: `EXPLAIN (ANALYZE, BUFFERS)` on realistic data and parameters. 2. **Find the expensive node**, subtracting children's time and multiplying by `loops`. Usually one or two nodes dominate. 3. **Compare estimated and actual rows** at that node and below. 4. **Look for the classic symptoms:** a `Seq Scan` with a huge `Rows Removed by Filter` that returns few rows (missing or unusable index); high `loops` on anything other than a cheap index lookup; `Sort Method: external merge Disk: ...` or hash `Batches` above 1 (memory spills); high `Heap Fetches` on an index-only scan (needs vacuuming). 5. **Change one thing and measure again.** High `loops` often comes from a correlated subquery. This one finds each York customer's latest order: ```sql EXPLAIN ANALYZE SELECT c.customer_id, c.name, (SELECT max(o.ordered_on) FROM orders AS o WHERE o.customer_id = c.customer_id) AS last_order FROM customers AS c WHERE c.city = 'York'; ``` ```text Seq Scan on customers c (cost=0.00..53917.35 rows=39308 width=23) (actual time=0.040..83.464 rows=40000 loops=1) Filter: (city = 'York'::text) Rows Removed by Filter: 160006 SubPlan 2 -> Result (cost=1.24..1.25 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=40000) InitPlan 1 -> Limit (cost=0.42..1.24 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=40000) -> Index Only Scan Backward using orders_customer_date_idx on orders o (cost=0.42..4.51 rows=5 width=4) (actual time=0.001..0.001 rows=1 loops=40000) Index Cond: (customer_id = c.customer_id) Heap Fetches: 0 Planning Time: 0.445 ms Execution Time: 84.628 ms ``` The subquery runs 40,000 times, once per York customer, but each run is a microsecond-scale backward index-only scan (PostgreSQL turned `max()` into "read the last index entry"), so the total is fine. Without that index, each loop would scan a million orders. ## Practice 1. Run the York query above with `EXPLAIN ANALYZE` and confirm the subplan's total cost in time: per-loop time × loops. 2. Inside a transaction, drop `orders_customer_date_idx` and run **plain** `EXPLAIN` (not `ANALYZE` — why?) on the same query. What is the estimated total cost, and which node explains it? 3. Still inside the transaction, rewrite the query as a `LEFT JOIN` with `GROUP BY` and run `EXPLAIN ANALYZE`. Which join algorithm is chosen, and how long does it take? Roll back to restore the index. **Solution** 1. The subplan's nodes show `actual time=0.001..0.001` with `loops=40000`: roughly 0.001 ms × 40,000 ≈ 40 ms of the 85 ms total, the rest being the scan of customers. Rounding at this scale is coarse, so compare `Execution Time` totals. 2. Use plain `EXPLAIN` because the query would now take a very long time to run. ```sql BEGIN; DROP INDEX orders_customer_date_idx; EXPLAIN SELECT c.customer_id, c.name, (SELECT max(o.ordered_on) FROM orders AS o WHERE o.customer_id = c.customer_id) AS last_order FROM customers AS c WHERE c.city = 'York'; ``` ```text QUERY PLAN -------------------------------------------------------------------------- Seq Scan on customers c (cost=0.00..753486603.91 rows=39861 width=23) Filter: (city = 'York'::text) SubPlan 1 -> Aggregate (cost=18902.73..18902.74 rows=1 width=4) -> Seq Scan on orders o (cost=0.00..18902.71 rows=5 width=4) Filter: (customer_id = c.customer_id) (6 rows) ``` The total cost jumps to about 753 million units. The `SubPlan` costs about 18,900 per execution — a sequential scan of all orders — and it is executed once for each of the roughly 39,861 York customers: 18,900 × 39,861 ≈ 753 million. 3. A join lets the planner process all customers in one pass: ```sql EXPLAIN ANALYZE SELECT c.customer_id, c.name, max(o.ordered_on) AS last_order FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.customer_id WHERE c.city = 'York' GROUP BY c.customer_id; ROLLBACK; ``` ```text HashAggregate (cost=25208.55..25607.16 rows=39861 width=23) (actual time=253.975..261.697 rows=40000 loops=1) Group Key: c.customer_id Batches: 1 Memory Usage: 5393kB -> Hash Right Join (cost=5182.34..24210.32 rows=199645 width=23) (actual time=21.359..209.299 rows=200000 loops=1) Hash Cond: (o.customer_id = c.customer_id) -> Seq Scan on orders o (cost=0.00..16398.37 rows=1001737 width=8) (actual time=0.021..56.309 rows=1000010 loops=1) -> Hash (cost=4684.08..4684.08 rows=39861 width=19) (actual time=21.087..21.089 rows=40000 loops=1) Buckets: 65536 Batches: 1 Memory Usage: 2522kB -> Seq Scan on customers c (cost=0.00..4684.08 rows=39861 width=19) (actual time=0.024..15.710 rows=40000 loops=1) Filter: (city = 'York'::text) Execution Time: 263.873 ms ``` A **Hash Right Join**: it hashes the 40,000 York customers, scans the million orders once, and probes. "Right" means the preserved side of the `LEFT JOIN` is the hashed input. About 260 ms: slower than the indexed subquery (85 ms), but thousands of times faster than the unindexed one. The same question can have wildly different costs depending on access paths, and the plan shows you which one you got. ## Key takeaways - `EXPLAIN` shows the chosen plan with estimated cost, rows and width; `EXPLAIN ANALYZE` runs the query and adds actual time, rows and loops. - Read plans from the innermost nodes outward; times are cumulative and per loop, so subtract children and multiply by `loops`. - Know the scan nodes (Seq, Index, Index Only, Bitmap) and join nodes (Nested Loop, Hash, Merge), and when each is appropriate. - Large gaps between estimated and actual rows mean bad statistics: run `ANALYZE`, or add extended statistics for correlated columns. - Look for big `Rows Removed by Filter`, high `loops`, disk sorts and hash batches; change one thing at a time and re-measure. - `EXPLAIN ANALYZE` executes the statement, so wrap data changes in `BEGIN` … `ROLLBACK`. ## Quiz 1. A plan node shows (cost=0.42..8.44 rows=1 width=19). What does 0.42..8.44 mean? A. Actual milliseconds to first and last row B. The minimum and maximum number of rows expected C. Estimated startup cost and total cost, in arbitrary planner units D. Pages read from cache and from disk **Answer: C.** Without ANALYZE, all numbers are estimates. The first cost is the work before the first row can be emitted, the second is the total; units are relative (a sequential page read costs 1.0 by default). 2. An inner node shows (actual time=0.016..0.016 rows=1 loops=5). How much time and how many rows does it account for in total? A. About 0.08 ms and 5 rows B. 0.016 ms and 1 row C. 0.016 ms and 5 rows D. About 0.08 ms and 1 row **Answer: A.** Actual time and rows are reported per loop. Multiply by loops: 0.016 ms x 5 is about 0.08 ms, and 1 row x 5 is 5 rows. 3. What happens when you run EXPLAIN ANALYZE DELETE FROM orders WHERE status = 'cancelled';? A. PostgreSQL shows the plan without deleting anything B. It fails, because EXPLAIN ANALYZE only accepts SELECT C. It deletes the rows only if you add the BUFFERS option D. The rows are really deleted, so you should wrap it in BEGIN ... ROLLBACK **Answer: D.** EXPLAIN ANALYZE executes the statement to measure it, including any data changes. Use plain EXPLAIN, or run it inside a transaction you roll back. 4. A Seq Scan with two conditions on customers estimates rows=22 but returns rows=112, because one column determines the other. What is the most direct fix for the estimate? A. Add more indexes on both columns B. Create extended statistics on the two columns with CREATE STATISTICS and run ANALYZE C. Increase work_mem D. Rewrite the query with a CTE **Answer: B.** The planner multiplies per-column selectivities, assuming independence. CREATE STATISTICS ... (dependencies) records the functional dependency so the estimate becomes about 111. Indexes and work_mem do not change row estimates.