In this lesson, you will learn how to manually steer the PostgreSQL query planner when its cost estimation fails to find the most efficient execution path.
Why Hints and Rewrites?
PostgreSQL’s cost-based optimizer is incredibly sophisticated, but it is not infallible. When dealing with complex JOIN orders or obscure data distributions, the planner might miscalculate the cost of a plan, leading it to choose a slow sequential scan over a fast index lookup. Because PostgreSQL—unlike some other RDBMS—does not natively support traditional "optimizer hints" (like /*+ INDEX(table) */), we rely on structural query rewrites to achieve the same result.
Forcing Plan Shapes
When the planner fails, we can force a specific join order or access method by changing the structure of the SQL itself.
For instance, if the planner insists on a Hash Join when you know a Nested Loop would be faster for a tiny subset of data, you can isolate the subset using a subquery that the planner treats as a barrier.
Consider this example where we want to force the evaluation of a small table before joining a massive one:
-- Before: Planner might choose a Hash Join on huge_table
SELECT * FROM huge_table h
JOIN small_table s ON h.id = s.id
WHERE s.status = 'ACTIVE';
-- After: Rewriting as a CTE or subquery to force order
WITH filtered_small AS MATERIALIZED (
SELECT * FROM small_table WHERE status = 'ACTIVE'
)
SELECT * FROM huge_table h
JOIN filtered_small s ON h.id = s.id;
By using the MATERIALIZED keyword (introduced in newer versions), we effectively break the optimizer’s ability to "flatten" the query. This prevents the planner from reordering the joins or merging the subquery, forcing it to consume the filtered_small result set first.
Strategic Rewriting
Rewriting isn't just about forcing operations; it’s about reducing the search space for the optimizer. If you notice the planner is struggling with a complex OR condition, splitting the query into two SELECT statements connected by a UNION ALL can provide the planner with two simpler, independent sub-plans. This bypasses the cost model's confusion regarding correlated predicates, allowing each segment to be optimized in isolation.
Key Takeaway: When the PostgreSQL optimizer makes poor choices due to faulty cost estimation, use structural rewrites like explicit CTEs or UNION ALL to bypass the planner's heuristics and enforce an efficient execution sequence.