AnalyticDB:Common table expressions

How it works

A CTE defines a temporary result set scoped to a single query. Reference it multiple times within the same query to avoid repeating subquery logic:

WITH x1 AS
(SELECT a FROM t1),
x2 AS
(SELECT b FROM t1)
SELECT * FROM
x1 JOIN x2 ON x1.a = x2.b;

The query optimizer decides how to compute a CTE using one of two strategies:

• MATERIALIZED — computes the WITH clause first, stores the result set, then references it in the outer query. This isolates the CTE from the outer query's conditions, which can change the join order.

• NOT MATERIALIZED — folds the WITH clause into the outer query, as if the CTE were written inline. This allows the optimizer to push conditions from the outer query into the CTE.

Before V7.0, this automatic selection was the only option. In V7.0, override the default by specifying MATERIALIZED or NOT MATERIALIZED explicitly.

Control CTE materialization

Specify MATERIALIZED or NOT MATERIALIZED immediately after the CTE name.

MATERIALIZED — forces separate computation of the WITH clause:

EXPLAIN WITH x1 AS MATERIALIZED (SELECT * FROM t1) SELECT * FROM x1 WHERE a > 1;

The execution plan shows the optimizer computes x1 first via a Shared Scan, then applies the filter in the outer query:

QUERY PLAN
---------------------------------------------------------------------------------------
 Gather Motion 3:1 (slice1; segments:3) (cost=0.00..3085.25 rows=86100 width=8)
     -> Subquery Scan on x1 (cost=0.00..1937.25 rows=28700 width=8)
         Filter: (x1.a > 1)
         -> Shared Scan (share slice:id 1:0) (cost=321.00..355.50 rows=28700 width=8)
             -> Seq Scan on t1 (cost=0.00..321.00 rows=28700 width=8)
 Optimizer: Postgers query optimizer
(6 rows)

NOT MATERIALIZED — folds the WITH clause into the outer query:

EXPLAIN WITH x1 AS NOT MATERIALIZED (SELECT * FROM t1) SELECT * FROM x1 WHERE a > 1;

The execution plan shows the optimizer merges the CTE into a single sequential scan with the filter applied directly:

QUERY PLAN
------------------------------------------------------------------------------
 Gather Motion 3:1 (slice1; segments:3) (cost=0.00..775.42 rows=28700 width=8)
     -> Seq Scan on t1 (cost=0.00..392.75 rows=9567 width=8)
         Filter: (a > 1)
 Optimizer: Postgres query optimizer
(4 rows)

Examples

Example 1: Query execution plan without CTEs

A three-table join without CTEs. The optimizer joins t1 with t2 first, then joins the result with t3.

Example 2: Changed join order with MATERIALIZED (t1 → t3 → t2)

MATERIALIZED changes the join order. The optimizer joins t1 with t3 first, then joins the result with t2.

Example 3: Changed join order with MATERIALIZED (t2 → t3 → t1)

MATERIALIZED with a different CTE structure changes the join order again. The optimizer joins t2 with t3 first, then joins the result with t1.