why the optimizer doesn't choose the optimal plan
The optimizer strives to get a plan that is "good enough", and this is not always the optimal one.

A very common reason is a too complex query. Breaking it down to a few queries helps the optimizer choose a better plan.

In some cases, too many indexes on a table can also cause this, as the optimizer 
might use an index that is not the best one because as we said, it doesn't look for the best plan but rather a plan that is good enough.

Other reasons can be the use of local variables (the optimizer doesn't know their values), non SARGABLE arguments, missing indexes, improper use of hints and plan guides, and more..