Lesson 26 · Senior PostgreSQL backend engineering

Planner misestimates and cardinality

Why Postgres picks the wrong plan, and how row-count thinking explains more than “the planner is dumb.”

Your win: explain why planner mistakes usually start with row-count mistakes, and describe the senior debugging path from misestimate to a better plan.

In plain English Plain English: the planner is guessing how many rows will survive each step. Bad guesses create bad plans.

The problem: a plan built on the wrong picture of the data

When engineers say “Postgres chose a bad plan,” what they usually mean is “Postgres believed the data shape was different from reality.” That is a much better starting point for debugging, because now you can ask: which estimate was wrong, and what plan choice followed from it?

The proverb to memorise Costs come after cardinality. If the planner thinks ten rows will survive but the real answer is ten million, many downstream choices become wrong for understandable reasons.

Where misestimates come from

stale or coarse statistics
skewed value distributions
column correlation the planner cannot model well enough
complex predicates, expressions, or joins
parameter-sensitive queries where one input value is tiny and another is huge

The important habit is to stop treating the planner like a black box. It is following a model. Your job is to find where the model no longer matches reality.

Anchor — where this bites in backend code In this repo, tenant filtering, soft deletes, JSONB / array conditions, and hand-built optional predicates are exactly the kind of patterns that can shift selectivity dramatically between requests.

Common mistake Looking only at total execution time without comparing estimated rows to actual rows node by node in EXPLAIN ANALYZE.

Planner statistics + Using EXPLAIN

These are the official sources behind the estimate-versus-actual workflow. Read them with one question in mind: “What did the planner think would happen?”

→ planner statistics
→ Using EXPLAIN

Check yourself (from memory)

Q1. Many bad plans start with…

Wrong cardinality pushes the planner toward the wrong cost model.

What is cardinality thinking in one sentence?

recall, then click to reveal

Reasoning about how many rows survive each step of a plan, because the planner’s choices depend heavily on those row-count expectations.

Want a worked example of an estimate-vs-actual mismatch and the fix it suggests, node by node? Ask me.

Sources. Planner Statistics; Using EXPLAIN.