Implementation Matters: CTEs In Postgres And SQL Server

Kevin Feasel

2018-08-07

Syntax

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:

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:

That’s less than ideal.

The comments are valuable here as well.

Related Posts

ISNUMERIC And Unexpected Results

Jen Stirrup explains why ISNUMERIC isn’t all that great: I noted that one of the columns failed to convert VARCHAR to DECIMAL. The error message is below, and it’s usually fairly easy to sort:Error converting data type varchar to numeric Normally, I’d use ISNUMERIC to identify the rows that fail to have a value in that column that could be […]

Read More

Reference Column Names

Jon Shaulis shows why you want to reference tables when including column names in queries: If you don’t read the rest of this setup, I want you to take away one thing.  Always reference your tables with your columns when more than one table is involved in the query! This post is made primarily with […]

Read More

Categories

August 2018
MTWTFSS
« Jul Sep »
 12345
6789101112
13141516171819
20212223242526
2728293031