Hologres:クエリ パフォーマンスの最適化

統計情報が正しいかどうかの確認

実行計画を表示できます:

統計情報が時間内に同期されない場合、最適ではない実行計画が生成される可能性があります。例:

tmp1 テーブルには 1,000 万行があり、tmp テーブルには 1,000 行があります。Hologres では、統計情報のデフォルトの行数は 1,000 です。explain SQL 文を実行すると、結果は tmp1 テーブルの行数が実際の行数と一致しないことを示す場合があります。これは、統計情報が更新されていないことを示します。

Seq Scan on tmp1 (cost=0.00..5.01 rows=1000 width=1)

統計情報の更新

analyze tmp;
analyze tmp1;

explain の結果が rows=1000 を返す場合、統計情報が欠落していることを意味します。パフォーマンスの低下は、多くの場合、オプティマイザーに統計情報がないことが原因です。これを修正するには、analyze <tablename> を実行して統計情報を更新します。これは、クエリパフォーマンスを最適化するための簡単で高速な方法です。

統計情報を更新する推奨シナリオ

次の状況では、analyze <tablename> コマンドを実行する必要があります。

• データをインポートした後。

• 多数の INSERT、UPDATE、または DELETE 操作を実行した後。

• 内部テーブルと外部テーブルの両方に対して ANALYZE を実行する必要がある場合。

• パーティションテーブルの親テーブルに対して ANALYZE を実行する必要がある場合。

• 次の問題が発生した場合は、インポートタスクを実行する前に analyze <tablename> を実行して効率を最適化します。

  • 複数テーブルの結合中に OOM エラーが発生する。

    エラーメッセージは通常 Query executor exceeded total memory limitation xxxxx: yyyy bytes used です。

  • インポート効率が低い。

    Hologres でデータをクエリまたはインポートすると、効率が低く、タスクの完了に時間がかかります。

統計情報の更新

前述のように、結合内のテーブルの統計情報が時間内に更新されない場合、大きい方のテーブルがハッシュテーブルの作成に使用される可能性があります。これにより、結合効率が低下します。テーブルの統計情報を更新して、SQL パフォーマンスを向上させることができます。

analyze <tablename>;

適切な分散キーの選択

分散キーは、複数のシャードにデータをパーティション分割するために使用されます。均等なパーティション分割により、データスキューが防止されます。同じ分散キーを持つ複数の関連テーブルを設計すると、ローカル結合を高速化できます。テーブルを作成するときは、次の原則に基づいて適切な分散キーを選択します。

• 分散キーを設定するための提案

  • 結合クエリから結合条件列を分散キーとして選択します。

  • `GROUP BY` 句で頻繁に使用される列を分散キーとして選択します。

  • データが均等かつ離散的に分散されている列を分散キーとして選択します。

  • 分散キーの原則と使用法の詳細については、「分散キー」をご参照ください。

• 分散キーの設定例

  たとえば、`tmp` テーブルと `tmp1` テーブルを結合する場合、`explain` SQL 文の実行計画に `Redistribution Motion` が表示されることがあります。これは、データが再配布されており、ローカル結合がないことを意味し、クエリ効率が低くなります。複数テーブル結合中のデータ再配布の余分なオーバーヘッドを回避するには、テーブルを再作成し、結合キーを分散キーとして設定する必要があります。 次の DDL 文は、2 つのテーブルを再作成するための例です。

  begin;
  create table tmp(a int, b int, c int);
  call set_table_property('tmp', 'distribution_key', 'a');
  commit;
  
  begin;
  create table tmp1(a int, b int, c int);
  call set_table_property('tmp1', 'distribution_key', 'b');
  commit;
  
  -- Set the distribution key to the join key.
  select count(1) from tmp join tmp1 on tmp.a = tmp1.b ;

  テーブルの分散キーをリセットして `explain` SQL 文を再度実行すると、赤いボックス内の Motion オペレーターが実行計画から削除されます。データは、同じハッシュキーを使用してシャード間に分散されます。データ分布が同じであるため、Motion オペレーターは不要になります。これは、データが再配布されないことを示し、冗長なネットワークオーバーヘッドを回避します。

Runtime Filter の使用

V2.0 以降、Hologres は Runtime Filter をサポートしています。この機能は通常、複数テーブルの結合、特に大きなテーブルと小さなテーブルを結合するシナリオで使用されます。手動で設定する必要はありません。オプティマイザーと実行エンジンは、クエリ時に結合プロセス中のフィルタリング動作を自動的に最適化します。これにより、スキャンされるデータ量が削減され、I/O オーバーヘッドが低下し、結合クエリのパフォーマンスが向上します。詳細については、「Runtime Filter」をご参照ください。

Join Order アルゴリズムの最適化

• SQL 結合関係が複雑な場合や、多くのテーブルが結合される場合、クエリ オプティマイザー (QO) は最適な結合順序の選択に多くの時間を費やします。特定のシナリオでは、結合順序ポリシーを調整することで、クエリの最適化に費やす時間を短縮できます。次の構文を使用して、オプティマイザーの結合順序アルゴリズムを設定できます。

  set optimizer_join_order = '<value>'; 

• パラメーターの説明

  パラメーター	説明
  value	オプティマイザーの結合順序アルゴリズム。オプションは次のとおりです。 exhaustive2 (V2.2 以降のデフォルト): アップグレードされ最適化された動的計画法アルゴリズム。 exhaustive (以前のバージョンのデフォルト): 結合順序変換に動的計画法アルゴリズムを使用します。最適な実行計画を生成しますが、オプティマイザーのオーバーヘッドが最も高くなります。 query: 結合順序変換を実行しません。SQL 文に記述された順序で結合を実行し、オプティマイザーのオーバーヘッドが最も低くなります。 greedy: 貪欲法アルゴリズムを使用して結合順序を探索します。オプティマイザーのオーバーヘッドは中程度です。

• 追加情報

  デフォルトの exhaustive2 アルゴリズムは、最適な実行計画をグローバルに検索します。ただし、10 を超えるような多数のテーブルとの結合では、最適化時間が長くなる可能性があります。`query` または `greedy` アルゴリズムを使用すると、オプティマイザーの時間を短縮できますが、最適な実行計画が生成されない場合があります。

Broadcast などの Motion オペレーターの最適化

Hologres には 4 種類の Motion ノードが含まれています。次の表に示すように、各タイプはデータ再配布シナリオに対応しています。

`explain` SQL 文の結果に基づいて、次の点を考慮してください:

• Motion オペレーターに時間がかかる場合は、分散キーを再設計する必要があります。

• 不正確な統計情報が原因で Gather Motion または Broadcast Motion が生成される場合は、analyze <tablename> コマンドを使用して、より効率的な Redistribute Motion 分散メソッドに変更できます。

• Broadcast Motion は、シャード数が少なく、ブロードキャストテーブルが小さいシナリオでのみ有利です。したがって、小さなテーブルのブロードキャストシナリオでは、テーブルのシャード数を減らす必要があります。クエリ効率を向上させるために、Shard Count をワーカー数に比例させるようにしてください。Shard Count の詳細については、「Shard Count」をご参照ください。

Fixed Plan の使用

Fixed Plan は、高スループットのシナリオに適しています。簡略化された実行パスを使用して、大幅に高いパフォーマンスとスループットを実現します。設定方法と使用方法については、「Fixed Plan を使用した SQL 実行の高速化」をご参照ください。

PQE オペレーターの再書き込み

Hologres には、複数の基盤となる実行エンジンがあります。ネイティブの Hologres Query Engine (HQE) (ベクトルエンジン) と、Postgres Query Engine (PQE) (分散 Postgres エンジン) です。SQL 文に HQE がサポートしていないオペレーターが含まれている場合、システムはそのオペレーターを PQE に送信して実行します。この場合、クエリパフォーマンスは完全には最適化されないため、クエリ文を修正する必要があります。

explain SQL クエリを使用して実行計画を確認すると、計画に External SQL (Postgres) が含まれている場合、SQL 文の一部が PQE で実行されていることを意味します。

explain select * from tmp where a not in (select a from tmp1);

explain select * from tmp where not exists (select a from tmp1 where a = tmp.a);

select * from tmp where not exists (select a from tmp1 where a = tmp.a);

select name,unnest(string_to_array(age,',')) from demo;

select cast(substring(c1, 13, 2) as int) AS hour from t2;

select extract(hour from to_timestamp(c1, 'YYYYMMDD HH24:MI:SS')) from t2;

select regexp_replace(c1::text,'-','0') from t2;

select replace(c1::text,'-','') from t2;

select date_trunc('day',to_timestamp(c1, 'YYYYMMDD HH24:MI:SS')  at time zone 'utc') from t2

select date_trunc('day',to_timestamp(c1, 'YYYYMMDD HH24:MI:SS') ) from t2;

select cast(c1 as timestamp) from t2;

select to_timestamp(c1, 'yyyyMMdd hh24:mi:ss') from t2;

select c1::text from t2;

select to_char(c1, 'yyyyMMdd hh24:mi:ss') from t2;

あいまい検索の回避

`LIKE` 操作などのあいまい検索では、インデックスは使用されません。

結果キャッシュのクエリへの影響

デフォルトでは、Hologres は同一のクエリまたはサブクエリの結果をキャッシュします。同じクエリを繰り返し実行すると、キャッシュにヒットします。次のコマンドを使用してキャッシュを無効にし、パフォーマンステストへの影響を回避できます:

set hg_experimental_enable_result_cache = off;

OOM の最適化方法

OOM エラーは通常、インスタンスの計算メモリが不足している場合に発生します。以下は一般的なエラーメッセージです。OOM エラーは、複雑な計算や高い同時実行性など、さまざまな要因によって引き起こされる可能性があります。特定の原因に基づいて対象を絞った最適化を実行して、OOM エラーを減らすことができます。詳細については、「OOM 問題のトラブルシューティングガイド」をご参照ください。

Total memory used by all existing queries exceeded memory limitation. 
memory usage for existing queries=(2031xxxx,184yy)(2021yyyy,85yy)(1021121xxxx,6yy)(2021xxx,18yy)(202xxxx,14yy); Used/Limit: xy1/xy2 quota/sum_quota: zz/100

Order By Limit シナリオの最適化

Hologres V1.3 より前のバージョンでは、`Order By Limit` シナリオで Merge Sort オペレーターはサポートされていませんでした。実行計画が生成されると、最終出力で余分なソートが実行され、パフォーマンスが低下していました。V1.3 以降、エンジンは `Order By Limit` シナリオを最適化して Merge Sort オペレーターをサポートするようになりました。これにより、多方向マージソートが可能になり、余分なソートが不要になり、クエリパフォーマンスが向上します。

Count Distinct の最適化

• APPROX_COUNT_DISTINCT への書き換え

  Count Distinct は正確な重複排除方法です。同じキーを持つレコードを同じノードにシャッフルして重複排除する必要があるため、リソースを大量に消費します。Hologres は `APPROX_COUNT_DISTINCT` 拡張関数を提供しており、HyperLogLog カーディナリティ推定を使用して不正確な `COUNT DISTINCT` 計算を行い、クエリパフォーマンスを向上させます。平均エラー率は 0.1% から 1% の範囲で制御できます。必要に応じてクエリを書き換えることができます。詳細については、「APPROX_COUNT_DISTINCT」をご参照ください。

• UNIQ 関数を使用します。

  V1.3 以降、Hologres は正確な重複排除のための `UNIQ` 関数をサポートしています。`GROUP BY` キーのカーディナリティが高い場合、`UNIQ` は `Count Distinct` よりもパフォーマンスが良く、メモリを節約できます。`Count Distinct` で OOM エラーが発生した場合は、`UNIQ` に置き換えることができます。詳細については、「UNIQ」をご参照ください。

• 適切な分散キーの設定

  同じキーを持つ複数の `Count Distinct` 操作があり、データが離散的かつ均等に分散されている場合は、`Count Distinct` キーを分散キーとして設定できます。これにより、同じデータが同じシャードに分散されるため、データシャッフルが回避されます。

• Count Distinct の最適化

  V2.1 以降、Hologres には `Count Distinct` シナリオに対する多くのパフォーマンス最適化が含まれています。これらの最適化は、単一の `Count Distinct`、複数の `Count Distinct`、データスキュー、および `Group By` フィールドのない SQL 文をカバーしています。より良いパフォーマンスを得るために、手動で文を `UNIQ` に書き換える必要はありません。`Count Distinct` のパフォーマンスを向上させるには、Hologres インスタンスを V2.1 以降のバージョンにアップグレードしてください。

Group By の最適化

`Group By` キーにより、計算中にグループ化列のキーに基づいてデータが再配布されます。`Group By` 操作に時間がかかる場合は、`Group By` 列を分散キーとして設定できます。

-- If data is distributed based on the values in column a, runtime data redistribution is reduced, and the parallel computing capability of shards is fully utilized.
select a, count(1) from t1 group by a; 

V4.0 以降、Hologres は、複数の関連列でグループ化する際のマージを削減するためのマージ最適化を提供します。たとえば、 GROUP BY COL_A, ((COL_A + 1)), ((COL_A + 2)) のような句は、同等の GROUP BY COL_A に書き換えられます。探索オーバーヘッドのため、検索深度は最大 5 層に制限されます。例:

CREATE TABLE tbl (
    a int,
    b int,
    c int
);

-- Query
SELECT
    a,
    a + 1 as a1,
    a + 2 as a2,
    sum(b)
FROM tbl
GROUP BY
    a,
    a1,
    a2;

実行計画は次の図に示されています。クエリの書き換えが実行され、GROUP BY 句には列 A のみが含まれていることがわかります。

次の Grand Unified Configuration (GUC) パラメーターを使用して、この機能を無効にできます:

-- Disable the feature at the session level.
SET hg_experimental_remove_related_group_by_key = off; 

-- Disable the feature at the database level.
ALTER DATABASE <database_name> SET hg_experimental_remove_related_group_by_key = off; 

データスキューの処理

-- hg_shard_id is a built-in hidden column in each table that describes the shard where the corresponding row of data is located.
select hg_shard_id, count(1) from t1 group by hg_shard_id;

With 式の最適化 (ベータ)

Hologres は PostgreSQL と互換性があり、共通テーブル式 (CTE) をサポートしています。CTE は、`WITH` を使用した再帰クエリで一般的に使用されます。実装は PostgreSQL と同様にインライン化に基づいているため、CTE を複数回参照すると計算が繰り返されます。Hologres V1.3 では、次の GUC パラメーターを使用して CTE 再利用を有効にできます。これにより、CTE を一度計算して複数回参照できるようになり、計算リソースを節約し、クエリパフォーマンスを向上させることができます。Hologres インスタンスが V1.3 より前のバージョンの場合は、インスタンスをアップグレードしてください。

set optimizer_cte_inlining=off;

• 例

  create table cte_reuse_test_t
  (
      a integer not null,
      b text,
      primary key (a)
  );
  
  insert into cte_reuse_test_t values(1, 'a'),(2, 'b'), (3, 'c'), (4, 'b'), (5, 'c'), (6, ''), (7, null);
  
  
  set optimizer_cte_inlining=off;
  
  explain with c as (select b, max(a) as a from cte_reuse_test_t group by b)
  select a1.a,a2.a,a1.b, a2.b
  from c a1, c a2
  where a1.b = a2.b
  order by a1.b
  limit 100;
                                      

• 実行計画の比較

  • Hologres V1.3 (V1.1) より前のバージョンの実行計画

  • Hologres V1.3 の実行計画

  実行計画の比較から、Hologres V1.3 より前のバージョンでは複数の AGG 計算 (HashAggregate) が実行されることがわかります。対照的に、Hologres V1.3 では結果が一度だけ計算されて再利用されるため、パフォーマンスが向上します。

単一ステージ Agg から複数ステージ Agg への最適化

Agg オペレーターに時間がかかる場合は、ローカルのシャードレベルの事前集計が実行されているかどうかを確認します。

set optimizer_force_multistage_agg = on;

同じ名前の複数の集計関数の最適化

本番シナリオでは、SQL 文に同じ列を集計する複数の同一の集計関数が含まれる場合があります。V4.0 以降、Hologres は計算回数を減らしてクエリパフォーマンスを向上させるための同等の書き換えをサポートしています。以下は使用例です:

• データの準備

  -- Create a test table.
  CREATE TABLE tbl(x int4, y int4);
  
  -- Insert test data.
  INSERT INTO tbl VALUES (1,2), (null,200), (1000,null), (10000,20000);

• サンプルクエリ

  SELECT
      sum(x + 1),
      sum(x + 2),
      sum(x - 3),
      sum(x - 4)
  FROM
      tbl;

• 次の図は実行計画を示しています。クエリが書き換えられて計算が最適化されていることがわかります。

  

• 次の GUC パラメーターを使用して、この機能を無効にできます:

  -- Disable the feature at the session level.
  SET hg_experimental_remove_related_group_by_key = off; 
  
  -- Disable the feature at the database level.
  ALTER DATABASE <database_name> SET hg_experimental_remove_related_group_by_key = off; 

ストレージタイプの選択

Hologres は、行指向、列指向、および行列ハイブリッドストレージモードをサポートしています。次の表で説明するように、ビジネスシナリオに適したストレージタイプを選択できます。

データ型の選択

Hologres は複数のデータ型をサポートしています。次の原則に基づいて、ビジネスシナリオと要件に適したデータ型を選択する必要があります:

• 必要なストレージスペースが少ない型を使用します。

  • BIGINT 型の代わりに INT 型を使用します。

  • 正確な DECIMAL/NUMERIC 型を使用します。精度 (PRECISION, SCALE) を指定し、できるだけ小さく保ちます。統計的要約でのエラーを避けるために、FLOAT や DOUBLE PRECISION などの不正確な型の使用を減らします。

  • `GROUP BY` 列に FLOAT や DOUBLE などの不正確な型を使用しないでください。

  • より汎用性の高い TEXT を使用します。VARCHAR(N) と CHAR(N) を使用する場合は、N の値をできるだけ小さく保ちます。

  • 日付型には TIMESTAMPTZ または DATE を使用します。TEXT の使用は避けてください。

• 結合条件には一貫したデータ型を使用します。

  複数テーブルの結合を実行する場合、結合列に同じデータ型を使用します。これにより、Hologres が異なる型の列に対して暗黙の型変換を実行することによって生じる余分なオーバーヘッドが回避されます。

• UNION や GROUP BY などの操作で FLOAT/DOUBLE などの不正確な型を使用しないでください。

  `UNION` や `GROUP BY` などの操作は、現在 DOUBLE PRECISION および FLOAT データ型をサポートしていません。DECIMAL 型を使用する必要があります。

プライマリキーの選択

プライマリキーを適切に設定すると、group by pk,a,b,c を含むクエリなど、特定のシナリオでオプティマイザーがより良い実行計画を生成するのに役立ちます。

ただし、列ストアのシナリオでは、プライマリキーを設定するとデータ書き込みパフォーマンスに大きな影響を与える可能性があります。通常、プライマリキーなしの書き込みパフォーマンスは、プライマリキーありの場合の 3 倍です。

パーティションテーブルの選択

インデックスの選択

call set_table_property('tmp', 'clustering_key', 'a,b,c');
select * from tmp where a > 1 ;  --Can use the cluster index.
select * from tmp where a > 1 and c > 2 ;   --Only 'a' can use the cluster index.
select * from tmp where a > 1 and b > 2 ;  --Both 'a' and 'b' can use the cluster index.
select * from tmp where a > 1 and b > 2 and c > 3 ; --'a', 'b', and 'c' can all use the cluster index.
select * from tmp where b > 1 and c > 2 ;   --Neither 'b' nor 'c' can use the cluster index.

call set_table_property('tmp', 'bitmap_columns', 'a,b,c');
select * from tmp where a = 1 and b = 2 ;  --The bitmap index can be used.
select * from tmp where a = 1 or b = 2 ; --The bitmap index can be used.

インデックスが使用されているかどうかの確認

次の文を使用して tmp テーブルを作成し、インデックスフィールドを指定できます。

begin;
create table tmp(a int not null, b int not null, c int not null);
call set_table_property('tmp', 'clustering_key', 'a');
call set_table_property('tmp', 'segment_key', 'b');
call set_table_property('tmp', 'bitmap_columns', 'a,b,c');
commit;

• クラスターインデックスが使用されているかどうかを確認するには、次の文を実行します。

  explain select * from tmp where a > 1;

  

• ビットマップインデックスが使用されているかどうかを確認するには、次の文を実行します。

  explain select * from tmp where c = 1;

  

• セグメントキーが使用されているかどうかを確認するには、次の文を実行します。

  explain select * from tmp where b > 1;