Where the estimate comes from
ANALYZE takes a snapshot. Everything the planner knows is from that moment, and the gap between it and now is where bad plans come from.
12 min · runs against the events dataset
The planner does not look at your table. It looks at statistics somebody collected the last time ANALYZE ran: how many rows, how many distinct values in each column, which values are common enough to name, and a histogram of everything else.
The most-common-values list is what makes an equality estimate good. If the value you asked about is in it, its frequency is known and the estimate is right. If it is not, the planner spreads the remaining probability over the remaining distinct values — which is a far better guess than dividing by the distinct count on a skewed column.
A rare value. Compare the estimate to the actual.
plan
| node | est | actual | loops | pages | share |
|---|---|---|---|---|---|
| Bitmap Heap Scan on eventsRecheck Cond: (kind = 'error') | 647 | 827.9x under | 1 | 82 | |
| → Bitmap Index Scan on events_kind_happened_idxIndex Cond: (kind = 'error') | 647 | 827.9x under | 1 | 4 |
A common one. Same column, very different plan.
plan
| node | est | actual | loops | pages | share |
|---|---|---|---|---|---|
| Seq Scan on eventsFilter: (kind = 'click')Rows Removed by Filter: 4,971 | 3K | 3K | 1 | 91 |
Correlated predicates
Ask for two conditions and the planner multiplies their selectivities, as though knowing one told you nothing about the other. When the columns are correlated — a city and its country, a status and its date — that assumption is badly wrong, and it is wrong in the direction that matters: the estimate comes out far too small, and the planner picks a nested loop for what turns out to be a large join.
Every row with this actor has this email. The planner does not know that.
plan
| node | est | actual | loops | pages | share |
|---|---|---|---|---|---|
| Bitmap Heap Scan on eventsRecheck Cond: (actor_id = 42)Filter: (email = '[email protected]') | 1 | 2020x over | 1 | 20 | |
| → Bitmap Index Scan on events_actor_idxIndex Cond: (actor_id = 42) | 20 | 20 | 1 | 3 |
Did it land?
- The rare value and the common one
Return the ids of every 'error' event, oldest first. There are far fewer of these than of any other kind, and the plan should reflect that.