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:

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

Joins When No Join Types Are Valid

Hugo Kornelis has a brain-teaser for us: The query below can be executed in any version of the AdventureWorks sample database. Don’t bother understanding the logic, there is none. It is merely constructed to show how SQL Server handles what appears to be an impossible situation. 1 2 3 4 SELECT          d1.Name, d2.GroupName FROM            HumanResources.Department AS d1 FULL OUTER […]

Read More

The Value Of CROSS APPLY

Kendra Little talks about one of my favorite T-SQL operators: Here’s my top 3 favorite uses for CROSS APPLY and OUTER APPLY: APPLY is fantastic for calling table valued functions. I didn’t include questions about those in the quiz, simply for the purposes of keeping the code simple, and because I wanted the quiz to […]

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Categories

August 2018
MTWTFSS
« Jul  
 12345
6789101112
13141516171819
20212223242526
2728293031