AI 助理
备案控制台
官方文档
输入文档关键字查找
首页 实时数仓 Hologres 操作指南 性能优化 优化执行计划 CTE Reuse策略优化

CTE Reuse策略优化

更新时间：
产品详情
我的收藏

CTE（Common Table Expression，公用表表达式）在SQL中用于简化复杂查询并提升可读性。当使用CTE时，优化器会自动将CTE物化处理，即将结果临时存储，以便在后续SQL查询中进行多次调用。CTE Reuse策略则是指优化器如何有效重用CTE，以避免重复计算，从而提高查询性能。

设置CTE Reuse策略

在Hologres中，优化器可以通过SQL的执行计划自适应决定CTE复用。

在Hologres V3.2之前版本，只能通过一个GUC参数optimizer_cte_inlining控制CTE是否复用。

在Hologres V3.2及之后版本，可通过两个GUC参数optimizer_cte_inlining和hg_cte_strategy同时控制CTE是否复用。

参数说明

参数

参数说明

支持版本

optimizer_cte_inlining

  • ON（默认值），CTE强制Inline。

  • OFF，CTE策略为Reuse。

所有版本

hg_cte_strategy

  • AUTO（默认值）,优化器自适应决定CTE是否复用。

  • INLINING,强制Inline，即不复用CTE，每次CTE引用时重新计算子查询，适用于CTE计算简单，结果集较小的场景。

  • REUSE,强制Reuse，即仅计算一次，CTE结果缓存后复用，适用于CTE计算复杂，结果集较大且被多次引用的场景。

V3.2及以上

参数设置

  • Hologres V3.2之前版本

    SET optimizer_cte_inlining ={on|off}

  • Hologres V3.2及以上版本

    SET optimizer_cte_inlining ={on|off}

SET hg_cte_strategy ={AUTO|INLINING|REUSE};

    • 使用说明

      • optimizer_cte_inlining优先级高于hg_cte_strategy。

      • 当设置optimizer_cte_inlining = off时，CTE策略为Reuse。此时hg_cte_strategy参数设置将不再生效，只能使用默认值AUTO或显式设置成REUSE，但不能显式设置成INLINING，否则会报错。

      • 当设置optimizer_cte_inlining = on（无论是默认值还是显式设置）时，不再强制Inline，CTE策略由hg_cte_strategy的取值决定。

使用示例

通过如下示例展示hg_cte_strategy设置不同参数时，执行计划的区别。

  1. 准备示例数据。

    CREATE TABLE t1 (
    a INT,
    b INT,
    c INT
);

  2. 使用hg_cte_strategy的不同策略查看执行计划。

    • 当hg_cte_strategy为默认值AUTO时，执行计划如下。从执行计划来看，只有cte1复用了，cte2没有复用。

      EXPLAIN WITH cte1 AS (SELECT DISTINCT a,b,c FROM t1),cte2 AS (SELECT a,b,c FROM t1)
SELECT * FROM cte1 
UNION ALL 
SELECT * FROM cte1
UNION ALL
SELECT * FROM cte2
UNION ALL
SELECT * FROM cte2;

      返回结果如下。

      QUERY PLAN
Gather  (cost=0.00..25.00 rows=4 width=12)
  CTE cte1  (cost=0.00..5.00 rows=1 width=12)
      ->  Forward  (cost=0.00..5.00 rows=1 width=12)
            ->  HashAggregate  (cost=0.00..5.00 rows=1 width=12)
                  Group Key: t1_2.a, t1_2.b, t1_2.c
                  ->  Redistribution  (cost=0.00..5.00 rows=1 width=12)
                        Hash Key: t1_2.a, t1_2.b, t1_2.c
                        ->  Local Gather  (cost=0.00..5.00 rows=1 width=12)
                              ->  Seq Scan on t1 t1_2  (cost=0.00..5.00 rows=1 width=12)
  ->  Append  (cost=0.00..20.00 rows=4 width=12)
        ->  CTE Scan on cte1  (cost=0.00..5.00 rows=1 width=12)
        ->  CTE Scan on cte1  (cost=0.00..5.00 rows=1 width=12)
        ->  Local Gather  (cost=0.00..5.00 rows=1 width=12)
              ->  Seq Scan on t1  (cost=0.00..5.00 rows=1 width=12)
        ->  Local Gather  (cost=0.00..5.00 rows=1 width=12)
              ->  Seq Scan on t1 t1_1  (cost=0.00..5.00 rows=1 width=12)
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 3.2.0

    • 当hg_cte_strategy为INLINING时，执行计划如下。可以看到执行计划中没有CTE复用。

      SET hg_cte_strategy = INLINING;
EXPLAIN WITH cte1 AS (SELECT DISTINCT a,b,c FROM t1), 
cte2 AS (SELECT a,b,c FROM t1) 
SELECT * FROM cte1 
UNION ALL 
SELECT * FROM cte1 
UNION ALL 
SELECT * FROM cte2 
UNION ALL 
SELECT * FROM cte2;

      返回结果如下。

      QUERY PLAN
Gather  (cost=0.00..20.00 rows=4 width=12)
  ->  Append  (cost=0.00..20.00 rows=4 width=12)
        ->  HashAggregate  (cost=0.00..5.00 rows=1 width=12)
              Group Key: t1.a, t1.b, t1.c
              ->  Redistribution  (cost=0.00..5.00 rows=1 width=12)
                    Hash Key: t1.a, t1.b, t1.c
                    ->  Local Gather  (cost=0.00..5.00 rows=1 width=12)
                          ->  Seq Scan on t1  (cost=0.00..5.00 rows=1 width=12)
        ->  HashAggregate  (cost=0.00..5.00 rows=1 width=12)
              Group Key: t1_1.a, t1_1.b, t1_1.c
              ->  Redistribution  (cost=0.00..5.00 rows=1 width=12)
                    Hash Key: t1_1.a, t1_1.b, t1_1.c
                    ->  Local Gather  (cost=0.00..5.00 rows=1 width=12)
                          ->  Seq Scan on t1 t1_1  (cost=0.00..5.00 rows=1 width=12)
        ->  Local Gather  (cost=0.00..5.00 rows=1 width=12)
              ->  Seq Scan on t1 t1_2  (cost=0.00..5.00 rows=1 width=12)
        ->  Local Gather  (cost=0.00..5.00 rows=1 width=12)
              ->  Seq Scan on t1 t1_3  (cost=0.00..5.00 rows=1 width=12)
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 3.2.0

    • 当hg_cte_strategy为REUSE时，执行计划如下，可以看到cte1和cte2都有复用。

      SET hg_cte_strategy = REUSE;
EXPLAIN WITH cte1 AS (SELECT DISTINCT a,b,c FROM t1), 
cte2 AS (SELECT a,b,c FROM t1) 
SELECT * FROM cte1 
UNION ALL 
SELECT * FROM cte1 
UNION ALL
SELECT * FROM cte2 
UNION ALL
SELECT * FROM cte2;

      返回结果如下。

      QUERY PLAN
Gather  (cost=0.00..30.00 rows=4 width=12)
  CTE cte1  (cost=0.00..5.00 rows=1 width=12)
      ->  Forward  (cost=0.00..5.00 rows=1 width=12)
            ->  HashAggregate  (cost=0.00..5.00 rows=1 width=12)
                  Group Key: t1_1.a, t1_1.b, t1_1.c
                  ->  Redistribution  (cost=0.00..5.00 rows=1 width=12)
                        Hash Key: t1_1.a, t1_1.b, t1_1.c
                        ->  Local Gather  (cost=0.00..5.00 rows=1 width=12)
                              ->  Seq Scan on t1 t1_1  (cost=0.00..5.00 rows=1 width=12)
  CTE cte2  (cost=0.00..5.00 rows=1 width=12)
      ->  Forward  (cost=0.00..5.00 rows=1 width=12)
            ->  Local Gather  (cost=0.00..5.00 rows=1 width=12)
                  ->  Seq Scan on t1  (cost=0.00..5.00 rows=1 width=12)
  ->  Append  (cost=0.00..20.00 rows=4 width=12)
        ->  CTE Scan on cte1  (cost=0.00..5.00 rows=1 width=12)
        ->  CTE Scan on cte1  (cost=0.00..5.00 rows=1 width=12)
        ->  CTE Scan on cte2  (cost=0.00..5.00 rows=1 width=12)
        ->  CTE Scan on cte2  (cost=0.00..5.00 rows=1 width=12)
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 3.2.0
上一篇：HBO下一篇：HINT