Common table expressions
AnalyticDB for PostgreSQL V7.0 adds explicit control over how the query optimizer handles common table expressions (CTEs). Specify MATERIALIZED or NOT MATERIALIZED in a WITH clause to override default optimizer behavior and improve query performance.
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
WITHclause 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
WITHclause 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.
test=# explain select * from t1 join t2 on t1.a = t2.i join t3 on t2.i = t3.m;
QUERY PLAN
------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=1359.50..19638521.85 rows=638277381 width=24)
-> Hash Join (cost=1359.50..11128156.77 rows=212759127 width=24)
Hash Cond: (t1.a = t3.m)
-> Hash Join (cost=679.75..128744.45 rows=2471070 width=16)
Hash Cond: (t1.a = t2.i)
-> Seq Scan on t1 (cost=0.00..321.00 rows=28700 width=8)
-> Hash (cost=321.00..321.00 rows=28700 width=8)
-> Seq Scan on t2 (cost=0.00..321.00 rows=28700 width=8)
-> Hash (cost=321.00..321.00 rows=28700 width=8)
-> Seq Scan on t3 (cost=0.00..321.00 rows=28700 width=8)
Optimizer: Postgres query optimizer
(11 rows)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.
test=# explain with x1 as materialized (select * from t1 join t3 on t1.a = t3.m) select * from x1 join t2 on x1.m = t2.i;
QUERY PLAN
--------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=2545.25..90591971.45 rows=638277381 width=24)
-> Hash Join (cost=2545.25..82081606.37 rows=212759127 width=24)
Hash Cond: (share0_ref1.m = t2.i)
-> Shared Scan (share slice:id 1:0) (cost=128744.45..131818.92 rows=2471070 width=16)
-> Hash Join (cost=679.75..128744.45 rows=2471070 width=16)
Hash Cond: (t1.a = t3.m)
-> Seq Scan on t1 (cost=0.00..321.00 rows=28700 width=8)
-> Hash (cost=321.00..321.00 rows=28700 width=8)
-> Seq Scan on t3 (cost=0.00..321.00 rows=28700 width=8)
-> Hash (cost=1469.00..1469.00 rows=86100 width=8)
-> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1469.00 rows=86100 width=8)
-> Seq Scan on t2 (cost=0.00..321.00 rows=28700 width=8)
Optimizer: Postgres query optimizer
(13 rows)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.
test=# explain with x1 as materialized (select * from t2 join t3 on t2.i = t3.m) select * from x1 join t1 on x1.i = t1.a;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=679.75..37400324.20 rows=638277381 width=24)
-> Hash Join (cost=679.75..28889959.12 rows=212759127 width=24)
Hash Cond: (share0_ref1.i = t1.a)
-> Shared Scan (share slice:id 1:0) (cost=128744.45..131818.92 rows=2471070 width=16)
-> Hash Join (cost=679.75..128744.45 rows=2471070 width=16)
Hash Cond: (t2.i = t3.m)
-> Seq Scan on t2 (cost=0.00..321.00 rows=28700 width=8)
-> Hash (cost=321.00..321.00 rows=28700 width=8)
-> Seq Scan on t3 (cost=0.00..321.00 rows=28700 width=8)
-> Hash (cost=321.00..321.00 rows=28700 width=8)
-> Seq Scan on t1 (cost=0.00..321.00 rows=28700 width=8)
Optimizer: Postgres query optimizer
(12 rows)