DB Query Statistics

Query Statistics and Selectivity

How the planner uses table statistics (pg_stats, histograms, most-common-values) to estimate row counts and choose execution plans.

When to Use

• Diagnosing planner misestimations (estimated rows vs actual rows differ by 10x+)
• Understanding why the planner chose a bad plan despite correct indexes
• Tuning statistics targets for columns with skewed distributions
• After bulk data loads, migrations, or large deletes that change data distribution
• Investigating queries where EXPLAIN shows correct indexes but wrong join strategies

Instructions

Key Concepts

The planner does not look at actual table data at query time. Instead, it uses pre-computed statistics stored in pg_statistic (accessible via the pg_stats view). These statistics are sampled approximations, not exact counts.

Key statistics columns in pg_stats:

• n_distinct -- estimated number of distinct values. Positive values are absolute counts; negative values are fractions of total rows (e.g., -1.0 means every row is unique)
• most_common_vals -- the N most frequent values in the column
• most_common_freqs -- the frequency of each most-common value (fractions of total rows)
• histogram_bounds -- equal-frequency histogram bucket boundaries for values not in the MCV list
• null_frac -- fraction of rows that are NULL
• correlation -- how well the physical row order matches the logical (sorted) order. Values near 1.0 or -1.0 mean high correlation

The ANALYZE command samples the table and updates these statistics:

ANALYZE orders;                    -- analyze one table
ANALYZE orders (status);            -- analyze one column
ANALYZE;                            -- analyze all tables in the database

Autovacuum runs ANALYZE automatically, but it may lag behind large data changes.

Worked Example

Examining statistics for the status column on an orders table:

SELECT
  attname,
  n_distinct,
  most_common_vals,
  most_common_freqs,
  null_frac,
  correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

 attname | n_distinct | most_common_vals            | most_common_freqs        | null_frac | correlation
---------+------------+-----------------------------+--------------------------+-----------+------------
 status  |          3 | {completed,active,cancelled} | {0.85,0.12,0.03}         |         0 |       0.15

How the planner uses this: For WHERE status = 'active', the estimated rows = total_rows * 0.12. On a 10M-row table, the estimate is 1.2M rows. For WHERE status = 'cancelled', the estimate is 300K rows.

Misestimation scenario: After a data migration that marked 90% of orders as cancelled (previously 3%), the statistics are stale:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'cancelled';

Index Scan using idx_orders_status on orders
  (cost=0.43..12345.67 rows=300000 width=52)
  (actual time=0.031..8923.450 rows=9000000 loops=1)

Estimated 300K rows, actual 9M -- a 30x misestimation. The planner chose Index Scan (good for 300K) instead of Seq Scan (better for 9M). Fix:

ANALYZE orders;

After ANALYZE, the planner sees the updated distribution and switches to Seq Scan:

Seq Scan on orders (cost=0.00..223456.00 rows=9010000 width=52)
  (actual time=0.012..1234.567 rows=9000000 loops=1)
  Filter: (status = 'cancelled')
Execution Time: 1345.678 ms

Increasing statistics granularity for skewed distributions:

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

The default default_statistics_target is 100 (100 histogram buckets and 100 MCV entries). For highly skewed columns, increasing to 500-1000 gives the planner a more accurate picture.

Anti-Patterns

1. Never running ANALYZE after bulk operations. After a large INSERT, DELETE, or UPDATE that changes data distribution, statistics are stale. The planner uses the old distribution, producing bad plans. Always ANALYZE tablename; after bulk changes.

2. Setting default_statistics_target too low. The default of 100 works for uniform distributions but fails for skewed data. Columns with thousands of distinct values at varying frequencies need higher targets.

3. Ignoring n_distinct misestimates. For high-cardinality columns (e.g., user_id with 50M distinct values), the sampled n_distinct can be significantly off. Override with: ALTER TABLE orders ALTER COLUMN user_id SET (n_distinct = -1); (tells the planner every value is unique).

4. Disabling autovacuum. Autovacuum also runs auto-ANALYZE. Disabling it means statistics are never refreshed automatically, guaranteeing plan degradation over time.

PostgreSQL Specifics

default_statistics_target controls the number of histogram buckets and MCV entries. Default: 100. Maximum: 10000. Higher values increase ANALYZE time but improve estimation accuracy:

-- Global setting:
SET default_statistics_target = 200;

-- Per-column override:
ALTER TABLE events ALTER COLUMN event_type SET STATISTICS 500;

Extended statistics for correlated columns (PostgreSQL 10+):

CREATE STATISTICS stat_orders_status_region (dependencies)
ON status, region FROM orders;
ANALYZE orders;

Without extended statistics, the planner assumes columns are independent. If status = 'active' AND region = 'us-east' are correlated (e.g., 80% of us-east orders are active), the default estimation multiplies their individual frequencies, producing a significant underestimate.

Monitoring ANALYZE freshness:

SELECT relname, last_analyze, last_autoanalyze, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

If last_autoanalyze is days old and n_dead_tup is high, autovacuum is falling behind.

Details

Advanced Topics

Multivariate statistics evolution:

• PostgreSQL 10: functional dependencies (dependencies)
• PostgreSQL 12: MCV lists for column combinations (mcv)
• PostgreSQL 14: expression statistics (expressions)

CREATE STATISTICS stat_orders_multi (dependencies, mcv)
ON status, region, tenant_id FROM orders;

Selectivity estimation for complex predicates. AND clauses multiply individual selectivities (assuming independence). OR clauses use inclusion-exclusion. NOT inverts selectivity. These assumptions break down for correlated columns -- extended statistics fix this.

The 1/n_distinct fallback. For values not in the MCV list and not in the histogram range, PostgreSQL estimates selectivity as 1/n_distinct. This is a rough guess and can be significantly wrong for new or rare values.

pg_statistic_ext stores extended statistics data. Query it to verify that your extended statistics are being computed:

SELECT stxname, stxkeys, stxkind
FROM pg_statistic_ext
WHERE stxrelid = 'orders'::regclass;

Engine Differences

MySQL uses ANALYZE TABLE (not just ANALYZE) to update statistics:

ANALYZE TABLE orders;

MySQL stores statistics in mysql.innodb_index_stats and mysql.innodb_table_stats. Key differences:

• Persistent statistics (innodb_stats_persistent = ON by default since 5.6): statistics survive restarts
• Sampling pages (innodb_stats_persistent_sample_pages, default 20): controls sample size for ANALYZE. Much smaller than PostgreSQL's default of 30,000 * default_statistics_target rows
• Histogram support added in MySQL 8.0: ANALYZE TABLE t UPDATE HISTOGRAM ON col WITH 100 BUCKETS;
• No extended/multivariate statistics -- MySQL assumes column independence for all multi-column predicates

MySQL's optimizer uses a simpler statistics model overall. For complex queries with correlated columns, PostgreSQL's extended statistics provide significantly better estimates.

Real-World Case Studies

Reporting system with daily batch inserts of 5M rows. After each nightly batch, morning report queries degraded from 2 seconds to 30+ seconds. Investigation showed that autovacuum's ANALYZE had not run since the batch completed -- the statistics still reflected the previous day's distribution. Adding ANALYZE reporting_events; to the batch job's post-load step ensured fresh statistics. Query performance remained consistent at 2 seconds. Selectivity estimation error dropped from 100x (stale stats) to under 2x (fresh stats).

Source

• PostgreSQL Planner Statistics
• PostgreSQL Row Estimation Examples

Process

1. Read the key concepts to understand how the planner uses pg_stats for selectivity estimation.
2. Apply ANALYZE after bulk data changes, and increase statistics targets for columns with skewed distributions.
3. Verify by checking pg_stats for your key columns and comparing EXPLAIN estimated rows to actual rows.

Harness Integration

• Type: knowledge -- this skill is a reference document, not a procedural workflow.
• No tools or state -- consumed as context by other skills and agents.
• related_skills: db-explain-reading, db-scan-types, db-query-rewriting, db-btree-index

Success Criteria

• Statistics freshness is verified after bulk operations (ANALYZE runs in post-load steps).
• Misestimations are diagnosed by comparing pg_stats values to actual data distribution.
• Extended statistics are created for correlated columns that cause multi-column estimation errors.