seniorCloud Postgres platformPro answerTested in a controlled lab, with the output captured

Take me from that sample to a query plan, what does ANALYZE compute, and how does the planner use it?

Take a query with WHERE status = 'shipped'. If 'shipped' is in the column's most-common-values list, the planner reads its stored frequency and multiplies it by the estimated row count. That row estimate drives the scan type, the join order and whether parallelism is worth trying. Listing the statistics is the warm-up. The question is about this arithmetic, and about what you do when a plan gets worse and nobody changed the SQL. This page gives a 90-second way to narrate the chain, the two statistics that carry most of the weight, and the failure that a bigger sample cannot fix.

What the interviewer is scoring

Name the statistics that drive the estimate: n_distinct and the most-common-values list. Connect them to selectivity multiplied by the estimated row count.

In short: They want the chain from raw sample rows to per-column statistics, and then how the planner turns a WHERE clause into a row estimate.

ProFrom a real interview loop

The spoken answer, backed by captured output

The question, the intro, what the interviewer is scoring, the reasoning outline, what you would verify and the follow-up probes are all open above. Pro unlocks the spoken answer to “Take me from that sample to a query plan, what does ANALYZE compute, and how does the planner use it?”, the reasoning behind it, and the verification that backs it.
  • The full 90-second answer, written first person, the way you would actually say it
  • Why: the mechanism, the decision points, and where the claim stops
  • Verification steps with output captured verbatim from a controlled PostgreSQL lab run.
  • Worked responses to the 3 follow-up probes listed above, plus the traps that lose the point
Get instant access — 7-Day Pass, $12

One-time payment. Does not auto-renew.

Compare plans

How to reason through it

  • List what ANALYZE computes per column: null fraction, average width, n_distinct, most-common values and their frequencies, a histogram, and physical correlation.
  • Say where it lands: the per-column statistics catalog, surfaced readably through the pg_stats view.
  • Explain the planner's move: predicate → selectivity → selectivity × estimated rows = estimated output rows.
  • Note that a wrong n_distinct or a missing most-common value is what makes estimates, and plans, go bad.
  • Close on the diagnostic: find the lowest node where estimated and actual rows diverge.

What I would verify

  • Read the plan from the bottom and find the first node where estimated and actual rows separate materially.
  • Check whether the filtered value appears in the column's most-common-values list.
  • Compare the distinct estimate against reality when the predicate is an equality on a low-cardinality column.
  • Test whether the error only appears when two predicates combine; that points at correlation, not sample size.

Follow-ups they push on

  • The estimate is out by two orders of magnitude on a single equality predicate. Where do you look?
  • Two predicates, each estimated correctly, but combined the estimate collapses. Why?
  • Can you override any of these numbers?

The probes are open. Pro carries the spoken answer, the reasoning behind it, and the verification steps, including a worked response to each of these.

Concepts tested

pg_statisticn_distinctMCV & histogramSelectivity estimation

Learn it, run it, then say it

Three steps, in order. Nothing here is a detour.

Questions that go with this one