These screenshots are from my old laptop, with a 500Mhz cpu and a Trident Cyber 9525 with an amazing 2.5mb video ram, and of course no 3d support.
Also, it works with Virtual Machines too! I can get cool effects using just the vesa driver on VirtualBox.
Screenshots taken with KDE 4.1 Beta 1 on Kubuntu.
You can also have shadows, that were fixed to work with XRENDER after beta 1 (so I don't have them on my laptop yet).
Most effects are completely usable, consuming of course some cpu, but as they are one-time (animations, alt+tab, etc) don't have that much impact. Semi-transparent windows of course take their toll (especially when dragging them), but if you have a decent cpu, it can probably handle it fine.
Thanks to the kwin folks, your rock!