News for hackers. Stuff that matters.
Re:Expressive Power of SQL (2003) [pdf] by dspillett on Friday November 03, 2017 @10:31AM (#15619438)
Be careful with CTEs with regard to performance, some SQL DBs can optimise by pushing predicates through to the inner queries, where for some (postgres being the one example that I know but there are probably others) a CTE presents an optimisation fence.
Re:Expressive Power of SQL (2003) [pdf] by MarkusWinand on Friday November 03, 2017 @12:41PM (#15620471)
PostgreSQL being the one noteworthy: http://modern-sql.com/feature/with/performance
Re:Expressive Power of SQL (2003) [pdf] by nerdponx on Friday November 03, 2017 @12:54PM (#15620561)
I never understood this. Why can't CTEs be transparently compiled into subqueries?
Re:Expressive Power of SQL (2003) [pdf] by dspillett on Friday November 03, 2017 @01:09PM (#15620683)
There is plenty of discussion about it out there (search for "postgres cte optimisation fence") including official word.

Note though that simply replacing CTE references with appropriate sub-queries simply won't work in the recursive case, and in some cases where a CTE is referred to multiple times the result may be an even less optimal query plan.