This answer takes a different look at the constant speed propeller.
I was confused about them too, many years ago.
I think the confusion started because everyone always describes the prop lever as controlling the pitch, and full forward as decreasing the pitch. Leading to the assumption that full forward must be least pitch or flat. And as we all know a "flat" blade (i.e., zero angle of attack) produces no thrust. So why do you want no thrust for takeoff?!? But this is not the way it works (or at least , not the way to think about it)
Let's start with the name: constant speed propeller.
As the name implies a CS propeller tries to maintain a set RPM in the face of changes in engine power settings.
Think of the prop as always trying to absorb all the power the engine is putting out and thus reach equilibrium.
The prop lever sets the prop RPM or really, the upper speed limit of the prop.
Full forward says, "Go as fast as possible".
Of course, for a given power setting, the prop can only go faster by decreasing the blade pitch.
At any given prop setting, the prop is basically modulating its pitch in order to maintain the selected RPM. If it's going too slow, it reduces pitch which allows it to speed up. If it's going too fast, it increases pitch which forces it to slow down.
Essentially, the constant speed prop always tries to absorb the engine's power at the speed selected by the pilot.
For takeoff one wants the most thrust you can get and since the blade is just an airfoil and the thrust for the propeller is really the lift generated by the blade's airfoil, thrust (1/2 ρ V2 CLα α A) goes up more quickly with the relative wind velocity (V) than it does with angle of attack (α) so we would like the highest V (RPM) that we can get.
But here's where the confusion comes in. When you set the Prop to fine pitch it doesn't go to "flat" and stay there. What you are really doing is saying go to "full fast". Once the blade reaches its top speed allowed by the prop governor it will try to maintain that speed by increasing the blade pitch in order to absorb the rest of the power put out by the engine. Therefore, in this takeoff configuration you're developing maximum engine power and running it through the prop at it's maximum speed, thus developing maximum thrust.
Now, what happens when you reduce the prop while maintaining full engine power? Well, the prop sees it has to slow down, so it increases blade pitch. This increases drag on the prop which is seen as torque at the engine. With that increased load on the engine the engine slows down. (Think about going up a hill in a car with out adding pressing a bit more on the accelerator) But note that load on the engine really transfers back all the way to the cylinders and pistons so that the pistons see an increase in resistance to motion. Inside the cylinders this "push back" from the pistons results in higher pressures being developed in the cylinders and higher torques in the drive shaft. Too much torque in the engine and it breaks. Too much compression in the cylinders and it starts acting like a diesel and detonates prematurely.
So,clearly, pushing too much power through the engine/prop is bad!!
To prevent this you always need the prop set so as to be able to absorb the anticipated new engine power setting. So when moving to a higher power setting adjust the prop first so it is allowed to speed up before adding power. And when moving to a lower power setting, always reduce the power first before slowing the prop (i.e., increasing blade pitch/torque).
Also note that if the engine is not producing enough power, the prop attempt to go to its lowest pitch- trying to get the RPM up, but it won't be able to. This is what happens when you've got the prop lever full forward but the throttle at idle.
As far as why a CS prop is better than a fixed pitch prop, it comes down to being able to control the angle of attack of the propeller blades.
We know if you set too low of an RPM at a given power setting the prop will try to absorb that extra power by increasing pitch. It will do this without regard to how efficiently the prop is operating. That is, it would take the blade all the way to 90 degree angle of attack if it could to try to slow the prop down. Obviously this isn't desirable since it would not be generating any lift/thrust - it would all just be drag. You can get out of this inefficient condition by setting a higher prop speed, causing the prop to decrease blade angle of attack in order to reach the higher RPM, or by reducing power also causing the prop to decrease blade angle of attack in order to maintain the set RPM.
At some point in its travel from full blade pitch to no blade pitch the prop will cross a point of "optimal" blade pitch for the given flight conditions.
Therefore for a given set of flight conditions there is an optimal power/speed combination that allows the prop to operate at its optimal angle of attack.
For best performance you always want to make sure you demand an RPM that "matches" a given power setting so that you keep the prop operating at its optimum efficiency. The more useful and important of these combinations are given in the aircraft's POH.
Other answers here discuss how the angle of relative wind as seen by the prop changes with RPM and forward airspeed so I won't repeat that. But the thing to remember is that unless you can change the blade angle, then for any RPM there is only one forward airspeed that delivers optimal blade efficiency. if you're not there then you are getting more drag than you need to for the given thrust generated. Some props are designed to give best performance in climb ( a slower airspeed), some at cruise ( a higher airspeed) and some a compromise somewhere in the middle. They still work, and are less expensive to maintain and are easier to operate but they aren't as efficient.
Bottom line: Don't think about setting a blade pitch, 'cuz that isn't what's happening. Think about setting the RPM at which you want the prop to absorb the engine's power.
Hope that helps.