Heed the caution about CTEs being optimization fences. I had a case just this past week, where one of our engineers needed help with a query that used a LEFT JOIN against a CTE which generated text-search vectors for some subset of rows, in some large table. The query was taking 9-10 minutes, cache-warmed.

Used that way — an outer join against a CTE — the planner was forced to generate a plan that produced a row for every candidate row in the table being used as input for the text-search, whether or not it would be needed, visiting nearly all of its pages.

Just by moving the CTE inline (that is: "LEFT JOIN ( #{ subquery } ) AS blah"), the query completed in 17ms.

This way, the planner could apply conditions from the rest of the query to the subplan from which text-search vector rows were being produced, such that it only pages containing rows it already knew it would care about would even be retrieved.

The rest of this article is pretty on-point, too.

Source: This stuff is my day job.

EDIT: As a counter-point, because they are incredibly useful, I've also had countless cases where rewriting a query to use CTEs was the several orders of magnitude win. This also wasn't the only possible fix; the qualifying conditions in the CTE could have been improved, eliminating the extra work where it would have occurred.

Like all things computers, the real answer is, "it depends..."