Hologres:Best practices for internal table performance tuning

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

実行計画を表示します。

古い統計情報は、不適切な実行計画につながる可能性があります。例：

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 エラーを生成します。

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

    クエリまたはインポートタスクが遅く、完了に時間がかかります。

統計情報の更新

前述のように、結合内のテーブルの統計情報が古い場合、オプティマイザーはより大きいテーブルを使用してハッシュテーブルを構築する可能性があり、結合効率が低下します。したがって、SQL パフォーマンスを向上させるためにテーブルの統計情報を更新する必要があります。

analyze <tablename>;

適切な分散キーの選択

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

• 分散キーの推奨事項

  • 結合条件で使用される列を分散キーとして選択します。

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

  • 均一で離散的なデータ分布を持つ列を分散キーとして選択します。

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

• 分散キー設定の例

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

  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」をご参照ください。

結合順序アルゴリズムの最適化

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

  set optimizer_join_order = '<value>'; 

• パラメーターの説明

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

• 追加情報

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

ブロードキャストなどのモーションオペレーターの最適化

Hologres には 4 種類のモーションノードがあり、次の表に示すように、4 つのデータ再分散シナリオに対応しています。

EXPLAIN SQL ステートメントの結果に基づいて、次の点を考慮してください。

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

• 統計情報が正しくないために Gather Motion または Broadcast Motion が生成される場合は、ANALYZE <tablename> コマンドを実行します。これにより、オプティマイザーがより効率的な Redistribute Motion を選択するのに役立ちます。

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

Fixed Plan の使用

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

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

Hologres には、ネイティブの Hologres Query Engine (HQE)、ベクトルエンジン、分散 Postgres エンジンである Postgres Query Engine (PQE) など、複数の基盤となる実行エンジンがあります。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` シナリオでマージソートオペレーターがサポートされていませんでした。実行計画には最終出力に余分なソートが含まれており、パフォーマンスが低下していました。V1.3 以降、エンジンはマージソートオペレーターをサポートすることでこれらのシナリオを最適化します。これにより、多方向マージソートが可能になり、余分なソートが不要になり、クエリパフォーマンスが向上します。

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、データスキュー、GROUP BY フィールドのない SQL など、COUNT DISTINCT シナリオに対する多くのパフォーマンス最適化が含まれています。より良いパフォーマンスを実現するために、`COUNT DISTINCT` を `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 以降、マージ最適化が利用可能です。この最適化は、複数の同等の列で `GROUP BY` を実行するときに、グループ化列の数を減らします。たとえば、 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 のみが含まれていることを確認できます。

次の 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 と同様に、Hologres はインライン化を使用して CTE を実装しており、CTE が複数回参照されると繰り返し計算が発生します。Hologres V1.3 以降では、GUC パラメーターを使用して CTE 再利用を有効にできます。これにより、CTE が 1 回だけ計算され、複数回参照されるため、計算リソースが節約され、クエリパフォーマンスが向上します。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 の実行計画は次のとおりです。

  実行計画の比較は、V1.3 以前のバージョンでは複数の `AGG` 計算 (HashAggregate) が実行されることを示しています。Hologres V1.3 では、結果は 1 回だけ計算され、その後再利用されるため、パフォーマンスが向上します。

単一ステージ 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;

• 実行計画は次の図に示されています。クエリの再書き込みが実行され、データは列 `a` のみでグループ化されていることがわかります。

  

• 次の 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; 

Top-N 分析シナリオにおけるソートウィンドウ関数の最適化

• OLAP シナリオでは、グループ内の上位 N レコードを取得することが一般的な要件です。たとえば、次の SQL クエリは、t テーブルから各 b パーティション内の上位 2 レコードを a でソートして取得します。

  CREATE TABLE t (
    a int,
    b int
  );
  
  INSERT INTO t VALUES (2, 1), (3, 1), (4, 1), (5, 2), (6, 2);
  
  SELECT
      *
  FROM (
    SELECT
    a,
    b,
    row_number() OVER (PARTITION BY b ORDER BY a) AS rn
    FROM
    t) t1
  WHERE
      rn <= 2;

  実行結果は次のとおりです。

  a	b	rn
  5	2	1
  6	2	2
  2	1	1
  3	1	2

• このシナリオでは、Hologres V4.1 以降、Partition Sort オペレーターがサポートされています。このオペレーターはストリームソートをサポートし、LIMIT 句を Partition にプッシュダウンします。これにより、ソート中にデータを事前にフィルタリングし、Top-N シナリオでの row_number や rank などのソートウィンドウ関数のメモリ消費量を削減し、クエリ OOM エラーの発生確率を低減します。この機能はデフォルトで有効になっています。次の GUC パラメーターを使用して無効にできます。

  -- Disable the feature at the session level.
  SET hg_experimental_enable_hash_partitioned_sort_v2 = off; 
  
  -- Disable the feature at the database level.
  ALTER DATABASE <database_name> SET hg_experimental_enable_hash_partitioned_sort_v2 = 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` の使用は避けてください。

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

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

• `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 ;  --The cluster index can be used.
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;