My question here this, for the purpose of convenience and ease of maintenance, is it worth to follow ORM pattern and ignore the performance trade off?
It depends, mainly on the following things:
does your application suffer from measureable performance issues?
does your ORM provide further optimization features?
do you use the ORM just for avoiding the need of writing SQL manually, or do you use it to decouple your application from the specific DB vendor (and it's SQL dialect)?
In case the answer to "1." is "no", stop overthinking this and stick to your ORM. Optimizing "just in case" leads to overengineering and is known as "premature optimization".
If the answer to "1" is yes, "2" should be always worth a check, make sure you understood the capabilities of your ORM well and tried what is possible. Try googling for " performance optimization", and you will get tons of results for every popular ORM.
If "2" does not help, and coupling to a specific SQL dialect is not a huge problem for you, at least not in some restricted areas of the code, then go ahead and write your queries manually where it helps you to reach the performance requirements.
To be honest, ORM technology is still subject of controversial debates about if it is really useful, or if it is an anti pattern. You will find lots of articles in the Web or posts on Stackoverflow (like this 9 year old one) discussing the pros and cons of ORMs versus "native SQL programming". Lots of the arguments against ORMs are founded on the observations you already made, that it is often easier to optimize queries manually than to configure an ORM to produce optimized queries.