pg_plan_advice: Plan Stability and User Planner Control for PostgreSQL
October 20–23
PostgreSQL’s query planner attempts to estimate the runtime cost of various plans, but those estimates can be based on statistics which are sometimes misleading and can change over time. This means that the planner will sometimes switch from a good plan that runs quickly to a poor one that runs extremely slowly, sometimes without warning. At other times, even the initial choice of plan will be suboptimal. Historically, PostgreSQL has provided few ways for users to control planner behavior, making such problems difficult to prevent or resolve.
In this talk, I’ll discuss my work on pg_plan_advice, a contrib module which I hope will ship as part of PostgreSQL 19. pg_plan_advice is intended to serve a variety of use cases, including (1) regenerating in whole or in part a plan previously discovered to work well, (2) enforcing choice of an alternate plan that the user prefers over the one that the planner would normally select, or (3) debugging the failure of the planner to produce what the user believes to be the correct plan. In this talk, I’ll give an overview of what this module does, how it does that, and what problems had to be solved in order to enable it to do those things In addition, I’ll briefly discuss potential future features that could use this feature as scaffolding.
