In this lesson, you will learn how to globally discourage specific execution strategies by tuning system configuration parameters to steer the PostgreSQL query planner toward more efficient paths.
The Planner’s Cost-Based Decision Making
As we discussed in previous lessons, the PostgreSQL optimizer assigns a "cost" to every possible plan (Nested Loop vs. Hash Join, Sequential Scan vs. Index Scan). It calculates these costs using internal constants like seq_page_cost and cpu_tuple_cost. Sometimes, the planner incorrectly assumes that an expensive operation (like an external disk sort or a sequential scan on a massive table) is cheaper than it actually is.
Instead of forcing a plan with complex rewrites, you can use configuration parameters to adjust the "weight" of these operations, effectively nudging the planner away from strategies that you know will perform poorly in your specific environment.
Adjusting Global Preferences
If you notice the planner frequently chooses a Sequential Scan when an Index Scan is almost always faster, you can discourage the scan by increasing the cost factor for sequential reads:
-- Discourage sequential scans globally
SET seq_page_cost = 2.0;
By doubling the seq_page_cost, you tell the optimizer that reading pages from a sequential scan is twice as "expensive" as it previously thought. Consequently, if an index is available, the optimizer will see the Index Scan as the lower-cost, more favorable alternative.
Similarly, if you want to avoid expensive Nested Loops on large datasets, you can manipulate the cpu_tuple_cost:
-- Increase the cost of processing each tuple to favor Hash/Merge joins
SET cpu_tuple_cost = 0.05;
When to Use This Approach
Global configuration changes should be treated with caution. Because these are system-wide parameters, a change intended to fix a slow report might accidentally degrade the performance of a high-concurrency transactional query. Always test the impact of these changes using EXPLAIN ANALYZE before applying them to a production configuration file (postgresql.conf).
Unlike query-level hints, these parameters provide a "soft nudge" rather than a hard constraint, allowing the planner to still revert to its original plan if it detects a scenario where the discouraged operation is significantly better.
Key Takeaway: You can steer the query planner by globally adjusting cost constants in the system configuration, making inefficient access methods appear more expensive and encouraging the optimizer to select more performant alternatives.