Tarm: Super heroes are popular because of many things and they might often be ridiculous but I'd hardly call them stupid. If I did I'd be calling a big part of the human psyche stupid. King Arthur and similar myths are the basis for our current super heroes popularity. Humans need larger than life persons in their lives be they real or current.
I agree that larger-than-life people are a fixture in most cultures, and that they fulfill a fundamental role in many cultures.
However, I would also like to point out that hardly any other culture, historical or present, has larger-then-life figures that are as silly and primitive as "superheroes". If you look at (for example) Greek, Norse, or Indian mythology, you find rich sets of characters, each with their own motivations. Their sagas often deal with the interrelations of these personalities, or how certain events changed them.
Superheroes, however, are extremely primitive. They are built on a weird concept that people can be purely "good" or "evil", and the solution to any problem is that the "good" characters kill or incapacitate the "evil" ones, for which extreme violence is often seen as justified because the opponents are "evil".
Now look at the mythological larger-than-life personalities from other cultures. You mentioned the Arthur saga - is Lancelot "good" or "evil"? He is the most valiant knight of the kingdom, but his forbidden love to the Queen also brings the kingdom down in the end. He's neither good or evil, he's a human being with all the strengths and weaknesses that this entails - and which enable interesting stories to be told about him.
Likewise, is Prometheus good or evil? Hera? Kali? Loki? Hagen? Arguably, Hagen of Tronje is one of the most interesting Nibelung characters precisely
because he has a complex set of motivations.
Unfortunately, US culture has boiled the concept of larger-than-life figures down to the extremely primitive, and ultimately boring, concept of supergood superheroes battling superbad supervillains. It's quite sad when you consider how rich and varied Norse or Greek culture was centuries ago.