I would like to chime in - you can't go wrong with a maxed octane2, fuel or Tezro - maxed octane2 might be the easiest and cheapest to get (as opposed to maxed tezro or fuel). I would recommend a Tezro (V12, quad cpu), followed by maxed octane2(2x600mhz, V12) and last a fuel. I wouldn't consider anything less (indy, indigo2, o2). I suppose if you stumble across an Onyx system (it happens) that might be something else, but I don't have any insight into the big iron setups and how they rank compared to the workstations.
I wont repeat what others have said, but one point to keep in mind is that entire movies have been made on SGI's with the software mentioned so really great work is possible with some patience. I completely suck at 3d and video editing/compositing, but I putz around on SGI's just for fun, I Can't speak for using these things for a living.