Several years ago I bought a dual 500MHz Gigabit PowerMac G4 off eBay to use as a second computer at work (didn't have a Mac at my office at that time, so I decided to buy one myself). This was in the OS X 10.3 days. I think within a month I upgraded the CPU to a 1.4GHz PPC7447B from OWC. Also maxed out the RAM with left over sticks I had laying around from various old machines. Ran like a champ.
About 5 months ago, my 7 year old daughter says she wants a computer of her own. So I haul the G4 out of the basement and connect a spare monitor. Crash city. Turns out the fan on the Radeon 8500 in the machine has stopped running and the chip was overheating. Turned to eBay and bought a $100 Mac-flashed 128MB Radeon 9800. Now the machine is running Leopard quite well (hooray parental controls). Not a digital video barnburner, but for web surfing its works perfectly.
So while spending $100 on what is otherwise a very old machine is worth it, I'd personally would take the $400-$500 the OP was going to spend and buy a base model Mac mini instead. Unless you need to run two displays or need a certain PCI card, a Mac mini will likely do everything several times faster than your Quicksilver can do, even upgraded.
But, if you're sold on upgrading the G4, I say go dual-processor. While a bigger L3 helps offset the relatively slow bus speed of the G4 machines, if you're doing any sort of multi-tasking, the dualie will be better in the end. If you aren't doing any real multi-tasking at all, then the 7448 will probably suit you just fine. You mention Photoshop CS2. You should check any filters you use on a regular basis are multi-processor aware.