2 ms·

> a fragile mess Oh, I'm not saying it wasn't a fragile mess... Seriously I actually considered the fragility of it to be a positive, precisely because it was

by ris 6y ago

> a fragile mess Oh, I'm not saying it wasn't a fragile mess... Seriously I actually considered the fragility of it to be a positive, precisely because it wasn't a hard "optimizer decision" that I was forcing. If you force something like that, you've got to take full responsibility for it - the planner will lose all intelligence over a certain decision and no longer do the clever thing query planners do and take the current distribution of the database's data into account to allow it to make better decisions. When you upgrade the database and the query planner gets smarter (or dumber), you've got to re-evaluate whether that's still the right decision. I could imagine an app with a bunch of out of date hard-forced decisions to be crippling performance wise. A look-aside of automatically deduced hints with a validity of less than a week seemed like a much lighter touch.

shoo 6y ago
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.

by ris 6y ago

shoo 6y ago

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.