it does code translation from PPC to be run on intel (x86) hardware, hence the slowdown. any software u see that is noted as being universal has dual code for PPC or intel Macs. software which is PPC only will run through rosetta, like cs2. if u want to try cs3, see if u can get a hold of the beta. it'll run native and won't be slowed down by rosetta translations.
u can see how an application is coded by selecting it and hitting command+i.