すべてのプロダクト
Search
ドキュメントセンター

PolarDB:グローバルインデックス

最終更新日:Jul 01, 2024

グローバルインデックス作成は、パーティションテーブルのインデックス作成手法です。 非パーティションキーを使用して、パーティションテーブルにグローバルインデックスを作成できます。 グローバルインデックスは、一意の制約を提供できます。

前提条件

この機能は、次のエンジンを実行するPolarDB for PostgreSQLクラスターでサポートされています。

PostgreSQL 14 (バージョン14.6.4.0以降)

説明

次のステートメントを実行して、PolarDB for PostgreSQLクラスターのリビジョンバージョンを表示できます。

select version();

背景情報

ビジネスデータが増加するにつれて、データ分割はエンタープライズレベルのデータベースの重要な機能になり、データ規模を縮小する重要な手段になります。 パーティションテーブルは、パーティションキーに基づいていくつかの独立したサブテーブルに分割されます。 サブテーブルは、管理性、全体的なパフォーマンス、および負荷分散を改善するために個別に管理されます。

PolarDB for PostgreSQL を使用すると、パーティションテーブルのパーティションを管理できます。 通常、パーティションは時間の次元で管理されます。

  • 時間は、パーティションテーブルのパーティションキーです。

  • 新しいパーティションは定期的 (毎週または毎月) に作成され、新しいデータが新しいパーティションに追加されます。

  • 古いパーティションは定期的にアーカイブされ、O&Mコストを削減します。

上記のシナリオでは、主キーまたは一意のIDではなく、時間がパーティションキーです。 これにより、次の問題が発生します。

  • 非パーティションキーのデータに対するクエリでは、データがどのパーティションにあるかが明確ではないため、すべてのパーティションがスキャンされます。

  • 非パーティションキーのデータを変更すると、データがパーティションテーブル内で一意でない場合があります。

PolarDB for PostgreSQL は、グローバルインデックス機能を提供します。 グローバルインデックスは、パーティションテーブルに作成されます。 デフォルトで各パーティションに作成されるローカルインデックスとは異なり、グローバルインデックスはパーティションテーブル全体のデータ用です (各インデックスは複数のパーティションに対応します) 。 これにより、非パーティションキーに対してグローバルに一意の制約が提供され、非パーティションキーのクエリ性能が大幅に向上します。

制限

  • グローバルインデックスが作成されているパーティションテーブルでも、パーティションをアタッチまたはデタッチできます。

  • グローバルインデックスを作成するには、ステートメントにglobalキーワードを追加してインデックスを作成します。 このキーワードを追加しない場合、デフォルトでローカルインデックスが作成されます。

  • concurrentlyキーワードを追加すると、グローバルインデックスを同時に作成できます。

  • 非パーティションテーブルまたはサブパーティションテーブルにグローバルインデックスを作成することはできません。

  • グローバルインデックスを式インデックスにすることはできません。

  • パーティションテーブルのパーティションキー列にグローバルインデックスを作成することはできません。

構文

グローバルインデックスを作成します。

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
[ GLOBAL/LOCAL ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

    パーティション以外のキークエリを高速化

    1. パーティションキーとして時間列を持つパーティションテーブルを作成します。

      CREATE TABLE partition_range (
          id INT,
          a INT,
          b INT,
          created_date TIMESTAMP WITHOUT TIME ZONE
      ) PARTITION BY RANGE (created_date);
      
      CREATE TABLE partition_range_part01 PARTITION OF partition_range FOR VALUES FROM (MINVALUE) TO ('2020-01-01 00:00:00');
      CREATE TABLE partition_range_part02 PARTITION OF partition_range FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2020-02-01 00:00:00');
      CREATE TABLE partition_range_part03 PARTITION OF partition_range FOR VALUES FROM ('2020-02-01 00:00:00') TO ('2020-03-01 00:00:00');
    2. 非パーティションキー条件に基づいてパーティションテーブルを照会します。

      EXPLAIN (COSTS OFF) SELECT * FROM partition_range WHERE id = 1;

      結果は、すべてのパーティションがスキャンされ、パーティションプルーニング機能を使用できないことを示しています。

                              QUERY PLAN
      ------------------------------------------------------------
       Append
         ->  Seq Scan on partition_range_part01 partition_range_1
               Filter: (id = 1)
         ->  Seq Scan on partition_range_part02 partition_range_2
               Filter: (id = 1)
         ->  Seq Scan on partition_range_part03 partition_range_3
               Filter: (id = 1)
      (7 rows)
    3. パーティションテーブルにローカルインデックスを作成し、クエリを再度実行します。

      CREATE INDEX partition_range_idx_local ON partition_range(id);
      
      EXPLAIN (COSTS OFF) SELECT * FROM partition_range WHERE id = 1;

      結果は、ローカルインデックスが個々のパーティションで作成されるため、すべてのパーティションのローカルインデックスがスキャンされることを示しています。

                                                  QUERY PLAN
      --------------------------------------------------------------------------------------------------
       Append
         ->  Index Scan using partition_range_part01_id_idx on partition_range_part01 partition_range_1
               Index Cond: (id = 1)
         ->  Index Scan using partition_range_part02_id_idx on partition_range_part02 partition_range_2
               Index Cond: (id = 1)
         ->  Index Scan using partition_range_part03_id_idx on partition_range_part03 partition_range_3
               Index Cond: (id = 1)
      (7 rows)

    4. globalキーワードを使用してパーティションテーブルにグローバルインデックスを作成し、クエリを再度実行します。

      CREATE INDEX partition_range_idx_global ON partition_range(id) GLOBAL;
      
      EXPLAIN (COSTS OFF) SELECT * FROM partition_range WHERE id = 1;

      結果は、データを含むパーティションを見つけるためにグローバルインデックスが使用されることを示しています。

                                    QUERY PLAN
      -----------------------------------------------------------------------
       Global Index Scan using partition_range_idx_global on partition_range
         Index Cond: (id = 1)
      (2 rows)

    非パーティションキーの一意の制約

    前の例のパーティションテーブルは引き続き使用されます。 パーティションキーはcreated_dateですが、一意の制約列はidです。

    CREATE UNIQUE INDEX partition_range_id_unique_idx ON partition_range(id);
    ERROR:  unique constraint on partitioned table must include all partitioning columns
    DETAIL:  UNIQUE constraint on table "partition_range" lacks column "created_date" which is part of the partition key.
    説明

    非パーティションキーのローカルインデックスに対して一意の制約を作成すると、インデックスにパーティションキーを含める必要があることを示すエラーメッセージが返されます。

    グローバルインデックスに一意の制約を追加すると、そのような制約は削除されます。

    CREATE UNIQUE INDEX partition_range_id_unique_idx ON partition_range(id) GLOBAL;

    パフォーマンステスト

    pgbenchを使用して80,000行のデータを生成し、パーティションテーブルと非パーティションテーブルを作成します。

    非パーティションキーのポイントクエリのパフォーマンス

    項目

    TPS

    Prepared Statement

    使用されない

    使用中

    Concurrency

    1

    32

    64

    1

    32

    64

    Common table

    27,732

    494,433

    430,848

    53,935

    985,880

    886,882

    Common table

    367

    4,155

    3,688

    856

    8,742

    6,790

    Partitioned table and global index

    19,006

    308,128

    262,941

    45,090

    820,924

    731,557

    非パーティションキーのTPC-Bパフォーマンス

    説明

    ポイントクエリとDMLの両方が含まれています。

    項目

    TPS

    Prepared Statement

    使用されない

    使用中

    Concurrency

    1

    32

    64

    1

    32

    64

    ommon table

    1,115

    51,025

    60,409

    4,822

    90,312

    100,802

    Partitioned table and local index

    271

    2,903

    2,524

    550

    5,276

    4,237

    Partitioned table and global index

    非対応

    4,334

    69,040

    75,232

    結論

    グローバルインデックスは、ポイントクエリとDMLのパフォーマンスを指数関数的に向上させます。