Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →No: a CTE is not automatically slower than a subquery, and writing WITH does not by itself create a temporary table. A database optimizer can fold or merge either kind of query expression into its parent, or materialize an intermediate result. Which happens depends on the database, its version, the query’s shape, and sometimes optimizer controls. To understand whether a query is spooling or spilling, inspect its execution plan rather than inferring behavior from its SQL formatting.
What is the difference between a CTE and a subquery?
A subquery is a SELECT nested inside another query. It can supply a value, filter rows through a predicate such as IN or EXISTS, or act as a derived table in the FROM clause. A common table expression (CTE) is a query introduced with WITH and given a name that can be used elsewhere in the statement.
As an Amazon Associate I earn from qualifying purchases.
That distinction is about how the query is written, not necessarily how it runs. Optimizers can transform query expressions before execution. PostgreSQL describes WITH as a way to break a complicated query into simpler parts, while its PostgreSQL 17 documentation also says eligible CTEs can be folded into the parent query. MySQL 8.4 likewise documents merging and materialization as alternative strategies for derived tables, views, and CTEs. A subquery does not inherently run once per outer row, and a CTE does not inherently spool to a temporary table.
What do folding, merging, and materialization mean?
Folding or merging
When the optimizer folds or merges a query expression, it incorporates that expression into the surrounding query plan rather than first storing its complete result as a separate intermediate table. This can expose more of the combined query to optimization. For example, an outer filter may be pushed toward the underlying table scan, potentially reducing the rows processed earlier.
#1 Best Overall
Materialization
Materialization means the engine produces an intermediate result and stores it for later use in the query. Readers sometimes call this “spooling.” It can be beneficial if producing the result is expensive and the query uses it more than once. It can also add work and storage, especially when the intermediate result is large. Whether that storage is in memory or on disk, and how the engine reports it, depends on the database and the particular operation.
MySQL’s 26.7 manual describes subquery materialization as using an in-memory temporary table when possible, with on-disk storage as a fallback if the table becomes too large. That is a MySQL-specific description, not a universal rule for CTEs or a claim about every MySQL version. The PostgreSQL references discussed here do not establish a corresponding memory threshold or spill statistic.
How PostgreSQL 17 handles CTEs
In PostgreSQL 17, a nonrecursive CTE can be folded into its parent if it is side-effect-free—that is, a SELECT containing no volatile functions. By default, PostgreSQL folds such a CTE when the parent references it once, but does not fold it by default when the parent references it more than once.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →For eligible CTEs, PostgreSQL provides MATERIALIZED and NOT MATERIALIZED annotations to influence that choice. NOT MATERIALIZED can make it possible for outer restrictions to be applied directly to base-table scans. Materialization, on the other hand, can keep an expensive expression from being recomputed for multiple uses. Neither annotation is a blanket performance fix: the best choice depends on what the plan does with the rows and how often it must produce them.
Recursive CTEs are a separate case. PostgreSQL describes their evaluation as iterative, using working and intermediate tables as the recursion proceeds. That mechanism should not be confused with the fold-or-materialize choice for an ordinary, nonrecursive CTE.
How MySQL 8.4 handles CTEs and derived tables
MySQL 8.4 documents merging a query block into its parent or materializing it into an internal temporary table. It aims to avoid unnecessary materialization when possible, which can allow conditions to be pushed down from an outer query. Materialization may also be delayed until its result is needed; if earlier join processing makes that result unnecessary, the engine may skip producing it.
Some query features prevent merging in MySQL 8.4. The documented examples include aggregation, window functions, DISTINCT, GROUP BY, HAVING, LIMIT, and UNION or UNION ALL. MERGE and NO_MERGE hints can influence the strategy where other rules do not prevent it.
If MySQL materializes a CTE, it materializes it once for the query even when multiple references use it. MySQL documents recursive CTEs as always materialized. These are MySQL behaviors; do not assume PostgreSQL or another engine makes the same choice.
Best Value
How the documented behaviors compare
| Engine and version | Documented behavior | What it means for diagnosis |
|---|---|---|
| PostgreSQL 17 | A nonrecursive, side-effect-free CTE referenced once is normally folded; one referenced multiple times is not folded by default. Eligible CTEs can use MATERIALIZED or NOT MATERIALIZED. |
Check whether the CTE’s reference count and eligibility fit the default, then confirm the actual plan. |
| MySQL 8.4 | Derived tables, views, and CTEs can be merged or materialized. A materialized CTE is produced once per query even if referenced several times; recursive CTEs are always materialized. | Look for the chosen strategy and whether merge-blocking query features apply. |
| MySQL 26.7 | For subquery materialization, an in-memory temporary table may fall back to on-disk storage if it becomes too large. | Treat this as a version-scoped account of MySQL subquery behavior, not a cross-engine spill rule. |
How to tell whether a query is materialized
Start with the engine and version, then read that version’s plan output. The SQL text alone cannot establish whether the optimizer folded, merged, or materialized an expression. In MySQL, the manual documents plan and trace cues, but the labels are MySQL-specific rather than portable SQL terminology.
- Identify the exact engine and release. The examples above are scoped to PostgreSQL 17 and MySQL 8.4 or 26.7; optimizer behavior can differ across releases.
- Inspect an execution plan for the query. PostgreSQL and MySQL both provide
EXPLAIN. Use the output to check whether the expression appears as part of a combined plan or as a separate intermediate operation. Interpret the plan using documentation for that engine and version. - For MySQL, use the relevant documented cues. Its documentation describes
SUBQUERYversusDEPENDENT SUBQUERYand extendedEXPLAINtext containing terms such asmaterializeormaterialized-subquery. For CTE handling, optimizer trace output may showcreating_tmp_tableandreusing_tmp_table. - Compare plans for controlled variants only when the engine supports the control. In PostgreSQL, compare eligible CTE forms with and without
MATERIALIZEDorNOT MATERIALIZED. In MySQL, relevant merge hints may be available if other rules do not bar merging. A hint changes what the optimizer may choose; it is not evidence that the resulting plan is faster. - Measure on representative data. Compare actual rows with estimates, the amount of intermediate data, and execution time or temporary I/O where the engine exposes them. A plan that saves repeated computation may still lose if it processes and stores a much larger result than a pushed-down plan.
There is no portable spill label or universal diagnostic threshold established here. If a plan suggests temporary storage, consult the documentation for the exact engine and release before concluding that it spilled to disk.
When should you choose a CTE or a subquery?
Choose the form that makes the query easiest to understand, then verify the plan when performance matters. A CTE can give a complex expression a name and make its reuse clearer. A subquery can keep a small operation local to the expression that needs it. Neither form is a reliable performance shortcut across engines.
When comparing alternatives, focus on the actual work:
- Was the expression folded or merged, or was it materialized?
- Could the optimizer push predicates toward base-table scans, and were useful indexes still available in the chosen plan?
- If the result is referenced more than once, does the engine reuse one materialized result or repeat work?
- How many rows and how much data width does the intermediate result contain, and what temporary storage does the engine use?
- Do estimated and actual row counts support the plan’s assumptions?
- Does recursion or a feature that blocks merging dictate an engine-specific strategy?
Materialization may avoid repeated computation; folding or merging may preserve predicate pushdown and broader optimization. Those are competing benefits, not a universal ranking. No runtime benchmark or quantified speed advantage is established for CTEs versus subqueries; decide from a plan and measurement on representative data.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
