OS X uses your graphics card to render things to screen, and not just in a passive, CPU-constructed kind of way (like Windows XP does, for example).
10.4 started using the graphics card to render 2d and 3d effects. 10.5 expanded on this usage greatly. All those fancy animations in Time Machine? They will use your graphics card if it is fast enough. The dock bouncing? yup. The water ripples in dashboard? That too. All of those drop shadows on windows and the subtle blurring of elements underneath the translucent drop-down menus...all of those things will run on the GPU unless your GPU is slow enough that the OS decides to fall back to "software" mode. Vista also does this with it's "Aero."
Aperture is painfully slow on G5 PowerMacs with the default graphics card, the 5200 Ultra. In fact it will tell you that it is not a supported graphics card and you have to trick it into opening the application...
It is marginal on the 7300 of the base-level MacPros.
Your Aperture experience is almost directly dependent on your graphics card's performance as long as you meet a certain baseline with your CPU(s) and your hard drive speeds...which most Macs that have or can have a sufficient GPU already meet.
Photoshop CS3 most certainly does off-load some of its processes onto the GPU if you have one and it is enabled in preferences. They haven't gone into any detail about what sorts of things it actually uses the graphics card for, but it is pretty obvious from their description in their online materials that it is being taken advantage of if you have a fast enough card:
Video Card support
Photoshop CS3 Extended defaults to using 3D acceleration on the video card. We recommend using a graphics card with 128 MB of video RAM or higher and Pixel Shader 2 or higher. We specifically recommend the following graphics cards:
ATI 1000 Series or higher
ATI Fire GL FireGL V3300 or higher
Nvidia GeForce 6000 series or higher
Nvidia Quadro 1000 series or higher
If your video card does not have sufficient memory, then 3D acceleration in Photoshop must be turned off. To do this, go to Preferences>Performance and uncheck the "Enable 3D Acceleration" box.
In addition, there are known problems with the NVidia 5200 card, including problems tiling. We do not recommend using 3D in Photoshop CS3 Extended with this card.
When the new MacPro is released someday, and they update the graphics options, it is VERY recommended that you get something equivalent to an 8800-series or faster if you want to keep using your machine for years to come and running up-to-date software. What you DON'T need is a physics processing unit!