7.2.5 — Cost and Cardinality: How the Planner Decides

Chapter 7.2.3 said the sentence that matters most when reading a plan: almost every bad plan is a bad estimate. This page opens the box. By the end you will be able to look at rows=1000 in a plan, work out where that number came from, work out why it is wrong, and fix the cause instead of adding an index and hoping.

Start with the query from that chapter.

SELECT o.id, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'refunded'
ORDER BY o.placed_at DESC
LIMIT 1000;

The engine has, honestly, dozens of ways to run this. Scan all nine million orders and filter. Use an index on status. Use an index on placed_at and filter as it goes. Join customers first or orders first. Hash the customers or loop over them. Every one of those returns exactly the same rows. The planner's job is to guess which one is cheapest without running any of them, and it does that with two numbers computed for every step: how many rows will come out, and how much work it takes to produce them.

1. Cardinality is the row count, and it is a guess

Cardinality means "how many". In a plan it means the number of rows a node produces. rows=1000 in EXPLAIN output is a cardinality estimate.

The word gets used for two related things and it is worth separating them once, because interviews mix them up. The cardinality of a table is its row count. The cardinality of a column is how many distinct values it holds — a status column with five possible values has low cardinality, an email column where every row differs has high cardinality. Both meanings are in play here: the planner starts from table cardinality and uses column cardinality to work out how much a filter cuts it down.

Selectivity is the fraction that survives a condition, a number between 0 and 1. If status = 'refunded' is true for 2 rows in every 10,000, its selectivity is 0.0002. Then:

\text{estimated rows} = \text{table rows} \times \text{selectivity}

Read aloud: the rows you expect equals the rows you have, times the fraction that get through. With nine million orders and a selectivity of 0.0002, the estimate is 1,800 rows. That is the entire calculation, and every difficulty in this chapter is about getting that fraction right.

Why the estimate matters more than it looks. A wrong row count is not a wrong answer — the query still returns correct rows. It is a wrong decision. If the planner thinks a step produces 10 rows and it produces 500,000, it will happily choose a nested loop that now runs half a million times. The query goes from milliseconds to minutes, and the query text is blameless.

2. Where the fraction comes from: the statistics table

The planner does not look at your data when planning. That would mean reading the table to decide how to read the table. Instead it reads a small summary collected earlier by ANALYZE, and everything it believes comes from there.

SELECT attname, null_frac, n_distinct, most_common_vals, most_common_freqs, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

attname           | status
null_frac         | 0
n_distinct        | 5
most_common_vals  | {shipped,paid,pending,cancelled,refunded}
most_common_freqs | {0.61,0.28,0.09,0.0198,0.0002}
correlation       | 0.31

Each of those five numbers does a specific job.

null_frac is the fraction of rows where the column is null. It is how WHERE col IS NULL gets an estimate directly, and why IS NOT NULL estimates as one minus it.

n_distinct is how many different values the column holds — its column cardinality. Here, five. A negative value means something different and catches people out: it is a fraction of the table. n_distinct = -1 means every row is unique, which is what you see on a primary key or an email column. ANALYZE uses -1-style fractions when it believes the count grows with the table, and a fixed number when it believes the count is fixed no matter how many rows arrive.

most_common_vals and most_common_freqs are the MCV list: the values that appear most often, with the exact fraction of rows each one covers. This pair is doing most of the work in this chapter. When your WHERE value is in the MCV list, the planner is not estimating at all — it is reading the measured frequency. status = 'refunded' has frequency 0.0002 recorded, so the estimate is 9,000,000 × 0.0002 = 1,800 rows, and it will be almost exactly right.

correlation is how closely the physical order of rows on disk matches the sorted order of this column, from −1 to +1. A column of insert timestamps on an append-only table is close to +1, because later rows sit later in the file. This number does not affect how many rows the planner expects — it affects how expensive it thinks reading them is, because reading 1,000 rows that sit near each other is far cheaper than 1,000 rows scattered across the whole table. Section 5 uses it.

When the value is not in the MCV list, the planner falls back to spreading the remainder evenly:

\text{selectivity} = \frac{1 - \sum \text{MCV frequencies}}{n\_distinct - \text{number of MCVs}}

In words: take the fraction of rows not covered by any common value, and split it equally among all the remaining distinct values. This is the assumption that quietly breaks on skewed data, and section 6 shows what to do about it.

For ranges, there is a histogram. most_common_vals handles equality on common values; a histogram handles <, > and BETWEEN. ANALYZE sorts a sample of the column and records the boundary values that cut it into equal-sized buckets — by default 100 of them, so each bucket holds 1% of the rows, whatever the values are.

The buckets have equal height by construction — each holds the same share of rows — so their widths tell you where the data is dense. A range condition is estimated by counting the buckets it spans.

So WHERE total_minor > 3400 lands on a bucket boundary in that figure with three buckets above it, and the estimate is 30% of the rows. When the value falls inside a bucket rather than on a boundary, the planner interpolates — it assumes the values are spread evenly inside that bucket and takes the matching proportion of it. That is the second everyday assumption, and it is the reason estimates on a column with a few enormous outliers can be poor: one bucket covers a giant range and the planner treats it as smooth.

default_statistics_target controls the resolution — 100 by default, meaning up to 100 MCV entries and 100 histogram buckets. Raising it for one skewed column gives the planner a finer picture at the cost of a slower ANALYZE and a slightly slower planning step:

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

3. Combining conditions, and the assumption that breaks

Real queries have several conditions, and the planner must turn several selectivities into one. It does that with three rules from ordinary probability.

AND multiplies. If condition A passes 10% of rows and condition B passes 20%, the planner estimates 0.10 × 0.20 = 0.02, so 2% of rows survive both.

OR adds, then subtracts the overlap. s_A + s_B - s_A s_B, so 0.10 + 0.20 − 0.02 = 0.28.

NOT is one minus. A condition passing 10% means its negation passes 90%.

Multiplying for AND is only correct if the two columns are independent, meaning knowing one tells you nothing about the other. That is the single most consequential assumption in the whole planner, and real data violates it constantly.

SELECT * FROM addresses WHERE city = 'Paris' AND country = 'France';

Suppose 2% of addresses are in Paris and 5% are in France. The planner multiplies: 0.02 × 0.05 = 0.001, so on ten million addresses it expects 10,000 rows. The truth is that essentially every Paris address is in France, so the real answer is the 200,000 Paris rows. The estimate is twenty times too small, and every choice made above that node — join algorithm, join order, whether to sort — was made on a number that is off by a factor of twenty.

This is not a bug. It is a deliberate simplification, because storing the joint distribution of every pair of columns would be enormous. The fix is to tell the planner about the specific pair you care about, with an extended statistics object:

CREATE STATISTICS addr_city_country (dependencies, ndistinct, mcv)
  ON city, country FROM addresses;                              -- (1)
ANALYZE addresses;                                              -- (2)

(1) Three kinds of multi-column summary, and it is worth knowing what each buys. dependencies records "knowing city tells you country with probability 0.99", which is exactly the functional dependency idea from Chapter 7.1 measured on real data rather than declared. ndistinct records how many distinct combinations of (city, country) exist, which fixes GROUP BY city, country estimates that would otherwise be the product of the two counts. mcv records the most common pairs with their real frequencies, which is the strongest of the three because it needs no assumption at all for the common cases. (2) The statistics object is only a declaration of intent — nothing is measured until ANALYZE runs. Forgetting this line is why the fix sometimes appears not to work.

4. Join cardinality, and why errors grow as they climb

Joins are where small estimation errors turn into catastrophic plans, because a join's output estimate is built from its inputs' estimates.

For an equality join between A and B on one column, the standard formula is:

|A \bowtie B| = \frac{|A| \times |B|}{\max(n\_distinct_A,\; n\_distinct_B)}

Read aloud: the number of rows out of the join equals the number of rows in A times the number of rows in B, divided by the larger of the two columns' distinct-value counts.

Why divide by the larger one? Think about what each row of A can match. If customers.id has 90,000 distinct values and orders.customer_id has 90,000 distinct values, then one order row matches the customers that share its customer_id — and since id is a primary key, that is exactly one. Dividing by the larger distinct count is the formula's way of saying "the side with more distinct values is the more selective one, so use it". Work it through:

• orders has 9,000,000 rows, n_distinct(customer_id) = 90,000.
• customers has 90,000 rows, n_distinct(id) = 90,000.
• Estimate = (9,000,000 × 90,000) / 90,000 = 9,000,000 rows.

Which is right: joining every order to its one customer gives you back one row per order. The formula quietly encoded "a foreign key join does not multiply rows".

Now apply the filter first, the way the real query does. status = 'refunded' cut orders to 1,800 rows, so:

• Estimate = (1,800 × 90,000) / 90,000 = 1,800 rows out of the join.

Here is the compounding problem. Suppose the leaf estimate had been wrong — the MCV list was stale and refunded is now 2% of orders rather than 0.02%, so the truth is 180,000 rows rather than 1,800. The join estimate inherits the error exactly: it also says 1,800 when the truth is 180,000. The planner sees a tiny outer input and picks a nested loop with an index lookup per row, which is a superb plan for 1,800 rows and a disaster for 180,000. One stale statistic at a leaf becomes a hundred-fold error at the top of a three-join query, because each level multiplies the error along rather than correcting it. This is why Chapter 7.2.3 tells you to look for the lowest node where estimated and actual diverge: that node is the cause, and everything above it is a symptom.

Multi-column joins make it worse, because the planner combines the two columns' selectivities by multiplying, which brings back the independence assumption from section 3. A join on (tenant_id, order_id) where the two are strongly related will be underestimated for the same reason Paris and France were.

5. Cost: turning row counts into a number to compare

Once the planner knows how many rows a step produces, it needs to price the step. Cost is an abstract number, not milliseconds. Its unit is defined by convention: reading one page sequentially from disk costs 1.0, and everything else is priced relative to that.

The five constants that matter, with their PostgreSQL defaults:

Setting	Default	What it prices
seq_page_cost	1.0	One page read in order
random_page_cost	4.0	One page read out of order
cpu_tuple_cost	0.01	Processing one row
cpu_index_tuple_cost	0.005	Processing one index entry
cpu_operator_cost	0.0025	Evaluating one operator or function

Two things jump out. A random read is priced at four times a sequential read, which is the single most important ratio in the model — it is why an index that has to jump around the table loses to a straight scan once enough rows match. And CPU work is priced at a hundredth of a page read or less, which encodes the assumption that this is an input-output-bound system where fetching data dominates.

The sequential scan cost, derived. Our orders table is 9,000,000 rows in 90,000 pages, so about 100 rows per page. Scanning it with one filter condition costs:

\text{cost} = \underbrace{90{,}000 \times 1.0}_{\text{read every page}} + \underbrace{9{,}000{,}000 \times 0.01}_{\text{handle every row}} + \underbrace{9{,}000{,}000 \times 0.0025}_{\text{test the filter once per row}}

= 90{,}000 + 90{,}000 + 22{,}500 = \mathbf{202{,}500}

That is where a number like cost=0.00..202500.00 in a plan comes from. It is not a mystery unit — it is pages plus rows, weighted. Notice the shape: this cost does not depend at all on how many rows the filter keeps. A sequential scan costs the same whether the filter matches everything or nothing.

The index scan cost, derived. Suppose an index on status and a query matching N rows. The engine descends the tree, walks the matching leaf entries, and then fetches each matching row from the table. Simplified to its dominant terms:

\text{cost} \approx \underbrace{N \times 4.0}_{\text{one random page fetch per matched row}} + \underbrace{N \times (0.005 + 0.01 + 0.0025)}_{\text{index entry, row, filter}}

For N = 1{,}800: about 1,800 × 4.0 = 7,200 plus 1,800 × 0.0175 ≈ 32, so roughly 7,230. Against 202,500 for the scan, the index wins by a factor of 28, which is why the plan in Chapter 7.2.3 dropped from 4,188 ms to 1.4 ms.

Now find the crossing point, because that is the whole game. Set the two equal and solve for N:

4.0175 \, N = 202{,}500 \quad\Longrightarrow\quad N \approx 50{,}400 \text{ rows}

50,400 rows out of 9,000,000 is 0.56% of the table. So on this table, an index stops being worth it somewhere below one percent selectivity. People remember the rule as "indexes stop helping above a few percent", and now you can see where the number comes from and why it varies: it depends entirely on how many rows fit in a page. A narrow table with 400 rows per page has a much lower crossing point than a wide table with 5 rows per page, because for the wide table each random fetch brings back proportionally more of what you wanted.

The sequential scan's cost is flat because it reads everything regardless. The index scan's cost climbs with the number of matched rows. There is always a crossing point, and the planner's whole job at this node is to work out which side of it you are on.

The real formula is kinder to the index than the simple one, and the reason is worth a sentence. Once you have fetched a few thousand random rows from a 90,000-page table, some of the pages you need are already in memory from an earlier fetch, so you do not pay for them twice. PostgreSQL uses a formula from a 1989 paper by Mackert and Lohman that estimates how many distinct pages N random row fetches actually touch, which is well below N once N approaches the page count. That pushes the true crossing point up — for this table, nearer 0.8% than 0.56% — and it explains the bitmap scan's existence: by collecting all the row locations first and sorting them into physical order, a bitmap heap scan converts random reads into a mostly-sequential sweep and therefore prices somewhere between the two lines. That is the green region in the figure.

random_page_cost is the one setting here worth changing. Its default of 4.0 describes a spinning disk, where moving the head is genuinely expensive. On solid-state storage a random read costs barely more than a sequential one, so the default makes the planner far too shy of indexes. Setting it to 1.1 on flash storage is standard practice and routinely fixes "why is it not using my index" without touching a single query.

ALTER SYSTEM SET random_page_cost = 1.1;   -- typical for SSD or cloud block storage
SELECT pg_reload_conf();

And this is what cost=48211.10..48213.60 in a plan means, finally in full. The first number is the start-up cost: the work done before the first row can be handed upward. The second is the total cost for all rows. For a sequential scan the start-up cost is near zero, because the first row is available immediately. For a sort or a hash build it is nearly the whole cost, because nothing can be returned until the last input row has arrived. That is why a LIMIT above a sort saves almost nothing while a LIMIT above an index scan saves nearly everything, and why the planner treats a query with LIMIT completely differently: with a limit it minimises start-up + (limit/rows) × (total − start-up) rather than the total, so it will deliberately choose a plan with a higher total cost that returns the first rows sooner.

6. Why an estimate goes wrong, and what to do about each cause

Five causes cover almost everything you will meet. The fix differs for each, which is why "just add an index" so often fails.

Stale statistics. The table changed a lot since the last ANALYZE. Classic trigger: a bulk load followed immediately by a query, so the planner is describing yesterday's table. Fix: ANALYZE tablename; at the end of every bulk load. Check when it last ran:

SELECT relname, last_analyze, last_autoanalyze, n_live_tup, n_mod_since_analyze
FROM pg_stat_user_tables WHERE relname = 'orders';

n_mod_since_analyze is the number of rows changed since the summary was built. If it is a large share of n_live_tup, your statistics are fiction.

Correlated columns. The independence assumption from section 3. Fix: CREATE STATISTICS.

Skew that the MCV list misses. One tenant owns 60% of the rows but there are 400 tenants and the default MCV list holds 100 values, so the big tenant might be captured while the medium ones are averaged into a single flat estimate. Every query for a medium tenant gets the average, which is wrong in both directions depending on the tenant. Fix: raise SET STATISTICS on that column so the MCV list covers more values.

Expressions and functions the planner cannot see through. WHERE lower(email) = $1 has no statistics at all, because statistics are gathered per column, not per expression. The planner falls back to a hard-coded default guess — 0.5% for an equality on an unknown expression. Fix: create the expression index, which in PostgreSQL also causes ANALYZE to gather statistics on the expression, so you get an estimate as well as an access path. That second effect is not widely known and is often the larger win.

Values outside the histogram. You query WHERE placed_at > now() - interval '1 hour' on a table whose last ANALYZE ran yesterday. Every row from today is beyond the top histogram boundary, so the planner estimates almost zero rows and picks a plan for a tiny result. This is the single most common cause of a query that is fast all day and terrible right after a busy period, and it has a name: the ascending-key problem. Fix: analyse more often on append-heavy tables by lowering autovacuum_analyze_scale_factor for that table.

7. Choosing the join order, and the limit on searching

With three tables there are 12 possible join orders. With six there are thousands. With ten the number of shapes is in the millions. The planner cannot try them all, and the way it copes is worth knowing because it explains a class of "why did it suddenly get slow" that no index fixes.

PostgreSQL uses dynamic programming, the approach from IBM's System R in 1979 and the same technique as Chapter 4.22: find the cheapest plan for every pair of tables, then use those to build the cheapest plan for every triple, and so on. Solving the small problems once and reusing the answers turns a factorial search into an exponential one, which is survivable up to about a dozen tables.

Above that it switches to a genetic algorithm — it generates random join orders, keeps the cheap ones, mixes them, and repeats. A genetic search is not deterministic, so the same query planned twice can get different plans. The switch happens at geqo_threshold, which defaults to 12 tables.

Two other limits shape what is even considered. join_collapse_limit (default 8) is the number of tables the planner will flatten into one big optimisation problem; beyond that it keeps your explicit JOIN nesting as written rather than reordering freely. from_collapse_limit (default 8) does the same for subqueries pulled up into the parent query. So on a query joining fifteen tables, the order you wrote the joins in starts to matter, which is not true at all on a query joining four.

The practical consequence: a report query that grows from eight joins to nine can change plans dramatically, not because the data changed but because the planner crossed a limit and stopped searching as hard. When a big analytical query is unstable, splitting it into two steps with a materialized intermediate result is often the real fix, and Chapter 7.2.6 covers the tool for that.

8. Plan caching: the same query, planned once, used a thousand times

Planning is not free. For a simple query it is a millisecond or less, but a query run 50,000 times a second cannot afford to be planned 50,000 times a second. The answer is a prepared statement: parse and plan once, then execute repeatedly with different parameter values.

PREPARE recent_for_customer (bigint) AS
  SELECT id, placed_at FROM orders
  WHERE customer_id = $1 ORDER BY placed_at DESC LIMIT 20;

EXECUTE recent_for_customer(42);

And this creates a genuine dilemma, which PostgreSQL resolves in an unusual and clever way. A plan made without knowing the parameter value cannot use the MCV list, so it must assume an average case. A plan made with the value is better but must be rebuilt each time. Which is right depends on the data: if customer_id values are all similar in frequency, one plan serves everybody; if one customer has two million orders and the rest have four, no single plan is right.

PostgreSQL's resolution: plan with the actual values for the first five executions, remember what those custom plans cost on average, then build one generic plan and compare. If the generic plan's estimated cost is no worse than the average custom plan, switch to it permanently and stop planning. Otherwise keep planning each time. You can see and override the decision:

SET plan_cache_mode = 'force_custom_plan';    -- always re-plan with real values
SET plan_cache_mode = 'force_generic_plan';   -- plan once, never again
SET plan_cache_mode = 'auto';                 -- the five-execution rule (default)

Where this bites in real systems: a heavily skewed column plus a prepared statement — which every ORM and every connection pooler uses under the hood — gives you a plan that is excellent for the average customer and catastrophic for the biggest one. The symptom is one specific tenant or one specific product being slow while everything else is fine, with an identical query. force_custom_plan on that statement is the direct fix, and paying a millisecond of planning to avoid a two-second scan is an easy trade.

9. Why PostgreSQL has no hints, and what to do instead

Oracle, SQL Server and MySQL all let you write instructions into the query telling the planner what to do — /*+ INDEX(orders idx_status) */ and similar. PostgreSQL deliberately does not, and the reasoning is worth understanding because it shapes how you fix things here.

The argument against hints: a hint is a decision frozen at the moment somebody wrote it, based on the data volume of that day. Data grows, distributions shift, and the hint keeps forcing yesterday's plan long after it stopped being right — while the planner, which would have adapted, has been told not to. Hints also spread. One hint fixes one query; a year later there are two hundred, nobody knows which are still needed, and upgrading the database becomes frightening.

The argument for hints: sometimes you know something the planner cannot, you need the problem fixed in the next ten minutes, and rewriting the application is not an option.

What you actually have in PostgreSQL:

SET enable_seqscan = off;      -- diagnostic only, never in application code
EXPLAIN ANALYZE SELECT …;
SET enable_seqscan = on;

These enable_* switches do not truly disable anything — they add an enormous constant to that node type's cost so the planner avoids it if any alternative exists. Their real use is diagnosis, not treatment. Turn off the sequential scan, see what the index plan actually costs and how long it actually takes, and you learn whether the planner's choice was wrong or its estimate was wrong. Those need completely different fixes, and this is the fastest way to tell them apart. If the index plan is genuinely faster, the estimate is the problem, and section 6 lists the causes.

For the rare case where you genuinely need a hint, the pg_hint_plan extension adds Oracle-style hints as comments. Treat it as a splint: useful, and something you expect to remove.

10. Reading it all in one plan

Back to the opening query, with the numbers this page has built.

Limit  (cost=0.43..1247.88 rows=1000 width=48) (actual rows=1000 loops=1)
  ->  Nested Loop  (cost=0.43..2245.19 rows=1800 width=48) (actual rows=1000 loops=1)
        ->  Index Scan using orders_refunded_idx on orders o
              (cost=0.43..905.19 rows=1800 width=16) (actual rows=1000 loops=1)
        ->  Index Scan using customers_pkey on customers c
              (cost=0.29..0.74 rows=1 width=40) (actual rows=1 loops=1000)

Reading it with everything from this page: the leaf estimate of rows=1800 is 9,000,000 × 0.0002, straight from the MCV frequency for refunded. The join estimate is also 1,800 because dividing by max(n_distinct) correctly predicts that a foreign-key join does not multiply rows. The inner node's rows=1 is a primary-key lookup, which is n_distinct = -1 telling the planner every value is unique. The nested loop was chosen because 1,800 outer rows × one cheap indexed lookup each is far below the cost of hashing 90,000 customers. And the Limit node's cost of 1,247 out of the loop's 2,245 is the planner scaling the total by 1000/1800, because a nested loop has almost no start-up cost and can stop early.

Every number in that plan is now derivable. That is the difference between reading a plan and guessing at one.

What the interviewer will push on

"What is cardinality, and why does the planner care?" The number of rows a step produces. It matters because every downstream choice — join algorithm, join order, whether to sort — is made from it, so an estimate that is off by a hundred does not give a wrong answer, it gives a plan that is a hundred times too slow. The tell is separating table cardinality from column cardinality and knowing selectivity is the fraction that links them.

"Where does the planner get its numbers?" From pg_stats, built by ANALYZE on a sample: null fraction, distinct count, an MCV list with measured frequencies, a histogram with equal-population buckets, and the physical-order correlation. Then the detail that shows you have looked: if your value is in the MCV list the frequency is measured, not estimated, which is why estimates on common values are excellent and on rare ones are guesses.

"Why would the planner estimate 10,000 rows when there are 200,000?" Correlated columns. It multiplies the two selectivities because it assumes independence, and city and country are not independent. Fix with CREATE STATISTICS … (dependencies, mcv) plus an ANALYZE. The wrong answer stops at "the statistics are stale" — that is a different cause with a different fix, and naming both distinguishes you.

"What is cost measured in?" Nothing — it is relative, anchored so that one sequential page read is 1.0. Then give the two ratios that decide everything: a random read is priced at 4.0, and CPU work per row at 0.01, which is why random_page_cost = 1.1 on flash storage is the standard fix for a planner that refuses to use indexes.

"At what point does a sequential scan beat an index?" Derive it rather than quote it: the scan's cost is flat, the index scan's cost rises with matched rows, so they cross. For a table with 100 rows per page the crossing is under 1%. The tell is knowing the crossing depends on rows per page and that the bitmap scan exists to cover the middle by sorting row locations into physical order.

"Same query, one customer is slow, everyone else fine. Why?" A prepared statement that settled on a generic plan, which is right for an average customer and wrong for the one with two million orders. Explain the five-execution rule, then plan_cache_mode = 'force_custom_plan'. Almost nobody gets this one, and it is a real production pattern.

One thing to volunteer: mention the ascending-key problem — a query filtering on created_at > now() - interval '1 hour' on a table analysed yesterday, where every matching row sits beyond the top histogram bucket, so the planner estimates near-zero rows and chooses a plan for a tiny result. It explains "fast all day, terrible after a busy hour" better than anything else, and the fix is analysing that table more often rather than touching the query.

Recall

• Cardinality is a row count; selectivity is the fraction a condition keeps. Estimated rows = table rows × selectivity, and every plan decision above a node rests on that one number.
• Statistics live in pg_stats, built by ANALYZE on a sample: null_frac, n_distinct (negative means a fraction of the table), the MCV list with measured frequencies, a histogram of equal-population buckets, and correlation for physical ordering.
• A value in the MCV list is measured, not estimated. Outside it, the remaining fraction is split evenly among the remaining distinct values — the assumption that skew breaks.
• AND multiplies selectivities, which assumes the columns are independent. Paris and France are not, so the estimate comes out twenty times too small. Fix with CREATE STATISTICS (dependencies, ndistinct, mcv) and then ANALYZE.
• Equi-join estimate is |A| \times |B| / \max(n\_distinct), which correctly predicts that a foreign-key join does not multiply rows. Errors at a leaf are inherited, not corrected, so find the lowest node where estimate and actual diverge.
• Cost is relative, anchored at seq_page_cost = 1.0. random_page_cost = 4.0 describes a spinning disk — set it near 1.1 on flash storage and half the "why no index" problems disappear.
• A sequential scan's cost is flat; an index scan's cost rises with matched rows. They always cross, near 1% for a table with 100 rows per page, and a bitmap scan covers the middle by sorting row locations into physical order.
• cost=start..total: start-up is the work before the first row, which is why LIMIT above a sort saves nothing and above an index scan saves nearly everything.
• Join order is searched by dynamic programming, then by a non-deterministic genetic algorithm above geqo_threshold = 12, with join_collapse_limit = 8 deciding how much gets flattened at all.
• Prepared statements plan with real values five times, then switch to a generic plan if it is no more expensive. On a skewed column that gives one slow tenant and a fast everybody else — plan_cache_mode = 'force_custom_plan'.
• PostgreSQL has no hints on purpose. enable_seqscan = off is a diagnostic that tells you whether the planner's choice or its estimate was wrong.

Self-test: Where does rows=1800 come from on a status = 'refunded' filter? · Why is n_distinct = -1 not a count? · Why does the Paris-and-France estimate come out twenty times too small, and what is the exact fix? · Derive the point where a sequential scan beats an index · What does the second number in cost=0.43..905.19 mean? · Why is one tenant slow when the query is identical for everyone?

Next: 7.2.6 covers the tool this page kept pointing at — views for naming a query once and materialized views for storing its answer, including what a materialized view costs you in staleness and what REFRESH CONCURRENTLY needs before it will run.