I've used CTE's in DB2 for hierarchical data using recursive SQL (8 ? years ago) and the performace was much better than using subselects.

I don't know much about Postgres, but for some things CTEs are a good tool.