Much more important than the GPU is the CPU.
At least in Photoshop, but probably also in After Effects, the GPU can only speed up a few specific tasks by a few seconds.
With out knowing just what your system is, I would say upgrading to a decent quad core or an i7 will give you a much larger performance boost than a new GPU.
That, along with more RAM and a 64 bit OS, will allow your apps to really pick up the pace.
If you could post your current system specs as specifically as possible, we can help you locate your most cost effective upgrade path.
A faster processor should make Applying Filters much faster and other effects. More RAM helps you edit large images as there would be less thrashing on the hdisk. 2GB of RAM is ideally the minimum with PS CS4 when editing big images a few at a time. When you are editing 10MP photos 10 or 20 at a time, 4GB++ would do wonders.
Even with a GeForce 250 or ATi 4850, what would only be sped up is when you are scrolling/zooming through a large image.
Here's my specs:
Intel Core Duo 2.66GHz
What feels sluggish to me, more than anything else, is switching between programs and opening programs (even after a fresh reboot). Adobe programs and Outlook sometimes take minutes to open. Opening a file/project takes equally long. The program appears to crash but eventually opens the file, several minutes later. To remedy this I've installed 4Gb of reasonably speedy memory but unfortunately Win XP can't use all of it, as I'm sure you know.
About the sluggishness; once I'm working with a program I understand that a lot of it is related to my CPU. However I'm hoping that some problems will be resolved with a new graphics card. Issues like barely being able to move windows around inside Photoshop, flickering and even crashes when I use various masking tools etc. After Effects likes to crash as well when I scrub back and fourth in a timeline. That's also something which I attibute to my old graphics card which probably was faulty.
One major drawback is of course that I'm running Windows XP. I'm not allowed to upgrade to 64bit vista or win7 due to corporate IT restrictions.
Other than that I've got no hardware conflicts, have optimized scratch disk space, checked for bad sectors, searched for bad memory, updated to the latest drivers for everything, re-installed Windows twice, rebuilt my entire workstation with all new hardware, upgraded from CS3 to CS4... all in en effort to hunt down the cause of the sluggishness and other issues.
As a last resort I switched my GeForce 9500 card for an older card I had lying around. A bunch of problems (flicker among others) dissapeared. So I figure that shy of getting rid of all hardware again and starting over - a new graphics card may go some way in battling my problems.
Actually, Photoshop CS4 is a lot more GPU dependent than any of the other versions. So GPU is very important if you're using CS4. This was never used to be the case, but they decided to change it with the current version.
I too need to upgrade my video card, as the cheapy I have in my system causes photoshop to crash all the time, or give me an error stating "Could not complete the [Insert Filter Here] command because of a program error". It's the most frustrating part of CS4. I have a quad core CPU and 4GB of ram. So either Adobe is being lazy and not putting out an update (only one small update since it's release a year ago) or my GPU just can't handle the processing needed.
Oh, and when I get that error, I either have to close photoshop, open it back up...open my document, and apply the filter quickly before resources get taken up. If the file isn't too large, it'll work. Otherwise I have to flatten the image, save it as a new file, close photoshop, re-open...then apply the filter. Then open my original document and drag the filter applied one over.
If you could imagine my frustration...
In general any gaming gpu will fit the bill here.....All you need is a good powerful CPU......I dont have a sizeable experience in After effects or Photoshop....I heavily use 3d apps mainly Max and maya.....and my gaming gpu gets the job done no matter how much polygons i use in the screen.....i dont see a valid point in investing loads of money in workstation cards like quadro which r said to be specific to the apps....I once used quadro fx 3500 which did actually nothing other than being an expensive card with mediocre performance.....workstation specific GPUs help only when u understand GPU programming.....Go get an i7 and render frames in After FX almost instantly.....