Hologres:クラスタリングキー

クラスタリングキーの概要

-- Hologres V2.1 以降でサポートされる構文
CREATE TABLE <table_name> (...) WITH (clustering_key = '[<columnName>[,...]]');

-- すべてのバージョンでサポートされる構文
BEGIN;
CREATE TABLE <table_name> (...);
CALL set_table_property('<table_name>', 'clustering_key', '[<columnName>{:asc} [,...]]');
COMMIT;

使用上の推奨事項

• クラスタリングキーは、ポイントクエリと範囲クエリに最適です。where a = 1 や where a > 1 and a < 5 のようなフィルター操作のパフォーマンスを大幅に向上させます。クラスタリングキーとビットマップ列の両方を設定することで、最適なポイントクエリのパフォーマンスを実現できます。

• クラスタリングキーは、左端プレフィックス一致の原則に従います。このため、適用可能なシナリオを制限しないように、クラスタリングキーには 2 つを超えるフィールドを設定しないでください。クラスタリングキーはソートに使用されるため、キー内の列の順序が重要です。先にリストされた列ほど、ソートの優先順位が高くなります。

• クラスタリングキーのフィールドを指定する際、フィールド名の後に :asc を追加してインデックス構築のソート順を設定できます。デフォルトのソート順は昇順を指定する asc です。Hologres V2.1 より前のバージョンでは、インデックス構築のソート順を降順 (desc) に設定することはできません。ソート順を降順に設定すると、クラスタリングキーがヒットせず、クエリのパフォーマンスが低下します。Hologres V2.1 以降では、次の Grand Unified Configuration (GUC) パラメーターを有効にすることで、クラスタリングキーを desc に設定できます。ただし、この機能は Text、Char、Varchar、Bytea、Int などの型のフィールドでのみサポートされています。他のデータ型のフィールドは、現在クラスタリングキーを desc に設定することをサポートしていません。

  set hg_experimental_optimizer_enable_variable_length_desc_ck_filter = on;

• 行指向テーブルの場合、クラスタリングキーはデフォルトでプライマリキーになります。Hologres V0.9 より前のバージョンでは、デフォルトは設定されていませんでした。プライマリキーとは異なるクラスタリングキーを設定すると、Hologres はテーブルに対して 2 つのソート順 (1 つはプライマリキー用、もう 1 つはクラスタリングキー用) を作成します。これにより、データ冗長性が発生します。

制限事項

• クラスタリングキーを変更するには、テーブルを再作成してデータをインポートする必要があります。

• クラスタリングキーは、`NOT NULL` の列または `NOT NULL` の列の組み合わせである必要があります。Hologres V1.3.20 から V1.3.27 までのバージョンでは、Null 値許容のクラスタリングキーがサポートされていました。V1.3.28 以降では、データの正確性に影響を与える可能性があるため、Null 値許容のクラスタリングキーはサポートされていません。ビジネス要件で Null 値許容のクラスタリングキーが必要な場合は、SQL 文の前に次のパラメーターを追加できます。

  set hg_experimental_enable_nullable_clustering_key = true;

• 次のデータ型はクラスタリングキーではサポートされていません：Float、Float4、Float8、Double、Decimal(Numeric)、Json、Jsonb、Bit、Varbit、Money、Time With Time Zone、およびその他の複雑なデータ型。

• Hologres V2.1 より前のバージョンでは、インデックス構築のソート順を降順 (desc) に設定することはサポートされていません。ソート順を降順に設定すると、クラスタリングキーがヒットせず、クエリのパフォーマンスが低下します。V2.1 以降では、次の GUC パラメーターを有効にすることで、クラスタリングキーを desc に設定できます。ただし、この機能は Text、Char、Varchar、Bytea、Int などのデータ型のフィールドでのみサポートされています。他のデータ型のフィールドは、現在クラスタリングキーを desc に設定することをサポートしていません。

  set hg_experimental_optimizer_enable_variable_length_desc_ck_filter = on;

• 列指向テーブルの場合、クラスタリングキーはデフォルトで空です。ビジネスシナリオに基づいて明示的に指定する必要があります。

• Hologres では、各テーブルに設定できるクラスタリングキーは 1 つだけです。つまり、テーブルを作成する際に call コマンドは 1 回しか使用できません。コマンドを複数回実行することはできません。次の例をご参照ください：

  • V2.1 以降でサポートされる構文：

    -- 正しい例
    CREATE TABLE tbl (
        a int NOT NULL,
        b text NOT NULL
    )
    WITH (
        clustering_key = 'a,b'
    );
    
    -- 誤った例
    CREATE TABLE tbl (
        a int NOT NULL,
        b text NOT NULL
    )
    WITH (
        clustering_key = 'a',
        clustering_key = 'b'
    );

  • すべてのバージョンでサポートされる構文：

    -- 正しい例
    BEGIN;
    CREATE TABLE tbl (a int NOT NULL, b text NOT NULL);
    CALL set_table_property('tbl', 'clustering_key', 'a,b');
    COMMIT;
    
    -- 誤った例
    BEGIN;
    CREATE TABLE tbl (a int NOT NULL, b text NOT NULL);
    CALL set_table_property('tbl', 'clustering_key', 'a');
    CALL set_table_property('tbl', 'clustering_key', 'b');
    
    COMMIT;

技術的原則

物理ストレージの観点から、クラスタリングキーはファイル内のデータをソートします。デフォルトの順序は昇順 (asc) です。以下のセクションでは、クラスタリングキーのレイアウトの概念を説明します。

• 論理レイアウト

  クラスタリングキーのクエリは、左端プレフィックス一致の原則に従います。クエリがプレフィックスに一致しない場合、クラスタリングキーを使用して高速化することはできません。次のシナリオは、Hologres におけるクラスタリングキーの論理レイアウトを示しています。

  Name、Date、Class のフィールドを持つテーブルを考えます。

  • Date をクラスタリングキーとして設定した場合、テーブル内のデータは日付順にソートされます。

  • Class と Date をクラスタリングキーとして設定した場合、データはまずクラスでソートされ、次に日付でソートされます。

  クラスタリングキーとして異なるフィールドを設定すると、次の図に示すように、データレイアウトが異なります。

• 物理ストレージレイアウト

  クラスタリングキーの物理ストレージレイアウトを次の図に示します。

使用例

高度なチューニング方法

MySQL や SQL Server などの従来のデータベースのクラスター化インデックスとは異なり、Hologres はテーブル全体ではなく、ファイル内でのみデータをソートします。したがって、クラスタリングキーに対して order by 操作を実行するには、依然としてある程度のオーバーヘッドがあります。

Hologres V1.3 以降には、クラスタリングキーのシナリオでより良いパフォーマンスを提供するための多くのパフォーマンス最適化が含まれています。主な最適化は、次の 2 つのシナリオに対するものです。1.3 より前のバージョンをご利用の場合は、フィードバックを送信できます。詳細については、「一般的なアップグレード準備の失敗」をご参照ください。また、Hologres DingTalk グループに参加してフィードバックを送信することもできます。詳細については、「オンラインサポートをさらに受けるにはどうすればよいですか？」をご参照ください。

• クラスタリングキーに対する Order By シナリオ

  Hologres では、ファイル内のデータはクラスタリングキーの定義に従ってソートされます。V1.3 より前では、オプティマイザーはこのソート順を使用して最適な実行計画を生成できませんでした。さらに、Shuffle ノードを通過するデータはソートされたままであることが保証されていませんでした (多重マージ)。これにより、計算量が増加し、クエリ時間が長くなることがよくありました。Hologres V1.3 はこのプロセスを最適化します。生成された実行計画がクラスタリングキーのソート順を使用し、Shuffle ノード間でその順序を維持できるようにすることで、クエリのパフォーマンスを向上させます。次の点にご注意ください：

  • テーブルがクラスタリングキーでフィルターされていない場合、システムはデフォルトで SeqScan を実行し、IndexScan は実行しません。IndexScan のみがクラスタリングキーのソート済みプロパティを使用します。

  • オプティマイザーは、常にクラスタリングキーのソート順に基づいて実行計画を生成するわけではありません。これは、ソート順を使用することにはある程度のオーバーヘッドがあるためです。データはファイル内でソートされますが、メモリ内で追加のソートが必要になります。

  以下に例を示します。

  • テーブルの DDL は次のとおりです。

    V2.1 以降でサポートされる構文：

    DROP TABLE IF EXISTS test_use_sort_info_of_clustering_keys;
    
    CREATE TABLE test_use_sort_info_of_clustering_keys (
        a int NOT NULL,
        b int NOT NULL,
        c text
    )
    WITH (
        distribution_key = 'a',
        clustering_key = 'a,b'
    );
    
    INSERT INTO test_use_sort_info_of_clustering_keys SELECT i%500, i%100, i::text FROM generate_series(1, 1000) as s(i);
    
    ANALYZE test_use_sort_info_of_clustering_keys;

    すべてのバージョンでサポートされる構文：

    DROP TABLE if exists test_use_sort_info_of_clustering_keys;
    BEGIN;
    CREATE TABLE test_use_sort_info_of_clustering_keys
    (
              a int NOT NULL,
              b int NOT NULL,
              c text
    );
    CALL set_table_property('test_use_sort_info_of_clustering_keys', 'distribution_key', 'a');
    CALL set_table_property('test_use_sort_info_of_clustering_keys', 'clustering_key', 'a,b');
    COMMIT;
    
    INSERT INTO test_use_sort_info_of_clustering_keys SELECT i%500, i%100, i::text FROM generate_series(1, 1000) as s(i);
    
    ANALYZE test_use_sort_info_of_clustering_keys;

  • クエリ文。

    explain select * from test_use_sort_info_of_clustering_keys where a > 100  order by a, b;

  • 実行計画の比較

    • V1.3 より前のバージョン (V1.1) の実行計画は次のとおりです。explain SQL 文を実行できます。

       Sort  (cost=0.00..0.00 rows=797 width=11)
         ->  Gather  (cost=0.00..2.48 rows=797 width=11)
               Sort Key: a, b
               ->  Sort  (cost=0.00..2.44 rows=797 width=11)
                     Sort Key: a, b
                     ->  Exchange (Gather Exchange)  (cost=0.00..1.11 rows=797 width=11)
                           ->  Decode  (cost=0.00..1.11 rows=797 width=11)
                                 ->  Index Scan using holo_index:[1] on test_use_sort_info_of_clustering_keys  (cost=0.00..1.00 rows=797 width=11)
                                       Cluster Filter: (a > 100)

    • V1.3 の実行計画は次のとおりです。

       Gather  (cost=0.00..1.15 rows=797 width=11)
         Merge Key: a, b
         ->  Exchange (Gather Exchange)  (cost=0.00..1.11 rows=797 width=11)
               Merge Key: a, b
               ->  Decode  (cost=0.00..1.11 rows=797 width=11)
                     ->  Index Scan using holo_index:[1] on test_use_sort_info_of_clustering_keys  (cost=0.00..1.01 rows=797 width=11)
                           Order by: a, b
                           Cluster Filter: (a > 100)

    以前のバージョンの計画と比較して、V1.3 の実行計画は、テーブルのクラスタリングキーのソート順を使用して、直接マージ出力を実行します。これにより、実行全体をパイプライン化でき、大量のデータを扱う際の遅いソートを防ぐことができます。実行計画の比較から、V1.3 は Hashagg よりも複雑さが低く、パフォーマンスが優れた Groupagg を生成することがわかります。

• クラスタリングキーでの結合シナリオ (ベータ版)

  Hologres V1.3 では SortMergeJoin 結合タイプが導入されました。この結合タイプは、実行計画がクラスタリングキーのソート順を使用して計算量を削減し、パフォーマンスを向上させることを保証します。次の点にご注意ください：

  • この機能はベータ版であり、デフォルトでは無効になっています。有効にするには、クエリの前に次のパラメーターを追加します。

    -- マージ結合を有効にする
    set hg_experimental_enable_sort_merge_join=on;

  • テーブルがクラスタリングキーでフィルターされていない場合、システムはデフォルトで SeqScan を実行し、IndexScan は実行しません。IndexScan のみがクラスタリングキーのソート済みプロパティを使用します。

  • オプティマイザーは、常にクラスタリングキーのソート順に基づいて実行計画を生成するわけではありません。これは、ソート順を使用することにはある程度のオーバーヘッドがあるためです。データはファイル内でソートされますが、メモリ内で追加のソートが必要になります。

  以下に例を示します。

  • テーブルの DDL は次のとおりです。

    V2.1 以降でサポートされる構文：

    DROP TABLE IF EXISTS test_use_sort_info_of_clustering_keys1;
    CREATE TABLE test_use_sort_info_of_clustering_keys1 (
        a int,
        b int,
        c text
    )
    WITH (
        distribution_key = 'a',
        clustering_key = 'a,b'
    );
    
    INSERT INTO test_use_sort_info_of_clustering_keys1 SELECT i % 500, i % 100, i::text FROM generate_series(1, 10000) AS s(i);
    ANALYZE test_use_sort_info_of_clustering_keys1;
    
    DROP TABLE IF EXISTS test_use_sort_info_of_clustering_keys2;
    CREATE TABLE test_use_sort_info_of_clustering_keys2 (
        a int,
        b int,
        c text
    )
    WITH (
        distribution_key = 'a',
        clustering_key = 'a,b'
    );
    
    INSERT INTO test_use_sort_info_of_clustering_keys2 SELECT i % 600, i % 200, i::text FROM generate_series(1, 10000) AS s(i);
    ANALYZE test_use_sort_info_of_clustering_keys2;

    すべてのバージョンでサポートされる構文：

    drop table if exists test_use_sort_info_of_clustering_keys1;
    begin;
    create table test_use_sort_info_of_clustering_keys1
    (
      a int,
      b int,
      c text
    );
    call set_table_property('test_use_sort_info_of_clustering_keys1', 'distribution_key', 'a');
    call set_table_property('test_use_sort_info_of_clustering_keys1', 'clustering_key', 'a,b');
    commit;
    insert into test_use_sort_info_of_clustering_keys1 select i%500, i%100, i::text from generate_series(1, 10000) as s(i);
    analyze test_use_sort_info_of_clustering_keys1;
    
    drop table if exists test_use_sort_info_of_clustering_keys2;
    begin;
    create table test_use_sort_info_of_clustering_keys2
    (
      a int,
      b int,
      c text
    );
    call set_table_property('test_use_sort_info_of_clustering_keys2', 'distribution_key', 'a');
    call set_table_property('test_use_sort_info_of_clustering_keys2', 'clustering_key', 'a,b');
    commit;
    insert into test_use_sort_info_of_clustering_keys2 select i%600, i%200, i::text from generate_series(1, 10000) as s(i);
    analyze test_use_sort_info_of_clustering_keys2;
                                    

  • クエリ文は次のとおりです。

    explain select * from test_use_sort_info_of_clustering_keys1 a join test_use_sort_info_of_clustering_keys2 b on a.a = b.a and a.b=b.b where a.a > 100 and b.a < 300;

  • 実行計画の比較

    • V1.3 より前のバージョン (V1.1) の実行計画は次のとおりです。

       Gather  (cost=0.00..3.09 rows=4762 width=24)
         ->  Hash Join  (cost=0.00..2.67 rows=4762 width=24)
               Hash Cond: ((test_use_sort_info_of_clustering_keys1.a = test_use_sort_info_of_clustering_keys2.a) AND (test_use_sort_info_of_clustering_keys1.b = test_use_sort_info_of_clustering_keys2.b))
               ->  Exchange (Gather Exchange)  (cost=0.00..1.14 rows=3993 width=12)
                     ->  Decode  (cost=0.00..1.14 rows=3993 width=12)
                           ->  Index Scan using holo_index:[1] on test_use_sort_info_of_clustering_keys1  (cost=0.00..1.01 rows=3993 width=12)
                                 Cluster Filter: ((a > 100) AND (a < 300))
               ->  Hash  (cost=1.13..1.13 rows=3386 width=12)
                     ->  Exchange (Gather Exchange)  (cost=0.00..1.13 rows=3386 width=12)
                           ->  Decode  (cost=0.00..1.13 rows=3386 width=12)
                                 ->  Index Scan using holo_index:[1] on test_use_sort_info_of_clustering_keys2  (cost=0.00..1.01 rows=3386 width=12)
                                       Cluster Filter: ((a > 100) AND (a < 300))

    • V1.3 の実行計画は次のとおりです。

        Gather  (cost=0.00..2.88 rows=4762 width=24)
         ->  Merge Join  (cost=0.00..2.46 rows=4762 width=24)
               Merge Cond: ((test_use_sort_info_of_clustering_keys2.a = test_use_sort_info_of_clustering_keys1.a) AND (test_use_sort_info_of_clustering_keys2.b = test_use_sort_info_of_clustering_keys1.b))
               ->  Exchange (Gather Exchange)  (cost=0.00..1.14 rows=3386 width=12)
                     Merge Key: test_use_sort_info_of_clustering_keys2.a, test_use_sort_info_of_clustering_keys2.b
                     ->  Decode  (cost=0.00..1.14 rows=3386 width=12)
                           ->  Index Scan using holo_index:[1] on test_use_sort_info_of_clustering_keys2  (cost=0.00..1.01 rows=3386 width=12)
                                 Order by: test_use_sort_info_of_clustering_keys2.a, test_use_sort_info_of_clustering_keys2.b
                                 Cluster Filter: ((a > 100) AND (a < 300))
               ->  Exchange (Gather Exchange)  (cost=0.00..1.14 rows=3993 width=12)
                     Merge Key: test_use_sort_info_of_clustering_keys1.a, test_use_sort_info_of_clustering_keys1.b
                     ->  Decode  (cost=0.00..1.14 rows=3993 width=12)
                           ->  Index Scan using holo_index:[1] on test_use_sort_info_of_clustering_keys1  (cost=0.00..1.01 rows=3993 width=12)
                                 Order by: test_use_sort_info_of_clustering_keys1.a, test_use_sort_info_of_clustering_keys1.b
                                 Cluster Filter: ((a > 100) AND (a < 300))

    以前のバージョンの計画と比較して、V1.3 の実行計画はクラスタリングキーのソート順を使用します。各シャード内でマージソートを実行し、その後直接 SortMergeJoin を実行します。これにより、実行全体をパイプライン化できます。また、ハッシュ側が大きすぎてメモリに収まらない場合に HashJoin で発生する可能性のある OOM (メモリ不足) の問題を回避できます。

参考

Hologres 内部テーブルのデータ定義言語 (DDL) 文の詳細については、次のトピックをご参照ください：

• CREATE TABLE

• CREATE TABLE AS

• CREATE TABLE LIKE

• ALTER TABLE

• DROP TABLE