PolarDB:概要

背景情報

パーティションテーブル上のローカルインデックスは、各テーブルパーティションにマッピングされたインデックスパーティションを指します。 ローカルインデックスの分割は、テーブルの分割と一致しています。 したがって、ローカルインデックスには、一意の制約をサポートするパーティションキーを含める必要があります。

グローバルインデックスは、パーティションテーブル全体に対して定義された単一のインデックスであり、多くのテーブルパーティションにまたがっています。 グローバルインデックスのないパーティションテーブルがクエリされると、システムは、ストレージ全体に分散されたテーブルの物理パーティションからデータを取得する必要があります。 グローバルインデックスを使用すると、システムはパーティションに個別にアクセスするのではなく、複数のパーティションからのデータに集合的にアクセスできます。

グローバルインデックスは、個別の子パーティションではなく、パーティション分割されたテーブル上に作成されたBツリーインデックスです。 パーティション以外のキー列で作成でき、一意の制約をサポートします。

グローバルパーティションインデックス

グローバルパーティションインデックスは、多くの場合、大量のデータを含むパーティションテーブル上に作成されるため、構築がより複雑になります。 グローバル分割インデックスの分割は、テーブルの分割と必ずしも一致しません。 パーティション間で関連するレコードを効率的に見つけることに加えて、グローバルパーティションインデックスは、インデックス付きパーティションキーをターゲットとするクエリのパフォーマンスをさらに向上させます。

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

構文

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/global_partitioned_index ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]
global_partitioned_index:
    GLOBAL PARTITION BY {RANGE (column_list) (index_partitioning_clause) |
                            HASH (column_list) (hash_partitions_by_quantity)}

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

CREATE unique index m_city_id_idx_global ON measurement(city_id) global;

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

CREATE UNIQUE INDEX m_peaktemp_idx_global ON measurement(peaktemp) global
PARTITION BY range (peaktemp)
(
PARTITION m_peaktemp_idx_global_p1 values less than(1000),
PARTITION m_peaktemp_idx_global_p2 values less than(5000),
PARTITION m_peaktemp_idx_global_p3 values less than(MAXVALUE)
);

ノート

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

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

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

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

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

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

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

• グローバルインデックスは、テーブルパーティションキーで分割できます。

• グローバル分割インデックスは、一意の制約をグローバルに適用できます。

• グローバルインデックスのパーティション分割は、インデックス列の左プレフィックスに基づく必要があります。

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

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

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

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

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

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

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

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

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

• グローバルパーティション分割インデックスのみのスキャン

• グローバルインデックスビットマップスキャン

• グローバルパーティション分割インデックスビットマップスキャン

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

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

例

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

  explain select * from measurement where city_id = 5;
                                             QUERY PLAN                                           
  ------------------------------------------------------------------------------------------------
   Global Index Scan using m_city_id_idx_global on measurement  (cost=0.12..8.14 rows=1 width=20)
     Index Cond: (city_id = 5)
  (2 rows)

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

  explain select * from measurement where peaktemp = 5;
                                                      QUERY PLAN                                                    
  ------------------------------------------------------------------------------------------------------------------
   Global Partitioned Index Scan(1/3) using m_peaktemp_idx_global on measurement  (cost=0.50..8.52 rows=1 width=20)
     Index Cond: (peaktemp = 5)
  (2 rows)

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

  explain select city_id from measurement where city_id = 5;
                                               QUERY PLAN                                             
  ----------------------------------------------------------------------------------------------------
   Global Index Only Scan using m_city_id_idx_global on measurement  (cost=0.12..8.14 rows=1 width=4)
     Index Cond: (city_id = 5)
  (2 rows)

• グローバルパーティション分割インデックスのみのスキャン

  explain select peaktemp from measurement where peaktemp = 5;
                                                        QUERY PLAN                                                      
  ----------------------------------------------------------------------------------------------------------------------
   Global Partitioned Index Only Scan(1/3) using m_peaktemp_idx_global on measurement  (cost=0.12..8.14 rows=1 width=4)
     Index Cond: (peaktemp = 5)
  (2 rows)

• グローバルインデックスビットマップスキャン

  explain select * from measurement where city_id > 5 and city_id < 10000;
                                             QUERY PLAN                                           
  ------------------------------------------------------------------------------------------------
   Append  (cost=1.03..14.47 rows=32 width=20)
     ->  Bitmap Heap Scan on measurement_y2023q1  (cost=1.03..3.58 rows=8 width=20)
           Recheck Cond: ((city_id > 5) AND (city_id < 10000))
           Filter: ((city_id > 5) AND (city_id < 10000))
           ->  Bitmap Global Index Scan on m_city_id_idx_global  (cost=0.00..4.13 rows=1 width=0)
                 Index Cond: ((city_id > 5) AND (city_id < 10000))
     ->  Bitmap Heap Scan on measurement_y2023q2  (cost=1.03..3.58 rows=8 width=20)
           Recheck Cond: ((city_id > 5) AND (city_id < 10000))
           Filter: ((city_id > 5) AND (city_id < 10000))
           ->  Bitmap Global Index Scan on m_city_id_idx_global  (cost=0.00..4.13 rows=1 width=0)
                 Index Cond: ((city_id > 5) AND (city_id < 10000))
     ->  Bitmap Heap Scan on measurement_y2023q3  (cost=1.03..3.58 rows=8 width=20)
           Recheck Cond: ((city_id > 5) AND (city_id < 10000))
           Filter: ((city_id > 5) AND (city_id < 10000))
           ->  Bitmap Global Index Scan on m_city_id_idx_global  (cost=0.00..4.13 rows=1 width=0)
                 Index Cond: ((city_id > 5) AND (city_id < 10000))
     ->  Bitmap Heap Scan on measurement_y2023q4  (cost=1.03..3.58 rows=8 width=20)
           Recheck Cond: ((city_id > 5) AND (city_id < 10000))
           Filter: ((city_id > 5) AND (city_id < 10000))
           ->  Bitmap Global Index Scan on m_city_id_idx_global  (cost=0.00..4.13 rows=1 width=0)
                 Index Cond: ((city_id > 5) AND (city_id < 10000))
  (21 rows)

• グローバルパーティション分割インデックスビットマップスキャン

  explain select peaktemp from measurement where peaktemp > 5 and peaktemp< 1000;
                                                      QUERY PLAN                                                    
  ------------------------------------------------------------------------------------------------------------------
   Append  (cost=1.03..14.47 rows=32 width=4)
     ->  Bitmap Heap Scan on measurement_y2023q1  (cost=1.03..3.58 rows=8 width=4)
           Recheck Cond: ((peaktemp > 5) AND (peaktemp < 1000))
           Filter: ((peaktemp > 5) AND (peaktemp < 1000))
           ->  Bitmap Global Partitioned Index Scan(1/3) on m_peaktemp_idx_global  (cost=0.00..4.13 rows=1 width=0)
                 Index Cond: ((peaktemp > 5) AND (peaktemp < 1000))
     ->  Bitmap Heap Scan on measurement_y2023q2  (cost=1.03..3.58 rows=8 width=4)
           Recheck Cond: ((peaktemp > 5) AND (peaktemp < 1000))
           Filter: ((peaktemp > 5) AND (peaktemp < 1000))
           ->  Bitmap Global Partitioned Index Scan(1/3) on m_peaktemp_idx_global  (cost=0.00..4.13 rows=1 width=0)
                 Index Cond: ((peaktemp > 5) AND (peaktemp < 1000))
     ->  Bitmap Heap Scan on measurement_y2023q3  (cost=1.03..3.58 rows=8 width=4)
           Recheck Cond: ((peaktemp > 5) AND (peaktemp < 1000))
           Filter: ((peaktemp > 5) AND (peaktemp < 1000))
           ->  Bitmap Global Partitioned Index Scan(1/3) on m_peaktemp_idx_global  (cost=0.00..4.13 rows=1 width=0)
                 Index Cond: ((peaktemp > 5) AND (peaktemp < 1000))
     ->  Bitmap Heap Scan on measurement_y2023q4  (cost=1.03..3.58 rows=8 width=4)
           Recheck Cond: ((peaktemp > 5) AND (peaktemp < 1000))
           Filter: ((peaktemp > 5) AND (peaktemp < 1000))
           ->  Bitmap Global Partitioned Index Scan(1/3) on m_peaktemp_idx_global  (cost=0.00..4.13 rows=1 width=0)
                 Index Cond: ((peaktemp > 5) AND (peaktemp < 1000))
  (21 rows)

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

PolarDB for PostgreSQL (Oracle互換) では、グローバルインデックスのおかげで、パーティションテーブルの任意の列をプライマリキーまたは外部キーにすることができます。 これは、主キーまたは外部キーを定義するための前提条件が、目的の列に一意のインデックスが存在することであるためです。 グローバルインデックスは任意の列で一意に作成できますが、ローカル一意インデックスにはパーティションキーを含める必要があります。 つまり、グローバルインデックスがない場合、主キーまたは外部キーの定義には常にパーティションキーが含まれている必要があります。 グローバルインデックスを使用すると、主キーまたは外部キーの作成がより柔軟になります。

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

例

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

  -- local index
  CREATE TABLE pk_rel(a int primary key, b int, c int, d int) PARTITION BY RANGE (a);
  
  postgres=# \d pk_rel
                 Table "public.pk_rel"
   Column |  Type   | Collation | Nullable | Default 
  --------+---------+-----------+----------+---------
   a      | integer |           | not null | 
   b      | integer |           |          | 
   c      | integer |           |          | 
   d      | integer |           |          | 
  Partition key: RANGE (a)
  Indexes:
      "pk_rel_pkey" PRIMARY KEY, btree (a)
  Number of partitions: 0
  
  -- global index 
  CREATE TABLE pk_rel(a int, b int primary key, c int, d int) PARTITION BY RANGE (a);
  
  postgres=# \d pk_rel
                 Table "public.pk_rel"
   Column |  Type   | Collation | Nullable | Default 
  --------+---------+-----------+----------+---------
   a      | integer |           |          | 
   b      | integer |           | not null | 
   c      | integer |           |          | 
   d      | integer |           |          | 
  Partition key: RANGE (a)
  Indexes:
      "pk_rel_pkey" PRIMARY KEY, btree (b) GLOBAL
  Number of partitions: 0
  

  説明

  • このステートメントを使用して、インデックスをプライマリキーとして明示的に指定することもできます。ALTER TABLE xxx ADD primary key USING index xxxです。

  • PolarDB for PostgreSQL (Compatible with Oracle) では、主キーまたは一意のインデックスが列に作成されている場合、パーティション分割テーブルの任意の列を外部キーにすることができます。 列がパーティションキーの場合、ローカルインデックスが作成されます。 そうでない場合は、グローバルインデックスが作成されます。

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

  CREATE TABLE fk_rel(a int, b int REFERENCES pk_rel(b), c int, d int) PARTITION BY RANGE (a);
  
  postgres=# \d fk_rel
                 Table "public.fk_rel"
   Column |  Type   | Collation | Nullable | Default 
  --------+---------+-----------+----------+---------
   a      | integer |           |          | 
   b      | integer |           |          | 
   c      | integer |           |          | 
   d      | integer |           |          | 
  Partition key: RANGE (a)
  Foreign-key constraints:
      "fk_rel_b_fkey" FOREIGN KEY (b) REFERENCES pk_rel(b)
  Number of partitions: 0

パフォーマンステスト

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

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

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

結論

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