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.

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

SET MATERIALIZE_CTES = { 'ALL' | 'AUTO' | 'OFF' };

The following settings are available:

• ALL: Each CTE that appears more than once in the query is materialized into an internal result table.
• AUTO: A suite of optimizations are enabled. Currently, only the Predicate Pushdown optimization is available.
• OFF: This is the default value. CTEs are inlined, which is similar to VIEW definitions.

Remarks

• 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 SingleStore DB is shown below.

WITH foo AS (SELECT * FROM t)
SELECT *
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)
SELECT *
FROM foo, foo as bar
WHERE foo.a = 1 AND bar.b = 2;

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

Was this Article Helpful?

Thanks For Your Feedback!