[sk1pper]

Nitpick:

> when a nested query is refactored into a common table expression (CTE), this kind of change doesn’t have any functional impact on either a query or its outcome

This isn’t quite true, at least in Postgres. It won’t affect the outcome, but it can affect the query plan.

[zasdffaa]

I believe that was true but in current PG the CTE no longer acts as an optimisation barrier.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

[captaintobs]

For this use case, it’s intentional, since only the outcome is important to us. That’s kind of the draw of a declarative language, you ask what you want and don’t have too much control over how that’s done (the engine should optimize that away).