Implementation Matters: CTEs In Postgres And SQL Server

Brent Ozar looks at a couple of places where Postgres and SQL Server differ in implementation details:

In SQL Server, if you write this query:

1 2 3 4

With AllPosts AS (SELECT * FROM StackOverflow.dbo.Posts) SELECT *   FROM AllPosts   WHERE 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:

1 2 3

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.