在公用表表达式中指定MATERIALIZED控制执行计划-云原生数据仓库AnalyticDB-阿里云-云原生数据仓库AnalyticDB(AnalyticDB)-阿里云帮助中心

AnalyticDB PostgreSQL版7.0版本增强了公用表表达式（Common Table Expression，简称CTE）功能，支持对CTE语句指定MATERIALIZED或NOT MATERIALIZED，可以更好地控制执行计划，能够有效提升SQL性能。

功能简介

CTE可以在单个语句的执行范围内定义临时结果集，该结果集只在查询期间有效。CTE可以自引用，也可在同一查询中多次引用，实现了代码段的重复利用。常见CTE语句格式如下：

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

CTE对查询语句有如下两种处理方法：

MATERIALIZED：先在WITH子查询内部进行计算，然后再汇总计算。
NOT MATERIALIZED：将WITH子查询强行拉取到父查询中进行计算。

7.0版本以前，数据库的优化器会自行决定采用上述的其中一种方法。7.0版本以后，您可以通过指定MATERIALIZED或NOT MATERIALIZED来干预上述行为。示例如下：

指定MATERIALIZED

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

通过执行计划可以看出，系统先计算了子查询，再进行汇总计算。

                                    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

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

通过执行计划示例可以看出，系统直接将子查询拉取到父查询中进行计算。

                                    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)

示例

示例一：不使用CTE时的执行计划

查看一个三表JOIN的执行计划，通过以下执行计划可以看出，默认JOIN顺序为表t1先JOIN表t2后再JOIN表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)

示例二：使用CTE时指定MATERIALIZED

通过指定MATERIALIZED的方式改变JOIN顺序，通过以下执行计划可以看出，JOIN顺序变为表t1先JOIN表t3后再JOIN表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)

示例三：使用CTE时指定MATERIALIZED

通过指定MATERIALIZED的方式改变JOIN顺序，通过以下执行计划可以看出，JOIN顺序变为表t2先JOIN表t3后再JOIN表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)