ANALYZE & Autoanalyze

Autovacuum workers run ANALYZE when tables change enough. Bulk ETL, migrations, and skewed hot tables still need manual ANALYZE on PostgreSQL 18.4 before you trust new plans.

Recipe

Quick-reference recipe card - copy-paste ready.

ANALYZE VERBOSE orders;
ANALYZE orders (status, created_at);

When to reach for this: After large COPY, partition attach, or plan regressions on a table that "should" be analyzed automatically.

Working Example

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
 
INSERT INTO orders (status)
SELECT 'pending' FROM generate_series(1, 500000);
 
SELECT relname, last_analyze, last_autoanalyze, n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname = 'orders';
 
-- Simulate threshold: manual analyze after bulk load
ANALYZE VERBOSE orders;
 
EXPLAIN (ANALYZE)
SELECT count(*) FROM orders WHERE status = 'pending';

What this demonstrates:

• n_mod_since_analyze monitoring
• Post-load manual analyze
• Plan stability after stats refresh

Deep Dive

How It Works

• Autoanalyze fires when n_mod_since_analyze exceeds insert/update/delete thresholds based on autovacuum_analyze_scale_factor (default 0.1) plus autovacuum_analyze_threshold.
• ANALYZE takes a share lock; usually brief on large tables because of sampling.
• Column-targeted ANALYZE t (col) speeds runs when only one skewed column changed.
• VACUUM (ANALYZE) combines visibility cleanup and stats (prefer separate tuning in production).

Threshold Tuning Example

ALTER TABLE orders SET (
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_analyze_threshold = 1000
);

When Manual ANALYZE Is Required

Gotchas

• Disabling autovacuum to "reduce load" - Stats rot alongside bloat. Fix: Tune per-table thresholds, never global off.
• Analyzing during peak without need - I/O spike on huge tables. Fix: Schedule after ETL; use column lists.
• Assuming CREATE INDEX updates stats - Index stats differ; table stats may be stale. Fix: ANALYZE table after index build.
• Ignoring inherited tables - Partitioned parent may need ANALYZE on children. Fix: ANALYZE ONLY vs default behavior per docs.
• Comparing plans pre-analyze - False negatives on index usefulness. Fix: Standardize analyze in perf test harness.

Alternatives

FAQs

Related

• Statistics Basics - What gets collected
• Extended Statistics - Beyond per-column stats
• Autovacuum Tuning - Shared worker settings
• Statistics Best Practices - Team checklist

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+. 3730363230327c393430316f6967637c6f692e73656469756765646f63