SQL CTEs Performance
The cluster focuses on Common Table Expressions (CTEs) in SQL, especially their performance impacts in PostgreSQL such as acting as optimization barriers in older versions, improvements in recent releases, and comparisons to subqueries or recursive CTEs.
Activity Over Time
Top Contributors
Keywords
Sample Comments
Can you give a situation where CTEs are still a optimization barrier in Postgres?
I don't think recursive CTEs are that bad
I never understood this. Why can't CTEs be transparently compiled into subqueries?
Starting at PostgreSQL 12 you don't even have to feel bad about all of those CTE's.
I can't speak for any other server but Postgres recurvise CTEs should be reasonably performant
CTE's are indeed nice, but can also make your queries substantially slower, at least in Postgres
Careful with CTEs in older postgres. Until the most recent versions they were an optimisation fence (predicate push-down would not pass through, meaning the CTE would often result in an index or table scan, potentially multiple times in the recursive case. This can be a huge problem over large data, though obviously in many circumstances with small enough data it is still efficient enough.
Nope, there’s no performance downside to using CTEs in recent postgres versions, unless the CTE is recursive or has side effects (which would be weird).
Or better yet, use CTEs: https://duckdb.org/docs/stable/sql/query_syntax/with.html
My ORM (and probably yours) handles CTEs ;)