In SQL Server, if you write this query:
1234 With AllPosts AS (SELECT * FROM StackOverflow.dbo.Posts)SELECT *FROM AllPostsWHERE Id = 1;
SQL Server builds a query plan for the entire operation at once, and passes the WHERE clause filter into the CTE. The resulting query plan is efficient, doing just a single clustered index seek.
In Postgres, CTEs are processed separately first, and subsequent WHERE clauses aren’t applied until later. That means the above query works just fine – but performs horribly. You’ll get much better results if you include your filters inside each CTE, like this:
123 With AllPosts AS (SELECT * FROM StackOverflow.dbo.Posts WHERE Id = 1)SELECT *FROM AllPosts;
That’s less than ideal.
The comments are valuable here as well.