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?

This is the follow-on question, and it is where a memorised answer runs out. Listing the statistics ANALYZE computes is a warm-up; the interviewer is waiting for the arithmetic that turns a WHERE clause into a row count, because that number is what chooses the scan type, the join order, and whether parallelism is worth attempting. The strongest version of this answer ends somewhere useful: not with a description of the catalog, but with what you would actually do when a plan degrades and nobody changed any SQL. This page covers how to narrate the chain in about ninety seconds, which two statistics carry most of the weight, and how to name the failure mode that a bigger sample cannot fix.

What the interviewer is scoring

Score points for naming the statistics that actually drive the estimate, n_distinct and the most-common-values list, and for connecting them to a 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
  • How I reason through it: the mechanism, the decision points, and where the claim stops
  • Verification steps with output captured verbatim from a controlled PostgreSQL 18 lab run.
  • Worked responses to the 3 follow-up probes listed above, plus the traps that lose the point

Card required. Cancel before day 7 and you are not charged.

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