The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Are CTEs slower than subqueries? Not inherently. A CTE and a subquery are ways to express a query; neither syntax guarantees that the database will create a temporary table or execute the inner query separately. Depending on the database, version, and query, the optimizer may fold or merge the expression into its parent, or materialize an intermediate result. To know what happens, check the execution plan for your specific engine.
What is the difference between a CTE and a subquery?
A subquery is a SELECT nested inside another statement. It can provide a value, act as an IN or EXISTS test, or supply rows in a FROM clause. A common table expression (CTE) is a named query introduced with WITH; the statement can refer to that name later.
For example, these queries express the same basic filtering operation. The first uses a derived-table subquery; the second gives that query a name:
SELECT orders.customer_id, orders.total
FROM (
SELECT customer_id, total
FROM orders
WHERE status = 'paid'
) AS paid_orders;
WITH paid_orders AS (
SELECT customer_id, total
FROM orders
WHERE status = 'paid'
)
SELECT customer_id, total
FROM paid_orders;The CTE can make a multi-step statement easier to read or reuse by name. That is a difference in query organization, not proof of a physical execution boundary. PostgreSQL 17 and MySQL 8.4 both document optimizations that can combine eligible query expressions with their parent statements.
#1 Best Overall
Does a CTE create a temporary table?
Not necessarily. A CTE is a logical query expression. The optimizer may fold it into the surrounding query, or it may materialize the result—compute it and store an intermediate result for later use. Some readers call this storage “spooling,” but the documented terms and the exact behavior vary by database.
Folding or merging can expose more of the statement to joint optimization. For example, an outer filter may be pushed down toward the underlying table scan. Materialization can be beneficial when it avoids repeating work, particularly if a result is referenced more than once. It can also introduce work and temporary storage. Neither strategy is automatically faster.
| Plan choice | What it means | Potential benefit or cost |
|---|---|---|
| Fold or merge | The query expression is combined with its parent rather than kept as a separate stored result. | Can enable joint optimization and predicate pushdown. The actual plan determines which scans and indexes are used. |
| Materialize | The database computes an intermediate result and stores it for use by the rest of the statement. | Can avoid recomputing work, but requires temporary storage and may limit opportunities to push outer conditions into the underlying query. |
How PostgreSQL 17 handles CTEs
In PostgreSQL 17, a nonrecursive, side-effect-free CTE—defined in the documentation as a SELECT with no volatile functions—can be folded into its parent query. By default, PostgreSQL folds it when the parent references it once. A CTE referenced more than once is not folded by default.
PostgreSQL provides MATERIALIZED and NOT MATERIALIZED annotations to influence the choice in eligible cases. For instance, the following shows where the annotations go; the right choice depends on the query:
WITH filtered AS NOT MATERIALIZED (
SELECT customer_id, total
FROM orders
WHERE status = 'paid'
)
SELECT customer_id, total
FROM filtered;NOT MATERIALIZED can allow restrictions from the outer query to be applied directly to base-table scans. Conversely, materializing can prevent an expensive expression from being recomputed for multiple uses. Do not add either annotation as a blanket speed fix: compare the resulting plans and performance on representative data.
Recursive CTEs are a separate case
A recursive CTE is evaluated iteratively. PostgreSQL describes the process in terms of working and intermediate tables as each iteration produces rows. This mechanism is not the same question as whether an ordinary, nonrecursive CTE is folded into its parent.
How MySQL handles CTEs and derived tables
MySQL 8.4 documents two strategies for derived tables, views, and CTEs: merge the query block into its parent or materialize it into an internal temporary table. The optimizer tries to avoid unnecessary materialization where possible, which can enable condition pushdown. Materialization can be delayed until its result is needed; if earlier join processing makes the result unnecessary, it may be skipped.
MySQL 8.4 also documents MERGE and NO_MERGE hints that can influence the strategy when other rules permit. Certain query features prevent merging, including aggregation, window functions, DISTINCT, GROUP BY, HAVING, LIMIT, and UNION or UNION ALL. A hint cannot override every such constraint.
Repeated and recursive CTE references
If MySQL materializes a CTE, it materializes it once per query even when the CTE is referenced multiple times. MySQL documentation also says recursive CTEs are always materialized. These are MySQL-specific rules; do not assume PostgreSQL or another database handles the same query identically.
What does “materialized” or “spilled to disk” mean?
Materialization means the engine stores an intermediate result instead of treating the expression only as part of a larger combined query. In MySQL 26.7’s documentation, subquery materialization uses an in-memory temporary table when possible and can fall back to on-disk storage if the table becomes too large. MySQL may use a hash index to make lookups into the materialized result efficient.
That description is specific to MySQL 26.7 and its documented subquery optimization. It does not establish a universal memory threshold, spill rule, or diagnostic label for all MySQL versions or database engines. A materialized CTE should not automatically be described as a disk spill: temporary-table materialization may remain in memory, and execution behavior depends on the engine and query.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchPC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11MySQL’s documented subquery materialization can let a noncorrelated subquery run once instead of being rewritten into a correlated form evaluated against outer rows. Eligibility depends on details including data-type compatibility, BLOB restrictions, and NULL semantics, so the optimizer may not choose it for every expression.
Best Value
How to tell what your query actually does
- Identify the database and version. Optimizer rules and plan labels are engine- and release-specific. The behavior described here is scoped to PostgreSQL 17, MySQL 8.4, and the separately identified MySQL 26.7 subquery-materialization documentation.
- Inspect the execution plan. Use the engine’s
EXPLAINfacility for the query you are investigating. Look for whether the expression was folded or merged, or whether a separate materialized result appears. Do not infer a spool from the presence ofWITHor nested SELECT syntax alone. - For MySQL, check its documented cues. MySQL’s EXPLAIN output can distinguish
SUBQUERYfromDEPENDENT SUBQUERY; extended EXPLAIN text can include terms such asmaterializeormaterialized-subquery. Optimizer trace output for CTEs can showcreating_tmp_tableandreusing_tmp_table. These are MySQL labels, not portable SQL terms. - Compare plans under controlled conditions. If the engine supports a relevant control, compare the ordinary query with a materialized or merge-influenced variant. Use representative data and the same conditions; examine estimated and actual rows, execution time, and temporary I/O where available.
- Evaluate the tradeoff for the query’s shape. Consider whether conditions reach base-table scans, whether repeated references reuse work, and how many rows and how much data the intermediate result contains. A plan-specific measurement—not the SQL formatting—shows which effect matters.
Why a rewrite can help—or make things worse
Changing a subquery into a CTE, or the reverse, does not by itself guarantee a faster plan. A rewrite matters when it changes the optimizer’s available transformations or when a database-specific control changes materialization. Folding can help if it lets a selective outer condition reach the underlying scan. Materialization can help if it avoids repeating a costly computation. The same choice can be beneficial in one query and harmful in another.
When investigating a slow statement, first verify the plan rather than rewriting for style. Then compare the intermediate result’s size, the handling of repeated references, and the plan’s row estimates with observed execution. If a result appears to use temporary storage, determine what the specific engine exposes about that storage; the available evidence here does not establish a portable diagnostic for disk spills.
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.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
