There are too many questions to make any absolute statements. You hint at a couple here.

Specialization of requests reduces data transport, but increases testing surface area and drastically reduces caching options - including within the database. Years ago we discovered that our bottleneck in Oracle was being caused by query cache evictions (worse, in 9i only queries currently in the cache could run, so new queries were blocking in the query planner. I'm not sure if this was ever fixed.)

One of the important uses of etags within your infrastructure is to allow you to incorporate data manipulation into your caches. If your functions are deterministic you can store the results of a calculation, instead of the source data for that calculation. Cache hits then don't just save bandwidth, they also save computation, which reduces the sequential parts of the response, fighting Amdahl's Law.

If you intentionally write poor code with plain SQL you will have the same problem as the auto-generated poor code of a lot of ORMs? Yes?

I don't think Wordpress is relevant here in any way. If the code is poorly written and has N+1 queries problems then it is either poorly written or is trying to half-ass an ORM.

Lastly, I would like a lot of citations to prove to me that ORMs produce more efficient queries. What produces more efficient queries in all my experience is a good understanding of relational databases, better query planners and better database design.

This was very much a problem for WordPress back in its "glory days." I had to go in and optimize a lot of stuff to get our website's performance to be sub-5s. (I haven't worked with WordPress in years, so I can't comment on its current state, but I wouldn't be surprised if this is still a thing.)