As a counter-point here, I would point out that there are cases where we've found (with the LedgerSMB project) drastic increases in performance in switching to CTE's, especially with recursive ones.
The reason is that if you have a stable result set which is complex to calculate (particularly if it involves recursive processing), but needs to be checked against multiple conditions in different subqueries, it is far better to materialize it once and check a few times, than to materialize it in every subquery.
For example, our menu uses a simple referential tree. Generating this with connectby() when we had to support 8.3 primarily usually worked unless you wanted to exclude a large number of rows based on permissions. In that case, it would take better part of a minute to run. Not good for a web app.
Moving to recursive CTE's cut the execution time of our best performance in half, and increased our worst cases by orders of magnitude.
To be honest though, for shareable code, I think views are often better because they represent a stable interface that can be documented regarding acceptable uses (should you join this view? self-join it?). What CTE's often buy you though is an ability to break a query off into multiple, independently testable pieces (where a view might be overkill due to a lack of sharing), and that allows you to better debug when something goes wrong.
The reason is that if you have a stable result set which is complex to calculate (particularly if it involves recursive processing), but needs to be checked against multiple conditions in different subqueries, it is far better to materialize it once and check a few times, than to materialize it in every subquery.
For example, our menu uses a simple referential tree. Generating this with connectby() when we had to support 8.3 primarily usually worked unless you wanted to exclude a large number of rows based on permissions. In that case, it would take better part of a minute to run. Not good for a web app.
Moving to recursive CTE's cut the execution time of our best performance in half, and increased our worst cases by orders of magnitude.
To be honest though, for shareable code, I think views are often better because they represent a stable interface that can be documented regarding acceptable uses (should you join this view? self-join it?). What CTE's often buy you though is an ability to break a query off into multiple, independently testable pieces (where a view might be overkill due to a lack of sharing), and that allows you to better debug when something goes wrong.