ヒントの使用方法とシナリオ - Hologres - Alibaba Cloud ドキュメントセンター

ヒントは、SQL 文の実行方法を変更できる補助的な SQL 構文です。これにより、特別な SQL 最適化が可能になります。Hologres V2.2 以降のバージョンはヒント構文をサポートしています。ヒントを使用して SQL 文の実行メソッドを変更し、SQL クエリをチューニングして、より良いパフォーマンスを実現できます。このトピックでは、ヒントの使用方法と一般的なシナリオについて説明します。

前提条件

ヒント機能を使用する前に、セッションレベルまたはデータベースレベルで `pg_hint_plan_enable_hint` Grand Unified Configuration (GUC) パラメーターを `on` に設定します。

セッションレベルでパラメーターを有効にする

SET pg_hint_plan_enable_hint=on;  /* セッションレベルでヒントを有効にする */

データベースレベルでパラメーターを有効にする。この設定は新しい接続に対して有効になります。
```
ALTER DATABASE <dbname> SET pg_hint_plan_enable_hint=on; /* データベースレベルでヒントを有効にする */
```

制限事項

ヒント機能には Hologres V2.2 以降が必要です。ご利用のインスタンスが V2.1 以前のバージョンの場合は、インスタンスをアップグレードする必要があります。

使用上の注意

ヒントは、通常テーブル (外部テーブルを含む)、サブクエリ、および共通テーブル式 (CTE) に指定できます。ビューではヒントはサポートされていません。
すべてのヒントコンテンツは /*+HINT と */ で囲む必要があります。ヒント内ではコメントは許可されていません。
ヒントキーワードでは、大文字と小文字は区別されません。
ヒントには複数のキーワードを含めることができます。
ヒントワードは特定のクエリレベルに適用されます。ヒントワードで指定するパラメーターは、現在の範囲内で参照可能である必要があります。親クエリまたは別のサブクエリのパラメーターを使用することはできません。たとえば、次の SQL 文では、ヒントワード /*+HINT Leading(tt t2) */ は、パラメーターとして tt と t2 のみを指定できます。t1, t3, or t は使用できません。同様に、ヒントワード /*+HINT Leading(t t1) */ は t と t1 のみを使用できます。t2, t3, or tt は使用できません。
```
SELECT /*+HINT Leading(t t1) */ * FROM t1 join (
  SELECT /*+HINT Leading(tt t2) */ * FROM t2 join (
    SELECT * FROM t3
  ) tt
) t;
```

INSERT INTO ... SELECT 文の場合、`INSERT` キーワードの範囲には、宛先テーブルと `SELECT` 文の最も外側のソーステーブルが含まれます。`SELECT` キーワードの範囲には、宛先テーブルは含まれません。競合を防ぐため、`INSERT` の後にヒントワードを指定した場合、`SELECT` 文で別のヒントワードを指定することはできません。以下の例では、正しい使用方法と誤った使用方法を示します。

正しい使用方法

-- 例 1: INSERT の後のヒントは、パラメーターとして target、t1、t2 をサポートします。
INSERT /*+HINT Leading(target (t1 t2)) */ INTO target SELECT t1.a,t2.b FROM t1 JOIN t2 ON t1.a=t2.a

-- 例 2: SELECT の後のヒントは、パラメーターとして t1 と t2 のみをサポートします。
INSERT INTO target SELECT /*+HINT Leading(t2 t1) */ t1.a,t2.b FROM t1 JOIN t2 ON t1.a=t2.a;

誤った使用方法

-- INSERT と SELECT の両方の後にヒントを使用することはできません。使用した場合、エラーが報告されます。
INSERT /*+HINT Leading(target (t1 t2)) */ INTO target SELECT /*+HINT Leading(t2 t1) */ t1.a,t2.b FROM t1 JOIN t2 ON t1.a=t2.a;

-- エラーメッセージ
ERROR: insert statement with hint should not have sub select with hint at the same time

GUC ヒントは現在のクエリに対してのみ有効です。クエリ内のどのレベルで設定された GUC ヒントも、クエリ全体に影響します。クエリの実行後、後続のクエリは GUC ヒントの影響を受けません。
例：サブクエリで GUC ヒントワードを設定して count distinct reuse を無効にした場合、その設定はクエリ全体に適用されます。
```
SELECT
    count(DISTINCT a),
    count(DISTINCT b)
FROM (
    SELECT
        /*+HINT set(hg_experimental_enable_reuse_cte_of_count_distinct off) */
        t1.a t2.b
    FROM
        t1
        JOIN t2 ON t1.a = t2.a)
```
ヒントワードで丸括弧 `()` を使用すると、パラメーターの優先順位を指定できます。丸括弧の入れ子も可能です。例えば、ヒントワード Leading(t1 t2 t3) では、まず `t1` と `t2` が結合され、次にその結果が `t3` と結合されます。ヒントワード Leading(t1 (t2 t3)) では、まず `t2` と `t3` が結合され、次にその結果が `t1` と結合されます。
結合メソッドヒントと結合順序ヒントが有効になるには、少なくとも 2 つの有効なパラメーターが必要です。有効なパラメーターの数が不十分な場合、ヒントは有効になりません。
説明
有効なパラメーター: 有効なパラメーターとは、現在のクエリレベルの範囲に含まれるテーブル、サブクエリ、および CTE です。たとえば、ヒント Leading(t1 t1) および Leading(t1) に含まれる有効なパラメーターは、`t1` の 1 つのみです。
ランタイムフィルターヒントは HashJoin 操作でのみ有効です。
生成されたプランに、結合メソッドのヒントワードで指定されたテーブル結合が含まれていない場合、そのヒントワードは有効になりません。たとえば、`HashJoin(t1 t2)` を指定しても、生成されたプランが t1 Join t3 操作を実行してから `t2` を結合する場合、ヒントワードは有効になりません。この場合、Leading(t1 t2) を追加して結合順序を強制できます。
`SELECT` キーワードの後に /*+HINT と */ で囲まれた複数の連続したヒントが続く場合、最初のヒントのみが有効になります。例えば、SELECT /*+HINT HashJoin(t1 t2) */ /*+HINT Leading(t1 t2) */ ... 文では、`HashJoin` ヒントのみが有効になり、`Leading` ヒントは無視されます。
同じタイプのヒントで定義されたテーブルが互いに競合する場合、最初に定義されたヒントが優先されます。
説明
競合は次のシナリオで発生します。
- 2 つのヒントに同じテーブルが含まれている。
- テーブルのセットが同一である。
- 結合順序ヒントの場合、一方のヒントのパラメーターが他方のヒントのパラメーターのサブセットである。
- 結合メソッド、ランタイムフィルター、またはスキュージョインヒントのパラメーターは、別のヒントのパラメーターのサブセットであってはなりません。
- 例 1: ヒントワード HashJoin(t1 t2) と NestLoop(t2 t1) は、同じテーブルのセットを指定します。この場合、競合が発生し、HashJoin(t1 t2) ヒントワードのみが有効になります。
```
SELECT /*+HINT HashJoin(t1 t2) NestLoop(t2 t1) */ ...
```
- 例 2: Leading(t1 t2) ヒントのテーブルは、Leading(t1 t2 t3) ヒントのテーブルのサブセットです。この場合、競合が発生し、Leading(t1 t2) ヒントのみが有効になります。
```
SELECT /*+HINT Leading(t1 t2) Leading(t1 t2 t3) */ ...
```
ヒントワードで指定された計画が計画生成条件を満たさない場合、実行計画は生成できません。たとえば、2 つのテーブルに対して `NestLoop` と `Right Join` を指定した場合、指定された計画はサポートされません。その結果、ERROR: ORCA failed to produce a plan : No plan has been computed for required properties のようなエラーメッセージが返されます。これは、必要なプロパティが満たされていないため、計画を生成できないことを示しています。

構文

SQL 文内でヒントを記述するには、次の構文を使用します。

SELECT|UPDATE|INSERT|DELETE /*+HINT <HintName(params)>  */ ...

HintName(params) は、ヒントキーワードとそれに対応するパラメーターを表します。詳細については、「ヒントキーワード」をご参照ください。

説明

ヒントキーワードでは、大文字と小文字は区別されません。
ヒントは、`INSERT`、`UPDATE`、`DELETE`、または `SELECT` キーワードの直後にのみ指定できます。
ヒントの内容は /*+HINT と */ で囲む必要があります。

ヒントキーワード

次の表は、サポートされているヒントキーワードとそれらのパラメーターフォーマットを示しています。

タイプ	パラメーターフォーマット	説明	例	注
結合メソッド	`NestLoop(table1 table2[ table...])`	ネステッドループ結合を強制します。	`SELECT /+HINT NestLoop(table1 table2) / * FROM table1 JOIN table2 ON table1.a = table2.a;`	ヒントは、少なくとも 2 つの有効なパラメーターが含まれている場合にのみ有効になります。説明有効なパラメーターには、現在のクエリレベルのスコープにあるテーブル、サブクエリ、および共通テーブル式 (CTE) が含まれます。この機能は Hologres V2.2 以降でサポートされています。
結合メソッド	`HashJoin(table1 table2[ table...])`	ハッシュ結合を強制します。	`SELECT /+HINT HashJoin(table1 table2 table3) / table1.a FROM table1 JOIN table2 ON table1.a = table2.a JOIN table3 ON table1.a = table3.a;`
結合順序	`Leading(table1 table2[ table...])`	指定された順序で結合を強制します。	`SELECT /+HINT Leading(table2 table1) / table1.a from table1 Join table2 on table1.a = table2.a;`
結合順序	`Leading(<Join pair>)`	結合の順序と方向を強制します。説明結合ペアは、丸括弧で囲まれたテーブルまたは他の結合ペアのペアです。これらのペアはネスト構造を形成できます。	`SELECT /+HINT Leading((table2 table1) (table3 table4)) / * FROM table1 LEFT JOIN table2 ON table1.a = table2.a RIGHT JOIN table3 ON table1.a = table3.a LEFT JOIN table4 ON table3.a = table4.a ORDER BY table1.a;`
ランタイムフィルター	`RuntimeFilter(table1 table2[ table...])`	指定されたテーブルでのハッシュ結合にランタイムフィルターの使用を強制します。	`SELECT /+HINT RuntimeFilter(table1 table2) / * FROM table1 JOIN table2 ON table1.a = table2.a;`	HashJoin にのみ適用されます。 Hologres V2.2 以降でサポートされています。
GUC	`Set(GUC-parameter value)`	プラン作成中に GUC パラメーターの値を指定します。説明 GUC-parameter は GUC パラメーターの名前を示します。値は GUC パラメーターの値を示します。 Hologres でサポートされている GUC パラメーターの詳細については、「GUC パラメーター」をご参照ください。	`EXPLAIN SELECT /+HINT set(hg_experimental_enable_reuse_cte_of_count_distinct off) / count(DISTINCT a), count(DISTINCT b) FROM table1;`	ヒントは現在のクエリにのみ有効です。現在のクエリが完了した後、後続のクエリは GUC ヒントの影響を受けません。これは Hologres V2.2 以降のバージョンでサポートされています。
モーションヒント	`Broadcast(table table[ table...])`	JOIN の片側のコレクションのブロードキャストを強制します。	`SELECT /+HINT Broadcast(t2) Leading(t1 t2) / * FROM t1 JOIN t2 ON t1.a=t2.a;`	通常、Leading と一緒に使用されます。 Hologres V3.0 以降でサポートされています。
	`NoBroadcast(table table[ table...])`	JOIN でコレクションのテーブルがブロードキャストされるのを防ぎます。	`SELECT /+HINT NoBroadcast(t2) Leading(t1 t2) / * FROM t1 JOIN t2 ON t1.a=t2.a;`
	`Gather(table table[ table...])`	JOIN でテーブルコレクションの片側で Gather 操作を強制します。	`SELECT /+HINT Gather(t2) Leading(t1 t2) / * FROM t1 JOIN t2 ON t1.a=t2.a;`
	`NoGather(table table[ table...])`	JOIN 操作でテーブルコレクションの片側の Gather ステップをスキップするように強制します。	`SELECT /+HINT NoGather(t2) Leading(t1 t2) / * FROM t1 JOIN t2 ON t1.a=t2.a;`

シナリオ

以下の例は、ヒントを使用できるシナリオを示しています。例のテーブルは、次のデータ定義言語 (DDL) 文を実行して作成されます。

CREATE TABLE target (a int primary key, b int);
CREATE TABLE t1 (a int, b int);
CREATE TABLE t2 (a int, b int);
CREATE TABLE t3 (a int);
CREATE TABLE t4 (a int);

ヒントを使用した結合順序の調整

結合順序ヒントの主な目的は、テーブルが結合される順序を調整することです。非効率的な結合順序は、SQL クエリのパフォーマンスに大きな影響を与える可能性があります。非効率的な結合順序は、通常、統計情報の欠落または不正確さによって引き起こされます。

統計情報の欠落：これは通常、`ANALYZE` 操作がタイムリーに実行されないために発生します。`ANALYZE` 操作の詳細については、「ANALYZE と AUTO ANALYZE」をご参照ください。
不正確な統計情報：これは、フィルターまたは結合操作の後に発生する可能性があります。統計情報が古くなり、実際の結果セットと推定行数の間に大きな乖離が生じます。

結合順序が非効率的な場合、GUC パラメーターまたはヒントを使用して手動で調整できます。ヒントを使用して結合順序を調整する方が、GUC パラメーターを使用するよりも簡単で便利です。

次の SQL の例では、テーブルは t1 Join t2 として結合されます。ハッシュ結合では、データベースは小さい方のテーブルを使用してハッシュテーブルを構築します。この操作は、実行計画内の Hash オペレーターの下に表示されます。テーブル t2 の行数がテーブル t1 よりも大幅に多い場合、クエリパフォーマンスは低下します。この問題を解決するには、ANALYZE 操作を実行してデータベースの統計を更新するか、ヒントワードを使用して結合順序を変更することができます。たとえば、Leading(t2 t1) ヒントワードを使用すると、結合が t2 Join t1 に変更されます。これにより、実行計画がより効率的になり、パフォーマンスが向上します。

SQL の例

SELECT /*+HINT Leading(t2 t1) */ t1.a FROM t1 JOIN t2 ON t1.a = t2.a;

実行計画の比較

ヒントなしの実行計画

QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Gather  (cost=0.00..10.07 rows=1000 width=4)
   ->  Hash Join  (cost=0.00..10.05 rows=1000 width=4)
         Hash Cond: (t1.a = t2.a)
         ->  Redistribution  (cost=0.00..5.01 rows=1000 width=4)
               Hash Key: t1.a
               ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                     ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=4)
         ->  Hash  (cost=5.01..5.01 rows=1000 width=4)
               ->  Redistribution  (cost=0.00..5.01 rows=1000 width=4)
                     Hash Key: t2.a
                     ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                           ->  Seq Scan on t2  (cost=0.00..5.00 rows=1000 width=4)

ヒントありの実行計画

QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Gather  (cost=0.00..10.07 rows=1000 width=4)
   ->  Hash Join  (cost=0.00..10.05 rows=1000 width=4)
         Hash Cond: (t2.a = t1.a)
         ->  Redistribution  (cost=0.00..5.01 rows=1000 width=4)
               Hash Key: t2.a
               ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                     ->  Seq Scan on t2  (cost=0.00..5.00 rows=1000 width=4)
         ->  Hash  (cost=5.01..5.01 rows=1000 width=4)
               ->  Redistribution  (cost=0.00..5.01 rows=1000 width=4)
                     Hash Key: t1.a
                     ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                           ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=4)

GUC ヒントの使用

一部のシナリオでは、より良いクエリパフォーマンスを実現するために GUC パラメーターを使用する必要がある場合があります。GUC ヒントは、クエリレベルで GUC パラメーターを設定するために使用されます。クエリを実行する前に GUC パラメーターを設定して同じ効果を得ることもできますが、GUC ヒントは単一のクエリに対して効率的に設定する方法を提供します。クエリが完了すると、GUC パラメーターの設定は無効になり、後続のクエリには影響しません。

SQL の例

SELECT /*+HINT set(hg_experimental_query_batch_size 512) */t1.a FROM t1 JOIN t2 ON t1.a = t2.a;

実行プラン

QUERY PLAN
Hash Join  (cost=0.00..10.00 rows=1 width=4)
  Hash Cond: (t1.a = t2.a)
  ->  Gather  (cost=0.00..5.00 rows=1 width=4)
        ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
              ->  Seq Scan on t1  (cost=0.00..5.00 rows=1 width=4)
  ->  Hash  (cost=5.00..5.00 rows=1 width=4)
        ->  Gather  (cost=0.00..5.00 rows=1 width=4)
              ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                    ->  Seq Scan on t2  (cost=0.00..5.00 rows=1 width=4)

CTE およびサブクエリにヒントを使用する

CTE とサブクエリを含むシナリオでは、ヒントを使用してそれらの実行計画に影響を与えることができます。

SQL の例

WITH c1 AS ( /* CTE c1 にヒントを使用 */
        SELECT /*+HINT Leading(t2 t1) */ t1.a FROM (
            (
                SELECT /*+HINT leading(t2 t1) */ t1.a FROM t1 JOIN t2 ON t1.a = t2.a
            ) AS t1
            JOIN
            (
                SELECT /*+HINT NestLoop(t4 t3) */ t4.a FROM t3 JOIN t4 ON t3.a = t4.a
            ) AS t2
            ON t1.a = t2.a
        )
    ),
    c2 AS ( /* CTE c2 にヒントを使用 */
        SELECT /*+HINT leading(t1 t2) */ t2.a FROM (
            (
                SELECT /*+HINT Leading(t1 t2) */ t1.a FROM t1 JOIN t2 ON t1.a = t2.a
            ) AS t1
            JOIN
            (
                SELECT /*+HINT Leading(t4 t3) */ t4.a FROM t3 JOIN t4 ON t3.a = t4.a
            ) AS t2
            ON t1.a = t2.a
        )
    )
    SELECT /*+HINT NestLoop(v2 v1) */  * FROM ( /* サブクエリにヒントを使用 */
        (
            SELECT /*+HINT Leading (c1 t2) */ c1.a FROM c1 JOIN t2 ON c1.a = t2.a
        ) AS v1
        JOIN
        (
            SELECT /*+HINT Leading (t1 c2) */ c2.a FROM t1 JOIN c2 ON t1.a = c2.a
        ) AS v2
        ON v1.a = v2.a
    )
    ORDER BY v2.a;

実行プラン

QUERY PLAN
Sort  (cost=0.00..10660048.36 rows=1 width=8)
  Sort Key: t4_1.a
  ->  Gather  (cost=0.00..10660048.36 rows=1 width=8)
        ->  Nested Loop  (cost=0.00..10660048.36 rows=1 width=8)
              Join Filter: ((t1.a = t4_1.a) AND (t1.a = t1_1.a) AND (t2_1.a = t1_1.a) AND (t2_1.a = t4_1.a))
              ->  Hash Join  (cost=0.00..25.01 rows=1 width=8)
                    Hash Cond: (t1_1.a = t4_1.a)
                    ->  Redistribution  (cost=0.00..5.00 rows=1 width=4)
                          Hash Key: t1_1.a
                          ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                ->  Seq Scan on t1 t1_1  (cost=0.00..5.00 rows=1 width=4)
                    ->  Hash  (cost=20.00..20.00 rows=1 width=4)
                          ->  Hash Join  (cost=0.00..20.00 rows=1 width=4)
                                Hash Cond: ((t1_2.a = t4_1.a) AND (t1_2.a = t3_1.a) AND (t2_2.a = t3_1.a) AND (t2_2.a = t4_1.a))
                                ->  Hash Join  (cost=0.00..10.00 rows=1 width=8)
                                      Hash Cond: (t1_2.a = t2_2.a)
                                      ->  Redistribution  (cost=0.00..5.00 rows=1 width=4)
                                            Hash Key: t1_2.a
                                            ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                                  ->  Seq Scan on t1 t1_2  (cost=0.00..5.00 rows=1 width=4)
                                      ->  Hash  (cost=5.00..5.00 rows=1 width=4)
                                            ->  Redistribution  (cost=0.00..5.00 rows=1 width=4)
                                                  Hash Key: t2_2.a
                                                  ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                                        ->  Seq Scan on t2 t2_2  (cost=0.00..5.00 rows=1 width=4)
                                ->  Hash  (cost=10.00..10.00 rows=1 width=8)
                                      ->  Hash Join  (cost=0.00..10.00 rows=1 width=8)
                                            Hash Cond: (t4_1.a = t3_1.a)
                                            ->  Redistribution  (cost=0.00..5.00 rows=1 width=4)
                                                  Hash Key: t4_1.a
                                                  ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                                        ->  Seq Scan on t4 t4_1  (cost=0.00..5.00 rows=1 width=4)
                                            ->  Hash  (cost=5.00..5.00 rows=1 width=4)
                                                  ->  Redistribution  (cost=0.00..5.00 rows=1 width=4)
                                                        Hash Key: t3_1.a
                                                        ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                                              ->  Seq Scan on t3 t3_1  (cost=0.00..5.00 rows=1 width=4)
              ->  Materialize  (cost=0.00..10385.07 rows=40 width=8)
                    ->  Broadcast  (cost=0.00..10385.07 rows=40 width=8)
                          ->  Hash Join  (cost=0.00..10385.07 rows=1 width=8)
                                Hash Cond: (t1.a = t2_1.a)
                                ->  Hash Join  (cost=0.00..10380.07 rows=1 width=4)
                                      Hash Cond: ((t4.a = t1.a) AND (t3.a = t1.a) AND (t3.a = t2.a) AND (t4.a = t2.a))
                                      ->  Redistribution  (cost=0.00..10370.07 rows=1 width=8)
                                            Hash Key: t4.a
                                            ->  Nested Loop  (cost=0.00..10370.07 rows=1 width=8)
                                                  Join Filter: (t3.a = t4.a)
                                                  ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                                        ->  Seq Scan on t3  (cost=0.00..5.00 rows=1 width=4)
                                                  ->  Materialize  (cost=0.00..5.00 rows=40 width=4)
                                                        ->  Broadcast  (cost=0.00..5.00 rows=40 width=4)
                                                              ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                                                    ->  Seq Scan on t4  (cost=0.00..5.00 rows=1 width=4)
                                      ->  Hash  (cost=10.00..10.00 rows=1 width=8)
                                            ->  Hash Join  (cost=0.00..10.00 rows=1 width=8)
                                                  Hash Cond: (t2.a = t1.a)
                                                  ->  Redistribution  (cost=0.00..5.00 rows=1 width=4)
                                                        Hash Key: t2.a
                                                        ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                                              ->  Seq Scan on t2  (cost=0.00..5.00 rows=1 width=4)
                                                  ->  Hash  (cost=5.00..5.00 rows=1 width=4)
                                                        ->  Redistribution  (cost=0.00..5.00 rows=1 width=4)
                                                              Hash Key: t1.a
                                                              ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                                                    ->  Seq Scan on t1  (cost=0.00..5.00 rows=1 width=4)
                                ->  Hash  (cost=5.00..5.00 rows=1 width=4)
                                      ->  Redistribution  (cost=0.00..5.00 rows=1 width=4)
                                            Hash Key: t2_1.a
                                            ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                                  ->  Seq Scan on t2 t2_1  (cost=0.00..5.00 rows=1 width=4)

INSERT ヒントの使用

ほとんどの場合、INSERT INTO ... SELECT シナリオでは、宛先テーブルがソーステーブルに関連付けられており、結合順序の指定やその他の調整を行いたい場合に、ヒントワードを使用できます。INSERT INTO ... SELECT シナリオの SQL ロジックは複雑な場合があるため、実行計画に基づいてヒントワードを追加する必要が生じることがあります。

例 1：ヒントは INSERT 文の宛先テーブルと SELECT クエリの最も外側のソーステーブルに適用されます。

実行中に、t1 Join t2 操作によって生成されるデータ量が small で、宛先テーブル `target` のデータ量が large で、かつ統計情報が更新されていない場合、生成された実行計画が最適でない可能性があります。この場合、ヒントワードを使用して結合順序を調整し、より良いパフォーマンスを実現できます。

SQL の例

--ヒントは INSERT 文の target テーブルと SELECT クエリの最も外側のソーステーブルに有効です。
INSERT /*+HINT Leading(target (t1 t2)) */ INTO target SELECT t1.a,t2.b FROM t1 JOIN t2 ON t1.a=t2.a;

実行プラン

QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Gather  (cost=0.00..26.57 rows=1000 width=8)
   ->  Insert  (cost=0.00..26.54 rows=1000 width=8)
         ->  Project  (cost=0.00..16.12 rows=1000 width=8)
               ->  Hash Right Join  (cost=0.00..15.12 rows=1000 width=12)
                     Hash Cond: (target.a = t1.a)
                     ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                           ->  Seq Scan on target  (cost=0.00..5.00 rows=1000 width=4)
                     ->  Hash  (cost=10.07..10.07 rows=1000 width=8)
                           ->  Redistribution  (cost=0.00..10.07 rows=1000 width=8)
                                 Hash Key: t1.a
                                 ->  Hash Join  (cost=0.00..10.06 rows=1000 width=8)
                                       Hash Cond: (t1.a = t2.a)
                                       ->  Redistribution  (cost=0.00..5.01 rows=1000 width=4)
                                             Hash Key: t1.a
                                             ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                                                   ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=4)
                                       ->  Hash  (cost=5.01..5.01 rows=1000 width=8)
                                             ->  Redistribution  (cost=0.00..5.01 rows=1000 width=8)
                                                   Hash Key: t2.a
                                                   ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
                                                         ->  Seq Scan on t2  (cost=0.00..5.00 rows=1000 width=8)

例 2：ヒントは SELECT サブクエリに適用されます。

SQL の例

説明

ヒントを使用する次の 2 つの INSERT 文は同等です。

INSERT INTO target SELECT /*+HINT Leading(t2 t1) */ t1.a,t2.b FROM t1 JOIN t2 ON t1.a=t2.a;

INSERT /*+HINT Leading(t2 t1) */ INTO target SELECT t1.a,t2.b FROM t1 JOIN t2 ON t1.a=t2.a;

実行プラン

QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Gather  (cost=0.00..26.57 rows=1000 width=8)
   ->  Insert  (cost=0.00..26.54 rows=1000 width=8)
         ->  Project  (cost=0.00..16.12 rows=1000 width=8)
               ->  Hash Left Join  (cost=0.00..15.12 rows=1000 width=12)
                     Hash Cond: (t1.a = target.a)
                     ->  Redistribution  (cost=0.00..10.07 rows=1000 width=8)
                           Hash Key: t1.a
                           ->  Hash Join  (cost=0.00..10.06 rows=1000 width=8)
                                 Hash Cond: (t2.a = t1.a)
                                 ->  Redistribution  (cost=0.00..5.01 rows=1000 width=8)
                                       Hash Key: t2.a
                                       ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
                                             ->  Seq Scan on t2  (cost=0.00..5.00 rows=1000 width=8)
                                 ->  Hash  (cost=5.01..5.01 rows=1000 width=4)
                                       ->  Redistribution  (cost=0.00..5.01 rows=1000 width=4)
                                             Hash Key: t1.a
                                             ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                                                   ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=4)
                     ->  Hash  (cost=5.00..5.00 rows=1000 width=4)
                           ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                                 ->  Seq Scan on target  (cost=0.00..5.00 rows=1000 width=4)

UPDATE ヒントの使用

`UPDATE` 文では、ヒントは通常、宛先テーブルがソーステーブルに関連付けられており、手動での調整が必要な場合にも使用されます。

SQL の例
`t1` テーブルのデータ量は `target` テーブルよりも大きいです。ヒントを設定して `target` テーブルを使用してハッシュテーブルを構築し、結合順序を調整できます。
```
UPDATE /*+HINT Leading(t1 target) */ target SET b=t1.b+1 FROM t1 WHERE t1.a=target.a;
```

実行計画の比較

ヒントなしの実行計画

QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Gather  (cost=0.00..52.77 rows=1000 width=1)
   ->  Update  (cost=0.00..52.76 rows=1000 width=1)
         ->  Project  (cost=0.00..11.09 rows=1000 width=32)
               ->  Hash Join  (cost=0.00..10.08 rows=1000 width=32)
                     Hash Cond: (target.a = t1.a)
                     ->  Local Gather  (cost=0.00..5.00 rows=1000 width=28)
                           ->  Seq Scan on target  (cost=0.00..5.00 rows=1000 width=28)
                     ->  Hash  (cost=5.01..5.01 rows=1000 width=8)
                           ->  Redistribution  (cost=0.00..5.01 rows=1000 width=8)
                                 Hash Key: t1.a
                                 ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
                                       ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=8)

ヒントありの実行計画

QUERY PLAN                                          
----------------------------------------------------------------------------------------------
 Gather  (cost=0.00..52.77 rows=1000 width=1)
   ->  Update  (cost=0.00..52.76 rows=1000 width=1)
         ->  Project  (cost=0.00..11.09 rows=1000 width=32)
               ->  Hash Join  (cost=0.00..10.08 rows=1000 width=32)
                     Hash Cond: (t1.a = target.a)
                     ->  Redistribution  (cost=0.00..5.01 rows=1000 width=8)
                           Hash Key: t1.a
                           ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
                                 ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=8)
                     ->  Hash  (cost=5.00..5.00 rows=1000 width=28)
                           ->  Local Gather  (cost=0.00..5.00 rows=1000 width=28)
                                 ->  Seq Scan on target  (cost=0.00..5.00 rows=1000 width=28)

ランタイムフィルターヒントの使用

Hologres はランタイムフィルターをサポートしています。SQL 文がランタイムフィルターをトリガーする条件を満たさない場合、ヒントを使用して強制的にトリガーし、クエリのパフォーマンスを向上させることができます。

重要

ヒントを使用してランタイムフィルターの生成を強制できるのは、HashJoin 操作を実行する場合のみです。
ランタイムフィルターの生成を強制しても、必ずしもクエリのパフォーマンスが向上するとは限りません。特定のシナリオに基づいてその使用を評価してください。

SQL の例

SELECT /*+HINT runtimefilter(t1 t2) */ * FROM t1 JOIN t2 ON t1.a = t2.a;

実行プラン

-- 実行計画にランタイムフィルターが表示される場合、ランタイムフィルターがトリガーされたことを示します。
QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Gather  (cost=0.00..10.13 rows=1000 width=16)
   ->  Hash Join  (cost=0.00..10.07 rows=1000 width=16)
         Hash Cond: (t1.a = t2.a)
         Runtime Filter Cond: (t1.a = t2.a)
         ->  Redistribution  (cost=0.00..5.01 rows=1000 width=8)
               Hash Key: t1.a
               ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
                     ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=8)
                           Runtime Filter Target Expr: t1.a
         ->  Hash  (cost=5.01..5.01 rows=1000 width=8)
               ->  Redistribution  (cost=0.00..5.01 rows=1000 width=8)
                     Hash Key: t2.a
                     ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
                           ->  Seq Scan on t2  (cost=0.00..5.00 rows=1000 width=8)

モーションヒントの使用

例 1: `JOIN` 操作中にテーブル `t1` を強制的にブロードキャストします。このメソッドは、通常、統計情報が不正確なために大量のデータシャッフルが発生する可能性がある場合に使用されます。ハッシュ結合ではビルド側のテーブルしかブロードキャストできないため、`Leading` を使用して結合順序を指定する必要があります。これにより、統計情報の欠落によってブロードキャストのコストペナルティが高くなりすぎる場合に発生しうる、t1 JOIN t2 という順序をオプティマイザーが選択するのを防ぎます。

SQL の例

SELECT /*+HINT Leading(t2 t1) Broadcast(t1) */ * FROM t1 JOIN t2 ON t1.a = t2.a;

実行計画

QUERY PLAN                                          
----------------------------------------------------------------------------------------------
  Gather  (cost=0.00..100000000000000005366162204393472.00 rows=1000 width=16)
   ->  Hash Join  (cost=0.00..100000000000000005366162204393472.00 rows=1000 width=16)
         Hash Cond: (t2.a = t1.a)
         ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
               ->  Seq Scan on t2  (cost=0.00..5.00 rows=1000 width=8)
         ->  Hash  (cost=100000000000000005366162204393472.00..100000000000000005366162204393472.00 rows=3000 width=8)
               ->  Broadcast  (cost=0.00..100000000000000005366162204393472.00 rows=3000 width=8)
                     ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
                           ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=8)

例 2：`t1` テーブルがブロードキャストされないように強制します。これは、テーブルの統計情報が不正確なシナリオで役立ちます。これにより、大きなテーブルが小さいと推定され、そのテーブルでブロードキャストが実行されるとパフォーマンスが低下する可能性があります。

サンプルデータ

CREATE TABLE test1(a int, b int);
CREATE TABLE test2(a int, b int);

INSERT INTO test1 SELECT 1, i FROM generate_series(1, 10) AS i;
INSERT INTO test2 SELECT 1,i FROM generate_series(1, 1000000) AS i;

analyze test1,test2;

SQL の例

ヒントなしのブロードキャスト

explain SELECT * FROM test1 JOIN test2 ON test1.b = test2.b;

ヒントを使用したブロードキャストの無効化

explain SELECT /*+HINT NoBroadcast(test1)  */ * FROM test1 JOIN test2 ON test1.b = test2.b;

実行計画

ヒントなしのブロードキャスト

QUERY PLAN
---------------------------------------------------------------------------------
Gather  (cost=0.00..51.98 rows=1000000 width=16)
  ->  Hash Join  (cost=0.00..13.12 rows=1000000 width=16)
        Hash Cond: (test2.b = test1.b)
        ->  Local Gather  (cost=0.00..5.23 rows=1000000 width=8)
              ->  Seq Scan on test2  (cost=0.00..5.20 rows=1000000 width=8)
        ->  Hash  (cost=5.00..5.00 rows=200 width=8)
              ->  Broadcast  (cost=0.00..5.00 rows=200 width=8)
                    ->  Local Gather  (cost=0.00..5.00 rows=10 width=8)
                          ->  Seq Scan on test1  (cost=0.00..5.00 rows=10 width=8)

ヒントを使用したブロードキャストの無効化

QUERY PLAN
---------------------------------------------------------------------------------
Gather  (cost=0.00..53.23 rows=1000000 width=16)
  ->  Hash Join  (cost=0.00..14.37 rows=1000000 width=16)
        Hash Cond: (test2.b = test1.b)
        ->  Redistribution  (cost=0.00..6.48 rows=1000000 width=8)
              Hash Key: test2.b
              ->  Local Gather  (cost=0.00..5.23 rows=1000000 width=8)
                    ->  Seq Scan on test2  (cost=0.00..5.20 rows=1000000 width=8)
        ->  Hash  (cost=5.00..5.00 rows=10 width=8)
              ->  Redistribution  (cost=0.00..5.00 rows=10 width=8)
                    Hash Key: test1.b
                    ->  Local Gather  (cost=0.00..5.00 rows=10 width=8)
                          ->  Seq Scan on test1  (cost=0.00..5.00 rows=10 width=8)