Gemini 4 Carbon: Our next frontier model
deepmind.google·6d ago
AI Engineering
Lobsters·Jun 27·5 min read
pg_plan_advice — help the planner get the right plan
TL;DR
Database query planners sometimes make suboptimal decisions based on changing data distributions. The pg_plan_advice module allows users to provide specific guidance to the planner using a mini-language for plan advice.
✦ Why It Matters
Engineers can use pg_plan_advice to optimize query performance while being cautious of data changes.
Key Takeaways
- Load pg_plan_advice to influence query planning decisions.
- Use EXPLAIN with PLAN_ADVICE to analyze execution plans.
- Consider risks of constraining the planner's choices.
- Experiment with plan advice to find optimal query strategies.
Full Summary
Database query planners are designed to optimize how data is retrieved, but they can make poor choices if the underlying data changes. The pg_plan_advice module was developed to allow users to influence planner decisions by using a specialized mini-language that describes preferred execution plans.
Users can load this module system-wide or for individual sessions, enabling the use of the PLAN_ADVICE option in the EXPLAIN command to view advice strings for chosen plans. These advice strings include directives like JOIN_ORDER and HASH_JOIN, which guide the planner on how to execute queries.
While this tool can stabilize performance by enforcing good plans, it also carries risks if the planner is restricted from adapting to new data distributions. Engineers must weigh the benefits of using plan advice against the potential for poor performance due to rigid planning.