多段パーティション化テーブルでのグローバルインデックスの作成 - PolarDB

PolarDB for PostgreSQL (Compatible with Oracle) では、多段パーティション化テーブルおよびパーティションキーにグローバルインデックスを作成できます。グローバルインデックスは、データのパーティション化の方法に関係なく、テーブル階層全体で単一のインデックスを維持するため、非パーティションキー列でフィルターするクエリを効率的に実行できます。

前提条件

開始する前に、次のものがあることを確認してください：

リビジョンバージョン V1.1.35 以降の PolarDB for PostgreSQL (Compatible with Oracle) クラスター

制限事項とインデックスの選択

範囲：グローバルインデックスは、多段パーティション化テーブルのルートテーブルにのみ作成できます。サブパーティションにグローバルインデックスを作成することはサポートされていません。

グローバルインデックスを使用するタイミング：パーティションキーのグローバルインデックスは、必ずしもローカルインデックスよりパフォーマンスが優れているわけではありません。次の表を参考にして、適切なインデックスタイプを選択してください。

クエリパターン	推奨インデックスタイプ	理由
ハッシュパーティションテーブルでの範囲クエリ	グローバルインデックス	すべてのハッシュパーティションのスキャンを回避
等価クエリ	ローカルインデックス	ローカルインデックスはより少ないパーティションを探索

オプティマイザーは、より効率的なインデックスを自動的に選択します。ビジネスシナリオに基づいてクエリに使用するインデックスを手動で指定し、オプティマイザーのワークロードを削減することもできます。

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

以下の例では、sales という名前の 2段パーティション化テーブルを使用します。このテーブルは date で RANGE によってパーティション化され、各パーティションは country で LIST によってサブパーティション化されています。

ステップ 1: パーティションテーブルの作成

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY RANGE(date)
  SUBPARTITION BY LIST(country)
  (
    PARTITION q1_2012
      VALUES LESS THAN('2012-Apr-01')
      (
        SUBPARTITION q1_europe VALUES ('FRANCE', 'ITALY'),
        SUBPARTITION q1_asia VALUES ('INDIA', 'PAKISTAN'),
        SUBPARTITION q1_americas VALUES ('US', 'CANADA')
      ),
    PARTITION q2_2012
      VALUES LESS THAN('2012-Jul-01')
      (
        SUBPARTITION q2_europe VALUES ('FRANCE', 'ITALY'),
        SUBPARTITION q2_asia VALUES ('INDIA', 'PAKISTAN'),
        SUBPARTITION q2_americas VALUES ('US', 'CANADA')
      ),
    PARTITION q3_2012
      VALUES LESS THAN('2012-Oct-01')
      (
        SUBPARTITION q3_europe VALUES ('FRANCE', 'ITALY'),
        SUBPARTITION q3_asia VALUES ('INDIA', 'PAKISTAN'),
        SUBPARTITION q3_americas VALUES ('US', 'CANADA')
      ),
    PARTITION q4_2012
      VALUES LESS THAN('2013-Jan-01')
      (
        SUBPARTITION q4_europe VALUES ('FRANCE', 'ITALY'),
        SUBPARTITION q4_asia VALUES ('INDIA', 'PAKISTAN'),
        SUBPARTITION q4_americas VALUES ('US', 'CANADA')
      )
  );

ステップ 2: 非パーティションキー列でのグローバルインデックスの作成

パーティションキーではない part_no にグローバルインデックスを作成します：

CREATE index sales_part_no_idx_global ON sales(part_no) global;

クエリプランを検証して、グローバルインデックスが使用されていることを確認します：

EXPLAIN (costs off) SELECT * FROM sales WHERE part_no = '101';

期待される出力：

                        QUERY PLAN
-----------------------------------------------------------
 Global Index Scan using sales_part_no_idx_global on sales
   Index Cond: ((part_no)::text = '101'::text)

ステップ 3: パーティションキー列でのグローバルインデックスの作成

RANGE パーティションキーである date にグローバルインデックスを作成します：

CREATE index sales_date_idx_global ON sales(date) global;

クエリプランを検証します：

EXPLAIN (costs off) SELECT * FROM sales WHERE date = '2012-08-01';

期待される出力：

                       QUERY PLAN
--------------------------------------------------------
 Global Index Scan using sales_date_idx_global on sales
   Index Cond: (date = '01-AUG-12 00:00:00'::date)