Thing is, MySQL, with judicious use of STRAIGHT_JOIN, won't do the same thing. And generally MySQL is much more predictable because it's much less sophisticated: it only has a couple of join strategies (pre 8.0, only nested loop join) and quite limited query rewriting, so you can - with practice - expect a query plan as you write the SQL. And in practice, there's usually only two or three really big tables involved in performance-sensitive queries, tables which you need to pay attention that you don't end up doing scans on. The rest of the tables you can leave up to the planner.
> And in practice, there's usually only two or three really big tables involved in performance-sensitive queries, tables which you need to pay attention that you don't end up doing scans on.
The problem is that those few big tables are often critical to most of the queries and so each query has to carefully use the right hints or query order if the planner isn't doing much.
It almost makes me wonder if indexes themselves should get hints or at least priorities to help the planner order operations.
I honestly think that for live operations an SQL database is more trouble than it's worth - sooner or later you need more control than it gives you, so you're better off using a datastore that gives you lower-level access to construct and use your own indices explicitly. SQL makes sense for reporting-type use cases where you don't know exactly what queries and aggregations you'll be doing ahead of time (but have a rough idea of which columns you might need to index on), but that's all.
