Re:Use SQL subqueries to count distinct 50x faster
by pradocchia on Friday January 24, 2014 @10:25AM
(#7115280)
but CTEs in SQL Server are not an optimization boundary like they are in Postgres. This makes a big difference in their proper use.
In SQL Server, aside from recursion, CTEs are purely syntactic sugar and thus a tool for organizing code. Whereas in Postgres, due to the optimization boundary, they are a tool for query tuning. If you rewrite a query in Postgres using CTEs just to clean it up, you are doing it wrong, since the CTEs will also affect the planner.
Re:Use SQL subqueries to count distinct 50x faster
by radiowave on Friday January 24, 2014 @10:42AM
(#7115397)
Right, but that's assuming that performance is the most pressing constraint. Sometimes it's confidence in the query producing the right result, or maintenance further down the line.
In SQL Server, aside from recursion, CTEs are purely syntactic sugar and thus a tool for organizing code. Whereas in Postgres, due to the optimization boundary, they are a tool for query tuning. If you rewrite a query in Postgres using CTEs just to clean it up, you are doing it wrong, since the CTEs will also affect the planner.