Fundamentally, reality for humans involves the necessity of solving problems, which provides benefits but at a cost. Hence, solving problems in principle is good. However, there are problems so complex and difficult that no human can solve them at the moment, and they also can't solve many problems at the same time.
A hero is not just a "good person", it's someone great who embodies many "good values", which is very difficult or impossible to begin with, but also takes on the hardest challenges and costs for "what's right" or the "greater good".
It's only natural humans envision a version of themselves that can do all of this. The concept of a hero is a representation of an ideal of a person who solves the hardest problems despite the hardships.
This is why heroes are compelling.
Your "psychologically alive" and "realistic" arguments regarding villains can be explained in two ways:
First, villains represent the difficulty and cost of solving problems I mentioned above. No one respects or appreciates anyone solving easy problems for obvious reasons. What's compelling isn't the villain per se; it's how hard it is for the hero to defeat them.
Second, villains also represent the conflict people have within themselves. "Doing what's right" is often a very difficult problem to solve with corresponding costs. Many people can't take it, and they either give up or break. This is often more relatable for people. They see themselves in the villain's shoes because it's way more likely to fail than it is to solve a difficult problem.
In conclusion, heroes are more of wishful thinking, and villains more relatable. But ultimately, what's always more impressive, respected, and compelling is a difficult problem being solved: the hero arc.