公用表表达式

功能简介

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。

  
• 示例二：使用CTE时指定MATERIALIZED

  通过指定MATERIALIZED的方式改变JOIN顺序，通过以下执行计划可以看出，JOIN顺序变为表t1先JOIN表t3后再JOIN表t2。

  
• 示例三：使用CTE时指定MATERIALIZED

  通过指定MATERIALIZED的方式改变JOIN顺序，通过以下执行计划可以看出，JOIN顺序变为表t2先JOIN表t3后再JOIN表t1。