I think the general attitude in the Postgres community is been from a purist point of view. When you have a codebase around 40 years old, you do have to think carefully about what you put into it, as it might not be that easy to take it out again. However, yes, I do think hints would be useful for Postgres, providing they're done well. It would be good to at least have something to assist with selectivity estimations. Those are at least not directly forcing the planner into a single choice. New planner/executor smarts, such as something like Memoize added in PG14 could still be considered after upgrading an older pre-PG14 application with such hints, but perhaps not if the hint told the planner that it must nested loop join these two tables.
The optimizer killing your heavily-loaded production because it randomly switched a frequent query to a bad plan is pretty annoying. People mostly put up with this because for a single-instance DB you can easily maintain 4x spare CPU capacity but it's impossible at scale.
What may be better is if the executor provided feedback to the planner to tell it the new plan was worse than the old one. With that, you might be able to recover much more quickly and less likely to get a midnight phonecall. The tricky part would be when should the planner then retry the new plan again. Also, other factors that influence the plan's execution time such as locking would be a cause of noise for any sub-system that was monitoring this.
Not really, because at scale tables don't change suddenly unless you release something -- and that will re-generate the plans after the perf testing.
Whatever changes slowly accumulate over time will be covered in the next release which normally should be very frequent, daily or more.
That's called a hint :)
Give the API a scary name if you want, but getting 3 Am calls because the planner suddenly decided to go off the rails or seeing pages of convoluted SQL because in (current version) it’s what it takes to get the plan you need is not fun.
It’s long past time for database developers to accept that the high level is not always the right solution, it should be that in 95 or 99% of cases, and the better it is the better my life is, but sometimes you got to write exactly what you need.
Most language designers are fine with it and will allow either embedding or calling into lower-level language (down to hand-crafted assembly), it would be great if database devs could get on with that program.
And few people tend to remove hints after DB version upgrades, where the optimizer magic (or your table stats) have improved. Now you're limiting optimizer's choices. That's been a problem (by now) for decades in the Oracle world with lots of legacy SQL code full of random hints where some of them aren't even valid anymore, but others still are - and limit optimizer's choices.
At least Oracle folks had enough at some point and introduced an "optimizer_ignore_hints" parameter [1], so all legacy hints that were added 20 years ago just get ignored - and the modern optimizer does a much better job getting things right.
I do regularly use hints in SQL tuning and troubleshooting experiments, just to verify and prove that a better plan is theoretically and physically possible - and if yes, then go from there. When the (deliberately placed) hints make the query faster, the next step is to check the hinted, faster plan's optimizer cost estimate and drill down from there: Why did optimizer think that the other plan was cheaper or why did the optimizer think that the faster plan was more expensive. Then you end up with measuring row-count misestimates, etc...
But yes, plenty of people (including myself) have made legacy Oracle apps run much more efficiently and faster just by globally telling the DB to stop paying attention to all the old random hints lingering on and gathering object stats using the modern settings & defaults (doesn't always work though).
[1] https://docs.oracle.com/en/database/oracle/oracle-database/1...