Improving Cardinality Estimates with Extended Statistics
In this lesson, you will learn how to resolve poor join performance caused by PostgreSQL’s inability to account for correlations between columns.
The Problem: Independence Assumptions
By default, the PostgreSQL query planner assumes that columns are independent. If you have a table products with columns category and subcategory, the planner assumes that a filter like WHERE category = 'electronics' AND subcategory = 'laptops' is roughly as selective as the product of their individual probabilities. However, in reality, these columns are highly correlated—every 'laptop' belongs to 'electronics', but not every 'electronics' item is a 'laptop'. Because the planner doesn't know this, it often severely underestimates the number of rows (cardinality), leading it to choose a nested loop join when a hash join would have been significantly faster.
Creating Extended Statistics
To fix this, we can create extended statistics objects that instruct the planner to track dependencies between specific columns. When you create these statistics, PostgreSQL analyzes the data distribution across the combined set of columns, allowing it to "see" that certain combinations are far more likely (or impossible) than individual column stats suggest.
Consider a scenario where we frequently join on correlated attributes:
-- Create an extended statistics object for the correlated columns
CREATE STATISTICS stts_category_correlation
ON category, subcategory
FROM products;
-- Run ANALYZE to populate the statistics
ANALYZE products;
Once the ANALYZE command runs, the planner uses the multivariate statistics stored in the pg_statistic_ext catalog. Now, when the optimizer evaluates a query containing both columns in a JOIN or WHERE clause, it will use the cross-column density data to calculate a much more accurate row count estimate. This enables the planner to make better decisions about join order and algorithm selection, preventing the "nested loop trap" that occurs when the planner thinks a join will return only one row but it actually returns thousands.
These objects are lightweight and targeted, providing a surgical fix for performance issues that generic ANALYZE cannot solve.
Key Takeaway: Extended statistics allow the PostgreSQL planner to account for column correlations, preventing skewed cardinality estimates and allowing for more efficient join algorithm selection.