VDPAU works with nvidia 8 series cards and upwards (some higher end cards with the GTX/GTS prefix support only basic VDPAU - se A and some like 8800 GTX doesnt support it at all).
Integrated IGPs like 8200/8300 and low end 8400/GF 210 cards do a very good job in playing HD movies. The GT 220+ series also support VDPAU fully (set C).
But quite interestingly the high end spectrum 8/9 series GT/GTX and 200+ GTX only supports A set - resulting in many non playable videos with VDPAU. Some cards like the 8800GTX doesnt even support VDPAu.
Its really amazing stuff, all 3 computers i use have cards that support VDPAU - 1080p movies play on the nvidia GT 210 i have at ~5-10% CPU usage - 720p at 1-2% (core2duo@2.33 GHz).
On 8200 integrated supporting set B (on Asus M3N78-VM mobo) and athlon 3200+ single core 1080p is ~15-25% smooth playback.
I use Debian Squeeze with the multimedia repository and the latest nvidia drivers from their site.
List of supported GPUs can be found here:
http://en.wikipedia.org/wiki/Nvidia_PureVideoOn other cards (including older nvidias) mplayer-mt seems to do the job just fine (if you have at least 2 cores with a reasonable CPU of course). I only tested it on higher end CPUs (core2Du0) - the difference is that there are very few (in some cases when there are sudden CPU spikes in some scenes) dropped frames and the audio stays in sync.
With the single threaded mplayer, there were quite a few dropped frames/audio desync (although the more recent codecs seem to have alleviated these problems).
Best is to use smplayer. But for testing mplayer/mplayer-mt from command-line is the best (mplayer -vo vdpau -vc ffh264vdpau moviename).
If you need a card for HD play (no games or hi-res compiz), currently the best bet is GF 210, preferably with passive cooling (no fan) - i have one with fan and the fan is a bit noisy. It does play lower end games (urban terror/smokin guns/team combat elite and the like) perfectly well. But for higher-end bames and VDPAU a 220/240+ card is preferable.