I've heard much of the famed cube effect, and I think I have an adequately powerful test box on which to run it.
I've got a 2.4GHz P4, 512MB of RAM, and a GeForce 6200 LE, and compiz still runs great. You should be golden. I have no idea *cough* why Vista's graphical effects take so much, but compiz seems to be more along the lines of OS X's graphics effects in terms of power. Only reason I stopped using it was because it doesn't play nice with 3D games and Wine, but most people don't need to worry about that.