Hacker News Reader

You must be doing something wrong then. MSSQL resolves the entire statement (CTEs plus final query) into a single query that it optimizes as a whole.

Can you share an example of two queries that are equivalent where one is written using CTEs and it doesn't construct the same plan?

Postgres has no problems joining against any sized CTEs - at least that's what I'm seeing here with a considerably sized dataset (600 GB raw table size).

But: The optimizer will first fully resolve the CTE expression and only then join. If the join condition eliminates many of the rows selected in the CTE, this might indeed yield a much worse execution time than doing it inline.

This behaviour is documented on http://www.postgresql.org/docs/9.3/static/queries-with.html (at the end of 7.8.1)

I have not seen this behavior, and I use the fuck out of CTEs in SQL Server.

Sounds like you should look at the execution plan of the "trivial query" and see what's up with your database schema.

SQL Server's plan generation is not always perfect, but it's good 98 times out of 100.