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

Event	Action
`COPY` / bulk insert	`ANALYZE` before benchmark
New index on huge table	`ANALYZE` after `CREATE INDEX CONCURRENTLY`
Partition attach/detach	Analyze new partition
Major delete purge	`ANALYZE` same maintenance window

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

Alternative	Use When	Don't Use When
Higher `statistics_target` on skew columns	Heavy-tail predicates	Every column blindly
`pg_cron` scheduled analyze	Predictable ETL finish	Already meets autoanalyze
Logical snapshot refresh	Warehouse derived tables	OLTP primary paths

FAQs

ANALYZE vs VACUUM ANALYZE?

VACUUM ANALYZE does both. For hot tables, separate vacuum and analyze tuning is clearer.

Does ANALYZE block writes?

ShareUpdateExclusiveLock allows reads/writes; brief catalog updates at end.

How long does ANALYZE take?

Proportional to statistics target and table size; sampling keeps it sub-linear.

Can I cancel ANALYZE?

Yes via pg_cancel_backend. Partial stats may result; rerun.

autovacuum not analyzing?

Check log_autovacuum_min_duration, worker count, and table-level autovacuum_enabled.

ANALYZE on replicas?

Standbys do not run autovacuum/analyze; stats come from primary.

Foreign tables?

Use ANALYZE on foreign tables when FDW supports imported stats.

pg_stat_progress_analyze?

PostgreSQL 18 tracks analyze phases for long runs.

After TRUNCATE?

Stats reset; analyze empty or reloaded data.

Next?

Extended statistics when analyze alone does not fix estimates.

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+.

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

Event	Action
`COPY` / bulk insert	`ANALYZE` before benchmark
New index on huge table	`ANALYZE` after `CREATE INDEX CONCURRENTLY`
Partition attach/detach	Analyze new partition
Major delete purge	`ANALYZE` same maintenance window

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

Alternative	Use When	Don't Use When
Higher `statistics_target` on skew columns	Heavy-tail predicates	Every column blindly
`pg_cron` scheduled analyze	Predictable ETL finish	Already meets autoanalyze
Logical snapshot refresh	Warehouse derived tables	OLTP primary paths

FAQs

ANALYZE vs VACUUM ANALYZE?

VACUUM ANALYZE does both. For hot tables, separate vacuum and analyze tuning is clearer.

Does ANALYZE block writes?

ShareUpdateExclusiveLock allows reads/writes; brief catalog updates at end.

How long does ANALYZE take?

Proportional to statistics target and table size; sampling keeps it sub-linear.

Can I cancel ANALYZE?

Yes via pg_cancel_backend. Partial stats may result; rerun.

autovacuum not analyzing?

Check log_autovacuum_min_duration, worker count, and table-level autovacuum_enabled.

ANALYZE on replicas?

Standbys do not run autovacuum/analyze; stats come from primary.

Foreign tables?

Use ANALYZE on foreign tables when FDW supports imported stats.

pg_stat_progress_analyze?

PostgreSQL 18 tracks analyze phases for long runs.

After TRUNCATE?

Stats reset; analyze empty or reloaded data.

Next?

Extended statistics when analyze alone does not fix estimates.

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+.

ANALYZE & Autoanalyze

Buscar en la documentación

ANALYZE & Autoanalyze