Had to fight this a few times, planner thought it was smart to scan an index for a few million rows, then throw almost all of them away in a join further up, ending up with a few hundred rows.
Caused the query to take almost a minute. Once the join order was inverted (think I ended up with nesting queries) the thing took a second or two.
Join order / type and which indexes to use would go a long way, thats pretty much all I need to do on MSSQL server if the planner is not cooperating.