It's easier to show you some examples, than to tell you about CPLs. But first, CPLs generally do not have as strong effect as older Liner Polarizers could give. Still, it's what we have to use on our autofocus, through-the-lens metered cameras (linear pols also can mess with metering systems in some cases, in addition to the AF system).
A CPL's effect can be pretty subtle... especially on a bright, sunny day. When there is a lot of contrast and color saturation, the CPL really isn't needed. But, it will still deepen the blue of the sky and help make any clouds "pop". This is because the light from a blue sky is particularly scattered and the CPL only allows aligned light waves to pass through.
A CPL can be quite useful on an overcast day, when reflections off shinier surfaces of foliage and such can cause loss of color saturation. For example, here are two shots of similar subject shot on overcast days... The one on the left without an filter and the one on the right using a CPL dialed up to it's fullest effect:
Notice above the better color saturation in the right hand image? Yet, there's still some reflection of the white overcast sky. The CPL couldn't completely eliminate that. But the reds and greens are a bit "richer", thanks to the CPL.
As you've noted, a CPL is most effective when it's pointed 90 degrees from the light source. Here is an example where the sun was high above my head and slightly behind me. I wanted the CPL to deepen the blue sky and greenish blue water, while making the white of the fishing boats "pop"... But I also dialed it back a bit because I didn't want to completely lose the reflections of the boats on the water (that would look unnatural, IMO):
CPL will do virtually nothing if the sun (or other light source) is directly behind you (180 degrees) or right in front of you (0 degrees, actually within or close to within the image). In fact, any filter should be avoided when shooting directly into the sun or another strong light source, since it's likely to increase flare issues. A CPL is one of the worst, because it's actually a multi-layered filter. Unlike most single layer filters, a CPL has many more glass-to-air boundaries for light rays to cross, really amping up the likelihood of flare and other possible problems. I shot this sunset without any filter:
Another reason not to use a CPL for the above image was that a lot of it is reflections off water and wet sand... which I didn't want to reduce with a polarizer.
In comparison here's a test shot where I deliberately used a CPL for a sunset image (in this case a high quality B+W Kaesemann CPL):
The above is underexposed, but also there is a lot of loss of contrast and color to veiling flare, as well as some artifacts caused by ghost flare. Actually I was pleasantly surprised it was no worse than it was, and as able to clone out the artifacts, correct some chromatic aberrations, adjust exposure and significantly increase both contrast and saturation to improve the image:
Still, it would have been much better (and a lot less work) to simply shoot without the CPL (or any other filter).
There's also potential for uneven effect with a CPL, especially with wide angle lenses when capturing a broad expanse of a featureless sky or similar. This isn't necessarily a bad thing, just something to be aware of. I deliberately used it for the image below where the sun was low and off to my left, done with a wide angle lens and CPL to make a very plain blue sky a little less boring and sort of frame the lighthouse with the darker area the filter caused:
There is a big difference in filter quality... usually reflected in the price. I use a CPL more than any other type of filter and, after more than one mistake, now will only invest in high quality. I use B+W MRC or Kaesemann, but there are some other good ones from other manufacturers... Nearly all manufacturers also make various grades, too. Including B+W, who makes cheaper single or uncoated CPL, as well as the better ones. Hoya, for example, has about five or six grades of CPL.
Something relatively new I've been seeing are "HT" or High Transmissive CPL. These appear to "cost" less light lost to the filter. A typical CPL will cost between 1 and 2 stops, depending upon it's setting. That can make for a slower shutter speed, larger aperture or higher ISO than you'd like to use in some situations. The HT apparently allow more light through... but I am not sure if they also have less polarizing effect, so haven't used them. They also appear to cost more money!
Another type of CPL that's more expensive is a "thin" type. These are designed for particularly wide angle lenses, so that the filter won't cause vignetting. But, frankly, I've rarely found them necessary, even on 10mm and 16mm ultrawides I use. (Note:
Never stack filters unnecessarily, such a polarizer on top of a silly "protection" filter).
Some more recent CPL have special coatings that make them more dust resistant and easier to clean... Costing a little more, but it may be worth it.
Some people will buy a CPL in the size needed for their largest diameter lens, then use "step rings" to be able to fit the same filter to other, smaller diameter lenses. Personally, I won't do that and buy a filter in the size needed for each lens, because I always use a lens hood and that's often not possible when using an over-size filter with a step ring. I have CPL for many of my lenses, but find I more rarely need one with the longer telephotos or macro lenses (though it so happens I have sizes to fit them), so if I were buying I'd prioritize ones to fit my wide and to short telephoto lenses first, then add any for macro or long tele lenses later.
In the end, I recommend spending more for a high quality, fully coated CPL. Besides scenic shots and foliage I showed in my examples above, one also can be useful for portraiture when people have shiny skin or wear eyeglasses, and for many other situations where you need to control reflections. CPL effects are impossible or difficult to emulate in post-processing, too... While most other types of filters have become largely unnecessary with digital photography, the same or better effects can be done with in-camera settings or applied with software after the fact.