Why Your Query Plan is Lying to You: Understanding Cardinality Estimates

So you ran `EXPLAIN ANALYZE` and got a plan. It looks confident. All those neat rows and cost estimates. You think you know what's slow. Here's the thing: that plan is probably full of it. The optimizer isn't omniscient. It's guessing. And its guesses—called cardinality estimates—are often wildly off. You're making decisions based on a story your database told you, and parts of that story are fiction.

Cardinality Isn't About Birds. It's About Guessing Rows.

Forget the Vatican. In SQL, cardinality is just how many rows the optimizer *thinks* will come out of a step. Join? Filter? Group by? The optimizer makes a guess for each one. Actually, it's not a guess. It's a calculation based on statistics. But those stats are a simplified snapshot of your data. If they're old or wrong, the guess is garbage. And the whole cost-based model tumbles down.

How Your Database Pulls Numbers From a Hat

Those optimizer statistics. They're like a cheatsheet. Mostly, it's histograms and null counts. Sounds smart. But imagine summarizing a million-row table with a few hundred buckets. You lose details. Correlations between columns? Gone. The optimizer assumes your `status` column is independent from your `created_date`. In the real world, that's rarely true. So it guesses 10,000 rows will pass the filter. Only 10 do. The plan built for 10,000 rows chokes on 10.

The Classic Lies Your Plan Tells

Watch for these. A hash join chosen when a nested loop would be blazing fast. Why? The estimate said billions of rows, so it picked the "big" join. Reality: a handful. Or a table scan instead of an index seek. The optimizer thought your `WHERE` clause would return 80% of the table. It returned 0.1%. But the stats said the data was uniformly distributed. It wasn't. Your plan is a house of cards built on bad intel.

Becoming a Query Plan Skeptic

Don't just read the plan. Interrogate it. Compare `Estimate Rows` vs `Actual Rows` in `EXPLAIN ANALYZE`. See a huge discrepancy? That's your smoking gun. That's where the lie is. The cost percentages are derived from those row guesses. If the guess is wrong, the costs are fantasy. Your job isn't to accept the plan. Your job is to ask, "Why did you think *that*?" Follow the evidence back to the statistics.

Fixing the Guesswork (Yes, You Can)

Update statistics. Regularly. On big tables after big imports. Use sample rates that make sense. Maybe try filtered stats for those pesky, uneven date ranges. Sometimes you have to rewrite the query. Break it up. Use a CTE to materialize an intermediate result and kill a bad guess. In extreme cases, a well-placed hint can steer the optimizer away from a cliff. It's not cheating. It's course-correcting for a system that's working with outdated maps.