I don't think there's any doubt that it certainly can be resource-intensive, but to just make that blanket statement without more qualification is oversimplifying.
So much depends on what kind of application it is, what kinds of modules you're using ... how you're using them, how up-to-date they are, etc. etc.
If you're not careful and not on top of things, any CMS can become resource-intensive without your even knowing about it. That said, I'm not going to lie -- Drupal, more than most CMS's, does seem to come with a lot of extra "junk." This isn't so much an issue with core as it is with certain modules just not "cleaing up" after themselves. For example, I was using one module that, unbeknownst to me, was storing all kinds of info in a temporary directory without ever cleaning it out. It wasn't until after I was going through several of my directories that I learned this directory was already over 6 gigs. And if you're not on the lookout, there could be other modules that just write all kinds of extra data to your mysql tables, which just accumulates and sits there, doing nothing other than making your db unnecessarily large.
But like with anything else, you just have to roll with these things. You go and search for updates and patches to fix the problem or you fix them yourself (in my case, I just added some extra code to the module that cleans out that directory after every use). And with the database, I just found the module creating the extra data, and changed a few things.
Honestly, though, these could very well have been Joomla or Wordpress extensions, and it's all just part of the process. You love extensions because other people have contributed free code for you to use. But sometimes you hate them because ... like with anything else, the people coding these things aren't perfect, and they often have bugs, just like the stuff that we code.