The query was byte-for-byte identical on both systems. A monthly billing report: aggregate an events table into daily totals per customer, then pull one customer's month. On the old PostgreSQL 11 cluster it took 26 seconds. On the new MySQL 8 instance we were evaluating, the same WITH clause, same join, same predicates returned in 480 milliseconds. Fifty times faster, with no indexes added and no rewrite. Two years later, during a PostgreSQL 12 upgrade at a different company, I watched the mirror image: report queries that had been fast on 11 got dramatically slower on 12, again with no change to the SQL. Both surprises had the same root cause. The WITH clause is not a neutral piece of syntax that every planner reads the same way. It is a directive, and each engine reads a different directive into it.
Once you see CTE materialization as a semantic difference rather than a syntax difference, the porting surprises stop. This article is what I wish I had before that first migration: what each engine actually does with a WITH clause, what changed in PostgreSQL 12, what the EXPLAIN output looks like on both sides, how recursive CTEs differ, and how to force the behavior you want portably.
How did the same WITH clause produce a 50x difference?
It did because PostgreSQL 11 materialized the CTE in full before applying the outer filter, while MySQL merged the CTE into the outer query and pushed the customer filter down into the aggregation. The report looked like this, on both engines:
WITH daily_totals AS (
SELECT customer_id,
DATE(event_ts) AS day,
SUM(units) AS units
FROM events
GROUP BY customer_id, DATE(event_ts)
)
SELECT day, units
FROM daily_totals
WHERE customer_id = 42
AND day >= DATE '2024-05-01'
AND day < DATE '2024-06-01';The events table held about 180 million rows across roughly 40,000 customers. There was a composite index on (customer_id, event_ts). On MySQL, the optimizer dissolved the CTE, pushed customer_id = 42 inside, and the aggregation read only customer 42's rows through that index: about 4,500 index entries, half a second. On PostgreSQL 11, the planner treated the CTE as a self-contained unit of work: it computed daily totals for all 40,000 customers, wrote that intermediate result aside, and then filtered it down to the one customer the outer query asked for. The work done was four orders of magnitude larger than the work needed.
Neither planner was buggy. Each was obeying its own contract for what a CTE means, and the contracts differ. That is the whole story, and the next two sections are each side of it.
Does PostgreSQL materialize CTEs?
Before PostgreSQL 12, yes, always: every CTE was computed once, materialized into an internal work file, and treated as an opaque optimization fence that no predicate could cross. From PostgreSQL 12 onward, a non-recursive CTE that is referenced exactly once and has no side effects is inlined into the outer query by default, and you control the rest with two keywords. The fence is no longer the default, but it is still one keyword away:
-- PostgreSQL 12+: force the old behavior
WITH daily_totals AS MATERIALIZED (
SELECT customer_id, DATE(event_ts) AS day, SUM(units) AS units
FROM events
GROUP BY customer_id, DATE(event_ts)
)
SELECT day, units FROM daily_totals WHERE customer_id = 42;
-- PostgreSQL 12+: forbid materialization (e.g. the CTE is referenced twice)
WITH daily_totals AS NOT MATERIALIZED ( ... )
SELECT ...;The difference is plainly visible in EXPLAIN. Materialized, you get a separate subtree for the CTE and a scan over it, with the outer filter applied outside, which is exactly the 26-second plan:
CTE daily_totals
-> HashAggregate (actual time=24110.2..24890.4 rows=1440000)
-> Seq Scan on events (actual time=0.1..12600.3 rows=180000000)
-> CTE Scan on daily_totals (actual time=24110.3..25840.6 rows=30)
Filter: (customer_id = 42)Inlined, the predicate lands on the index and the plan looks like the aggregation was written by hand:
-> HashAggregate (actual time=312.4..330.1 rows=30)
-> Index Scan using events_customer_ts_idx on events
Index Cond: (customer_id = 42)Two subtleties from the upgrade incident worth keeping. First, the fence cut both ways: on PostgreSQL 11, experienced engineers sometimes used a WITH clause deliberately as a fence, to compute an expensive expression once or to stop a bad join order from forming, and PostgreSQL 12 silently removed that fence for single-use CTEs. Queries that were fast because of the fence got slow when it disappeared. If you ever wrote a comment saying "the CTE here is intentional", AS MATERIALIZED is how you make the intention survive upgrades. Second, some CTEs still always materialize on 12 and later: recursive CTEs, CTEs referenced more than once, and CTEs containing data-modifying statements. NOT MATERIALIZED is likewise only honored where inlining is legal.
Does MySQL materialize CTEs?
MySQL 8.0 treats a non-recursive CTE like a derived table: the optimizer merges it into the outer query whenever merging is legal, and materializes it into an internal temporary table when it is referenced more than once or is recursive. There is no MATERIALIZED or NOT MATERIALIZED keyword in MySQL's CTE syntax at all, so the decision is the optimizer's, guided only by the merge rules and, in current 8.0 releases, the same MERGE and NO_MERGE optimizer hints that apply to derived tables.
With EXPLAIN FORMAT=TREE the choice is spelled out. Merged, the CTE vanishes from the plan entirely and the predicate is inside the scan:
-> Aggregate: sum(events.units)
-> Index lookup on events using idx_customer_ts (customer_id=42)Referenced twice, the materialization appears explicitly as a Materialize step feeding a temp table scan:
-> Nested loop inner join
-> Table scan on daily_totals
-> Materialize
-> Aggregate: sum(events.units)
-> Index scan on events using idx_customer_ts
-> Index lookup on daily_totals2The practical consequence for our report: the single-reference CTE merged, the filter pushed down, and the query was fifty times faster than on PostgreSQL 11, because MySQL's default is what PostgreSQL 12 later adopted as its default. When I want to confirm which path a production MySQL query actually took, I check the digest row for it in performance_schema; the workflow in the sys schema daily-driver guide is how I keep those before-and-after numbers honest across a migration. One more MySQL-specific trap: because the merged CTE is just a rewritten query, EXPLAIN alone sometimes undersells what happens at scale, and EXPLAIN ANALYZE is worth the extra run on a staging copy before you trust a ported report.
How do recursive CTEs differ between MySQL and PostgreSQL?
They differ in one keyword and one safety valve: MySQL requires WITH RECURSIVE whenever the CTE is recursive and refuses the query without it, while PostgreSQL accepts plain WITH for recursive CTEs too and only needs the RECURSIVE keyword when recursion is present, and MySQL caps recursion depth by default where PostgreSQL does not. Here is the same running-total tree walk, in both dialects:
-- MySQL 8.0: RECURSIVE keyword is mandatory, or ERROR 3577
WITH RECURSIVE subordinates AS (
SELECT emp_id, manager_id, 1 AS depth
FROM employees
WHERE emp_id = 7
UNION ALL
SELECT e.emp_id, e.manager_id, s.depth + 1
FROM employees e
JOIN subordinates s ON e.manager_id = s.emp_id
)
SELECT emp_id, depth FROM subordinates;
-- PostgreSQL: RECURSIVE required only because it recurses;
-- plain WITH is accepted syntax for non-recursive CTEs
WITH RECURSIVE subordinates AS (
SELECT emp_id, manager_id, 1 AS depth
FROM employees
WHERE emp_id = 7
UNION ALL
SELECT e.emp_id, e.manager_id, s.depth + 1
FROM employees e
JOIN subordinates s ON e.manager_id = s.emp_id
)
SELECT emp_id, depth FROM subordinates;The bodies are close cousins: an anchor member, UNION ALL (or UNION, which both engines also accept, with the deduplication cost that implies), and a recursive member that references the CTE itself. Both engines restrict the recursive member in similar ways, notably no aggregate functions there. The operational differences sit around the syntax. MySQL enforces cte_max_recursion_depth, default 1000, and aborts a runaway recursive CTE with an error once the counter trips. PostgreSQL has no equivalent server-side depth cap: a recursive CTE with a bad termination condition runs until the disk or patience gives out, so the discipline of a depth column and a depth limit in the recursive member's WHERE clause is yours to bring. Porting from MySQL to PostgreSQL, every WITH RECURSIVE stays valid, because PostgreSQL accepts the keyword. Porting the other way, audit every WITH: a non-recursive CTE written with bare WITH works fine on MySQL, but anything recursive must gain the keyword, and any recursive CTE that leaned on running past a thousand levels needs either a raised session limit or a rewrite.
How do you force the behavior you want on both engines?
The portable playbook has three moves: use PostgreSQL's two keywords where they exist, keep MySQL CTEs single-reference when you want merging, and reach for a real temporary table when the materialization itself is the point. Concretely, that looks like this:
-- Force materialization, portable and explicit (both engines):
CREATE TEMPORARY TABLE daily_totals AS
SELECT customer_id, DATE(event_ts) AS day, SUM(units) AS units
FROM events
GROUP BY customer_id, DATE(event_ts);
-- Force inlining on PostgreSQL 12+:
WITH daily_totals AS NOT MATERIALIZED ( ... ) SELECT ...;
-- Keep merging on MySQL: reference the CTE exactly once,
-- non-recursive, no constructs that block merging.A few honest costs. The temp table is the only truly portable fence, and it is a real one: you pay for writing and reading it, you manage its lifetime, and you can index it, which is sometimes exactly why you wanted the fence. On PostgreSQL, resist the urge to stamp AS MATERIALIZED on everything out of version-11 nostalgia; the 12-and-later default is usually right, and the fence earns its keep only when the intermediate result is genuinely cheaper to compute once, when it protects a good plan from the optimizer, or when the CTE's own filter selectivity is terrible for the outer join. On MySQL, if you need the computed-once behavior for a multiply-referenced CTE, you already have it, because multiple references trigger materialization; what you do not get is a keyword to force it for a single reference, so the temp table or a NO_MERGE hint is the lever there. Finally, whatever you choose, verify with EXPLAIN on both engines, because the one universal property of CTE behavior is that it is a planner decision with defaults that have changed once already and can change again.
What does MonPG see on both sides of this?
Disclosure, since this is a vendor blog: MonPG monitors PostgreSQL today, and MySQL support is on the roadmap, not shipped. When it lands, plan-drift regressions of exactly this kind are what it needs to surface: a normalized statement whose mean execution time jumps 50x after an upgrade, with the before-and-after evidence attached, so "the planner changed how it reads WITH" is a diagnosis you reach in minutes rather than a theory you test at midnight. That work is tracked on the MySQL monitoring (coming soon) page. On the PostgreSQL side the equivalent visibility exists today: MonPG's PostgreSQL monitoring keeps pg_stat_statements history per normalized query, which is precisely how you catch the CTE-inlining regressions a major upgrade smuggles in. Until the MySQL half ships, the takeaways stand alone: know which contract your engine reads into WITH, write MATERIALIZED or NOT MATERIALIZED when the choice matters, and never port a CTE across engines without an EXPLAIN on the far side.