There are a lot of ways to write a SQL query against a set of tables, given a set of input parameters, to get the desired result. (Not just syntactic or other superficial differences, but differences in logic and how relationships are specified.)

And, of course, an optimizer will handle different SQL queries differently.

No such thing. The database interface is SQL. The query optimizer is something run in the database.

Yes I mean inside the database engine. The engine receives SQL and parses it and then pass the query tree to the optimizer which generate a query plan. My question is how the optimizer could have problems if the SQL is generated as opposed to hand-written? How could that make a difference?

As mentioned in another the comment, because SQL that is hand-written for non-trivial cases will too often be better than what an ORM generates.

This is so hand-wavy I can't really argue against it.