Skip to main content

Materializing Common Table Expressions

By using the variable materialize_ctes, common table expressions (CTEs) are materialized when queries with CTEs are run. The materialized results can then be referenced multiple times by the parent query. The CTE queries are cached, which results in optimized performance. This is particularly useful for running queries that contain an expensive CTE that produces a relatively small number of rows and is used two or more times.

The materializing CTEs functionality is enabled by default.

Enable or disable this functionality by setting the materialize_ctes engine variable, or setting it per session via the MATERIALIZE_CTES session variable:


The following settings are available:

  • ALL: Each CTE that appears more than once in the query is materialized into an internal result table.

  • AUTO: This is the default value. A suite of optimizations are enabled. Currently, only the Predicate Pushdown optimization is available.

  • OFF: CTEs are inlined, which is similar to VIEW definitions.

If users can't or don't want to use engine variables, the materialize_cte can be set on a per query basis by adding an OPTION expression at the end of the query:

SELECT * FROM foo OPTION(materialize_ctes="OFF");
SELECT * FROM foo OPTION(materialize_ctes="AUTO");
SELECT * FROM foo OPTION(materialize_ctes="ALL");

Materialization can also be turned off per CTE:

WITH foo as (select with(materialize=off) * from titanic), 
     bar as (select * from titanic) 
     select * from foo, bar;


  • Materialized CTEs appear in the EXPLAIN as m_foo_i, where foo is the name of the CTE in the original query, and i is an integer value generated during name normalization.

  • The results table that is created from a materialized CTE is analogous to an intermediate results table that is created as part of a distributed join query.

  • Plans for queries with CTEs are cacheable regardless of the setting of the materialize_ctes variable.

  • Some SELECT statements may not be materializable. For example, selects with limits, selects with aggregates and no group by, and selects without FROM clauses cannot be materialized. All CTEs whose selects are non-materializable will get inlined with the parent query.

Predicate Pushdown

To avoid materializing unused rows, a rudimentary form of predicate pushdown in CTEs is supported. This optimization is enabled only when the materialize_ctes session variable is set to AUTO. An example of how a query is internally rewritten by SingleStoreDB is shown below.

FROM foo, foo as bar
WHERE foo.a = 1 AND bar.b = 2;

is transformed to:

WITH foo AS (SELECT * FROM t WHERE t.a = 1 OR t.b = 2)
FROM foo, foo as bar
WHERE foo.a = 1 AND bar.b = 2;

Unlike normal predicate pushdown, the original top-level predicates remain.