Planner Statistics and EXPLAIN ANALYZE
An optimal index can exist on a table, yet PostgreSQL may still choose an inefficient execution plan. The query planner never inspects table data directly at runtime; instead, it relies on statistical estimates that model how data values are distributed across pages and columns.
If the optimizer estimates that a filter condition will match 20 rows when in reality it matches 2 million, a seemingly cheap nested loop join explodes into millions of repetitive page accesses. Under those true data volumes, a hash join or sequential scan would have finished in a fraction of the time.
Diagnosing Divergence with EXPLAIN ANALYZE
When analyzing a slow query, the essential starting point is EXPLAIN (ANALYZE, BUFFERS). Compare the estimated row counts (rows=...) against the actual rows processed (actual rows=...) across every step of the execution tree. The root cause of a pathological plan almost always lies at the earliest node where estimated cardinality sharply diverges from reality.
Estimation drift commonly occurs when:
- Data churn has outpaced automated background
ANALYZEruns. - Data distribution is heavily skewed across frequency buckets.
- Multiple query predicates are correlated, yet the planner treats them as statistically independent events.
Correlated Columns and Extended Statistics
In a multi-tenant schema, for example, columns like tenant_id and status frequently exhibit strong correlation: certain tenants may have millions of archived records while others have only active ones. By default, PostgreSQL calculates individual column statistics independently and computes combined selectivity by multiplying individual probabilities. This can drastically underestimate combined row counts, driving the planner toward an inappropriate access path.
Running CREATE STATISTICS to capture cross-column correlations and multivariate dependencies provides the optimizer with an accurate model. Coupled with tuning the statistics target (default_statistics_target or per-column SET STATISTICS), this recalibrates planner cost calculations without requiring manual join hints or redundant indexes.
Optimizing the Statistical Model
A suboptimal query plan does not always indicate a missing index. Before adjusting database parameters or creating new indexes, determine where cardinality estimates first went astray: the query planner can only optimize the reality its statistics are capable of describing.