In this lesson, you will learn how to manually override PostgreSQL’s column statistics to force the query planner to choose more efficient join strategies when automatic analysis falls short.
Why Statistics Fail
The PostgreSQL query planner relies on pg_statistic to estimate how many rows will be returned by a WHERE clause. This estimate determines whether the planner chooses a fast Nested Loop (good for few rows) or a resource-intensive Hash Join (better for many rows).
Sometimes, your data distribution is skewed in a way that the ANALYZE process doesn't capture—perhaps you have a high-frequency value that the histogram ignores, or a correlation between two columns that the planner isn't aware of. If the planner thinks a join will return 1 row but it actually returns 100,000, it may pick a catastrophic Nested Loop strategy.
Manually Adjusting Statistics
You can manually instruct PostgreSQL to "believe" a column has a specific distribution using the ALTER COLUMN ... SET STATISTICS command combined with manual manipulation of pg_stats. While SET STATISTICS usually just changes the depth of the histogram (up to 1000), you can use the pg_prewarm module or, more directly, manipulate the statistics of the table to force the planner's hand.
To influence the cost model without waiting for an automated ANALYZE, you can use the ALTER TABLE ... ALTER COLUMN ... SET (...) syntax:
-- Increase the resolution of the histogram for high-cardinality columns
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 500;
-- Manually fake the null fraction if the planner is miscalculating
-- row counts due to missing NULL metadata
ALTER TABLE orders ALTER COLUMN status SET (n_distinct = 10);
By increasing the STATISTICS target, you force the system to spend more time during the next ANALYZE to build a more granular histogram. If the planner still guesses wrong, providing these hints helps it realize that a column has fewer distinct values than it currently assumes, preventing it from incorrectly favoring index scans over sequential table scans.
Key Takeaway: Manually adjusting statistics resolution allows you to "guide" the planner when default histograms are too coarse to distinguish between skewed data patterns, ultimately ensuring the optimizer chooses the most performant join algorithm for your specific workload.