>I have heard that CTEs offer sometimes phenomenal performance gains. Is that true?

When used in the context of a derived table subquery (arguably the most common use), not really...or at least I'm not aware of any.

When used in the context of recursion - it's possible. There are instances where you can use a CTE in place of a "loop" - in that case, a CTE might be faster.