Totally get what you're saying. Ideally there's a way for user to supply additional information or constraints to the optimisation process which are used to influence the results without turning the optimisation process off completely. Although any scheme of doing that could, as you say, produce poor results if the distribution of data changes over time or the db planner code is changed.
I used to work on non-database decision support tool that incorporated a custom optimiser which was used to spit out crude engineering designs for a particular kind of construction problem. The optimisation problem was difficult & the implementation to solve it was not state of the art: there were a few preprocessing stages that were used to lock in some early decisions using heuristics -- which helped massively reduce the search space, then a global optimisation approach was run on the remaining sub problem. The result was that the overall algorithm would locally optimise after perhaps locking in a bad early decision. Once the software was delivered to the client and in use by a small team of users I later discovered that the users had figured out that by running the software repeatedly with very small adjustments to input parameters (adjustments that should not obviously matter) they could bump the optimiser into outputting wildly different designs. It was a little bit like repeatedly pulling the handle on a one armed bandit until it eventually gave you a decent output.
It was clever of our users to figure this out but the overall UI/UX was appalling, they had to click and re run a somewhat slow batch process until it produced a reasonable result. It would have been much better to give the users a user interface where they could directly override or constrain parts of of the engineering design problem in an ergonomic way, and then let the optimisation algorithm loose to make the remainder of the decisions.