Reading a plan
A plan is a tree of numbers. Estimated against actual is one column, and it localises more slow queries than everything else put together.
14 min · runs against the events dataset
Read a plan from the inside out: the deepest node runs first and hands its rows upward. Each node reports what the planner expected and, under ANALYZE, what actually happened. The gap between those two numbers is the diagnosis.
Read it upward: scan, group, sort, slice.
plan
| node | est | actual | loops | pages | share |
|---|---|---|---|---|---|
| Limit | 5 | 5 | 1 | 0 | |
| → SortSort Key: count(*) DESCSort Method: quicksort Memory: 14kB | 65 | 513x under | 1 | 0 | |
| → HashAggregateGroup Key: actor_id | 65 | 3445.3x over | 1 | 0 | |
| → Bitmap Heap Scan on eventsRecheck Cond: (kind = 'purchase') | 647 | 344 | 1 | 91 | |
| → Bitmap Index Scan on events_kind_happened_idxIndex Cond: (kind = 'purchase') | 647 | 344 | 1 | 6 |
rows=is the estimate.actual rows=is what came out.loops=is how many times the node ran. Anything above one means something above it re-ran it, once per row.Rows Removed by Filteris how many rows were read and thrown away — the single most useful line in the output.Pages Readis ours rather than Postgres's, and it is the number this whole course turns on.
When the estimate is wrong
A plan is only as good as the row counts it was built from. Estimate one row and get four hundred thousand, and every decision above that node — which join algorithm, which order, whether to sort — was made on a false premise. The plan is not wrong; the number it was reasoning from was.
Two causes account for almost all of it. The statistics are out of date, so the planner is describing a table that no longer exists. Or the predicates are correlated, and the planner multiplied their selectivities as if they were independent.
Did it land?
- Read it before you run it
Return the actor_id and event count for the five busiest actors among 'purchase' events, busiest first then by actor_id. Call the count events.