I can understand that people wants to get absolute control on their queries sometimes. I never used hints so I shouldn't even write this note but IMHO that page has a quite balanced view of pros and cons of existing hint systems.
I'd love to have a way to lock a plan in a temporary "emergency measure" fashion. But of course it's hard/impossible to design this without letting people abuse it and pretend it's just a hint system.
Could this be achieved by extending prepared statements? [1] The dirty option would be to introduce a new keyword like PREPAREFIXED.
The first time such a statement is executed, the execution plan could be stored and then retrieved on subsequent queries. There would be no hints and the changes in code should be minimal.
Once a query runs successfully, the users can be sure that the execution plan won't change.
>But of course it's hard/impossible to design this without letting people abuse it
Is this more important than having predictable execution times?
[1] https://www.postgresql.org/docs/current/sql-prepare.html
To me: no. To the Postgres team: apparently :)
I found the cons section somewhat disappointing given how much respect I have for Postgresql maintainers in general.
Most of the "problems" are essentially manifestations of dysfunction within users or PostgreSQL development itself. People won't report optimizer bugs if they can fix them themselves, etc. (far more likely they won't report the bugs if they have no way to prove their alternative query plan actually performs better, eg: by adding a hint).
Many are just assertions which I highly doubt would hold up if validated ("most of the time the optimizer is actually right", "hints in queries require massive refactoring" ...).
They don't need to run an alternative plan, they just need to report that the main plan did far more work than needed.
In the real world, when users have a problem in their mission-critical system, they ask for a solution, not just give up on the mission.
> Many are just assertions which I highly doubt would hold up if validated ("most of the time the optimizer is actually right", "hints in queries require massive refactoring" ...).
Their 20 years of experience beats your idle speculation.
Perhaps.
But it comes across to me on that page as arrogance and dismissiveness of user needs. Hence why I find it disappointing, even if it is mainly in how it is expressed.
And how do they know that the chosen plan is not already optimal? How many reports do you think you'd get if every time someone encountered a "slow" query they reported it? And what fraction of those would be finally found to be caused by a bad optimizer as opposed to the user's fault like a missing index, outdated statistics, ... 1%? 0.1%? See stack overflow for a sample of that ratio. Be careful what you wish for.
If hints existed performance reports could be "default plan of x is slow, I know because when I use hints w,z then it's fast".
What should they do in the meantime while they're waiting for the pg project to acknowledge the problem, someone to propose a solution, and someone to implement a fix, just sit on their hands and twiddle their thumbs while their database server spews smoke? Hints are an escape hatch that empowers users to take over a when the optimizer veers off course. Not having any way to control the plan is like a tesla autopilot that doesn't allow the driver to take control over the car in an emergency.
Also, distrusting users to report performance issues is a weird attitude and gives a bad taste. Maybe it's true, or maybe you need to make it easier.
It's almost self-evident that this is the case, because if they had, they'd have implemented some more fine-grained way of overriding the query optimizer when it inevitably fails at times.