Core image performance is one practical reason to upgrade.
The problem with the 7800GS (or X800 series) is power consumption. Power Mac G4 power supplies are not the most powerful out there and might not be able to handle a 7800GS if you are also running a bunch of internal drives and other expansion cards. Or, they might run on the hot side. Plus, these machines are starting to get old - running them in a high-stress condition will shorten their lives.
With that being said, my Digital Audio G4 ran for 6 years with a GeForce 6800GT, three internal HDDs, a SATA card, a USB 2.0 card, an extra internal fan, an internal DVD-RW and the internal Zip drive. Never had a problem. I recently put the 6800GT in storage and replaced it with a Radeon 7500 to reduce stress on the PSU. I want to keep my G4 for many years so no need to run the 6800GT when not gaming (plus, the 6800GT is useless for OS9 GPU acceleration, whereas the Radeon 7500 works great).
Actually, that brings ups good point - if you spend any significant amount of time running "Classic" OS (i.e. OS9 or previous), you're wasting your time with one of the uber-powerful video cards. Anything newer than a Radeon 9000/9200 or GeForce 3/GeForce4 MX would be complete overkill for most Classic OS applications, and a lack of OS 9 drivers for newer cards means that you're not taking advantage of the extra power anyway. The most powerful video card supported by OS 9 is the Geforce4 Ti 4600. Everything newer operates in a generic mode sans 3D acceleration when running OS 9.
So, for example, if you're trying to play Myth: The Fallen Lords with a GeForce 7800GS, it won't run because OS9 can't use the 7800's 3D acceleration - the generic drivers are too old to talk to a 7800 and there are no OS9-native drivers for the 7800 series. For the best OS 9 experience, you need an OS 9-era video card like the Radeon 9000.
Of course, if you want to be totally retro, go find yourself a
Voodoo 5....
