Hacker News Reader

Comment by simonz05 | Hacker News Reader

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

[2] https://en.wikipedia.org/wiki/Hierarchical_and_recursive_que...

edit: formatting

View on HN

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/

Reply on news.ycombinator.com