PostgreSQL 12 inlines your CTE

grep -rn "WITH " src/ was the first thing I ran after upgrading to PostgreSQL 12 this month. I was looking for a trick that had stopped working.

For years WITH was an optimization fence. The planner materialized the CTE first and only then ran the outer query. Everyone used this both ways. As a bug: you wrap a subquery into a CTE for readability, the planner stops pushing conditions inside, a fast query becomes a scan of half a table. As a feature: you write a CTE on purpose, to pin evaluation order and stop the planner from being creative. Half of the CTE advice on the internet is really advice about the fence.

In 12 the fence is gone. A CTE referenced once with no side effects gets inlined, predicates get pushed inside, the planner treats it like the subquery it always looked like. Queries of the first kind got faster for free. Queries of the second kind, the ones that used WITH as a hint, silently lost their hint.

The behavior is now explicit, which I like:

WITH stats AS MATERIALIZED (
    SELECT user_id, count(*) AS cnt
    FROM events GROUP BY user_id
)
SELECT ...

MATERIALIZED gives the old fence back. NOT MATERIALIZED forces inlining even when the CTE is referenced twice. No keyword, the planner decides. If a query depends on the fence, say so in the query. A hint that lives only in the author’s head does not survive an upgrade.

The grep found every WITH that touches a big table. I re-ran EXPLAIN on each. Two queries got faster, one report needed MATERIALIZED back. Twenty minutes.

Planner knowledge has a version attached. A recipe learned on 9.4 is a fact about 9.4, not about SQL. I had been giving that recipe in code reviews for years. Now I give it with a version number.