Personally I would drop the word Realism and instead use Believability when it comes to games. Former can hinder the game enjoyment (depending on game of course. still even for realistic war simulators a 50/50 realism/fun mix is preferable) if taken too far, as you said, but latter can only serve to make game more immersible. (again depending on game) This is very true to RPGs.
Dragon Age II, or any fantasty game, mage throwing bigass fireballs is believable. They are mages for Petes sake! Rogue doing acrobatic moves that are bit unrealistic. Still very believable! They are just VERY nimble and acrobatic, thats how we subconciously rationalize these things when reading fantasy stories or playing games.
Rogue doing an obvious teleportations and suspension of disbelief starts to waiver. Although I realise it is a gameplay element, not a lore element so the characters are not actually "capable" of doing those things. They are just skills and are there just for the gamers to use.
It just looks dumb. Realism has little to do with it.
Characters of Mass Effect 2 surviving space in gasmasks. Not realistic, and certainly not believable! Being a lore element it is an epic brainfart from a game that tries to establish itself as a serious Sci-Fi world. Not a game breaking design choice (actually it is one of my fav games of all time) but I cant figure out what the heck were they were thinking...
Keeping things believable is a very very important thing to me even though actual real world realism goes out of the window. I do not even care about realism that much.