Scenario
An ANALYZE on a large table or a partitioned table hierarchy is running — pg_stat_progress_analyze shows which phase it is in and how far through. Diagnose it -- Available since PostgreSQL 13: SELECT p.pid, p.datname, n.nspname…
Investigation Path
An ANALYZE on a large table or a partitioned table hierarchy is running — pg_stat_progress_analyze shows which phase it is in and how far through.
Diagnose it
-- Available since PostgreSQL 13:
SELECT p.pid,
p.datname,
n.nspname || '.' || c.relname AS table_name,
p.phase,
p.sample_blks_total,
p.sample_blks_scanned,
round(
100.0 * p.sample_blks_scanned
/ NULLIF(p.sample_blks_total, 0),
1) AS pct_sampled,
p.ext_stats_total,
p.ext_stats_computed,
p.child_tables_total,
p.child_tables_done,
p.started_by -- PostgreSQL 17+
FROM pg_stat_progress_analyze p
JOIN pg_class c ON c.oid = p.relid
JOIN pg_namespace n ON n.oid = c.relnamespace;phase values: initializing → acquiring sample rows → acquiring inherited
sample rows → computing statistics → computing extended statistics → finalizing analyze.
For partitioned tables, child_tables_total shows the full partition count.
Why it happens
ANALYZE samples random pages to build column statistics used by the query planner.
On large tables or partition hierarchies, this sampling can take minutes. The
ext_stats_* columns track progress on CREATE STATISTICS objects
(extended statistics), which require additional computation passes.
How to fix it
If ANALYZE is unexpectedly slow, check whether the table has many extended statistics
objects (ext_stats_total is high). Extended stats for many column combinations
can be expensive to compute — review whether all created statistics objects are actually
improving query plans.
Prevent it next time
Ensure autovacuum (which also runs ANALYZE) is enabled and tuned to run ANALYZE frequently
enough to keep statistics fresh. For partitioned tables, consider running
ANALYZE VERBOSE parent_table during off-peak hours to avoid the planner
using stale statistics on individual partitions.
Related & next steps
Career Impact
This scenario builds production judgment and operational confidence under pressure.