In the past, I've been inclined to believe that god-like characters are fun to write about. They are impossible to kill, cause omnipotent levels of destruction without consequence, and always come out on top. But lately I've been thinking it might be even more interesting for things to go horribly wrong. I find myself wanting to cause problems for my characters that reveal their humanity, and imperfection. Does this mentality make for better fantasy than the former? Why?
It is a better mentality for fiction, no matter which genre.
Probably everyone wants to be god-like one way or another (even so I doubt it would be fun if you really think it through). But reading about god-like characters is only one thing: boring!
Even if you describe monumental battles, nothing really happens. The reader knows who will win from the beginning. There is no suspense, tension, anything worth reading on. Just imagine Superman without Kryptonite: boring. (Besides that, Superman is one of the most boring characters anyway.)
If your character is already a god, in which direction should he develop? A good story shows how the protagonist goes from one end of the spectrum to the other. What the "spectrum" is, is defined by you, the author, for every new novel.
Do you want to read about Superman, flying around, watching kidnappers, catching them and throw them into jail, or do you want to read about that drunken bummer watching how this girl was kidnapped and no-one believes him? Who realizes that he is the only hope for that poor kid, which reminds him of his dead daughter (the reason he has started to drink in the first place)?
Just imagine how many obstacles will get into the bummer's way. How he develops, how he has to get rid of the alcohol to have a clear mind. How he can stand against the kidnappers, untrained and without weapons. He has to become more then he ever was, outgrow himself. That's what people want to read.
If you ask me: Superman sucks!
Indomitable heroes can bore your audience pretty quickly. When you have heroes like that, the general plot of your novel becomes somewhat predictable.
Characters that have flaws and frailties allows you to create more variations in the plot and create a successful series of them. The hero can fail, be hurt, have troubled relations, self discovery or have trouble coming to terms with what ever powers he has and the responsibilities they bring. It makes it more interesting read about how the hero has to face problems just like regular humans despite having special powers or no powers.
To add an extra perspective into the already excellent answer above.
In RP we call characters on a quest to game the rules and create a sort of Mary Sue RP surrogate "Minmaxers" or "Power Gamers" (these are both derogatory terms). As irritating as these people are in a game with an extensively defined ruleset (a "crunchy" system) they have the potential to be even more anguish inducing in a free form system where storytelling and narrative are the goals for reasons that should be self-evident.
One of the key issues is that the game team don't want their game session to devolve into an argument about whether Steve's character could really do x, y or z and whether his achievement of said task could be deemed to "break" narrative plausibility. It's a nightmare that Game Masters the world over wake up from in a cold sweat. It's the archetypal case of a session getting away from the person who's supposed to be running it.
This is true to such an extent that game writers have traditionally gone to great lengths to curtail the possibilities for such behaviour in the writing of their systems. Players must all be managed to expect that their characters will stick within reasonable grounds and that you can't just have a flying sky fortress and an army of drones to pilot the death planes and rain down fire upon the proles if they should dare to dissent.
I have taken a different approach to dealing with this problem. It's called: "Give them enough rope to hang themselves."
Fine, I say, have your sky fortress, have your drones, rain down fire on dissenting proles. But then what?
In one extreme case (which was actually a character concept invented by a creative and excellent game player, not your everyday min-maxer) we had a guy who basically was Sauron, he remained Sauron, he was a proper fully fledged demi-godlike dark lord and no one had ever offered him a sensible challenge in millennia of dark sorcerous despotism.
In the end it was the boredom that got him.
In the end he was begging the grim reaper to sharpen his scythe and get to chopping. There wasn't any land worth despoiling, no rebellion worth quashing, no hero worth humiliating. He'd seen it, he'd done it, he'd burned the t-shirt and called it heresy. The problem was death didn't want him.
In the end he locked himself in a tomb in his most remote castle, turned out the lights, lay down and hoped that, like some kind of epic-level insomniac he would eventually drift off.
The story of the character began at the point at which he, like the unsuccessful insomniac just accepted that it wasn't going to work that way and he left his castle, chastened, humbled and still very much filled with dark power and an irritating immortality and went on a quest to find death.
This is an extreme example but the point is that the story always finds every character in the end. Superman's story isn't about the point where he's beating up criminals and saving runaway trains. That stuff's easy to deal with. Hell, even Kryptonite's just a poison, put it in a lead jar on a high shelf clearly labelled, all sorted. The problem comes when all the cool things about the character become liabilities. When his son or daughter takes his or her halfway superpowered butt out to a party during adolescence and accidentally destroys half of downtown in a drunken accident. When Lois Lane is about to die at 84 years of age and Supes still looks like he's only just creeping up on forty from the right side. These are the points of drama and you invoke them if that's what you want to talk about.
If you want to actually write about heroes then your heroes have to start out kind of lame. If you want to write about gods you will write stories of the staleness and inadequacy of godlike powers. There is always a catch to anything.
In fact, early Superman stories were not about Superman. They were about the villains trying to outsmart or defeat Superman and their constant failures to do so. Superman was not there as a paragon except as a by-product. In story terms Superman is a problem to the characters we can most relate to, the frail, imbecilic and criminally inclined humans. Superman arises from a culture of Crime-Does-Not-Pay fiction with an irresistible moral framework that supposed some sort of natural justice in the world. Superman, in this context, is a cautionary tale: Don't commit crimes, Superman will get you.
These were simpler times, culturally speaking, less cynical times and cynical people have a much harder time with a classic Greek demigod hero like Superman. Ironically, the Greeks themselves had a very dim view of such wretched souls and they were always pawns of the gods themselves, their powers a curse attracting envious attention of jaded immortals. The crazy thing about Hercules was not his accomplishment of the 12 tasks he was set but the fact that he had to live with the knowledge that Hera had driven him mad and made him murder his own wife and children.
The message of any story is that, at some point life sucks. In heroic stories it should suck at the beginning and get better. In tragedies it should start fine and inexorably move towards sucking as you approach the end. Stories about Gods are all tragedies.