Everything you've read in the module so far is rules, and in performance rules get things wrong. Does that query really use the index? Does IN (SELECT ...) really become a semi-join, as 07-05 promised? Is the problem really the statistics? There's only one honest way to answer, and it's the tool that turns this whole module into a method: EXPLAIN. Here you'll learn to read an execution plan node by node, to tell the estimated plan from the actual one, to recognise the signal that gives stale statistics away, and to interpret the fifteen nodes that appear in 95 % of plans. And you'll perform the module's central demonstration: you'll build a two-million-row test table —because with GreenStore's 20 products there's nothing to demonstrate— and you'll see with your own eyes the difference between walking two million rows and jumping straight to ten.

1. EXPLAIN and EXPLAIN ANALYZE
2. How to read a plan
3. cost, rows, width, actual time and loops
4. The most useful signal: estimated against actual
5. The nodes you'll see most
6. Useful EXPLAIN options
7. The test bench: two million rows
8. The demonstration: without an index and with one
9. And in the real GreenStore: why the index is ignored
10. Verifying 07-05's promise
11. Measuring in other engines and visualisation tools
12. Maintenance: VACUUM, ANALYZE and bloat
13. A six-step method
14. Common Mistakes and Tips
15. Exercises
16. Module conclusion

The first costs microseconds and tells you which path it would choose; the second costs whatever the query costs and tells you what actually happened.

⚠️ The indispensable warning. EXPLAIN ANALYZE actually runs the query. With a SELECT that's harmless; with an UPDATE, a DELETE or an INSERT, it modifies the data. The safe way to analyse those is to wrap them in a transaction that gets undone:

The plan is displayed, the rows are deleted… and the ROLLBACK undoes it all. BEGIN, COMMIT and ROLLBACK are module 9; here it's enough to use this pattern as a mandatory precaution. It's the same safety net you already saw in 05-04 before a DELETE with no WHERE.

A plan is a tree of nodes: each one receives rows from its children, does something with them and passes them on to its parent. The text output represents it with indentation and -> arrows, and it's read in a specific way:

From the inside outwards and from the bottom upwards. The most indented node runs first; the one on the first line, last, and it's the one producing the final result.

In text, that same tree is printed the other way up: Limit at the very top, the two Seq Scans at the bottom. The two questions the tree's shape answers are always the same: where does the engine get into the data? (the leaf nodes) and how does it combine them? (the join nodes).

Every line of a plan carries its numbers. With a plain EXPLAIN there are only estimates; with ANALYZE the second half in parentheses is added:

The startup cost distinguishes two families of node: the ones that emit rows as they read them (Seq Scan, Index Scan, Nested Loop) have a startup cost of ≈ 0; the ones that need all the rows before returning the first (Sort, Hash, HashAggregate) have a high one. That matters a great deal with LIMIT.

The loops trap. actual time and rows are averages per execution, not totals. A node with actual time=0.05..0.08 rows=3 loops=20000 didn't take 0.08 ms: it took 0.08 × 20,000 = 1.6 seconds, and produced 60,000 rows. Always multiply by loops before deciding where the problem is.

At the end of the plan you get Planning Time (how long it took to decide the plan) and Execution Time (how long it took to run it). If they're comparable, you have a trivial query executed a great many times: the case of prepared statements and, often, of 08-04's N+1.

If you take only one thing away from this lesson, let it be this: compare the estimated rows= with the actual rows= on every node. A big divergence —an order of magnitude or more— is the root cause of almost every bad plan.

The planner expected 42 rows and 284,561 came out. With 42 rows, chaining a Nested Loop is the perfect decision; with 284,561, it's a catastrophe. The plan isn't badly chosen: it's well chosen for a reality that doesn't exist.

The four causes, in order of frequency: stale statistics after a load or after growth, fixed with ANALYZE table;; correlated columns the planner assumes are independent, with CREATE STATISTICS; a predicate it can't estimate (a complex expression, a custom function), which has to be rewritten or indexed as an expression; and a sample too small for a very uneven distribution, with ALTER TABLE ... SET STATISTICS. All four are developed in 08-04. When estimated and actual are similar, the plan is usually the best available: if it's still slow, the problem is one of indexes or volume, not of the planner.

Two auxiliary lines always worth looking at: Filter: with its Rows Removed by Filter: (rows read and thrown away — if they're in the millions, an index is missing or the filter should have come earlier) and Index Cond: (the part of the condition the index did resolve; whatever's left in Filter is what had to be checked row by row).

They go in parentheses, separated by commas: EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...;.

Why BUFFERS matters so much: the time depends on whether the machine is busy, on whether the data was in cache and on what else is running at the same time. The blocks read don't. A plan that reads 16,250 blocks will read 16,250 blocks today, tomorrow and on your colleague's laptop. On a shared machine —an integration server, a container, the cloud— the blocks are the only reproducible metric you have.

Here we have to be honest: nothing of this can be demonstrated with GreenStore. Twenty products and forty-seven lines fit in one disk page, and the planner will do a Seq Scan every time and be entirely right. Showing you an invented plan over those tables would be lying to you. So we're going to build a big, reproducible table with generate_series.

⚠️ MODULE 8 TEST BENCH. The orders_large table isn't part of GreenStore's canonical schema (01-06). It has no foreign keys, it isn't related to any other table and no later module uses it. When you finish the lesson, drop it: DROP TABLE orders_large;.

(A sample run. The exact size depends on the version and on type alignment; the order of magnitude doesn't.) Against products' single page, here there are sixteen thousand two hundred and fifty. Now there really is something to optimize.

Let's start with no index: customer_id has none — only id does, from its primary key.

(A sample run: the milliseconds and the costs depend on your machine. What has to be read are the proportions and the node types.) Three things this plan shouts: Parallel Seq Scan, because there's no index and the whole table has to be walked —it's so big that PostgreSQL spreads the work across three processes and collects it with Gather—; Rows Removed by Filter: 666663 per worker, that is, two million rows read and thrown away to keep ten; and Buffers: shared hit=16250, the table's 16,250 pages, every one of them.

Now the index, and the same query:

The comparison, which is the whole module's reason for existing:

And notice Index Cond against Filter: in the second plan there's no Filter. The index didn't filter after reading: it positioned itself directly on the ten rows. That's the difference between searching and discarding.

With a filter that returns a fair number of scattered rows, the planner chooses a middle route:

It reads from the bottom up: the Bitmap Index Scan walks the index and builds a bitmap of the blocks containing matches; the Bitmap Heap Scan reads them in physical order, once each. With 2,010 rows spread across almost 2,000 different blocks, an Index Scan would make 2,010 random jumps; the bitmap turns them into an almost sequential walk of 1,988 blocks. It's the planner's answer to "many rows, but not the whole table".

Come back to the world of 20 rows and create the catalogue's most reasonable index, on products.price:

Seq Scan, with the index freshly created and unused. And the planner is entirely right. Look at its cost: 1.25. It breaks down like this: 1 page × 1.0 (reading the table's only block) + 20 rows × 0.01 (processing each one) + 20 × 0.0025 (evaluating the filter) = 1.25. There's nothing cheaper than that, because the whole table is a single access.

We can force it to use the index to see what would have happened:

A cost of 12.35 against 1.25: ten times more expensive. And the real time, double. The reason is exactly 08-01's: using the index forces the metadata page to be read, the tree to be descended, seven ctids to be obtained and the same table page to be read anyway that the Seq Scan would have read in one go. Three or four accesses to do the work of one. Three conclusions, which close the module's first lesson: an index over a table that fits in one page never pays off, and the planner knows it; enable_seqscan = off is a diagnostic tool, not a solution, and it serves to answer "what would it have done with the index?" and nothing else; and the indexes you created in 08-02 don't speed anything up today — they're correct, they're well designed and they'd be decisive at real volume, but with 20 rows there's no problem to solve.

07-05 claimed that PostgreSQL turns IN (SELECT ...) into a semi-join, and that this is why its performance is equivalent to a JOIN's. Let's check:

Hash Semi Join. You wrote a subquery and the engine ran a join — a special join that stops at the first match of each left-hand row, which is why it returns 12 customers and not 20 rows. The promise was true, and now you don't believe it because you read it: you've seen it. Try swapping IN for NOT IN and you'll see the Semi Join disappear and a filter with a separately executed subquery appear: the visual confirmation of why 07-05 advised against NOT IN.

Dialect note: the concepts travel (sequential scan, index access, join types, estimated against actual), but the vocabulary and the units don't. PostgreSQL's cost isn't comparable with MySQL's or with Oracle's. And there are fundamental differences: SQL Server and MySQL/InnoDB use clustered indexes, where the table is the primary index, so their equivalent of the Index Scan doesn't need the second access 08-01 talked about. SQL Server's SET STATISTICS IO ON is BUFFERS' close relative, and for the same reason: it counts logical reads, which are reproducible.

And four tools that make a hundred-line plan readable: explain.dalibo.com, where you paste the plan (ideally in FORMAT JSON) and it draws it as a tree, highlighting the most expensive node and the wrong estimates; pev2, the same visualiser, embeddable in your own tools; auto_explain, a server module that automatically logs the plan of any query exceeding a time threshold —indispensable for what only fails in production—; and pg_stat_statements, the ranking of which queries to analyse (08-03).

There's a part of performance that doesn't depend on your queries. In PostgreSQL, an UPDATE doesn't modify the row: it writes a new version and marks the old one as dead. A DELETE doesn't delete either: it marks. The dead versions pile up and fatten tables and indexes without contributing anything: that's bloat. Its effects are measurable: a table with 60 % dead space takes up three times the necessary pages, so every Seq Scan reads three times as much, the cache performs at a third and Index Only Scans lose their effectiveness (the Heap Fetches go up).

Under normal conditions, autovacuum takes care of it on its own. It becomes a problem when a table receives a huge number of updates, or when a transaction has been open for hours and prevents the cleanup of dead rows it might still need. And here we reach this module's limit: the reason for all of this —why the engine keeps several versions of each row and why an open transaction blocks the cleanup— is the MVCC model, and it's studied in module 9. For now, keep the operational part: ANALYZE after any bulk load, and a spiking n_dead_tup as an alarm signal.

This is what really has to be taken away from the module. Faced with a slow query:

1. Reproduce and measure. Get the exact query with its real parameters and run it with EXPLAIN (ANALYZE, BUFFERS). With no measurement there's no diagnosis.
2. Locate the guilty node. Look for the node with the most actual time multiplied by loops, and the one reading the most blocks. It's almost always a single one and it's right at the bottom.
3. Compare estimated with actual. If the estimated and actual rows diverge a lot, the problem is the statistics: ANALYZE, and go back to step 1. Don't carry on optimizing over a plan based on false data.
4. Look at what it's reading too much of. A Seq Scan over a large table with an enormous Rows Removed by Filter = an index is missing or the condition isn't sargable. Sort ... Disk = a sorting index or memory is missing. A Nested Loop with thousands of loops = a bad estimate on the left branch.
5. Apply ONE single intervention and measure again. Rewriting (08-04) before indexing (08-02); indexing before changing the architecture. One change at a time, or you won't know which one worked.
6. Verify that the plan changed, not just that the time dropped (it may have dropped because of the cache). And weeks later, check in pg_stat_user_indexes that the index is still being used.

• Running EXPLAIN ANALYZE over an UPDATE or a DELETE with no transaction. It runs it for real. BEGIN ... ROLLBACK, always.
• Reading the plan top to bottom as if it were code. It's read from the inside outwards: the most indented node goes first. And don't forget to multiply by loops: a 0.08 ms node with 20,000 iterations is 1.6 seconds, and it's usually the culprit.
• Comparing the cost of two engines, or of two servers with different configurations. They're arbitrary, relative units.
• Trusting time alone on a shared machine. Use BUFFERS: the blocks read are reproducible. And don't measure just once: the first run fills the cache.
• Leaving enable_seqscan = off switched on. It's for diagnosing, never for production: you force the planner to get the rest of its decisions wrong. And don't optimize without a prior ANALYZE: if the estimates are wrong, everything you build on top is wrong.
• Tip: keep the "before" plan. A file with the EXPLAIN from before the change is the only objective proof that you've improved anything.
• Tip: analyse with real production data. A plan over 100 development rows says absolutely nothing about 20 million, and the problems that only appear at three in the morning can't be reproduced by hand: that's what auto_explain is for.

Read this plan, taken from a real-sized GreenStore (2 million orders, not this lesson's test bench):

1. In what order do the nodes run?
2. What's the main problem and what signal gives it away?
3. How much total time does the Index Scan really consume?
4. Propose two interventions, in the order you'd try them.

Over orders_large, already created and indexed by customer_id, predict before running anything which node the planner will choose in each case, and then check it with EXPLAIN ANALYZE.

Write the full sequence of commands —precautions included— to analyse this DELETE's performance without modifying the data, and say what you'd look for in the plan.

Solution 1

1. The order. First the Seq Scan over orders (the deepest node of the left branch); for each row it produces, the Nested Loop runs the Index Scan over customers; the Nested Loop emits the result.

2. The problem is a catastrophically wrong estimate. The signal is in the Seq Scan: rows=38 estimated against rows=18422 actual, almost 500 times more. With 38 rows, a Nested Loop is the perfect choice; with 18,422 it becomes 18,422 index lookups. And there are two supporting symptoms: Rows Removed by Filter: 1981578 (two million rows are read to keep 0.9 %, so an index on status is missing) and Buffers: shared hit=1204885, more than a million blocks read to return 18,422 rows.

3. The Index Scan's time. actual time is per execution: 0.004 ms × 18,422 loops ≈ 74 ms. In other words, the Index Scan isn't the problem: of the 9,421 ms, 1,842 are the Seq Scan, 74 the Index Scan, and the rest is eaten by the machinery of repeating the loop 18,422 times. Forgetting to multiply by loops is the most common reading mistake.

4. The interventions, in order. First, ANALYZE orders; and look at the plan again: it's free, instantaneous and attacks the root cause — if the estimate becomes correct, the planner will swap the Nested Loop for a Hash Join on its own and the time will fall without touching anything else. Then, if the estimate was already correct, an index for the pending ones, ideally partial: CREATE INDEX ... ON orders (customer_id) WHERE status = 'pending';, because they're less than 1 % and that way the index is tiny (08-02).

Solution 2

Solution 3

The key precaution is the BEGIN ... ROLLBACK: EXPLAIN ANALYZE really runs the DELETE, and without the transaction you'd lose the rows. What to look for in the plan:

• How it locates the rows. With no index on order_date, a Seq Scan with an enormous Rows Removed by Filter. With an index, a Bitmap Heap Scan — and since the data was generated in chronological order, the rows to delete are physically together, which is the best possible scenario.
• How many rows it deletes. The delete node's actual rows: around 285,000 (365 days × 782 a day). And the estimated/actual comparison, as always.
• A consideration this module can't resolve: a DELETE of 285,000 rows leaves 285,000 dead versions that VACUUM will have to clean up, and it holds locks for the whole transaction. That's why mass deletions are done in batches, and why partitioning (08-04) is so attractive for historical data: deleting a whole year is detaching a partition, not running a DELETE.

You close module 8 with the method, not just with the rules:

• EXPLAIN estimates; EXPLAIN ANALYZE runs and measures — and that's why an UPDATE or a DELETE has to be wrapped in BEGIN ... ROLLBACK.
• A plan is a tree read from the inside outwards. cost is in relative units, rows and width are estimates, and actual time and rows are averages per execution: they have to be multiplied by loops.
• The most useful signal of all is the divergence between the estimated and the actual rows. When it's large, the plan is well chosen for a reality that doesn't exist, and the cause is usually ANALYZE.
• You can recognise the fifteen usual nodes, from the Seq Scan to the Gather, and what it means when Rows Removed by Filter, Heap Fetches or Sort Method: external merge Disk appear. And you know that BUFFERS is a better metric than time on any shared machine: the blocks read are reproducible.
• You've seen it measured, not told: over two million rows, the same query goes from 16,250 blocks and ~142 ms to 13 blocks and ~0.09 ms. Whereas over GreenStore's 20 products the index costs ten times more than the Seq Scan, and the planner is right to ignore it. And 07-05's promise is verified with its name in the plan: Hash Semi Join.

And with that module 8 closes. In five lessons you've gone from "I don't know why this is slow" to having a procedure: you know what a B-tree is and why 15 million rows fit in 3 levels; you know GreenStore had eleven indexes nobody created and eleven uncovered foreign keys, because PostgreSQL doesn't index them; you know how to create composite, partial, expression and covering indexes, and how to choose between B-tree, hash, GIN, GiST, BRIN and SP-GiST; you know —and this is the rarest thing to find— when not to index; you know how to rewrite a query to make it sargable, spot an N+1 and update the planner's statistics; and you know how to read an execution plan and decide with data.

But there's an assumption we've kept for eighty-odd lessons without saying it out loud: that we're the database's only user. Everything you've measured here assumes nobody else is reading or writing at the same time, that no row changes while you're querying it and that no UPDATE is competing with yours for the same order line. That's false in any real system: in GreenStore, a customer confirms an order while the warehouse updates the same product's stock and the analyst fires off the monthly report over those very tables. In module 9, Transactions, you'll see what a transaction is and the four ACID guarantees, how they're controlled with BEGIN, COMMIT, ROLLBACK and SAVEPOINT, what isolation levels are and which anomalies each one allows, and how locks and deadlocks work — including, at last, the MVCC model that explains this lesson's bloat and why CREATE INDEX CONCURRENTLY had to exist.