I was very shocked when I installed Stalker COP and saw the graphics. They were worse then DX 8 games. I never installed mods to make it look better but as a DX 11 game, it should look fantastic out of the box. It was utter crap.
As for good looking demanding games, go with Dirt 2 (Dirt 3 is coming later), BFBC2, F1 2010 is decent, Mafia 2, Bulletstorm, UT3, AVP, Dead Space 2 looks good but won't make your system sweat one bit, Just Cause 2 is REALLY good and is huge, Bioshocks look good, Mass Effect and Assasins supposedly look good.
There is a huge difference between a game being demanding, and a game looking good. If it's poorly optimized and demanding but looks like crap then who cares. If PAC Man only runs at 5fps who cares. There some really good looking games that you might get 150fps in, but if it looks stunning and that should be all that matters. Even though typically more demanding games look better.