Well, most of what I know is from the Rogue forums, since most rogues you'll encounter normally will suck.
Basically, deep Assassination is good for PvE. Hunger for Blood, with the glyph, gives basically permanent +8% damage. Mutilate and Envenom do nice damage, especially since Envenom makes poisons proc more often, increasing damage by a looot. There's also a talent that makes Envenom refresh Slice n Dice so you have a permanent +40% attack speed. This is my PvE spec, and as soon as I specced into it my dps jumped drastically, and it's pretty easymode. Unfortunately, Assassination is my least favorite spec.
Combat is also a PvE-heavy spec, though I'm not sure how deep into Combat a "good" PvE spec would be. I know the 51 point talent is pretty good in PvP, because it's like 10 attacks instantly, it's awesome. The obvious difference between Combat and Assassination PvE is that Combat = swords/axes/maces/fist weapons and Assassination = daggers.
Subtlety is basically regarded as crap for PvE, which is unfortunate because I see some major potential in it, if it just had a few well-placed buffs. It's good for PvP because of mobility with Shadowstep and burst potential with Shadow Dance because you can spam Ambush for great justice.
What I usually hear is "subtlety kills cloth just as well as assassination but can't really kill plate"
The reason is because Assassination has a bunch of talents that make poisons actually useful, and Envenom ignores armor, so yeahhh.
tl;dr version: Mutilate (41 point assassination) + preparation (21 point subtlety) is generally regarded as the best pvp spec, while heavy Assassination is generally regarded as the best pve spec.
:/