Recursive CTEs are great for expressing very poorly performing queries. They might give the right answer, but after you've given up waiting.

If you've got tree or graph structures, you're better off denormalizing paths or transitive closures, to reduce the amount of indirection required to answer questions.