3 ms·
Common Table Expressions Be careful to watch how your chosen DB processes CTEs and what it means for performance in your queries. In postgres they are "optimi
by dspillett 11y ago
Common Table Expressions
Be careful to watch how your chosen DB processes CTEs and what it means for performance in your queries.
In postgres they are "optimisation fences", something which there is significant resistance to changing[1][2], where-as elsewhere (at least in SQL Server) the query planner and engine can optimise across CTE boundaries (pushing search predicates back into them where possible). This can make a massive difference to the performance of some queries, potentially turning a full index scan (or worse, multiple scans) into a small number of seeks.
As always: test with realistic data sizes/patterns to make sure things work as you are expecting them to.
[1] see http://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/ http://blog.2ndquadrant.com/postgresql-ctes-are-optimization...
[2] also https://news.ycombinator.com/item?id=7023907 https://news.ycombinator.com/item?id=7023907 for further discussion