Re:Use SQL subqueries to count distinct 50x faster
by joevandyk on Friday January 24, 2014 @07:23AM
(#7114566)
In PostgreSQL, you can use CTEs to force certain subqueries to run before others. That technique would be useful here, and would clean up the code a bit.
[ Parent
| on HN ]
Re:Use SQL subqueries to count distinct 50x faster
by brunnsbe on Friday January 24, 2014 @08:00AM
(#7114685)
CTEs are found in SQL Server as well:
http://technet.microsoft.com/en-us/library/ms190766(v=sql.10...
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.
Re:Use SQL subqueries to count distinct 50x faster
by bgilroy26 on Friday January 24, 2014 @07:32AM
(#7114591)
I didn't know what Common Table Expressions were. I had always called them WITH queries.
It's good to learn the name.
Re:Use SQL subqueries to count distinct 50x faster
by atwebb on Friday January 24, 2014 @09:40AM
(#7115038)
Am I incorrect in stating that this isn't necessarily true? Just because a statement is earlier in a script doesn't mean it will be optimized first.
Re:Use SQL subqueries to count distinct 50x faster
by joevandyk on Friday January 24, 2014 @10:20AM
(#7115246)
In Postgres, common table expressions are planned and executed separately.
I think of it as each section builds a temp table.
This can be a good or bad thing, but it's sometimes important to know that it's happening.