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) に設定することはサポートされていません。これらのバージョンでは、ソート順を降順に設定するとクラスタリングキーがヒットせず、クエリのパフォーマンスが低下していました。 V2.1 からは、特定の GUC パラメーターを有効にすることで、クラスタリングキーを desc に設定できます。ただし、この機能がサポートされるのは、Text、Char、Varchar、Bytea、Int などのデータ型のフィールドのみです。他のデータ型のフィールドでは、クラスタリングキーを desc に設定することはまだサポートされていません。

  set hg_experimental_optimizer_enable_variable_length_desc_ck_filter = on;

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

制限事項

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

• クラスタリングキーで指定された列は 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 回のみ実行できます。例：

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

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

    -- 正しい使用法
    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) に設定すると、テーブル内のデータは Date 列でソートされます。

  • クラスタリングキーを (Class, Date) に設定すると、データはまず Class 列でソートされ、次に各クラス内で Date 列でソートされます。

  クラスタリングキー列の選択によって、データの最終的なソート済みレイアウトが決まります。

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

  クラスタリングキーの物理ストレージレイアウトは、関連データをまとめてグループ化します。

例

高度なチューニング

MySQL や SQL Server などの従来のデータベースのクラスタリングキーとは異なり、Hologres のソートはテーブル全体ではなく、ファイル内でのみ実行されます。したがって、クラスタリングキーに対して ORDER BY 操作を実行すると、依然としてパフォーマンスコストが発生します。

V1.3 以降、Hologres はクラスタリングキーのユースケースに対して大幅なパフォーマンス最適化を導入しました。これらの最適化により、次の 2 つのシナリオでパフォーマンスが向上します。お使いの Hologres インスタンスが 1.3 より前のバージョンの場合は、「トラブルシューティング：アップグレード準備エラー」をご参照いただくか、「オンラインサポートの利用方法」の指示に従ってサポートにお問い合わせください。

• クラスタリングキーでのORDER BYの使用

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

  • クエリがテーブルのクラスタリングキーでフィルタリングしない場合、デフォルトでインデックススキャンではなくシーケンシャルスキャンになります。インデックススキャンのみがクラスタリングキーのソート済みプロパティを使用します。

  • このアプローチにはオーバーヘッドがあるため、オプティマイザは常にクラスタリングキーのソート順に依存する実行計画を選択するとは限りません。たとえば、ファイル内でソートされたデータでも、メモリ内で追加のソートが必要になる場合があります。

  例：

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

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

    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.1 などの V1.3 より前のバージョンでの実行計画を示しています。EXPLAIN ステートメントで計画を表示できます。

       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)

    V1.3 の実行計画はより効率的です。クラスタリングキーのソート済みプロパティを使用して出力を直接マージします。これにより、パイプライン実行が実現され、大規模なデータセットを扱う際の遅いソート操作を防ぎます。比較からわかるように、V1.3 の計画では Sort 演算子が Merge Key に置き換えられており、より効率的なマージベースのプロセスであることがわかります。

• クラスタリングキーでのJOINの使用 (ベータ版)

  Hologres V1.3 では マージ結合 演算子が導入されました。これにより、オプティマイザはクラスタリングキーのソート特性を活用し、計算負荷を軽減し、パフォーマンスを向上させることができます。ただし、次の点にご注意ください：

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

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

  • クエリがテーブルのクラスタリングキーでフィルタリングしない場合、デフォルトでインデックススキャンではなくシーケンシャルスキャンになります。インデックススキャンのみがクラスタリングキーのソート済みプロパティを使用します。

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

  例：

  • 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 NOT NULL,
        b int NOT NULL,
        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 NOT NULL,
        b int NOT NULL,
        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 NOT NULL,
      b int NOT NULL,
      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 NOT NULL,
      b int NOT NULL,
      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.1 などの V1.3 より前のバージョンでの実行計画を示しています。

       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))

    V1.3 の計画は、クラスタリングキーのソート特性を活用して マージ結合 を使用します。結合前に各シャード内でソートマージを実行し、効率的なパイプラインを構築します。このアプローチにより、結合対象の片方のテーブルがメモリに収まらないほど大きい場合に ハッシュ結合 で発生する可能性のあるメモリ不足 (OOM) エラーを回避できます。

関連ドキュメント

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

• CREATE TABLE

• CREATE TABLE AS

• CREATE TABLE LIKE

• ALTER TABLE

• DROP TABLE