Hologres:EXPLAIN および EXPLAIN ANALYZE について

実行プランの概要

Hologres では、クエリ オプティマイザ（QO）が各 SQL ステートメントの実行プランを生成します。 クエリ エンジン（QE）は、QO によって生成された実行プランに基づいて最終的な実行プランを生成します。 次に、QE は SQL ステートメントを実行し、結果を返します。 実行プランには、SQL 統計、演算子、演算子の実行時間などの情報が含まれています。 優れた実行プランは、より少ないリソースで SQL ステートメントを実行し、より速く結果を取得するのに役立ちます。 実行プランは、日常のデータ開発に不可欠です。 SQL ステートメントの問題を特定し、SQL ステートメントを最適化するのに役立ちます。

Hologres は PostgreSQL と互換性があります。 EXPLAIN および EXPLAIN ANALYZE ステートメントを実行することで、SQL ステートメントの実行プランをクエリできます。

• EXPLAIN: このステートメントは、実際の実行プランではなく、SQL ステートメントの特性に基づいて QO によって推定された SQL 実行プランを返します。 この推定実行プランは、SQL ステートメントの実行の参考になります。

• EXPLAIN ANALYZE: このステートメントは、実際の SQL 実行プランを返します。 EXPLAIN ステートメントによって返される推定実行プランと比較して、EXPLAIN ANALYZE ステートメントによって返される実際の実行プランには、実際に実行された演算子や各演算子の正確な実行時間などの実際の実行情報が含まれています。 各演算子の実行時間に基づいて、SQL ステートメントの最適化を実行できます。

EXPLAIN

• 構文

  次の EXPLAIN ステートメントを実行して、QO によって推定された実行プランをクエリできます。

  EXPLAIN <sql>;

• 例

  この例では、TPC-H クエリステートメントを使用しています。

  説明

  この例の戻り値は、TPC-H によって公開された結果として使用することはできません。

  EXPLAIN SELECT
          l_returnflag,
          l_linestatus,
          sum(l_quantity) AS sum_qty,
          sum(l_extendedprice) AS sum_base_price,
          sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
          sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
          avg(l_quantity) AS avg_qty,
          avg(l_extendedprice) AS avg_price,
          avg(l_discount) AS avg_disc,
          count(*) AS count_order
  FROM
          lineitem
  WHERE
          l_shipdate <= date '1998-12-01' - interval '120' day
  GROUP BY
          l_returnflag,
          l_linestatus
  ORDER BY
          l_returnflag,
          l_linestatus;

• 返された結果

  QUERY PLAN
  Sort  (cost=0.00..7795.30 rows=3 width=80)
    Sort Key: l_returnflag, l_linestatus
    ->  Gather  (cost=0.00..7795.27 rows=3 width=80)
          ->  Project  (cost=0.00..7795.27 rows=3 width=80)
                ->  Project  (cost=0.00..7794.27 rows=3 width=104)
                      ->  Final HashAggregate  (cost=0.00..7793.27 rows=3 width=76)
                            Group Key: l_returnflag, l_linestatus
                            ->  Redistribution  (cost=0.00..7792.95 rows=1881 width=76)
                                  Hash Key: l_returnflag, l_linestatus
                                  ->  Partial HashAggregate  (cost=0.00..7792.89 rows=1881 width=76)
                                        Group Key: l_returnflag, l_linestatus
                                        ->  Local Gather  (cost=0.00..7791.81 rows=44412 width=76)
                                              ->  Decode  (cost=0.00..7791.80 rows=44412 width=76)
                                                    ->  Partial HashAggregate  (cost=0.00..7791.70 rows=44412 width=76)
                                                          Group Key: l_returnflag, l_linestatus
                                                          ->  Project  (cost=0.00..3550.73 rows=584421302 width=33)
                                                                ->  Project  (cost=0.00..2585.43 rows=584421302 width=33)
                                                                      ->  Index Scan using Clustering_index on lineitem  (cost=0.00..261.36 rows=584421302 width=25)
                                                                            Segment Filter: (l_shipdate <= '1998-08-03 00:00:00+08'::timestamp with time zone)
                                                                            Cluster Filter: (l_shipdate <= '1998-08-03 00:00:00+08'::timestamp with time zone)
  

• 結果の説明

  実行プランは下から上に見ていく必要があります。 各矢印（->）はノードを示します。 各ノードには、演算子や返される行の推定数などの情報が含まれています。 次の表は、演算子のパラメータについて説明しています。

  パラメータ	説明
  cost	演算子の推定実行時間。 親ノードの cost 値には、子ノードの cost 値が含まれます。 cost パラメータには、2 つのピリオド（..）で区切られた推定開始コストと推定合計コストが含まれています。 推定開始コスト: 出力フェーズが開始される前のコスト。 推定合計コスト: 演算子の実行の推定合計コスト。 上記の返された結果では、Final HashAggregate ノードの推定開始コストは 0.00 で、ノードの推定合計コストは 7793.27 です。
  rows	演算子によって返される行の数。これは、テーブル統計に基づいて推定されます。 Index Scan ノードの rows パラメータの値は、デフォルトで 1000 です。 説明 ほとんどの場合、rows=1000 が返された場合、テーブルの統計は無効であり、テーブル統計に基づいて推定は実行されません。 analyze <tablename> ステートメントを実行して、テーブルの統計を更新できます。
  width	演算子によって返される列の推定平均長。 値が大きいほど、列の長さが長いことを示します。 単位: バイト。

EXPLAIN ANALYZE

• 構文

  次の EXPLAIN ANALYZE ステートメントを実行して、SQL ステートメントの実際の実行プランと各演算子の実行時間をクエリできます。 これは、SQL パフォーマンスの問題を診断するのに役立ちます。

  EXPLAIN ANALYZE <sql>;

• 例

  この例では、TPC-H クエリステートメントを使用しています。

  EXPLAIN ANALYZE SELECT
          l_returnflag,
          l_linestatus,
          sum(l_quantity) AS sum_qty,
          sum(l_extendedprice) AS sum_base_price,
          sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
          sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
          avg(l_quantity) AS avg_qty,
          avg(l_extendedprice) AS avg_price,
          avg(l_discount) AS avg_disc,
          count(*) AS count_order
  FROM
          lineitem
  WHERE
          l_shipdate <= date '1998-12-01' - interval '120' day
  GROUP BY
          l_returnflag,
          l_linestatus
  ORDER BY
          l_returnflag,
          l_linestatus;

• 返された結果

  QUERY PLAN
  Sort  (cost=0.00..7795.30 rows=3 width=80)
    Sort Key: l_returnflag, l_linestatus
  [id=21 dop=1 time=2427/2427/2427ms rows=4(4/4/4) mem=3/3/3KB open=2427/2427/2427ms get_next=0/0/0ms]
    ->  Gather  (cost=0.00..7795.27 rows=3 width=80)
        [20:1 id=100003 dop=1 time=2426/2426/2426ms rows=4(4/4/4) mem=1/1/1KB open=0/0/0ms get_next=2426/2426/2426ms]
          ->  Project  (cost=0.00..7795.27 rows=3 width=80)
              [id=19 dop=20 time=2427/2426/2425ms rows=4(1/0/0) mem=87/87/87KB open=2427/2425/2425ms get_next=1/0/0ms]
                ->  Project  (cost=0.00..7794.27 rows=0 width=104)
                      ->  Final HashAggregate  (cost=0.00..7793.27 rows=3 width=76)
                            Group Key: l_returnflag, l_linestatus
                          [id=16 dop=20 time=2427/2425/2424ms rows=4(1/0/0) mem=574/570/569KB open=2427/2425/2424ms get_next=1/0/0ms]
                            ->  Redistribution  (cost=0.00..7792.95 rows=1881 width=76)
                                  Hash Key: l_returnflag, l_linestatus
                                [20:20 id=100002 dop=20 time=2427/2424/2423ms rows=80(20/4/0) mem=3528/1172/584B open=1/0/0ms get_next=2426/2424/2423ms]
                                  ->  Partial HashAggregate  (cost=0.00..7792.89 rows=1881 width=76)
                                        Group Key: l_returnflag, l_linestatus
                                      [id=12 dop=20 time=2428/2357/2256ms rows=80(4/4/4) mem=574/574/574KB open=2428/2357/2256ms get_next=1/0/0ms]
                                        ->  Local Gather  (cost=0.00..7791.81 rows=44412 width=76)
                                            [id=11 dop=20 time=2427/2356/2255ms rows=936(52/46/44) mem=7/6/6KB open=0/0/0ms get_next=2427/2356/2255ms pull_dop=9/9/9]
                                              ->  Decode  (cost=0.00..7791.80 rows=44412 width=76)
                                                  [id=8 dop=234 time=2435/1484/5ms rows=936(4/4/4) mem=0/0/0B open=2435/1484/5ms get_next=4/0/0ms]
                                                    ->  Partial HashAggregate  (cost=0.00..7791.70 rows=44412 width=76)
                                                          Group Key: l_returnflag, l_linestatus
                                                        [id=5 dop=234 time=2435/1484/3ms rows=936(4/4/4) mem=313/312/168KB open=2435/1484/3ms get_next=0/0/0ms]
                                                          ->  Project  (cost=0.00..3550.73 rows=584421302 width=33)
                                                              [id=4 dop=234 time=2145/1281/2ms rows=585075720(4222846/2500323/3500) mem=142/141/69KB open=10/1/0ms get_next=2145/1280/2ms]
                                                                ->  Project  (cost=0.00..2585.43 rows=584421302 width=33)
                                                                    [id=3 dop=234 time=582/322/2ms rows=585075720(4222846/2500323/3500) mem=142/142/69KB open=10/1/0ms get_next=582/320/2ms]
                                                                      ->  Index Scan using Clustering_index on lineitem  (cost=0.00..261.36 rows=584421302 width=25)
                                                                            Segment Filter: (l_shipdate <= '1998-08-03 00:00:00+08'::timestamp with time zone)
                                                                            Cluster Filter: (l_shipdate <= '1998-08-03 00:00:00+08'::timestamp with time zone)
                                                                          [id=2 dop=234 time=259/125/1ms rows=585075720(4222846/2500323/3500) mem=1418/886/81KB open=10/1/0ms get_next=253/124/0ms]
  
  ADVICE: 
  [node id : 1000xxx] distribution key miss match!  table lineitem defined distribution keys : l_orderkey; request distribution columns : l_returnflag, l_linestatus; 
  shuffle data skew in different shards!  max rows is 20, min rows is 0
  
  Query id:[300200511xxxx]
  ======================cost======================
  Total cost:[2505] ms
  Optimizer cost:[47] ms
  Init gangs cost:[4] ms
  Build gang desc table cost:[2] ms
  Start query cost:[18] ms
  - Wait schema cost:[0] ms
  - Lock query cost:[0] ms
  - Create dataset reader cost:[0] ms
  - Create split reader cost:[0] ms
  Get the first block cost:[2434] ms
  Get result cost:[2434] ms
  ====================resource====================
  Memory: 921(244/230/217) MB,  straggler worker id: 72969760xxx
  CPU time: 149772(38159/37443/36736) ms, straggler worker id: 72969760xxx
  Physical read bytes: 3345(839/836/834) MB, straggler worker id: 72969760xxx
  Read bytes: 41787(10451/10446/10444) MB, straggler worker id: 72969760xxx
  DAG instance count: 41(11/10/10), straggler worker id: 72969760xxx
  Fragment instance count: 275(70/68/67), straggler worker id: 72969760xxx

• 結果の説明

  EXPLAIN ANALYZE ステートメントの出力は、SQL ステートメントの実際の実行パスを提供します。 出力はツリー構造で表示され、各フェーズの各演算子の詳細な実行情報が含まれています。 EXPLAIN ANALYZE ステートメントの出力には、クエリ プラン、アドバイス、コスト、リソースの各パートが含まれています。

クエリ プラン

クエリ プラン パートは、各演算子の詳細な実行情報を提供します。 EXPLAIN ステートメントと同様に、クエリ プランも下から上に見ていく必要があります。 各矢印（->）はノードを示します。

1 つの SQL ステートメントに複数の演算子が含まれる場合があります。 演算子の詳細については、「演算子」をご参照ください。

アドバイス

アドバイス パートは、EXPLAIN ANALYZE ステートメントの出力に基づいてシステムによって自動的に生成される最適化の提案を提供します。 次の提案があります。

• Table xxx misses bitmap index: テーブルに分散キー、クラスタリング キー、またはビットマップ インデックスを設定することをお勧めします。

• Table xxx Miss Stats! please run 'analyze xxx';: テーブルの統計情報がありません。

• shuffle data xxx in different shards! max rows is 20, min rows is 0: データ スキューが存在する可能性があります。

コスト

コスト パートは、SQL ステートメントの合計実行時間と各フェーズの実行時間を提供します。 各フェーズの実行時間に基づいて、パフォーマンスのボトルネックを特定できます。

合計コスト: SQL ステートメントの合計実行時間（ミリ秒単位）。 これは次の部分で構成されます。

• Optimizer cost: QO が実行プランを生成するのにかかる時間。 単位: ミリ秒。

• Build gang desc table cost: QO によって生成された実行プランを QE が必要とする形式のデータに変換するのにかかる時間。 単位: ミリ秒。

• Init gangs cost: QO によって生成された実行プランを前処理し、クエリ要求を QE に送信して start query フェーズをトリガーするのにかかる時間。 単位: ミリ秒。

• Start query cost: 初期化フェーズの期間。Init gangs ステップが完了したときに開始し、クエリ操作が開始したときに終了します。 初期化フェーズには、ロックの取得やスキーマ バージョンの調整などの操作が含まれ、次の部分で構成されます。

  • Wait schema cost: ストレージ エンジン（SE）バージョンと FE ノード バージョンをスキーマ バージョンに合わせるのにかかる時間。 テーブル スキーマが変更された場合、FE ノード バージョンと SE バージョンの両方を更新する必要があります。 FE ノード バージョンと SE バージョンがスキーマ バージョンと一致しない場合、スキーマのレイテンシが発生する可能性があります。 パーティション化された親テーブルで多数の DDL ステートメントが実行される場合、SE の処理速度が遅く、レイテンシが高くなります。 その結果、データの書き込みと読み取りが遅くなります。 この場合は、DDL ステートメントを実行する頻度を最適化できます。

  • Lock query cost: クエリがロックを取得するのにかかる時間。 時間が長い場合、クエリはロックを待機しています。

  • Create dataset reader cost: インデックス データ リーダーを作成するのにかかる時間。 時間が長い場合、キャッシュがヒットしない可能性があります。

  • Create split reader cost: ファイルを開くのにかかる時間。 時間が長い場合、ファイルのメタデータがキャッシュにヒットしません。 この場合、I/O オーバーヘッドが高くなります。

• Get result cost: start query フェーズが終了した時刻からすべての結果が返される時刻までの期間。 Get result cost には Get the first block cost が含まれます。 単位: ミリ秒。

  • Get the first block cost: start query フェーズが終了した時刻から最初のレコード バッチが返される時刻までの期間。 場合によっては、Get the first block cost は Get result cost に非常に近いか、または一致します。 たとえば、クエリ プランの最初のステップでハッシュ集計演算子が使用される場合、集計操作のハッシュ テーブルを作成するには、ダウンストリーム演算子の完全なデータが必要です。 フィルタ条件を持つ一般的なクエリの場合、データはリアルタイムで計算および返されます。 このシナリオでは、Get the first block cost は Get result cost と大きく異なります。 この違いはデータ量によって異なります。

リソース

リソース パートは、クエリの実行中に消費されるリソースの量を 消費されたリソースの合計量（消費されたリソースの最大量/消費されたリソースの平均量/消費されたリソースの最小量） の形式で提供します。

Hologres は分散エンジンです。 Hologres インスタンスには複数のワーカー ノードがあります。 ワーカー ノードの計算結果はマージされ、マージ結果はクライアントに返されます。 したがって、リソース消費情報は total(max worker/avg worker/min worker) の形式で表示されます。

• total: SQL クエリ ステートメントによって消費されるリソースの合計量。

• max: ワーカー ノードによって消費されるリソースの最大量。

• avg: ワーカー ノードあたりに消費されるリソースの平均量。これは、次の式を使用して計算されます。消費されたリソースの合計量/ワーカー ノードの数。

• min: ワーカー ノードによって消費されるリソースの最小量。

次の表は、リソース パートのメトリックについて説明しています。

演算子

SCAN

• seq scan

  Seq Scan は、テーブルからデータを順番に読み取るために使用されます。 フル テーブル スキャンが実行されます。 Seq Scan の後の on キーワードの後には、スキャンされたテーブルの名前が続きます。

  例: 次のステートメントを実行して、一般的な内部テーブルに対するクエリのプランを確認します。 クエリステートメントの実行プランで Seq Scan が返されます。

  EXPLAIN SELECT * FROM public.holo_lineitem_100g;

  次の図は、返された結果を示しています。

  

  • パーティション テーブルからのデータのクエリ

    パーティション テーブルからデータをクエリする場合、クエリのプランに Seq Scan on Partitioned Table キーワードが表示されます。 Partitions selected キーワードから、クエリステートメントでスキャンされたパーティションの数を確認できます。

    例: 次のステートメントを実行して、1 つのパーティションがスキャンされたパーティション親テーブルに対するクエリのプランを確認します。

    EXPLAIN SELECT * FROM public.hologres_parent;

    次の図は、返された結果を示しています。

    

  • 外部テーブルを使用したデータのクエリ

    外部テーブルを使用してデータをクエリする場合、クエリのプランに Foreign Table Type キーワードが表示され、外部テーブルのソースが指定されます。 Foreign Table Type の値は、MaxCompute、OSS、または Hologres です。

    例: 次のステートメントを実行して、MaxCompute 外部テーブルを使用したクエリのプランを確認します。

    EXPLAIN SELECT * FROM public.odps_lineitem_100;

    次の図は、返された結果を示しています。

    

• Index Scan および Index Seek

  インデックスを使用して、テーブルに対するクエリを高速化できます。 Hologres は、テーブルのストレージ モードに基づいて、下位層で異なるインデックスを使用します。 列指向ストレージ モードと行指向ストレージ モードに基づいて、インデックスは clustering_index と Index Seek (pk_index とも呼ばれます) に分類されます。

  • Clustering_index: セグメント キーやクラスタリング キーなど、列指向テーブルに適用可能なインデックスのタイプ。 列指向テーブルに対するクエリがインデックスにヒットした場合、このタイプのインデックスが使用されます。 Seq Scan Using Clustering_index は通常、Filter と共に使用されます。 Filter は子ノードであり、ヒットしたインデックスを一覧表示します。 Filter は、クラスタリング フィルタ、セグメント フィルタ、またはビットマップ フィルタです。 詳細については、「列指向ストレージ」をご参照ください。

    • 例 1: クエリがインデックスにヒットします。

      BEGIN;
      CREATE TABLE column_test (
       "id" bigint not null ,
       "name" text not null ,
       "age" bigint not null 
      );
      CALL set_table_property('column_test', 'orientation', 'column');
      CALL set_table_property('column_test', 'distribution_key', 'id');
      CALL set_table_property('column_test', 'clustering_key', 'id');
      COMMIT;
      
      INSERT INTO column_test VALUES(1,'tom',10),(2,'tony',11),(3,'tony',12);
      
      EXPLAIN SELECT * FROM column_test WHERE id>2;

      次の図は、返された結果を示しています。

      

    • 例 2: クエリがインデックスにヒットせず、clustering_index タイプのインデックスは使用されません。

      EXPLAIN SELECT * FROM column_test WHERE age>10;

      次の図は、返された結果を示しています。

      

  • Index Seek (pk_index とも呼ばれます): プライマリ キーなど、行指向テーブルに適用可能なインデックスのタイプ。 ほとんどの場合、プライマリ キーが設定された行指向テーブルに対するポイント クエリには、固定プランが使用されます。 プライマリ キーが設定された行指向テーブルに対するクエリで修正プランが使用されない場合、Index Seek タイプのインデックスが使用されます。 詳細については、「行指向ストレージ」をご参照ください。

    例: 行指向テーブルからデータをクエリします。

    BEGIN;
    CREATE TABLE row_test_1 (
        id bigint not null,
        name text not null,
        class text ,
    PRIMARY KEY (id)
    );
    CALL set_table_property('row_test_1', 'orientation', 'row');
    CALL set_table_property('row_test_1', 'clustering_key', 'name');
    COMMIT;
    INSERT INTO row_test_1 VALUES ('1','qqq','3'),('2','aaa','4'),('3','zzz','5');
    
    BEGIN;
    CREATE TABLE row_test_2 (
        id bigint not null,
        name text not null,
        class text ,
    PRIMARY KEY (id)
    );
    CALL set_table_property('row_test_2', 'orientation', 'row');
    CALL set_table_property('row_test_2', 'clustering_key', 'name');
    COMMIT;
    INSERT INTO row_test_2 VALUES ('1','qqq','3'),('2','aaa','4'),('3','zzz','5');
    
    --pk_index
    EXPLAIN SELECT * FROM (SELECT id FROM row_test_1 WHERE id = 1) t1 JOIN row_test_2 t2 ON t1.id = t2.id;
    

    次の図は、返された結果を示しています。

    

Filter

Filter は、SQL 条件に基づいてデータをフィルタリングするために使用されます。 ほとんどの場合、Filter は Seq Scan の子ノードであり、Seq Scan ノードと共に実行されます。 Filter は、データがフィルタリングされるかどうか、およびフィルタ条件がインデックスにヒットするかどうかを指定します。 このセクションでは、さまざまなタイプの Filter について説明します。

• Filter

  実行プランに Filter キーワードのみが含まれている場合、フィルタ条件はインデックスにヒットしません。 この場合は、テーブル インデックスを確認し、インデックスを再設定して SQL クエリを高速化する必要があります。

  説明

  実行プランに One-Time Filter: false が含まれている場合、出力は空です。

  例:

  BEGIN;
  CREATE TABLE clustering_index_test (
   "id" bigint not null ,
   "name" text not null ,
   "age" bigint not null 
  );
  CALL set_table_property('clustering_index_test', 'orientation', 'column');
  CALL set_table_property('clustering_index_test', 'distribution_key', 'id');
  CALL set_table_property('clustering_index_test', 'clustering_key', 'age');
  COMMIT;
  
  INSERT INTO clustering_index_test VALUES (1,'tom',10),(2,'tony',11),(3,'tony',12);
  
  EXPLAIN SELECT * FROM clustering_index_test WHERE id>2;

  次の図は、返された結果を示しています。

  

• Segment Filter

  Segment Filter は、クエリがセグメント キーにヒットしたことを示します。 Segment Filter は index_scan と共に使用されます。 詳細については、「イベント時間列（セグメント キー）」をご参照ください。

• Cluster Filter

  Cluster Filter は、クエリがクラスタリング キーにヒットしたことを示します。 詳細については、「クラスタリング キー」をご参照ください。

• Bitmap Filter

  Bitmap Filter は、クエリがビットマップ インデックスにヒットしたことを示します。 詳細については、「ビットマップ インデックス」をご参照ください。

• Join Filter

  Join Filter は、結合操作の後にデータをフィルタリングする必要があることを示します。

Decode

Decode は、テキスト データの計算を高速化するために、データのエンコードまたはデコードに使用されます。

Local Gather および Gather

Hologres では、データはシャード内のファイルとして保存されます。 Local Gather は、複数のファイルのデータを 1 つのシャードにマージするために使用されます。 Gather は、複数のシャードのデータを要約して結果を返すために使用されます。

例:

EXPLAIN SELECT * FROM public.lineitem;

次の図は、返された実行プランを示しています。 データはスキャンされ、Local Gather 演算子を使用して 1 つのシャードにマージされた後、Gather 演算子を使用して要約されます。

Redistribution

Redistribution は、データをハッシュ計算するか、1 つ以上のシャードにランダムに分散するために使用されます。

• 再配布演算子は、通常、次のシナリオで使用されます。

  • JOIN、COUNT DISTINCT (JOIN メソッド)、および GROUP BY 句: テーブルに分散キーが設定されていない場合、または分散キーの設定が無効な場合、クエリ中に複数のシャード間でデータがシャッフルされます。 複数のテーブルを結合するときに再配布が発生した場合、ローカル結合機能は使用されません。 その結果、クエリのパフォーマンスが低下します。

  • 式に含まれる結合キーや group by キーなどのキー: CAST 関数を使用してデータ型を変換する場合、ローカル結合機能は使用されません。 この場合、再配布が発生します。

• 例:

  • 例 1: 2 つのテーブルを結合します。 分散キーが無効であり、再配布が発生します。

    BEGIN;
    CREATE TABLE tbl1(
    a int not null,
    b text not null
    );
    CALL set_table_property('tbl1', 'distribution_key', 'a');
    CREATE TABLE tbl2(
    c int not null,
    d text not null
    );
    CALL set_table_property('tbl2', 'distribution_key', 'd');
    COMMIT;
    
    EXPLAIN SELECT * FROM tbl1  JOIN tbl2 ON tbl1.a=tbl2.c;

    次の図は、返された実行プランを示しています。 再配布演算子が含まれているため、分散キーが無効であることが示されます。 SQL ステートメントでは、tbl1.a=tbl2.c がテーブルを結合するように設定されています。 ただし、tbl1 テーブルの分散キーは a で、tbl2 テーブルの分散キーは d です。 その結果、結合操作中にデータがシャッフルされます。

    

    最適化の提案: SQL ステートメントに再配布演算子が含まれている場合は、分散キーの設定を確認することをお勧めします。 再配布のシナリオと分散キーの設定の詳細については、「分散キー」をご参照ください。

  • 例 2: 結合キーが式に含まれています。 データ型が変更されます。 その結果、ローカル結合機能は使用されず、再配布が発生します。

    最適化の提案: 式を使用しないでください。

Join

実行プランの Join 演算子の定義は、標準データベースの定義と同じです。 結合操作は、ハッシュ結合、ネステッド ループ結合、マージ結合に分類できます。

• Hash Join

  ハッシュ結合は、複数のテーブルを結合する方法です。 ハッシュ結合中、結合対象のテーブル（通常は小さなテーブル）に基づいてメモリ内にハッシュ テーブルが構築されます。 結合対象の列のデータはハッシュ計算され、ハッシュ テーブルに挿入されます。 他の結合対象のテーブルのデータは行ごとに読み取られ、ハッシュ計算され、ハッシュ テーブルのデータと照合されます。 一致したデータが返されます。 次の表は、ハッシュ結合のカテゴリについて説明しています。

  カテゴリ	説明
  Hash Left Join	複数のテーブルが結合されると、結合条件を満たす左テーブルのすべての行が返され、右テーブルと照合されます。 一致するデータがない場合は、null が返されます。
  Hash Right Join	複数のテーブルが結合されると、右テーブルのすべての行と、結合条件を満たす左テーブルの行が返されます。 右テーブルの行が左テーブルのデータと一致しない場合、左テーブルには null が返されます。
  Hash Inner Join	複数のテーブルが結合されると、結合条件を満たす行のみが返されます。
  Hash Full Join	複数のテーブルが結合されると、左テーブルと右テーブルのすべての行が返されます。 一方のテーブルのデータがもう一方のテーブルのデータと一致しない場合、データが一致しないテーブルには null が返されます。
  Hash Anti Join	一致しないデータのみが返されます。 このタイプの結合は、主に NOT EXISTS 句を含むクエリに使用されます。
  Hash Semi Join	データ レコードが一致すると、行が返されます。 返される行には、重複データは含まれません。 このタイプの結合は、通常、EXISTS 句を含むクエリに使用されます。

  ハッシュ結合操作の実行プランを表示する場合は、子ノードにも焦点を当てる必要があります。

  • hash cond: 結合条件。 例: hash cond(tmp.a=tmp1.b)。

  • hash key: 複数のシャードでハッシュ計算に使用されるキー。 ほとんどの場合、キーは GROUP BY のキーを示します。

  ハッシュ結合中、少量のデータを含むテーブルがハッシュ テーブルの作成に使用されているかどうかを確認する必要があります。 次の方法のいずれかを使用して、小さなテーブルがハッシュ テーブルの作成に使用されているかどうかを確認できます。

  • 実行プランでは、hash キーワードを含むテーブルがハッシュ テーブルの作成に使用されるテーブルです。

  • 実行プランでは、一番下のテーブルがハッシュ テーブルの作成に使用されるテーブルです。

  最適化の提案:

  • 統計の更新

    中心となる考え方は、小さなテーブルを使用してハッシュ テーブルを作成することです。 メモリ内に大きなテーブルを使用してハッシュ テーブルを作成すると、より多くのリソースが消費されます。 ほとんどの場合、この問題は、テーブルの統計が更新されず、QO が大きなテーブルを使用してハッシュ テーブルを作成するために発生します。

    この例では、hash_join_test_2 (tbl2 とも呼ばれます) テーブルには 1,000,000 行のデータが含まれ、hash_join_test_1 (tbl1 とも呼ばれます) テーブルには 10,000 行のデータが含まれています。 ただし、テーブル統計は更新されず、tbl2 テーブルに 1,000 行のデータが含まれていることが示されます。 その結果、tbl2 テーブルは小さなテーブルと見なされ、ハッシュ テーブルの作成に使用されます。 クエリの効率が低くなります。

    BEGIN ;
    CREATE TABLE public.hash_join_test_1 (
        a integer not null,
        b text not null
    );
    CALL set_table_property('public.hash_join_test_1', 'distribution_key', 'a');
    CREATE TABLE public.hash_join_test_2 (
        c integer not null,
        d text not null
    );
    CALL set_table_property('public.hash_join_test_2', 'distribution_key', 'c');
    COMMIT ;
    
    INSERT INTO hash_join_test_1 SELECT i, i+1 FROM generate_series(1, 10000) AS s(i);
    INSERT INTO hash_join_test_2 SELECT i, i::text FROM generate_series(10, 1000000) AS s(i);
    
    EXPLAIN SELECT * FROM hash_join_test_1 tbl1  JOIN hash_join_test_2 tbl2 ON tbl1.a=tbl2.c;

    次の図は、実行プランを示しています。 大きなテーブル hash_join_test_2 がハッシュ テーブルの作成に使用されています。

    テーブル統計が更新されていない場合は、analyze <tablename> ステートメントを手動で実行して統計を更新できます。 ステートメントの例:

    ANALYZE hash_join_test_1;
    ANALYZE hash_join_test_2;

    次の図は、テーブル統計が更新された後の実行プランを示しています。 小さなテーブル hash_join_test_1 がハッシュ テーブルの作成に使用され、QO によって推定された行の数は正しいです。

  • 結合順序の調整

    ほとんどの場合、テーブル統計を更新することで結合の問題を解決できます。 ただし、デフォルトのメカニズムに基づいて、SQL ステートメントが複雑で 5 つ以上のテーブルが結合される場合、Hologres QO は SQL ステートメントに基づいて最適な実行プランを選択します。選択プロセスには時間がかかります。 次のステートメントを実行して Grand Unified Configuration (GUC) パラメータを設定し、結合順序を制御して QO の選択プロセスを高速化できます。

    SET optimizer_join_order = '<value>'; 

    次の表は、GUC パラメータの有効な値について説明しています。

    有効な値	説明
    exhaustive (デフォルト値)	結合順序はアルゴリズムを使用して決定され、最適な実行プランが生成されます。 これにより、複数テーブル結合時の QO オーバーヘッドが増加する可能性があります。
    query	実行プランは SQL ステートメントに基づいて生成されます。 QO は変更を加えません。 この値は、結合対象の複数のテーブルに含まれるデータの行数が数億行以下の場合にのみ適用可能であり、QO オーバーヘッドを削減するのに役立ちます。 この GUC パラメータはデータベース レベルでは設定しないことをお勧めします。 そうしないと、他の結合操作のパフォーマンスが低下します。
    greedy	結合順序は欲張りアルゴリズムを使用して生成されます。 このモードでは、QO オーバーヘッドは中程度です。

• ネステッド ループ結合と Materialize

  複数のテーブルのネステッド ループ結合では、一方のテーブルから外部テーブルにデータが読み取られます。 外部テーブルの各データ レコードは、内部テーブルにトラバースされます。 次に、内部テーブルと外部テーブルがネステッド ループで結合されます。 このプロセスは、デカルト積を計算するプロセスと同じです。 実行プランでは、最初の内部テーブルには通常 Materialize 演算子があります。

  最適化の提案:

  • ネステッド ループの原則は、内部テーブルが外部テーブルによって駆動されることです。 外部テーブルによって返される各行は、内部テーブルの行と一致する必要があります。 したがって、返される結果セットは大きすぎてはいけません。 そうしないと、大量のリソースが消費されます。 小さな結果を返すテーブルを外部テーブルとして使用することをお勧めします。

  • 非等価結合は、通常、ネステッド ループ結合を生成します。 SQL ステートメントで非等価結合を回避することをお勧めします。

  • 次のコードは、ネステッド ループ結合の例を示しています。

    BEGIN;
    CREATE TABLE public.nestedloop_test_1 (
        a integer not null,
        b integer not null
    );
    CALL set_table_property('public.nestedloop_test_1', 'distribution_key', 'a');
    CREATE TABLE public.nestedloop_test_2 (
        c integer not null,
        d text not null
    );
    CALL set_table_property('public.nestedloop_test_2', 'distribution_key', 'c');
    COMMIT;
    
    INSERT INTO nestedloop_test_1 SELECT i, i+1 FROM generate_series(1, 10000) AS s(i);
    INSERT INTO nestedloop_test_2 SELECT i, i::text FROM generate_series(10, 1000000) AS s(i);
    
    EXPLAIN SELECT * FROM nestedloop_test_1 tbl1,nestedloop_test_2 tbl2 WHERE tbl1.a>tbl2.c;

    次の図は、実行プランを示しています。 Materialize 演算子と Nested Loop 演算子が表示されます。 これは、SQL ステートメントがネステッド ループ結合を使用していることを示しています。

• クロス結合

  Hologres V3.0 以降では、クロス結合は、小さなテーブルを含む非等価結合などのシナリオで、ネステッド ループ結合の最適化された実装として使用されます。 ネステッド ループ結合では、外部ループからデータの行が抽出され、内部ループのすべてのデータがトラバースされた後、内部ループのサブクエリ ステータスがリセットされます。 クロス結合では、小さなテーブルのすべてのデータがメモリにロードされ、データは大きなテーブルからストリーミング モードで読み取られたデータと結合されます。 これにより、計算パフォーマンスが大幅に向上します。 ただし、クロス結合はネステッド ループ結合よりも多くのメモリ リソースを消費します。

  次の図は、実行プランを示しています。 Cross Join 演算子が表示されます。 これは、SQL ステートメントがクロス結合を使用していることを示しています。

  

  クロス結合を無効にする場合は、次の SQL ステートメントを実行して無効にします。

  -- セッション レベルでクロス結合を無効にします。
  SET hg_experimental_enable_cross_join_rewrite = off;
  
  -- データベース レベルでクロス結合を無効にします。 この設定は、新しい接続に対して有効になります。
  ALTER database <データベース名> hg_experimental_enable_cross_join_rewrite = off;

Broadcast

Broadcast は、各シャードにデータを配布するために使用されます。 ほとんどの場合、ブロードキャスト結合は、小さなテーブルと大きなテーブルを結合するために使用されます。 SQL ステートメントが配信されると、QO は再配布とブロードキャストのコストを比較し、アルゴリズムに基づいて実行プランを生成します。

最適化の提案:

• クエリ対象のテーブルが小さく、インスタンスに含まれるシャードの数が少ない場合（5 シャードなど）、ブロードキャストをお勧めします。

  この例では、2 つのテーブルが結合されます。 broadcast_test_ 1 テーブルのデータ量と broadcast_test_ 2 テーブルのデータ量は大きく異なります。

  BEGIN;
  CREATE TABLE broadcast_test_1 (
      f1 int, 
      f2 int);
  CALL set_table_property('broadcast_test_1','distribution_key','f2');
  CREATE TABLE broadcast_test_2 (
      f1 int,
      f2 int);
  COMMIT;
  
  INSERT INTO broadcast_test_1 SELECT i AS f1, i AS f2 FROM generate_series(1, 30)i;
  INSERT INTO broadcast_test_2 SELECT i AS f1, i AS f2 FROM generate_series(1, 30000)i;
  
  ANALYZE broadcast_test_1;
  ANALYZE broadcast_test_2;
  
  EXPLAIN SELECT * FROM broadcast_test_1 t1, broadcast_test_2 t2 WHERE t1.f1=t2.f1;

  次の図は、返された結果を示しています。

  

• 結合対象のテーブルが小さなテーブルではないが、broadcast 演算子が使用されている場合、テーブル統計が期待どおりに更新されない可能性があります。 たとえば、統計ではテーブルに 1,000 行のデータが含まれていることが示されていますが、実際にはテーブルには 1,000,000 行のデータが含まれています。 この場合は、analyze <tablename> ステートメントを実行して、テーブル統計を更新します。

Shard prune および Shards selected

• Shard prune

  シャードを取得するために使用されるメソッド。 有効な値:

  • lazaily: システムは、ノードのシャード ID に基づいてシャードにラベルを付け、後続の計算操作で特定のシャードを使用します。

  • eagerly: システムはヒットしたシャードを選択します。

  QO は、実行プランに基づいてメソッドを自動的に選択します。

• Shards selected

  選択されたシャードの数。 1 out of 20 という値は、20 個のシャードから 1 つのシャードが選択されたことを示します。

ExecuteExternalSQL

Hologres アーキテクチャ で説明されているように、Hologres のクエリ エンジンは、Hologres Query Engine (HQE)、PostgreSQL Query Engine (PQE)、および Seahawks Query Engine (SQE) に分類されます。 PQE はネイティブ PostgreSQL エンジンです。 HQE でサポートされていない一部の演算子と関数は、PQE によって実行されます。 PQE は HQE よりも効率が劣ります。 プランに ExecuteExternalSQL 演算子が含まれている場合、PQE が使用されます。

• 例 1: SQL ステートメントは PQE を使用します。

  CREATE TABLE pqe_test(a text);
  INSERT INTO pqe_test VALUES ('2023-01-28 16:25:19.082698+08');
  EXPLAIN SELECT a::timestamp FROM pqe_test;

  次の実行プランでは、ExecuteExternalSQL が表示され、PQE が ::timestamp 演算子の処理に使用されます。

  

• 例 2: ::timestamp 演算子が to_timestamp に変更されます。 HQE が使用されます。

  EXPLAIN SELECT to_timestamp(a,'YYYY-MM-DD HH24:MI:SS') FROM pqe_test;

  次の実行プランでは、ExecuteExternalSQL は表示されず、PQE は使用されません。

最適化の提案: プランを使用して、PQE によって処理される関数または演算子を見つけ、SQL ステートメントを書き直して HQE が関数または演算子を処理できるようにします。 これにより、クエリの効率が向上します。 演算子の書き直しの詳細については、「クエリ パフォーマンスの最適化」をご参照ください。

EXPLAIN SELECT l_shipdate FROM public.lineitem ORDER BY l_shipdate;

EXPLAIN SELECT * FROM public.lineitem limit 1;

参考資料

HoloWeb では、プランを視覚的に表示できます。 詳細については、「実行プランの表示」をご参照ください。