3 ms·

For those of us who didn't know what a CTE was standing for. Here is a link to the PostgreSQL documentation on Common Table Expressions (CTEs)[1], and an articl

For those of us who didn't know what a CTE was standing for. Here is a link to the PostgreSQL documentation on Common Table Expressions (CTEs)[1], and an article on Wikipedia on Hierarchical and recursive queries, that explains the concept [2]. [1] https://www.postgresql.org/docs/12/queries-with.html https://www.postgresql.org/docs/12/queries-with.html [2] https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL#Common_table_expression https://en.wikipedia.org/wiki/Hierarchical_and_recursive_que... edit: formatting

cosmie 6y ago
Also for those who didn't know, and are just getting started with them: They're incredibly handy for a variety of reasons, but can also have unexpected performance impacts. For example, here are functionally equivalent queries written with a CTE vs an inlined/derived table: WITH cte_foo AS (SELECT * FROM bar LIMIT 1000000) SELECT * FROM cte_foo LIMIT 1 and SELECT * FROM (SELECT * FROM bar LIMIT 1000000) as inlined_foo LIMIT 1 Depending on the database you're using, those two could have wildly different performance due to a concept called an optimization fence[1]. In Postgres versions 11 and below, the CTE would have truly returned/materialized 1 million rows, then the outer query would execute and ultimately return 1 row for the resultset. Whereas the second version would have been optimized such that the outer LIMIT 1 would have been pushed into the subquery and not materialized those extraneous 999,999 rows to begin with. As mentioned in [1], Postgres 12 (and 13) have started to tackle that optimization fence within Postgres. But it's still a concern/concept to be aware of, since many databases that support CTEs have varying levels of optimization fences, and you'll want to be sure you understand what optimization/performance impacts exist for your particular database before you go down the CTE path. [1] https://auto1.tech/postgres12-a-precious-release/ https://auto1.tech/postgres12-a-precious-release/

cosmie 6y ago

Also for those who didn't know, and are just getting started with them: They're incredibly handy for a variety of reasons, but can also have unexpected performance impacts. For example, here are functionally equivalent queries written with a CTE vs an inlined/derived table: WITH cte_foo AS (SELECT * FROM bar LIMIT 1000000) SELECT * FROM cte_foo LIMIT 1 and SELECT * FROM (SELECT * FROM bar LIMIT 1000000) as inlined_foo LIMIT 1 Depending on the database you're using, those two could have wildly different performance due to a concept called an optimization fence[1]. In Postgres versions 11 and below, the CTE would have truly returned/materialized 1 million rows, then the outer query would execute and ultimately return 1 row for the resultset. Whereas the second version would have been optimized such that the outer LIMIT 1 would have been pushed into the subquery and not materialized those extraneous 999,999 rows to begin with. As mentioned in [1], Postgres 12 (and 13) have started to tackle that optimization fence within Postgres. But it's still a concern/concept to be aware of, since many databases that support CTEs have varying levels of optimization fences, and you'll want to be sure you understand what optimization/performance impacts exist for your particular database before you go down the CTE path. [1] https://auto1.tech/postgres12-a-precious-release/ https://auto1.tech/postgres12-a-precious-release/