That's unfortunately naive. For example, for a certain type of query there are two possible plans. Which one is better depends on the parameters to the plan and the distribution of the data, and getting the plan wrong can be the difference between the query finishing and the query not finishing. With a stability guarantee you can't even try to make an intelligent choice based on the parameters.
But that's exactly the point. If the SQLite got the plan right at first, you know it will always "get it right" in the future, i.e. the query will finish. Maybe it'll go from being optimal to somewhat less optimal, but it'll still be fine -- or at worst you'll see a very, very gradual slowdown (as your tables grow) that you'll have time to investigate later.
With MySQL or Postgres, that can suddenly change and go from, in your example, the query finishing, to the query not finishing, because the database suddenly chooses to use a different plan. That can be a catastrophic production failure, when a query that used to run in 10ms now takes 10s.
Reality is much more complex. Unless your data set size and the distribution continously stay the same, the algorithm that made complete sense initially (say an index nested loop) will often not make any sense anymore after some growth. Imagine a nested loop over a couple hundred thousand rows where the inner side's index lookup always has to hit storage. That just won't perform and will, if run concurrently, slow down the entire system to a standstill because it consumes all the IOPS. In many cases that slowdown won't be gradual at all - it'll be fast as long the working set fits entirely into the available memory, and will stop very soon afterwards (depends on value distribution).
For some data sets, it's reasonably quick (100ms), for others it's quite slow (1600ms).
Whereas if I add 'force index for order by' to the query, I get a predictable 150ms across the board.
Predictability is often more important than optimality. Variance in service response time is hard to work around, especially when there's a UI involved. Algorithmically, that often means always using the logarithmic data structure even when the constant overhead is higher for smaller data.
Another place where they do this is oversubscribing a server, by putting 4 services that need 20-30% of the system memory or network to run, and don't understand why throughput drops off by 90% when the system becomes saturated and starts swapping or dropping packets.
There are terrible drawbacks using any of those techniques (data changes, indexes being modified / dropped, etc) which is why, imo, they should very rarely be used. However, 'allowing direct specification of the query plan' is already implemented in that respect.
The better way to address the problem of different parameters causing performance issues is forcing recompiles which will solve the problem 95% of the time.
I know Oracle and Postgres implements this -- don't know about SQLServer, MySQL, etc...
[0] http://docs.oracle.com/cd/B19306_01/server.102/b14211/stats....
[1] http://www.postgresql.org/docs/current/static/runtime-config...
edit: found/added info for postgres
Having worked with SQL Server for years as a DBA, I've never had to use it once, but I was never working with databases over, say, 1 TB. So while we did encounter slow query problems, they could usually be resolved by introducing reliable indexes, and the performance cost of this was low.
With a "real" database, if your data has an unfortunate pattern that confuses ANALYZE statistics, you can suddenly get thousandfold slowdowns because the query planners assume that their statistical information is perfect and do not use any kind conservativeness in their planning. So you need a babysitter for them: a DBA.
I've missed the predictability of SQLite when moving stuff to PostgreSQL. If the query plan in SQLite looks good, it'll stay good. In a real database, you're sitting on a timebomb waiting to go off when autoanalyze ends up running at a bad time.