First, a caveat. Generally, good superpowers will not save an otherwise poor story and poorly-chosen superpowers probably won’t doom an otherwise good story. If the characters are a bore and the conflict fizzles, it doesn’t really matter which superpowers they have.
1. I would recommend going with versatile abilities/powers rather than more particular ones. It’s a lot more creative, memorable and often visually interesting to see a character use his powers in a way that the user’s manual never intended. In contrast, if Superman tries to fly, it’s generally a perfectly smooth operation and his success is never in doubt because he has a power that is good for nothing else but flying. In contrast, if Yomiko (from Read or Die) tries to fly by using her paper-control abilities to rig together a giant paper airplane, that takes real daring and cunning. “Do you know how to fly that thing?” “Uhh, what about the rain?” “Can your plane withstand gunfire?” The uncertainty helps make the improvised solution more interesting.
1.1. I’d like to see the characters in some situations where their powers are not obviously useful. I think the biggest reason some writers give their characters huge amounts of superpowers (5 or more, let’s say) is that they’re scared that their characters might be caught in a situation that can’t be immediately solved with a superpower. First, it’s more interesting/creative if a character can’t just solve a problem by turning his powers on. (See Superman vs. Yomiko above). Second, superpowers are only one part of the characters’ capabilities, right?* It’s okay if they have some problems/situations that have to be resolved by other means. (When was the last time you read about a wizard that solved all of his problems with magic?) If the superpowers are the only capability that the superhero uses, I would recommend reconsidering whether you’re neglecting the person behind the mask.
*For example, your characters hopefully have skills, practical life experience (from a job or elsewhere), talents besides superpowers, education, personal strengths, resources/assets, etc. Characters may also be able to leverage their reputation, authority and/or standing among different groups (like the police, criminal groups, the public, etc) in certain situations. For example, if your hero’s been framed as a criminal and her bank account’s been frozen, maybe she can march up to Fast Eddie on the corner and demand the perpetrator’s name and a flamethrower on credit. It would take one hell of a personality and/or reputation to convince a hardened criminal to cough up a flamethrower with threats. And she might also need to convince him that she’s likely enough to defeat the perpetrator that the perpetrator won’t come back and kill Fast Eddie for snitching.
2. An overly complex superpower may detract from the development of the rest of the story. My rule of thumb is that if a character’s superpowers take more than 1-2 sentences to explain, there’s probably too much going on. For the most part, time spent explaining superpowers is usually not spent on characterization, transitions/coherence, conflict development, motivations, major choices and other elements that publishers actually care about. (For example, I’ve seen quite a few publishers specify that they’re looking for believable, consistent and interesting characters–like Dark Horse Comics–but I’ve never seen anybody mention superpowers in the submission guidelines. They’re just a means to an end–an interesting story–not the end itself). Alternately, if you want to really delve into the superpowers and you feel like they’re such an interesting component of the story that they warrant that space, you could at least incorporate it into characterization, major choices and the like. For example, in Bitter Seeds, one protagonist’s powers are bestowed by malevolent spirits that demand gruesome sacrifices. Understandably, some characters do not take well to this, so the cost of the powers creates an obstacle to team cohesion and friendships/partnerships.
3. I’d recommend using capabilities appropriate to the story’s tone, style and target audience. If you’re doing an upbeat kid’s story, you might want to leave the machine guns at home. (We weep for you, children’s writers). Personally, I’m using mostly agility-based powers for The Taxman Must Die, an action-comedy that I’d like to keep a pretty soft PG-13.
4. Can the character be challenged? For more details on this, I’d recommend checking out How to Save Insufficiently Challenged Heroes (especially #4).