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

背景情報

ローカルインデックスは、パーティションテーブルの各パーティションのインデックスです。 ローカルインデックスの分割は、パーティションの分割と一致しています。 したがって、ローカルインデックスは、テーブルのパーティションキーが含まれている場合にのみ、一意のインデックスとして機能します。

グローバルインデックスは、ルートテーブル上のインデックスであり、テーブルパーティションにマップされます。 パーティションテーブルの場合、システムは、ストレージ全体に分散されたテーブルの物理パーティションからデータを取得する必要があります。 しかし、グローバルインデックスを用いると、システムは、パーティションに別々にアクセスする代わりに、グローバルインデックスに基づいて集合的に複数のパーティションからデータにアクセスすることができる。

グローバルインデックスは、ルートテーブル上に構築されたBツリーインデックスです。 それらはパーティション上にありません。 グローバルインデックスは、パーティションテーブルのパーティションキー以外のフィールドで作成でき、一意の制約をサポートします。

グローバル分割インデックス

グローバルパーティションインデックスは、多くの場合、大量のデータを含むパーティションテーブルで使用されるため、作成がより複雑になります。 グローバル分割インデックスは、グローバル分割インデックスがデータテーブルと同じロジックで分割されないという点でグローバルインデックスとは異なります。

グローバルインデックスの作成

構文

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [存在しない場合] name ] ON [ ONLY ] table_name [ USINGメソッド]
    ( { column_name | (式) } [ COLLATE照合順序] [ opclass [ ( opclass_parameter = value [, ...] ) ] ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...])
    [含まれる (column_name [, ...] ) ]
    [WITH ( storage_parameter [= value] [, ... ] ) ]
[グローバル /ローカル /global_partitioned_index]
    [TABLESPACE tablespace_name]
    [どこ述語]
global_partitioned_index:
    GLOBAL PARTITION BY {RANGE (column_list) (index_partitioning_clause) |
                            HASH (column_list) (hash_partitions_by_quantity)} 

パーティションテーブルのグローバルインデックスを作成します。

CREATE一意インデックスm_city_id_idx_global ON測定 (city_id) グローバル;

パーティションテーブルのグローバルパーティションインデックスを作成します。

CREATE UNIQUE INDEX m_peaktemp_idx_global ON measurement(peaktemp) global
範囲 (peaktemp) によるPARTITION
(
PARTITION m_peaktemp_idx_global_p1の値が (1000) 未満、PARTITION m_peaktemp_idx_global_p2の値が (5000) 未満、PARTITION m_peaktemp_idx_global_p3の値が (MAXVALUE) 未満
); 

ノート

• globalを指定すると、グローバルインデックスが作成されます。

• GLOBALキーワードを指定しない場合、デフォルトでローカルインデックスが作成されます。

• CONCURRENTLYは、グローバルインデックスを作成するCREATE INDEXステートメントで指定できます。

• 非パーティションテーブルまたはパーティションテーブルの子テーブルのグローバルインデックスは作成できません。

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

• グローバル分割インデックスは、範囲分割またはハッシュ分割できます。 構文は、データテーブルを分割する場合と同様である。

• グローバル分割インデックスはリスト分割できません。

• グローバルパーティションインデックスは、パーティションテーブルの親テーブルに対してのみ作成できます。 パーティションテーブルのパーティションキー列に基づいて作成できます。

• グローバル分割インデックスは、グローバルに一意の制約をサポートします。

• グローバル分割インデックスは、インデックス列の左プレフィックスで分割されます。

• 範囲分割グローバルインデックスは、順序付きスキャンをサポートします。

• ハッシュ分割グローバルインデックスは、順序付きスキャンをサポートしていません。

• グローバルパーティションインデックスを使用して、オプティマイザとエグゼキュータに基づくパーティションプルーニングを実装できます。

グローバルインデックススキャン

Bツリーインデックスと同様に、システムがインデックスキーに対してクエリを実行すると、グローバルインデックスはインデックススキャンによりパフォーマンスが向上します。

グローバルインデックスは、次のタイプのインデックススキャンをサポートします。

• グローバルインデックススキャン

• グローバル分割インデックススキャン

• グローバルインデックスのみスキャン

• グローバル分割インデックスのみスキャン

• Global Index Bitmapスキャン

• Global Partitioned Index Bitmap Scan

• グローバルインデックス並列スキャン

• グローバルのみのインデックス並列スキャン

例

• グローバルインデックススキャン

  select * from measurement where city_id = 5を説明します。
                                             クエリ計画
  ------------------------------------------------------------------------------------------------
   測定時にm_city_id_idx_globalを使用したグローバルインデックススキャン (コスト=0.12 .. 8.14行=1幅=20)
     インデックスCond: (city_id = 5)
  (2行) 

• グローバル分割インデックススキャン

  select * from measurement where peaktemp = 5を説明します。
                                                      クエリ計画
  ------------------------------------------------------------------------------------------------------------------
   測定時にm_peaktemp_idx_globalを使用したグローバルパーティション分割インデックススキャン (1/3) (コスト=0.50 .. 8.52行=1幅=20)
     インデックスCond :( peaktemp = 5)
  (2行) 

• グローバルインデックスのみスキャン

  city_id = 5の測定からcity_idを選択して説明し

  。
                                               クエリ計画
  ----------------------------------------------------------------------------------------------------
   グローバルインデックスのみ測定時にm_city_id_idx_globalを使用したスキャン (コスト=0.12 .. 8.14行=1幅=4)
     インデックスCond: (city_id = 5)
  (2行) 

• グローバル分割インデックスのみスキャン

  measurement where peaktemp = 5からselect peaktempを説明します。
                                                        クエリ計画
  ----------------------------------------------------------------------------------------------------------------------
   グローバルパーティションインデックスのみスキャン (1/3) 測定時にm_peaktemp_idx_globalを使用 (コスト=0.12 .. 8.14行=1幅=4)
     インデックスCond :( peaktemp = 5)
  (2行) 

• Global Index Bitmapスキャン

  select * from measurement where city_id > 5およびcity_id < 10000について説明します。
                                             クエリ計画
  ------------------------------------------------------------------------------------------------
   追加 (コスト=1.03 .. 14.47行=32幅=20)
     -> ビットマップのヒープスキャンmeasurement_y2023q1 (コスト=1.03 .. 3.58行=8幅=20)
           Recheck Cond :( (city_id > 5) AND (city_id < 10000))
           フィルター :( (city_id > 5) AND (city_id < 10000))
           -> ビットマップグローバルインデックススキャンm_city_id_idx_global (コスト=0.00 .. 4.13行=1幅=0)
                 インデックスCond :( city_id > 5) AND (city_id < 10000)
     -> ビットマップのヒープスキャンmeasurement_y2023q2 (コスト=1.03 .. 3.58行=8幅=20)
           Recheck Cond :( (city_id > 5) AND (city_id < 10000))
           フィルター :( (city_id > 5) AND (city_id < 10000))
           -> ビットマップグローバルインデックススキャンm_city_id_idx_global (コスト=0.00 .. 4.13行=1幅=0)
                 インデックスCond :( city_id > 5) AND (city_id < 10000)
     -> ビットマップのヒープスキャンmeasurement_y2023q3 (コスト=1.03 .. 3.58行=8幅=20)
           Recheck Cond :( (city_id > 5) AND (city_id < 10000))
           フィルター :( (city_id > 5) AND (city_id < 10000))
           -> ビットマップグローバルインデックススキャンm_city_id_idx_global (コスト=0.00 .. 4.13行=1幅=0)
                 インデックスCond :( city_id > 5) AND (city_id < 10000)
     -> ビットマップのヒープスキャンmeasurement_y2023q4 (コスト=1.03 .. 3.58行=8幅=20)
           Recheck Cond :( (city_id > 5) AND (city_id < 10000))
           フィルター :( (city_id > 5) AND (city_id < 10000))
           -> ビットマップグローバルインデックススキャンm_city_id_idx_global (コスト=0.00 .. 4.13行=1幅=0)
                 インデックスCond :( city_id > 5) AND (city_id < 10000)
  (21行) 

• Global Partitioned Index Bitmap Scan

  select peaktemp from measurement where peaktemp > 5 and peaktemp< 1000について説明します。
                                                      クエリ計画
  ------------------------------------------------------------------------------------------------------------------
   追加 (コスト=1.03 .. 14.47行=32幅=4)
     -> ビットマップのヒープスキャンmeasurement_y2023q1 (コスト=1.03 .. 3.58行=8幅=4)
           Recheck Cond :( (peaktemp > 5) AND (peaktemp < 1000))
           フィルター :( (peaktemp > 5) AND (peaktemp < 1000))
           -> m_peaktemp_idx_globalのビットマップグローバルパーティションインデックススキャン (1/3) (コスト=0.00 .. 4.13行=1 width=0)
                 インデックスCond :( (peaktemp > 5) AND (peaktemp < 1000)
     -> ビットマップのヒープスキャンmeasurement_y2023q2 (コスト=1.03 .. 3.58行=8幅=4)
           Recheck Cond :( (peaktemp > 5) AND (peaktemp < 1000))
           フィルター :( (peaktemp > 5) AND (peaktemp < 1000))
           -> m_peaktemp_idx_globalのビットマップグローバルパーティションインデックススキャン (1/3) (コスト=0.00 .. 4.13行=1 width=0)
                 インデックスCond :( (peaktemp > 5) AND (peaktemp < 1000)
     -> ビットマップのヒープスキャンmeasurement_y2023q3 (コスト=1.03 .. 3.58行=8幅=4)
           Recheck Cond :( (peaktemp > 5) AND (peaktemp < 1000))
           フィルター :( (peaktemp > 5) AND (peaktemp < 1000))
           -> m_peaktemp_idx_globalのビットマップグローバルパーティションインデックススキャン (1/3) (コスト=0.00 .. 4.13行=1 width=0)
                 インデックスCond :( (peaktemp > 5) AND (peaktemp < 1000)
     -> ビットマップのヒープスキャンmeasurement_y2023q4 (コスト=1.03 .. 3.58行=8幅=4)
           Recheck Cond :( (peaktemp > 5) AND (peaktemp < 1000))
           フィルター :( (peaktemp > 5) AND (peaktemp < 1000))
           -> m_peaktemp_idx_globalのビットマップグローバルパーティションインデックススキャン (1/3) (コスト=0.00 .. 4.13行=1 width=0)
                 インデックスCond :( (peaktemp > 5) AND (peaktemp < 1000)
  (21行) 

主キーまたは外部キーとしてのグローバルインデックス

では、グローバルインデックスのサポートにより、パーティションテーブルの任意の列をプライマリキーまたは外部キーにすることができます。 これは、主キーまたは外部キーであるためには、列に一意のインデックスが必要ですが、インデックスキーにパーティションキーが含まれている場合、ローカルインデックスは一意のインデックスとして機能できます。 ローカルインデックスのみがサポートされている場合、主キーにはパーティションキーが含まれ、外部キーにはパーティションキーが含まれている必要があります。 グローバルインデックスはこの制限を受けないため、どの列も主キーまたは外部キーにすることができます。

パーティションテーブルを作成するときにプライマリキーを指定できます。システムは、指定されたプライマリキーに基づいてローカルインデックスを作成するかグローバルインデックスを作成するかを決定します。 指定された主キーにパーティションキーが含まれている場合、システムは一意のインデックスとしてローカルインデックスを作成します。 それ以外の場合は、目的を果たすためにグローバルインデックスが作成されます。

例

• プライマリキーを指定したパーティションテーブルを作成します。

  -- ローカルインデックス
  CREATE TABLE pk_rel (intプライマリキー、b int、c int、d int) PARTITION BY RANGE (a);
  
  postgres=# \d pk_rel
                 テーブル "public.pk_rel"
   列 | タイプ | 照合 | Nullable | デフォルト
  ------- -------- -----------------------------------------------
   a | integer | | nullではない | 
   b | 整数 | | | 
   c | 整数 | | | 
   d | 整数 | | |
  仕切りのキー: RANGE (a)
  インデックス:
      "pk_rel_pkey" プライマリーキー、btree (a)
  パーティションの数: 0
  
  --- グローバルインデックス
  CREATE TABLE pk_rel(a int, b intプライマリキー, c int, d int) PARTITION BY RANGE (a);
  
  postgres=# \d pk_rel
                 テーブル "public.pk_rel"
   列 | タイプ | 照合 | Nullable | デフォルト
  ------- -------- -----------------------------------------------
   a | 整数 | | | 
   b | integer | | not null | 
   c | 整数 | | | 
   d | 整数 | | |
  仕切りのキー: RANGE (a)
  インデックス:
      "pk_rel_pkey" PRIMARY KEY, btree (b) グローバル
  パーティションの数: 0
  

  説明

  • ALTER TABLE xxx ADD PRIMARY KEY USING INDEX xxxステートメントを使用して、主キーとなるインデックスのタイプを指定することもできます。

  • では、パーティションテーブルの任意の列を外部キーにすることができますが、その列が主キーであるか、その列に対して一意のインデックスが作成されている場合に限ります。 列がパーティションキーの場合、ローカルインデックスが作成されます。 そうでない場合は、グローバルインデックスが作成されます。

• 外部キーを指定したパーティションテーブルの作成

  CREATE TABLE fk_rel(a int, b int REFERENCES pk_rel(b), c int, d int) PARTITION BY RANGE (a);
  
  postgres=# \d fk_rel
                 テーブル "public.fk_rel"
   列 | タイプ | 照合 | Nullable | デフォルト
  ------- -------- -----------------------------------------------
   a | 整数 | | | 
   b | 整数 | | | 
   c | 整数 | | | 
   d | 整数 | | |
  仕切りのキー: RANGE (a)
  外部キー制約:
      "fk_rel_b_fkey" 外国キー (b) 参照pk_rel(b)
  パーティションの数: 0 

パフォーマンステスト

pgbenchは、80,000行のデータを生成し、パーティションテーブルと非パーティションテーブルを作成するために使用されます。

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

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

結論

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