Every module is coded in a different way. While some can be benign, others might be less so. Some modules create additional load because they force pages to be re-rendered, or for permissions to be re-checked and so forth. This is a community-project, which means lots of free great code, but also not everything is coded perfectly at all times, since everyone is contributing in an open source fashion.

Each module that is activated also creates some additional function calls, depending on the functions found, those functions will either run once and be cached, or be perpetually accessed every time their hook_ is called.

Is adding one more hook_ a problem? Not inherently, but clearly the more you add, the more calls need to be made.

If you want a system that can handle hundreds of modules, each with potentially tens of functions, each of which is doing a calculation then surely that increases your server's stress? There is nothing magic about any CMS, it is, as you say, just a bunch of PHP code.

An optimal system needs careful consideration. If you want a site like facebook, where you are dealing with tremendous stress considerations, you generally just build the site up from scratch. No CMS, you put all the parts together, with the absolute zero-line that anything non-essential is scrapped. If you have the budget to build your site from the ground-up, you just don't bother with any CMS and build your own (or use an extremely lightweight framework as your base point).

Drupal gives you a lot out of the box, not many CMS's can crunch out full blown applications with such flexibility and extensibility, but flexibility comes with a price. Optimizing Drupal sometimes involves stripping things away, researching how things were done by others, and improving the code that others have made.

Your question and concern is extremely general, so it is hard to give you anything concrete in return.

Can you run a complex application on Drupal? Absolutely

Can you run a site that has an extremely fast response time? Most definitely

Can Drupal be used on a website that has high traffic, sometimes with burst traffic intervals? For sure

can I get a high performant site, by installing any module I find on the net, keeping it on, and just expect that clicking "setup" is enough to achieve high performance, and high speed? Doubtful…

You need to be good at web development to be good at performance and optimization. It is a very complex subject, and many solutions, depending on need, can bring you beyond drupal, to things like memcache / apache / mysql optimization.

Drupal gives you a giant flexible application building API/environment. You then need to take care of it to make sure it retains high performance.

Hope this helps!