CTE マテリアライズ戦略 - Hologres - Alibaba Cloud ドキュメントセンター

共通テーブル式 (CTE) は、複雑なクエリを簡素化し、可読性を向上させる SQL の機能です。CTE が使用されると、オプティマイザーはその結果セットをマテリアライズできます。このプロセスでは、クエリの後続部分で再利用するために結果セットが一時的に保存されます。CTE 再利用戦略は、オプティマイザーが CTE をどのように再利用して、繰り返しの計算を回避し、クエリパフォーマンスを向上させるかを決定します。

CTE 再利用戦略の設定

Hologres では、オプティマイザーは SQL 実行計画に基づいて CTE を再利用するかどうかを自動的に決定できます。

Hologres V3.2 より前のバージョンでは、Grand Unified Configuration (GUC) パラメーター optimizer_cte_inlining のみを使用して CTE の再利用をコントロールできます。

Hologres V3.2 以降では、optimizer_cte_inlining と hg_cte_strategy の両方の GUC パラメーターを使用して CTE の再利用をコントロールできます。

パラメーターの説明

パラメーター

説明

サポートされているバージョン

optimizer_cte_inlining

ON (デフォルト): CTE を強制的にインライン化します。
OFF: CTE 戦略を再利用に設定します。

すべてのバージョン

hg_cte_strategy

AUTO (デフォルト): オプティマイザーが CTE を再利用するかどうかを自動的に決定します。
INLINING: インライン化を強制します。CTE は再利用されず、CTE が参照されるたびにサブクエリが再計算されます。これは、CTE の計算が単純で結果セットが小さいシナリオに適しています。
REUSE: 再利用を強制します。CTE は一度だけ計算され、結果は再利用のためにキャッシュされます。これは、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` パラメーターの設定は効果がありません。`hg_cte_strategy` にはデフォルト値の `AUTO` を使用するか、明示的に `REUSE` に設定することしかできません。明示的に `INLINING` に設定することはできません。設定すると、エラーが報告されます。
  - optimizer_cte_inlining = on が (デフォルトまたは明示的に) 設定されている場合、CTE 戦略は自動的に `INLINING` に設定されません。代わりに、戦略は 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