In practice what happens in this case is that the query slows down over time, often days or weeks after deploying the thing that makes the query worse. It takes

In practice what happens in this case is that the query slows down over time, often days or weeks after deploying the thing that makes the query worse. It takes time to fill up the data that slows things down. This gives you plenty of time to monitor and take action. If it does happen more quickly than that (e.g. if a table starts getting filled for the first time, statistics can be off) then rolling back is easy. But if the query planner decides to screw up your query plan it happens instantaneously and there's no possibility of rollback - only emergency deploying code fixes to try to tweak the query. In most OLTP use cases, you almost always know exactly how a query is supposed to execute, so always using index hints is totally reasonable to prevent bad query plans. Basically, you breaking your own query with bad hints usually breaks things a lot less and at better times than the query planner doing it, and is usually easier to fix too.