I always use the rule of thumb that if you need optimizer hints for your query, its time to fix the query or the indexes. Of course in the teeth of an outage that generally isn't an option.

The tools on SQL server for query optimization are better then postgres, but that doesn't always mean problems like the OP don't still occur. I have had more than one SQL server query plan go to hell over time as the tables grew and the optimizer started to make odd decisions.

Edit: to make this a little clearer, I regard the SQL server query optimizer system as "lawful evil". It follows its own rules relentlessly.