Hacker News Reader

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.

View on HN

PostgreSQL being the one noteworthy: http://modern-sql.com/feature/with/performance

I never understood this. Why can't CTEs be transparently compiled into subqueries?

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.

Reply on news.ycombinator.com