I'm not sure if this has already been posted.. Anyway, it looks pretty amazing compared to the standard textures:
Edit: A short clip with the "ultra textures" found in the link above + enb (http://www.mediafire.com/?vhjmg2llkzy) that you should watch in 1080p @ fullscreen to be able to fully appreciate it:
Another edit: When you use enb it looks pretty cool in some scenes and is wayy too dark in others. I wouldn't be able to play through the game like that, but you could actually edit the enb-ini to change the settings and make it look the way you want it to look. Oh and on my PC (Phenom II 955 @ 3.7Ghz, 8GB RAM, GTX 570) the FPS were kinda low (barely playable), which was probably because of the combination of the ultra textures and enb.
The texture loading times are not as bad on my system, the high res textures took about 3-4 minutes to load and the ultra textures 7-8 minutes (all of them). And it does load that long every time you start the game, but not in the loading screens when you are playing.