How to recover corrupted statistics and indexes in PostgreSQL that caused slow queries

By Kevin Marcondes · Published on Oct 14, 2026 · 7 min read

Queries got slower and you suspect bad statistics or corrupted indexes? This practical guide shows how to confirm the diagnosis, refresh statistics with ANALYZE, rebuild indexes with minimal impact, and prevent recurrence. You’ll find typical signs, safe commands, and when to use REINDEX CONCURRENTLY or recreate indexes. Useful to act with confidence, not guesswork.

Signs the bottleneck is statistics or an index (not the application)

Before changing code, verify whether the optimizer has lost its way. Inaccurate statistics and damaged indexes can derail the plan, causing expensive sequential scans or ineffective filters.

Look for these symptoms in production:

• Plan switched from Index Scan to Seq Scan without a proportional change in data.
• Row estimates in EXPLAIN differ widely from the actual counts.
• Index usage (idx_scan) drops significantly after maintenance or an incident.
• Slowness started right after a spike of updates/deletes or a server crash.
• Tables with many dead tuples in pg_stat_user_tables.

Common causes: why statistics and indexes degrade

Understanding the root prevents repeating the problem. These are common plausible causes in PostgreSQL:

• Autovacuum/autoanalyze disabled or poorly tuned for large tables.
• Workload with many updates, leaving indexes bloated.
• Index creation interrupted; index remains unavailable or invalid.
• Crash, disk errors or unreliable write cache during maintenance.
• Partial upgrade or DDL operations without a clear rollback window.

Quick, safe diagnosis in production

Confirm the hypothesis before you change anything. Use EXPLAIN to inspect the plan, check dead tuples, look for invalid indexes and validate B-Tree integrity with amcheck.

EXPLAIN ANALYZE runs the query. For critical routes, run EXPLAIN without ANALYZE first. Avoid heavy tests during peak hours.

-- 1) Inspecione o plano da consulta problemática
EXPLAIN (ANALYZE, BUFFERS)
SELECT /* sua query aqui */ 1;

-- 2) Tabelas com muitos dead tuples (candidatas a VACUUM/ANALYZE)
SELECT relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- 3) Índices inválidos ou não prontos
SELECT c.relname AS index_name, i.indisvalid, i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid OR NOT i.indisready;

-- 4) Verifique a integridade de B-Trees (exige superuser)
CREATE EXTENSION IF NOT EXISTS amcheck;
SELECT bt_index_check(indexrelid)
FROM pg_index
WHERE indisvalid;

Recover statistics: ANALYZE and small adjustments

When the plan is wrong due to bad estimates, regenerate statistics. ANALYZE and VACUUM (ANALYZE) run online and do not block normal reads/writes. For very large tables, prioritize the most heavily used ones.

If a column has skewed distribution, increase the per-column statistics target. Small tuning reduces future regressions.

-- Atualize estatísticas nas tabelas críticas
ANALYZE VERBOSE schema.tabela_mais_lenta;
-- Opcional, se há muitos dead tuples
VACUUM (ANALYZE, VERBOSE) schema.tabela_mais_lenta;

-- Aumente a qualidade das estatísticas de uma coluna skewed
ALTER TABLE schema.tabela_mais_lenta
  ALTER COLUMN col_filtro SET STATISTICS 200;  -- 1–10000

-- Tornar a amostragem padrão mais rica (recarrega config)
ALTER SYSTEM SET default_statistics_target = 200;
SELECT pg_reload_conf();

Recover indexes: REINDEX with low impact and alternatives

If amcheck reports problems or an index is invalid, rebuild it. Prefer approaches that minimize table locks on hot tables.

REINDEX CONCURRENTLY reduces impact but doesn't cover every case. When unavailable, create a new index concurrently and then drop the old one. REINDEX on an entire database can block; use it with care.

• Slightly bloated index: REINDEX CONCURRENTLY on the index or table.
• Invalid/corrupted index: recreate with CREATE INDEX CONCURRENTLY and then DROP the old index.
• System catalogs affected: REINDEX SYSTEM, planned in a maintenance window with tests.

-- Reconstrução com menor bloqueio (PostgreSQL >= 12)
REINDEX INDEX CONCURRENTLY schema.idx_cliente_cpf;
-- Ou na tabela inteira
REINDEX TABLE CONCURRENTLY schema.clientes;

-- Alternativa compatível: recriar e trocar
CREATE INDEX CONCURRENTLY idx_cliente_cpf_v2 ON schema.clientes (cpf);
-- Depois que o v2 estiver pronto e usado:
DROP INDEX CONCURRENTLY schema.idx_cliente_cpf;

Post-fix verification and safe rollback

After ANALYZE/REINDEX, validate that the plan returned to expected behavior and the index is being used. Compare timings and estimates before and after.

Keep a rollback path: versioned index names make rollback easier. Avoid dropping the old index before confirming improvement under real load.

-- Reavalie o plano e o tempo de execução
EXPLAIN (ANALYZE, BUFFERS)
SELECT /* sua query aqui */ 1;

-- Índices mais usados pós-ajuste
SELECT relname AS index_name, idx_scan
FROM pg_stat_all_indexes
ORDER BY idx_scan DESC
LIMIT 10;

Prevention: autovacuum, routines and controlled changes

Prevent future degradation by keeping statistics fresh and indexes healthy. Automate checks, tune thresholds and treat DDLs carefully.

Schedule routines outside peak hours. Monitor trends to act before a crisis.

• Tune autovacuum for large tables (lower scale_factor and thresholds).
• Schedule targeted ANALYZE after heavy bulk loads.
• Automate periodic amcheck on critical indexes.
• Standardize DDL via database versioning for safe changes.
• Investigate jobs that cause recurrent locks before maintenance.

-- Exemplo: tornar o autovacuum mais agressivo em uma tabela quente
ALTER TABLE schema.clientes SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_threshold = 500
);

Frequently asked questions

How can I tell if slowness is due to bad statistics or inefficient SQL?

Compare the current plan with the expected plan using EXPLAIN. If estimates differ greatly from reality and the plan changed without data changes, regenerate statistics. If the plan remains poor even with accurate estimates, review the query and existing indexes.

Do ANALYZE and VACUUM block the application?

ANALYZE and VACUUM (without FULL) run online and do not block normal reads and writes. There are short metadata locks. For very active tables, run them outside peak times and monitor impact.

When should I use REINDEX CONCURRENTLY?

Use it when you suspect bloat or corruption in B-Tree indexes on critical tables and you need to minimize locks. If an index is invalid or missing, create a replacement with CREATE INDEX CONCURRENTLY and drop the old one after verification.

Should I reindex the entire database?

Only with strong justification and a maintenance window. REINDEX on the entire database can cause broad locks. Prefer targeting specific indexes or tables and escalate to schema/database/system level only after confirming need.

Doesn't autovacuum prevent this?

It helps, but default settings can be conservative for large tables and heavy workloads. Tune scale factors and thresholds, run targeted ANALYZE after bulk loads, and monitor amcheck and index usage.

Can I delete pg_statistic to rebuild it from scratch?

No. pg_statistic is a system catalog. The correct approach is to run ANALYZE (or VACUUM ANALYZE) and, if needed, increase sampling with default_statistics_target or SET STATISTICS per column.

See also

• REST API latency spikes from N+1 queries and PHP GC — how to diagnose and fix
• Postgres 'Too many connections' at billing peak — practical diagnosis and remediation guide
• Reduce costs migrating Magento to OpenMage with a rollback plan and acceptance tests
• Practical guide: recover a dropped PostgreSQL table with point-in-time recovery without downtime