[avianlyric]

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-...

[tda]

In postgresql 12 a patch [0] is merged to remove the optimization fence certain conditions. From the changelog [1]

" Allow common table expressions (CTEs) to be inlined into the outer query (Andreas Karlsson, Andrew Gierth, David Fetter, Tom Lane)

Specifically, CTEs are automatically inlined if they have no side-effects, are not recursive, and are referenced only once in the query. Inlining can be prevented by specifying MATERIALIZED, or forced for multiply-referenced CTEs by specifying NOT MATERIALIZED. Previously, CTEs were never inlined and were always evaluated before the rest of the query. "

[0] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... [1] https://www.postgresql.org/docs/12/release-12.html