Not 100% about the newest versions of Postgres. But certainly in older versions CTEs created query planner boundaries.

So the planner would optimise each CTE separately, but wouldn’t optimise the entire query with all CTEs together, which of course can result in some slightly nonsensical query plans.

To my knowledge this is considered a limitation rather than a feature as it can cause performance issues with some queries.

EDIT: Some quick Googling suggests the CTEs are no longer optimisation fences, and their handling can be tweaked on a CTE by CTE basic in Postgres 12 onwards [1]

[1] https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...