Good CTE, Bad CTE
boringsql.com
Good CTE, Bad CTE
1–10 of 47 posts
Re: Good CTE, Bad CTE
#2Use the term, never define the term, classic.
CTE stands for Common Table Expressions in SQL. They are temporary result sets defined within a single query using the WITH clause, acting like named subqueries to improve readability and structure.
Re: Good CTE, Bad CTE
#3Use the term, never define the term, classic. CTE stands for Common Table Expressions in SQL. They are temporary result sets defined within a single query using the WITH clause, acting like named subqueries to improve readability and structure.
OP here, damn - that's a very good point. Can't believe I missed it.
Re: Good CTE, Bad CTE
#4Great post - thanks. I think the columns in the index you suggested in the pre-pg12 section are in the wrong order (that index would get used)
Re: Good CTE, Bad CTE
#5I wrangle databases by day, and do martial arts of an evening. Two arenas where CTEs can cause significant headaches!
Re: Good CTE, Bad CTE
#6I've always thought of CTEs as a code organisation tool, not an optimisation tool. The fact the some rdbms treats them as an optimisation fence was a bug, not a feature.
Re: Good CTE, Bad CTE
#7> Recursive CTEs use an iterative working-table mechanism. Despite the name, they aren't truly recursive. PostgreSQL doesn't "call itself" by creating a nested stack of unfinished queries.
If you want something that is more like actual recursion (I.e., depth-first), Oracle has CONNECT BY which does not require the same kind of tracking. It also comes with extra features to help with cycle detection, stack depth reflection, etc.
If your problem is aligned with the DFS model, the oracle technique can run circles around recursive CTEs. Anything with a deep hierarchy and early termination conditions is a compelling candidate.
Re: Good CTE, Bad CTE
#8Great post - thanks. I think the columns in the index you suggested in the pre-pg12 section are in the wrong order (that index would get used)
Thanks - I will recheck later today.
Re: Good CTE, Bad CTE
#9I've always thought of CTEs as a code organisation tool, not an optimisation tool. The fact the some rdbms treats them as an optimisation fence was a bug, not a feature.
Improved readability is definitely the primary benefit.
Re: Good CTE, Bad CTE
#10Use the term, never define the term, classic. CTE stands for Common Table Expressions in SQL. They are temporary result sets defined within a single query using the WITH clause, acting like named subqueries to improve readability and structure.
OP here, damn - that's a very good point. Can't believe I missed it.
From the headline, I thought it might be about sports-related concussions!
I was morbidly curious what a "good CTE" could possibly be...