CTE Reuse策略最佳化 - Hologres

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設定不同參數時，執行計畫的區別。

準備樣本資料。

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

使用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