ワイドテーブルモードにおける特定列の統計分析 - PolarDB

ワイドテーブルには、数十から数百の列が含まれる場合があります。クエリでは、これらの列のうち一部のみを分析する必要があることがよくあります。列ストアインデックスを使用することで、これらのクエリを高速化できます。

背景情報

多くの SaaS 業務システムでは、テーブルに数十から数百の列が含まれることがあり、クエリに技術的な課題をもたらします。

一部のクエリでは数個の列を分析するだけで済みますが、行指向ストレージエンジンを使用すると、多くの無関係な列が読み取られます。これにより、システムの I/O 負荷が増加します。
クエリモードは固定されていません。数百の列をクエリする際に、複数のフィルター条件を使用できます。複合インデックスを構築する場合、事前にすべてのクエリシナリオを考慮する必要があります。クエリ条件が変更されると、複合インデックスは無効になります。

列ストアインデックスは、これら 2 つのシナリオに適しています。列ストアインデックスは列ベースであるため、1 つの列を読み取っても他の列には影響しません。また、列は独立しているため、複数のフィルター条件の順序が列ストアインデックスの有効性に影響することもありません。

結果

1 億行のデータセットと 4 の並列処理の次数で、列ストアインデックスを使用するクエリは、ネイティブ PostgreSQL の並列実行よりも 30 倍高速に実行されます。

クエリ	ネイティブ PostgreSQL での並列実行	列ストアインデックス
Q1	243 s	7.9 s

操作手順

ステップ 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 パラメーターを設定できます。このパラメーターを変更するとクラスターが再起動します。ビジネス運用を適切に計画し、注意して進めてください。

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

列ストアインデックス機能を有効にする方法は、ご利用の 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 のワークロードは同じノードで実行され、互いに影響します。
コスト	インメモリ列インデックス (IMCI) 読み取り専用ノードには追加料金が発生し、通常の計算ノードと同じ料金で課金されます。	追加コストはかかりません。

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

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

説明

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

コンソールでの追加

PolarDB コンソールにログインし、クラスターが配置されているリージョンを選択します。次のいずれかの方法で ノードの追加/削除 ウィザードを開くことができます。
- クラスター ページで、操作列の ノードの追加/削除 をクリックします。
- 対象クラスターの概要ページで、データベースノード セクションの ノードの追加/削除 をクリックします。
列ストアインデックス読み取り専用ノードの追加 を選択し、OK をクリックします。
クラスターのスペックアップ/スペックダウンページで、列ストアインデックス読み取り専用ノードを追加し、支払いを完了します。
1. [IMCI ノードの追加] をクリックし、ノード仕様を選択します。
2. 切り替え時間を選択します。
3. (任意) プロダクト利用規約とサービスレベル合意書 (SLA) を確認します。
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 拡張機能を作成する必要があります。
拡張機能のインストールに使用するデータベースアカウントは、特権アカウントである必要があります。

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

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

PolarDB コンソールにログインします。左側のナビゲーションウィンドウで クラスター をクリックします。クラスターが配置されているリージョンを選択し、クラスター ID をクリックしてクラスター詳細ページに移動します。
左側のナビゲーションウィンドウで、設定と管理 > プラグインの管理 を選択します。プラグインの管理 タブで、プラグインがインストールされていません を選択します。
ページの右上隅で、対象のデータベースを選択します。polar_csi 拡張機能の行で、[操作] 列の インストール をクリックします。表示される プラグインのインストール ダイアログボックスで、対象の データベースアカウント を選択し、確認をクリックして、対象のデータベースに拡張機能をインストールします。

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

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

CREATE EXTENSION polar_csi;

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

この例では、widecolumntable という名前のテーブルを使用します。このテーブルには、BIGINT、DECIMAL、TEXT、JSONB、TEXT[] などのデータ型の 24 列が含まれています。この例では、5 つの列 (id_1、domain、consumption、start_time、end_time) を分析して、過去 1 年間に各顧客が複数のドメインで費やした合計金額を計算します。

次のテーブルスキーマで widecolumntable という名前のテーブルを作成します。次に、必要に応じてテストデータを挿入します。

CREATE TABLE widecolumntable (
  id_1 BIGINT NOT NULL PRIMARY KEY,
  id_2 BIGINT,
  id_3 BIGINT,
  id_4 BIGINT,
  id_5 BIGINT,
  id_6 BIGINT,
  version INT,
  domain TEXT,
  consumption DECIMAL(18,3),
  c_level CHARACTER varying(1) NOT NULL,
  priority BIGINT,
  operator TEXT,
  notify_policy TEXT,
  call_id UUID NOT NULL,
  provider_id BIGINT NOT NULL,
  name_1 TEXT NOT NULL,
  name_2 TEXT NOT NULL,
  name_3 TEXT,
  start_time TIMESTAMP WITH TIME ZONE NOT NULL,
  end_time TIMESTAMP WITH TIME ZONE NOT NULL,
  comment JSONB NOT NULL,
  description TEXT[] NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL
);

id_1、domain、consumption、start_time、end_time の各列を列ストアインデックスに追加します。
```
CREATE INDEX idx_wide_csi ON widecolumntable USING CSI(id_1, domain, consumption,  start_time, end_time);
```

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

異なる実行エンジンを使用して、過去 1 年間に各顧客が異なるドメインで費やした合計金額を計算し、結果を合計金額でソートするクエリを実行します。

列ストアインデックスを使用します。

--- 列ストアインデックスを有効にし、並列処理の次数を 4 に設定します。
SET polar_csi.enable_query to on;
SET polar_csi.exec_parallel to 4;

---Q1
EXPLAIN ANALYZE SELECT id_1, domain,SUM(consumption) FROM widecolumntable WHERE start_time > '20230101' and end_time < '20240101' GROUP BY id_1, domain Order By SUM(consumption);

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

--- 列ストアインデックスを無効にし、ローストアエンジンを使用し、並列処理の次数を 4 に設定します。
SET polar_csi.enable_query to off;
SET max_parallel_workers_per_gather to 4;

---Q1
EXPLAIN ANALYZE SELECT id_1, domain,SUM(consumption) FROM widecolumntable WHERE start_time > '20230101' and end_time < '20240101' GROUP BY id_1, domain Order By SUM(consumption);