pg_statistic, ANALYZE, and production observability

A query that ran in 5ms yesterday takes 4 seconds today. Nothing in the application changed. The plan is different. The cause is almost always the same: statistics drifted and the planner made a different choice. Knowing how Postgres builds and uses statistics is how you prevent the next incident.

Inside pg_statistic

When you run ANALYZE orders, Postgres samples the table (by default 300 × default_statistics_target = 30,000 rows with the default target of 100) and computes per-column statistics stored in pg_statistic (readable via pg_stats):

How the planner uses these for WHERE x = 42:

1. Is 42 in the MCV list? Use its exact frequency directly.
2. Not an MCV? Find the histogram bucket containing 42, assume uniform distribution within the bucket.
3. Combine with reltuples (row count in pg_class) to get the estimated row count.

For range predicates (x > 100), the histogram is integrated over the range. For multi-column predicates, by default the planner assumes independence and multiplies selectivities — which is wrong for correlated columns (covered in lesson 05).

Read the catalog yourself:

SELECT tablename, attname, n_distinct, most_common_vals, most_common_freqs,
       histogram_bounds, correlation
FROM pg_stats
WHERE tablename = 'orders';

ANALYZE: when to run it

Autovacuum schedules ANALYZE per table based on:

autovacuum_analyze_threshold (default 50 rows) +
autovacuum_analyze_scale_factor × reltuples (default 0.1 = 10%)

On a 100M-row table, autovacuum analyzes only after 10M row changes — far too infrequent for tables with skewed or fast-changing data distributions. Operational fixes:

-- Lower the scale factor for hot tables:
ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.02);

-- Bump statistics target for skewed columns:
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

Manual ANALYZE runs in seconds even on 100M-row tables (it samples, not full-scans). Always run it:

• After bulk inserts or large UPDATEs that shift distributions
• After schema changes that add columns
• In post-deploy hooks before traffic resumes

EXPLAIN options for diagnostics

Beyond the basic plan, several EXPLAIN options are essential:

• FORMAT JSON | XML | YAML | TEXT — JSON for tooling (explain.depesz.com, explain.dalibo.com, pganalyze)
• VERBOSE — adds output column lists per node
• SETTINGS (PG 12+) — prints non-default planner GUCs; diagnose environment drift between staging and production
• WAL (PG 13+) — shows WAL bytes generated by the statement
• GENERIC_PLAN (PG 16+) — plans a parameterised query without sample values; essential for prepared-statement diagnostics (covered in lesson 06)
• SERIALIZE (PG 17+) — includes the cost of serialising rows for the client; closes the gap between EXPLAIN ANALYZE total time and client-observed latency

auto_explain and pg_stat_statements

Two extensions every production Postgres should have:

pg_stat_statements records every executed query (normalised by parameter), tracking calls, total_exec_time, mean_exec_time, rows, and buffer counts. Query: SELECT query, calls, total_exec_time, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20. These top-20 by total_exec_time are your optimisation targets — a 2ms query called 10M times beats a 500ms query called twice.

auto_explain automatically logs EXPLAIN ANALYZE for any query exceeding a duration threshold:

-- postgresql.conf additions:
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = true
auto_explain.log_buffers = true
auto_explain.log_format = 'json'
auto_explain.sample_rate = 0.01   -- 1% of slow queries to limit log volume

Slow queries land in the Postgres logs with full ANALYZE + BUFFERS plans, no need to reproduce them in staging. Together they answer “which queries are slow, and why” without instrumenting the application.