This one’s a funny inversion. Normally when a database gets smarter, queries get faster. But when Postgres 12 stopped materializing CTEs by default, a bunch of our queries got slower, and for weirdly interesting reasons.

What changed

Pre-12: a CTE was an optimization fence. The planner materialized it and that was that.

12+: CTEs can be inlined if they’re referenced only once and the planner thinks inlining is cheaper. The planner is usually right about cost but “usually” is the enemy.

The query that got slow

We had a pattern like:

WITH active_users AS (
  SELECT user_id, subscription_tier
  FROM users
  WHERE deleted_at IS NULL
    AND last_active_at > now() - interval '30 days'
)
SELECT au.subscription_tier, count(e.id)
FROM active_users au
JOIN events e ON e.user_id = au.user_id
WHERE e.created_at > now() - interval '1 day'
GROUP BY au.subscription_tier;

Pre-12: the CTE materializes ~80k rows, a hash join does the rest, done in 40ms.

Post-12: the CTE gets inlined, the planner tries to be smart, and the resulting nested plan joins events-first and filters users after. Because events is 200x the size of users, the “smart” plan is disastrously slow. Like 18 seconds slow.

Why does the planner get it so wrong

Two reasons.

First, statistics on last_active_at are subject to ndistinct estimation, and Postgres wasn’t great at inferring how selective that predicate was for the inlined form. Pre-12, materialization hid this — the number of rows from the CTE was measured at run time.

Second, the query planner’s cost model for nested joins is optimistic when the outer is small and the inner is indexed. Usually a win. When the estimation is wrong by 10x, you get this.

The fix (pre-12-era wisdom, rediscovered)

Postgres 12 gave us MATERIALIZED and NOT MATERIALIZED as explicit hints. The right fix for this query was:

WITH active_users AS MATERIALIZED (
  SELECT user_id, subscription_tier
  FROM users
  WHERE deleted_at IS NULL
    AND last_active_at > now() - interval '30 days'
)
SELECT au.subscription_tier, count(e.id)
FROM active_users au
JOIN events e ON e.user_id = au.user_id
WHERE e.created_at > now() - interval '1 day'
GROUP BY au.subscription_tier;

Back to 40ms.

But wait, there’s a more subtle case

Some of our queries got faster from inlining. Think of a CTE used as a row source where a predicate from the outer query could have been pushed into the CTE if it were inlined. Pre-12 that predicate couldn’t cross the fence.

Example:

WITH user_events AS (
  SELECT e.user_id, e.event_type, e.created_at
  FROM events e
)
SELECT *
FROM user_events
WHERE user_id = $1
  AND created_at > now() - interval '1 hour';

Pre-12: materialize ALL events, then filter. Hilariously bad. Post-12: inline, push predicates down, use index. Fast.

So the new default is actually better for the CTE-as-view pattern. It’s worse for the CTE-as-reusable-intermediate pattern.

The lesson (and the migration plan we settled on)

When we moved from 11 to 14, we did two sweeps:

  1. grep for WITH in our queries and categorized them: “reusable intermediate” vs “view-style single use.”
  2. Added MATERIALIZED explicitly to the first category.

We also added a small test harness that runs EXPLAIN on a representative data sample and fails CI if a query’s plan shape changes between runs. Not a perfect defense, but caught two regressions during the migration.

Reflection

The reason I like this bug is that it’s a reminder that “the optimizer is smarter now” is a statement of expected value, not worst case. For mixed workloads where some queries benefit and some regress, you need to be explicit about your intent. MATERIALIZED and NOT MATERIALIZED are good features, and I now recommend using them deliberately on any non-trivial CTE rather than relying on the default.

Also: I spent way too long debugging this. The plan changed, the query changed, the behavior changed, and the version bump was the only thing in the commit. Reading the release notes carefully is a skill I’ve been trying to develop.

Related: The bitmap heap scan that ate our p99 is another planner-gone-wrong story.