Unless you're doing realtime previz or something similar, you'll be better off spend your money on a faster processor and more RAM. Macs are slightly different in the sense that the Quartz technologies in Mac OS X (which include the graphics subsystems) do actually use the graphics card for processing more than other platforms, but CS3 doesn't take advantage of that except in After Effects, Premiere and I believe Photoshop Extended. But since most of CS3 is pretty old legacy code, Adobe don't really use it to anything like its maximum benefit.
I believe that some of the newer apps that have started to appear (like Pixelmator for example) are designed to really take advantage of the extra horsepower of the graphics card, but these apps are still in their very early generations and aren't anywhere near being ready for pro work yet. Most of them are barely out of Beta.
You might find that some of Apple's own stuff is using the card a lot more (e.g. Aperture, Final Cut etc.) but that's just a hunch (no pun intended) and you'd have to ask someone who uses them. I don't.