The primary problem is that picking indexes and execution algorithms essentially involves traversing an exponential space (and taking guesses about costs of things). Modern DB optimizers have hundreds (or thousands) of heuristics to do this well, but every once in a while they pick the wrong path and run a suboptimal query. This might be ok for offline analytics, but can be disastrous for production OLTP environments because a small change in statistical information can trip up such an edge case and the live system will crawl to a halt. For example, Postgres optimizer has been around for a long time and is very mature, but they still fix these bugs quite often AFAIK.

In many production environments admins end up having to hack the queries to "trick" the optimizer into doing what they want, which of course defeats the whole purpose. So, for real-time systems, being able to specify indexes manually is actually a productivity boost, because you often know exactly how you want the query executed.

I'll try to dig up some info on this, I don't have any links off the top of my head.

Even if you manually write the query plan, you are still subject to similar disasters. That's because your data is changing, which can cause the query performance to fall off a cliff without changing the plan.