I'd be hesitant to assume the generated CTEs are always going to be amenable to optimization. The examples on the linked page are pretty trivial queries - I wonder what happens when that ceases to be the case, as seems very likely with a tool that apparently doesn't do a great deal to promote understanding what goes on under the hood.
It's just worth recognizing that SQL itself is a tool that doesn't do a great deal promote understanding what goes on under the hood. (I've witnessed that firsthand many times.)
Granted, but I've not once yet seen it help to add a second hood for things to be going on under.
If nothing else, having to write SQL tends to lead engineers to the engine manual, where they have at least a chance to become aware of the existence of query planners.
