The essentials

Quick reference

One focused task per row. Jump to the related section for complete, working examples.

UseSyntaxExamples
Inspect estimated rows onlyEXPLAIN SELECT * FROM app.orders WHERE status = 'open';View examples
Measure actual rowsEXPLAIN (ANALYZE, BUFFERS) SELECT * FROM app.orders WHERE status = 'open';View examples
Expose planning settingsEXPLAIN (ANALYZE, SETTINGS) SELECT count(*) FROM app.orders;View examples
Analyze one tableANALYZE app.orders;View examples
Analyze selected columnsANALYZE app.orders (tenant_id, status);View examples
Inspect table statisticsSELECT * FROM pg_stats WHERE schemaname = 'app' AND tablename = 'orders';View examples
Check approximate table sizeSELECT reltuples, relpages FROM pg_class WHERE oid = 'app.orders'::regclass;View examples
Set a column targetALTER TABLE app.orders ALTER COLUMN tenant_id SET STATISTICS 500;View examples
Restore the default targetALTER TABLE app.orders ALTER COLUMN tenant_id SET STATISTICS DEFAULT;View examples
Create dependency statisticsCREATE STATISTICS app.orders_dep (dependencies) ON tenant_id, region FROM app.orders;View examples
Create MCV statisticsCREATE STATISTICS app.orders_mcv (mcv) ON tenant_id, status FROM app.orders;View examples
Inspect extended statisticsSELECT * FROM pg_stats_ext WHERE statistics_name = 'orders_tenant_status';View examples
Set a proportional distinct estimateALTER TABLE app.events ALTER COLUMN tenant_id SET (n_distinct = -0.02);View examples
Remove a distinct overrideALTER TABLE app.events ALTER COLUMN tenant_id RESET (n_distinct);View examples
Analyze a partition hierarchyANALYZE app.events;View examples
Inspect extended-stat data safelySELECT statistics_name, kinds FROM pg_stats_ext WHERE schemaname = 'app';View examples

PostgreSQL chooses scans, join orders, and algorithms from estimated row counts. Accurate estimates require representative statistics, appropriate per-column targets, and extended statistics for correlated predicates; tuning planner switches before fixing evidence often hides the real problem.

Step by step

Detailed examples

01

Compare estimated rows with measured rows

Start with EXPLAIN to inspect without executing. EXPLAIN ANALYZE really runs the statement, so use a transaction and rollback for data-changing statements, and remember volatile functions or external effects may not roll back. Large estimate-to-actual ratios near the first divergence usually point to stale or insufficient statistics.

Measure a read-only plan with buffers
EXPLAIN (ANALYZE, BUFFERS, SETTINGS, FORMAT TEXT)
SELECT * FROM app.orders
WHERE tenant_id = 42 AND status = 'open';

Note: Test on representative data and load; instrumentation and cold-cache effects can change timings.

Back to quick reference ↑
02

Refresh statistics after meaningful data changes

ANALYZE samples data under a lock compatible with ordinary reads and writes, though it conflicts with some maintenance and DDL and consumes I/O and CPU. Autovacuum normally analyzes changed tables, but bulk loads and skew shifts can justify a targeted manual run. Results remain approximate and can vary between samples.

Analyze selected skew-sensitive columns
ANALYZE VERBOSE app.orders (tenant_id, status, created_at);

Note: Run after a bulk load or major distribution change, not reflexively after every small write.

Back to quick reference ↑
03

Read the security-filtered pg_stats view

pg_stats exposes human-readable statistics only for tables the current user can read, unlike restricted pg_statistic. Most-common values, histogram bounds, null fraction, correlation, and distinct estimates explain many selectivity decisions. Arrays may be omitted for types whose operators are not readable by the user.

Inspect column distribution metadata
SELECT attname, null_frac, n_distinct, correlation, most_common_vals, most_common_freqs
FROM pg_stats
WHERE schemaname = 'app' AND tablename = 'orders'
  AND attname IN ('tenant_id', 'status');
Back to quick reference ↑
04

Raise detail only for columns that need it

A higher target increases the sample and the maximum sizes of MCV and histogram arrays, improving irregular distributions at added analyze time and catalog space. It does not make estimates exact. Use a focused target and re-run ANALYZE; target zero disables column statistics and is rarely appropriate for filter, join, grouping, or ordering columns.

Increase detail for a skewed tenant key
ALTER TABLE app.orders ALTER COLUMN tenant_id SET STATISTICS 500;
ANALYZE app.orders (tenant_id);

Note: ALTER TABLE requires ownership or suitable maintenance privileges and takes a relation lock; validate the benefit before standardizing the value.

Back to quick reference ↑
05

Model correlation across columns

The independence assumption can badly underestimate predicates such as city plus postal code. Extended statistics support dependencies, multivariate distinct counts, and multivariate MCV lists, but they are not currently used to improve join selectivity between tables. CREATE STATISTICS does not collect data until ANALYZE runs.

Capture correlated tenant and status values
CREATE STATISTICS app.orders_tenant_status (dependencies, mcv, ndistinct)
ON tenant_id, status FROM app.orders;
ANALYZE app.orders;

Note: The creator must own the table; the statistics object then has independent ownership. Inspect impact with representative plans.

Back to quick reference ↑
06

Use n_distinct overrides only with strong evidence

For huge tables, sampling can misestimate distinct values. A positive override is an absolute count; a negative value is the negative fraction of table rows, with -1 meaning unique. Overrides affect subsequent ANALYZE results and can outlive the workload assumption, so document and periodically validate them.

Model a tenant key proportional to table size
ALTER TABLE app.events ALTER COLUMN tenant_id SET (n_distinct = -0.02);
ANALYZE app.events (tenant_id);

Note: This asserts distinct tenants are approximately two percent of row count; use only after measurement.

Back to quick reference ↑
07

Account for partitions and statistics confidentiality

PostgreSQL 18 ANALYZE can sample a partitioned hierarchy, but parent-level statistics can become stale when only leaf partitions change; schedule parent ANALYZE where workload needs it. pg_statistic is restricted because values may reveal data. Use pg_stats and built-in operators, and avoid SECURITY DEFINER diagnostics with an unsafe search_path.

Refresh a partitioned parent after loading a leaf
ANALYZE app.events;

Note: This recursively analyzes partitions by default and gathers hierarchy statistics; schedule it after partition-only loads when needed.

Back to quick reference ↑

Keep exploring

More SQL cheat sheets

Sources and further reading

References

Authoritative documentation used to verify and expand this cheat sheet.

  1. PostgreSQL Global Development GroupStatistics Used by the Plannerpostgresql.org
  2. PostgreSQL Global Development GroupHow the Planner Uses Statisticspostgresql.org
  3. PostgreSQL Global Development GroupANALYZEpostgresql.org
  4. PostgreSQL Global Development GroupCREATE STATISTICSpostgresql.org
  5. PostgreSQL Global Development Grouppg_statspostgresql.org
  6. PostgreSQL Global Development Grouppg_stats_extpostgresql.org
  7. PostgreSQL Global Development GroupEXPLAINpostgresql.org

Help us improve

Found a typo or missing example?

Tell us what would make this cheat sheet clearer, more complete, or more useful.

Share feedback