Hologres:クラスタリングキー

概要

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

-- すべての Hologres バージョンでサポートされている構文
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 以降では、グローバルユーザー構成 (GUC) パラメーター hg_experimental_optimizer_enable_variable_length_desc_ck_filter を on に設定した後、クラスタリングキーのソート方法を 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 制約を満たす必要があります。Hologres V1.3.20 から V1.3.27 では、NULLABLE 型の列をクラスタリングキーとして指定できます。Hologres V1.3.28 以降では、NULLABLE 型の列をクラスタリングキーとして指定することはできません。これは、NULLABLE 型の列をクラスタリングキーとして指定すると、データの正確性に影響を与える可能性があるためです。ビジネス要件を満たすために NULLABLE 型の列をクラスタリングキーとして指定する必要がある場合は、クラスタリングキーを定義する 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) に設定することはできません。ソート方法が降順に設定されている場合、クラスタリングキーがヒットせず、クエリのパフォーマンスが低下します。Hologres V2.1 以降では、GUC パラメーター hg_experimental_optimizer_enable_variable_length_desc_ck_filter を on に設定した後、クラスタリングキーのソート方法を desc に設定できます。ただし、このソート方法は、TEXT、CHAR、VARCHAR、BYTEA、および INT データ型の列にのみ適用できます。他のデータ型の列では、クラスタリングキーのソート方法を desc に設定することはできません。

  set hg_experimental_optimizer_enable_variable_length_desc_ck_filter = on;

• デフォルトでは、列指向テーブルにはクラスタリングキーは指定されていません。この場合、ビジネスシナリオに基づいて明示的にクラスタリングキーを指定できます。

• Hologres では、各テーブルにクラスタリングキーは 1 つしか設定できません。テーブルのクラスタリングキーを構成する列を指定するには、テーブルの作成時に CALL ステートメントを 1 回だけ実行します。

  • Hologres 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'
    );

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

    -- 有効なステートメントの使用例
    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;

仕組みに関する説明

クラスタリングキーは、物理ストレージ内のファイルのデータをソートするために使用されます。デフォルトでは、データは昇順にソートされます。次の図は、クエリ結果の論理レイアウトと、クラスタリングキーに基づく物理ストレージのレイアウトを理解するのに役立ちます。

• クラスタリングキーに基づくクエリ結果の論理レイアウト

  クラスタリングキーベースのクエリは、左一致の原則に従います。クエリ対象のフィールドがクラスタリングキーフィールドと一致しない場合、クラスタリングキーを使用してクエリを高速化することはできません。次の例は、Hologres におけるクラスタリングキーに基づくクエリ結果の論理レイアウトを示しています。

  この例では、テーブルが準備されています。テーブルには、名前、日付、クラスの 3 つの列が含まれています。

  • 日付 列をクラスタリングキーとして指定すると、テーブル内のデータは日付列の値に基づいてソートされます。

  • クラス 列と 日付 列をクラスタリングキーを構成するように指定すると、テーブル内のデータは最初にクラス列の値に基づいてソートされ、次に日付列の値に基づいてソートされます。

  異なる列をクラスタリングキーを構成するように指定すると、ソート結果は異なります。次の図は結果を示しています。

• クラスタリングキーに基づく物理ストレージのレイアウト

  次の図は、クラスタリングキーに基づく物理ストレージのレイアウトを示しています。

例

高度な最適化手法

Hologres のクラスタリングキーは、MySQL データベースまたは SQL Server データベースのクラスター化インデックスとは異なります。クラスター化インデックスは、テーブル全体のデータをソートするために使用されます。Hologres のクラスタリングキーは、特定のファイル内のデータのみをソートするために使用されます。したがって、クラスタリングキーに基づいてデータに対して ORDER BY 操作を実行するには、追加のソートが必要です。

Hologres V1.3 以降では、次のシナリオでクラスタリングキーを使用する際のパフォーマンスが最適化されています。Hologres インスタンスのバージョンが V1.3 より前の場合、Hologres コンソールで Hologres インスタンスを手動でアップグレードするか、Hologres DingTalk グループに参加して Hologres テクニカルサポートに連絡してください。Hologres コンソールで Hologres インスタンスを手動でアップグレードする方法の詳細については、「手動アップグレード」をご参照ください。テクニカルサポートを受ける方法の詳細については、「Hologres のオンラインサポートを受ける」をご参照ください。

• クラスタリングキーに基づく ORDER BY 操作の実行

  Hologres では、ファイル内のデータは、ファイルに指定されたクラスタリングキーに基づいてソートされます。Hologres V1.3 より前のバージョンでは、オプティマイザーは、クラスタリングキー列の順序付けられたデータに基づいて最適な実行プランを生成できません。さらに、データがマージされないため、シャッフルプロセス中にデータの順序が乱れる可能性があります。これにより、計算するデータ量が大きくなり、時間がかかります。Hologres V1.3 は、この問題を解決するために最適化されています。この最適化により、クラスタリングキー列の順序付けられたデータに基づいて実行プランを生成でき、シャッフルプロセス中にデータの順序が保持されます。これにより、クエリのパフォーマンスが向上します。ただし、次の点に注意してください。

  • デフォルトでは、テーブルのクラスタリングキーを構成するように指定された列がフィルタリングされていない場合、IndexScan メソッドではなく SeqScan メソッドが使用されます。IndexScan メソッドは、クラスタリングキーが使用されている場合にのみ使用されます。

  • オプティマイザーは、必ずしもクラスタリングキー列の順序付けられたデータに基づいて実行プランを生成するわけではありません。クラスタリングキーは、単一ファイル内のデータの順序を保証します。ただし、Hologres インスタンスのメモリ内でデータを再度ソートする必要があります。

  例

  • テーブルの DDL ステートメント

    Hologres 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;

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

    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;

  • 実行プランの比較

    • 次のサンプルコードは、Hologres V1.3 より前のバージョンのクエリの実行プランを示しています。EXPLAIN ステートメントを実行して、実行プランを表示できます。この例では、Hologres V1.1 が使用されています。

       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)

    • 次のサンプルコードは、Hologres 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)

    Hologres V1.3 より前のバージョンで生成された実行プランと比較して、Hologres V1.3 で生成された実行プランは、クラスタリングキー列の順序付けられたデータを利用してマージと出力を行います。これにより、パイプライン実行が容易になり、大量のデータが関係するシナリオでのソートプロセスが高速化されます。Hologres V1.3 は、実行中に Groupagg 演算子を生成します。Hologres V1.3 より前のバージョンで生成された Hashagg 演算子と比較して、Groupagg 演算子はデータをより簡単に処理でき、パフォーマンスが向上します。

• クラスタリングキーに基づく JOIN 操作の実行 (ベータ)

  Hologres V1.3 は、ソートマージ結合操作をサポートしており、クラスタリングキー列の順序付けられたデータに基づいて実行プランが生成されます。これにより、計算するデータ量が削減され、パフォーマンスが向上します。ただし、次の点に注意してください。

  • この機能はパブリックプレビュー段階であり、デフォルトでは無効になっています。クラスタリングキーに基づいてソートマージ結合操作を実行するには、データをクエリする前に次のステートメントを実行して、この機能を有効にします。

    -- ソートマージ結合を有効にします。
    set hg_experimental_enable_sort_merge_join=on;

  • デフォルトでは、テーブルのクラスタリングキーを構成するように指定された列がフィルタリングされていない場合、IndexScan メソッドではなく SeqScan メソッドが使用されます。IndexScan メソッドは、クラスタリングキーが使用されている場合にのみ使用されます。

  • オプティマイザーは、必ずしもクラスタリングキー列の順序付けられたデータに基づいて実行プランを生成するわけではありません。クラスタリングキーは、単一ファイル内のデータの順序を保証します。ただし、Hologres インスタンスのメモリ内でデータを再度ソートする必要があります。

  例

  • テーブルの DDL ステートメント

    Hologres 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;

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

    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;

  • 実行プランの比較

    • 次のサンプルコードは、Hologres V1.3 より前のバージョンのクエリの実行プランを示しています。この例では、Hologres 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))

    • 次のサンプルコードは、Hologres 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))

    Hologres V1.3 より前のバージョンで生成された実行プランと比較して、Hologres V1.3 で生成された実行プランは、クラスタリングキー列の順序付けられたデータを利用し、シャード内でデータをマージおよびソートしてから、ソートマージ結合操作を実行します。これにより、パイプライン実行が容易になります。また、大量のデータが関係するシナリオで、ハッシュ結合操作中にハッシュ側をインスタンスメモリに書き込むプロセスによって発生する可能性のあるメモリ不足 (OOM) エラーも回避されます。

関連情報

Hologres 内部テーブルの DDL ステートメントの詳細については、以下のトピックをご参照ください。

• CREATE TABLE

• CREATE TABLE AS

• CREATE TABLE LIKE

• ALTER TABLE

• DROP TABLE