Simplistically it's analogous to gears in a car:
1st gives great acceleration, but poor top speed
5th gives quiet economic cruise, but weak acceleration, and not top speed
4th probably gives best top speed
3rd is a good balance between 1st and 5th
A fixed pitch aircraft is stuck in 3rd.. A variable pitch prop allows you to configure to fit your requirements
Variable props come in several flavours:
Ground adjustable (essentially fixed)
In flight adjustable, non regulated
In flight, single lever automatically set blade angles (e.g. Cirrus)
Constant Speed Unit - prop angle is automatically adjusted to maintain RPM within the limits of the coarse/fine pitch stops.
CSU is the more common implementation, even on GA aircraft. A lot of the ultralight/Light sport aircraft coming along seem to have variable pitch props however.
Generally CSU singles are riged to fail to fine pitch with a loss of oil pressure as it's the most safe condition for a single (no assymetric considerations). Some aerobatic aircraft (e.g. pitts) fail coarse to avoid overspeed conditions.
Multi engine a/c generally try to fail coarse/feather to reduce the drag on the dead donkey and limit the assymetric problem.
Negative torque systems stop the prop driving the engine by moving toward coarse/feather when unloaded. As far as I'm aware, they are only common on turboprops.
I was under the impression that gravity plays a part in making the speeder weights drop as rpm slows in the csu. However, that may be a simplification - I'm having a problem working out how that gells with extreme pos/neg G in a csu aerobat like the pitts/extra etc.