Настройка стоимости индексов с помощью ANALYZE и статистики

PostgreSQL's planner picks between index scans and sequential scans using statistics about your tables: row counts, value distributions, and more.

Stale statistics lead to bad plans.

The ANALYZE command samples a table and updates statistics stored in the system catalogs, so the planner estimates row counts accurately.

The autovacuum daemon also runs ANALYZE automatically when enough rows change. But heavy bulk loads may need a manual ANALYZE right away.

Use EXPLAIN ANALYZE to compare the planner's estimated rows with actual rows. A big gap signals stale or insufficient statistics.

The default_statistics_target controls sample size. Raise it on columns with skewed data for sharper histograms.

If the planner underestimates matching rows it may pick an index scan that becomes slow; overestimate and it may skip a perfectly good index for a seq scan.

The pg_stats view exposes the collected statistics like most common values and null fraction per column.

For correlated columns, create extended statistics so the planner understands dependencies between them.

Settings like random_page_cost tell the planner how expensive random I/O is. On SSDs, lowering it makes index scans more attractive.

If an index exists but is unused, suspect:

• Stale statistics
• Low selectivity (most rows match)
• A type mismatch preventing index use

1. Run EXPLAIN ANALYZE
2. Check estimate vs actual gap
3. ANALYZE or raise statistics target
4. Adjust cost settings if needed
5. Re-measure

The PostgreSQL planner chooses indexes based on statistics kept fresh by ANALYZE and autovacuum.

Compare estimated versus actual rows with EXPLAIN ANALYZE, raise the statistics target for skewed columns, add extended statistics for correlated ones, and tune cost parameters to guide index choice.