Basically it shoves water through a small nozzle real fast...basically, lol.
It helps with some species that live in fast currents. People also use powerheads to blow extra food/etc. to the intake of HOB or canisters. I use mine to do WC's on the 125 since our faucet has no power to take out the water with the python.
Some powerheads you can also hook up an air pump to them, so they will prove ide oxygen plus current. Well that's about all I know on powerheads.
You can use powerheads for under gravel filters, but IMO under gravel filters are more hassle than they're worth. I use powerheads to eliminate dead spots where debris collects in my tank.