And, its noted in the video/transcript, but for clarity: This is talking about new extensibility in Postgres 19 that makes it easier to do Postgres planner hints / plan management extensions.
The patch that was committed is part of a larger proposal (pg_plan_advice) which, if it ends up being committed, would add a version of planner hints to Postgres itself (in contrib). It remains to be seen where that goes for Postgres 19.
This is great, I have harped on PG's aversion to plan hints quite a bit in the past (and also lack of plan caching and reuse).
One of the biggest issues I run into operationally with relational db's is lack of plan stability and inability as a developer to tell it what I know is the right thing to do always in my application.
I use MS SQL Server more than PG currently and if it did not have plan hinting it would have been catastrophic for our customers.
Its still not perfect and over the years I am starting to think that having smart optimizers that use live statistics may be more of a detriment than help due to constantly fighting bad plan generation at typically the worst times (late at night in production). At least with compiler optimization it happens at build time and the results can be reasoned about and is stable. SQL is like some insane runtime JIT system like a javascript engine that de-optimizes but not deterministically based on the data in the system at the time.
Using various tricks and writing the query in slightly different ways while praying to the planner god to pick the correct plan is well infuriating due to lack of control over the system.
I much prefer systems like Linq or Kusto where it's still declarative but the pipeline execution order follows the order as written in the code. One of the most helpful and typical hints that solves issues is simply forcing join order and subquery / where order such that I want it to do this filter first then this other one second, then typically the optimizer picks the obviously correct indexes to use. Bad plans typically try and rewrite the order do much less selective thing first destroying performance. I as the developer normally know the correct order of operations for my applications use and write the query that way.
I've actually come around to the Postgres way of thinking. We shouldn't want or need plan hints usually.
Literally every slow Postgres statement I worked on in the last few years was due to lack of accurate statistics, missing indexes, or just badly designed queries. Every one was fixable at the source, by actually fixing the core issue.
This was in stark contrast to the myriads of Oracle queries I also debugged. The larger older ones had accumulated a "crust" of plan hints over the years. Most not so well thought out and not valid anymore. In fact, often just removing all hints made the query faster rather than slower on newer Oracle versions.
It's so tempting to just want to add a plan hint to "fix" the suboptimal query plan. However, the Postgres query planner often has an actual reason for why it does what it does and overall I've found the decisions to be very consistent.
Thanks for posting! There is a hand-edited transcript here as well, for those who prefer text: https://pganalyze.com/blog/5mins-postgres-19-better-planner-...
And, its noted in the video/transcript, but for clarity: This is talking about new extensibility in Postgres 19 that makes it easier to do Postgres planner hints / plan management extensions.
The patch that was committed is part of a larger proposal (pg_plan_advice) which, if it ends up being committed, would add a version of planner hints to Postgres itself (in contrib). It remains to be seen where that goes for Postgres 19.
This is great, I have harped on PG's aversion to plan hints quite a bit in the past (and also lack of plan caching and reuse).
One of the biggest issues I run into operationally with relational db's is lack of plan stability and inability as a developer to tell it what I know is the right thing to do always in my application.
I use MS SQL Server more than PG currently and if it did not have plan hinting it would have been catastrophic for our customers.
Its still not perfect and over the years I am starting to think that having smart optimizers that use live statistics may be more of a detriment than help due to constantly fighting bad plan generation at typically the worst times (late at night in production). At least with compiler optimization it happens at build time and the results can be reasoned about and is stable. SQL is like some insane runtime JIT system like a javascript engine that de-optimizes but not deterministically based on the data in the system at the time.
Using various tricks and writing the query in slightly different ways while praying to the planner god to pick the correct plan is well infuriating due to lack of control over the system.
I much prefer systems like Linq or Kusto where it's still declarative but the pipeline execution order follows the order as written in the code. One of the most helpful and typical hints that solves issues is simply forcing join order and subquery / where order such that I want it to do this filter first then this other one second, then typically the optimizer picks the obviously correct indexes to use. Bad plans typically try and rewrite the order do much less selective thing first destroying performance. I as the developer normally know the correct order of operations for my applications use and write the query that way.
I've actually come around to the Postgres way of thinking. We shouldn't want or need plan hints usually.
Literally every slow Postgres statement I worked on in the last few years was due to lack of accurate statistics, missing indexes, or just badly designed queries. Every one was fixable at the source, by actually fixing the core issue.
This was in stark contrast to the myriads of Oracle queries I also debugged. The larger older ones had accumulated a "crust" of plan hints over the years. Most not so well thought out and not valid anymore. In fact, often just removing all hints made the query faster rather than slower on newer Oracle versions.
It's so tempting to just want to add a plan hint to "fix" the suboptimal query plan. However, the Postgres query planner often has an actual reason for why it does what it does and overall I've found the decisions to be very consistent.
- any chance of vector support beyond pg-vector?
I’m still waiting for the future where planner is a plugin for db.
Seems this is a (small) step in that direction