Lesson 14 · Query performance

The planner & statistics

How Postgres decides how to run the query you only described.

Your win: explain how the planner chooses a plan, where its cost estimates come from, and why stale statistics are a classic cause of a query suddenly going slow.

You say what; the planner decides how

SQL is declarative (Lesson 1). For any query there are many ways to execute it — which scan, which index, which join method, which order. The planner (optimizer) enumerates candidate plans and picks the one with the lowest estimated cost.¹

Cost comes from statistics

To estimate cost, the planner must guess selectivity — what fraction of rows a WHERE condition will match. It reads statistics from the pg_statistic catalog (readable via the pg_stats view): row counts, number of distinct values, most-common values, and histograms.²

WHERE resource_path = 'jprep' AND status = 'QUEUED' │ planner asks stats: how selective is each condition? │ resource_path='jprep' → ~1/20 of rows status='QUEUED' → ~1/100 of rows ▼ estimate rows → cost each candidate plan → pick the cheapest (e.g. Index Scan on resource_path)

Stale stats = bad plans Statistics are refreshed by ANALYZE (and by autovacuum, Lesson 9). If they're stale — e.g. right after a bulk load — the planner misestimates selectivity and can pick a terrible plan (a seq scan where an index would win, or vice versa). "Run ANALYZE" fixes a surprising number of "suddenly slow" queries.

Anchor Autovacuum keeps your tables' stats fresh as they churn. resource_path is highly selective (one tenant is a small slice of the table), so the planner reliably favours the resource_path index that RLS makes every query filter on. Costs are in arbitrary units (relative to a sequential page read = 1) — it's the comparison that matters, not the absolute number.

PostgreSQL — Statistics Used by the Planner

How pg_statistic/pg_stats drive selectivity estimates, and extended (multivariate) statistics for correlated columns.

→ postgresql.org/docs — Statistics Used by the Planner

Check yourself (from memory)

Q1. The planner chooses a plan by…

It enumerates candidate plans and picks the lowest estimated cost — it doesn't run them to find out.

Q2. Cost estimates come from…

Row counts, distinct values, MCVs, histograms → a selectivity estimate. Viewable via pg_stats.

Q3. Statistics are refreshed by…

ANALYZE (manual) and autovacuum (automatic). Stale stats → misestimates → bad plans.

How does Postgres decide how to run a query?

recall, then click to reveal

SQL is declarative — the PLANNER decides how. It enumerates candidate plans (scan type, join method, join order) and picks the lowest ESTIMATED cost. Costs come from STATISTICS in pg_statistic (via pg_stats): row counts, distinct values, most-common values, histograms — used to estimate a WHERE clause's selectivity. Stats are refreshed by ANALYZE/autovacuum; stale stats → misestimated selectivity → a bad plan (a classic cause of a query going slow after a bulk load).

Want to peek at real stats — SELECT * FROM pg_stats WHERE tablename = 'emails' — or learn extended statistics for correlated columns? Ask me.

1. PostgreSQL — Performance Tips.

2. PostgreSQL — Statistics Used by the Planner.