thebench.bio #1 Ecosystem for the Bio-Tech Industry
All SQL questions

Question 16 of 20 Intermediate Concept

What is a common table expression, and when does PostgreSQL materialize it?

Short answer

A common table expression, written with WITH, names an intermediate result so a long query reads as steps. Since PostgreSQL 12, a simple CTE used once is folded into the main query and optimized with it. One used more than once, recursive or with side effects is computed once and stored.

Why

CTEs make analytical SQL readable: first select eligible samples, then their passing results, then the summary, each step named. Before PostgreSQL 12 every CTE was computed in full and stored before the rest of the query ran, which acted as an optimization fence. A filter in the outer query could not be pushed into the CTE, so WITH w AS (SELECT * FROM big_results) SELECT * FROM w WHERE sample_id = 'S-12' read the whole table.

The PostgreSQL 12 release notes record the change: CTEs are now inlined automatically if they have no side effects, are not recursive and are referenced once. The documentation explains you can override this. MATERIALIZED forces separate calculation, useful when an expensive function should run once per row or a CTE is reused.

NOT MATERIALIZED forces inlining for a CTE referenced several times when each use needs only a small part. CTEs can also contain INSERT, UPDATE or DELETE, and WITH RECURSIVE walks hierarchies such as aliquot lineage. Other databases make different choices, so a CTE that is fast in one engine can be slow in another.

When performance matters, read the plan rather than assuming.

A strong answer covers

  • CTE names an intermediate result for readability
  • Inlined automatically since PostgreSQL 12 when simple and used once
  • MATERIALIZED and NOT MATERIALIZED override the default
  • Recursive CTEs walk hierarchies
  • Engines differ, so check the plan

Common mistakes

  • Believing every CTE is an optimization fence, which was true only before PostgreSQL 12
  • Assuming a CTE referenced twice is computed twice