PolarDB:パーティションテーブルでの列ストアインデックスの使用

ステップ 1：環境の準備

1. クラスターのバージョンと構成が次の要件を満たしていることを確認します。

   • クラスターバージョン：

     • PostgreSQL 16 (マイナーエンジンバージョン 2.0.16.8.3.0 以降)

     • PostgreSQL 14 (マイナーエンジンバージョン 2.0.14.10.20.0 以降)

     説明

     コンソールで、または SHOW polardb_version; 文を実行することで、マイナーエンジンバージョン番号を確認できます。マイナーエンジンバージョンが要件を満たさない場合は、マイナーエンジンバージョンをアップグレードする必要があります。

   • ソーステーブルにはプライマリキーが必要です。列ストアインデックスを作成する際には、プライマリキー列を含める必要があります。

   • wal_level パラメーターを logical に設定する必要があります。これにより、論理レプリケーションをサポートするために必要な情報が先行書き込みログ (WAL) に追加されます。

     説明

     wal_level パラメーターはコンソールで設定できます。このパラメーターを変更すると、クラスターが再起動します。ビジネス運用を適切に計画し、注意して進めてください。

2. 列ストアインデックス機能を有効にします。

   PolarDB for PostgreSQL クラスターのマイナーエンジンバージョンによって、列ストアインデックス機能を有効にする方法が異なります。

   PostgreSQL 16 (2.0.16.9.8.0 以降) または PostgreSQL 14 (2.0.14.17.35.0 以降)

   これらのバージョンの PolarDB for PostgreSQL クラスターでは、2 つの方法が利用可能です。違いは次の表にまとめられています。ニーズに最も適した方法を選択できます。

   比較項目	[推奨] 列ストアインデックス読み取り専用ノードの追加	プリインストール済みの列ストアインデックス拡張機能の直接使用
   方法	コンソールの視覚的なインターフェイスを通じて、手動で列ストアインデックスノードを追加できます。	操作は不要です。拡張機能を直接使用できます。
   リソース割り当て	列ストアエンジンはすべてのリソースを排他的に使用し、利用可能なメモリを最大限に活用できます。	列ストアエンジンはメモリの 25% しか使用できません。残りのメモリはローストアエンジンに割り当てられます。
   ビジネスへの影響	トランザクション処理 (TP) と分析処理 (AP) のワークロードは異なるノードで分離されており、互いに影響しません。	TP と AP のワークロードは同じノードで実行され、互いに影響します。
   コスト	In-Memory Column Index (IMCI) 読み取り専用ノードには追加料金が発生し、通常の計算ノードと同じレートで課金されます。	追加コストなし。

   列ストアインデックス読み取り専用ノードの追加

   次の 2 つの方法のいずれかで、列ストアインデックス読み取り専用ノードを追加できます。

   説明

   クラスターには少なくとも 1 つの読み取り専用ノードが含まれている必要があります。シングルノードクラスターに列ストアインデックス読み取り専用ノードを追加することはできません。

   コンソールでの追加

   1. PolarDB コンソールにログインし、クラスターが所在するリージョンを選択します。次のいずれかの方法で ノードの追加/削除 ウィザードを開くことができます。

      • クラスター ページで、操作 列の ノードの追加/削除 をクリックします。

        

      • 対象のクラスターの概要 ページで、データベースノード セクションにあるノードの追加/削除をクリックします。

        

   2. 列ストアインデックス読み取り専用ノードの追加 を選択し、OK をクリックします。

   3. クラスターのスペックアップ/スペックダウンページで、列ストアインデックス読み取り専用ノードを追加し、支払いを完了します。

      1. [IMCI ノードの追加] をクリックし、ノード仕様を選択します。

      2. 切り替え時間を選択します。

      3. (任意) プロダクト利用規約とサービスレベル合意書 (SLA) を確認します。

      4. 今すぐ購入 をクリックします。

      

   4. 支払いが完了したら、クラスター詳細ページに戻り、列ストアインデックスの読み取り専用ノードが追加されるのを待ちます。ノードのステータスが 実行中 に変わると、追加は完了です。

   購入時の追加

   PolarDB 購入ページで、[IMCI 読み取り専用ノード] パラメーターの ノード数 数を選択します。

   

   PostgreSQL 16 (2.0.16.8.3.0 から 2.0.16.9.8.0) または PostgreSQL 14 (2.0.14.10.20.0 から 2.0.14.17.35.0)

   これらのバージョンの PolarDB for PostgreSQL クラスターでは、列ストアインデックスはデータベースクラスターに polar_csi 拡張機能としてデプロイされます。列ストアインデックスを使用する前に、指定したデータベースに拡張機能を作成する必要があります。

   説明

   • polar_csi 拡張機能のスコープはデータベースレベルです。クラスター内の複数のデータベースで列ストアインデックスを使用するには、データベースごとに polar_csi 拡張機能を作成する必要があります。

   • 拡張機能をインストールするために使用するデータベースアカウントは、特権アカウントである必要があります。

   次の 2 つの方法のいずれかで polar_csi 拡張機能をインストールできます。

   コンソールからのインストール

   1. PolarDB コンソールにログインします。左側のナビゲーションウィンドウで、クラスター をクリックします。クラスターが配置されている[リージョン]を選択し、クラスター ID をクリックしてクラスターの詳細ページに移動します。

   2. 左側のナビゲーションウィンドウで、設定と管理 > プラグインの管理 を選択します。プラグインの管理 タブで、プラグインがインストールされていません を選択します。

   3. ページの右上隅で対象のデータベースを選択し、polar_csi 拡張機能の行で [操作] 列の インストール をクリックします。表示される プラグインのインストール ダイアログボックスで、対象の データベースアカウント を選択し、確認 をクリックして対象のデータベースに拡張機能をインストールします。

      

   コマンドラインからのインストール

   データベースクラスターに接続し、polar_csi 拡張機能を作成するために必要な権限を持つ対象のデータベースで次の文を実行します。

   CREATE EXTENSION polar_csi;

ステップ 2：データの準備

この例では、多階層パーティションテーブルを作成し、3 億 2000 万行のシミュレーションデータ (約 16 GB) を挿入します。その後、パーティション条件に基づいて統計分析を実行します。

テスト用のパーティションテーブルのスキーマは次のとおりです。

• sales：プライマリテーブル。

• sales_2023：年でパーティション分割。

  • sales_2023_a：月でパーティション分割。このパーティションには 1 月から 6 月までのデータが含まれます。

  • sales_2023_b：月でパーティション分割。このパーティションには 7 月から 12 月までのデータが含まれます。

• sales_2024：年でパーティション分割。

  • sales_2024_a：月でパーティション分割。このパーティションには 1 月から 6 月までのデータが含まれます。

  • sales_2024_b：月でパーティション分割。このパーティションには 7 月から 12 月までのデータが含まれます。

1. sales という名前の多階層パーティションテーブルを作成します。sale_date 列をパーティションキーとして使用します。定義は次のとおりです。

   CREATE TABLE sales (
       sale_id serial,
       product_id int NOT NULL,
       sale_date date NOT NULL,
       amount numeric(10,2) NOT NULL,
       primary key(sale_id, sale_date)
   ) PARTITION BY RANGE (sale_date);
   
   CREATE TABLE sales_2023 PARTITION OF sales
       FOR VALUES FROM ('2023-1-1') TO ('2024-1-1')
       PARTITION BY RANGE (sale_date);
   CREATE TABLE sales_2023_a PARTITION OF sales_2023
       FOR VALUES FROM ('2023-1-1') TO ('2023-7-1');
   CREATE TABLE sales_2023_b PARTITION OF sales_2023
       FOR VALUES FROM ('2023-7-1') TO ('2024-1-1');
   
   CREATE TABLE sales_2024 PARTITION OF sales
       FOR VALUES FROM ('2024-1-1') TO ('2025-1-1')
       PARTITION BY RANGE (sale_date);
   CREATE TABLE sales_2024_a PARTITION OF sales_2024
       FOR VALUES FROM ('2024-1-1') TO ('2024-7-1');
   CREATE TABLE sales_2024_b PARTITION OF sales_2024
       FOR VALUES FROM ('2024-7-1') TO ('2025-1-1');

2. 約 16 GB のデータを生成し、パーティションテーブルに挿入します。

   INSERT INTO sales (product_id, sale_date, amount)
   SELECT
     (random()*100)::int AS product_id,
     '2023-01-1'::date + i/3200000*7 AS sale_date,
     (random()*1000)::numeric(10,2) AS amount
   FROM
     generate_series(1, 320000000) i;

3. テーブルに列ストアインデックスを作成します。sale_id、product_id、sale_date、amount フィールドを列ストアインデックスに追加します。

   CREATE INDEX ON sales USING CSI(sale_id, product_id, sale_date, amount);

ステップ 3：クエリの実行

異なる実行エンジンを使用してクエリを実行します。異なるパーティション条件に基づいて、Q1、Q2、Q3 の 3 つのクエリが生成されます。

• 列ストアインデックスの使用

  --- 列ストアインデックスを有効にし、クエリの並列処理の次数を 4 に設定します。
  SET polar_csi.enable_query to on;
  SET polar_csi.exec_parallel to 4;
  
  --- Q1
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2023-3-1' AND amount > 100 GROUP BY sale_date;
  --- Q2
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2023-9-1' AND amount > 100 GROUP BY sale_date;
  --- Q3
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2024-3-1' AND amount > 100 GROUP BY sale_date;

• 列ストアインデックスを無効にし、ローストアエンジンを使用

  --- 列ストアインデックスを無効にし、ローストアエンジンを使用して、クエリの並列処理の次数を 4 に設定します。
  SET polar_csi.enable_query to off;
  SET max_parallel_workers_per_gather to 4;
  
  --- Q1
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2023-3-1' AND amount > 100 GROUP BY sale_date;
  --- Q2
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2023-9-1' AND amount > 100 GROUP BY sale_date;
  --- Q3
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2024-3-1' AND amount > 100 GROUP BY sale_date;