式に仮想列を使用してクエリを最適化する

式の仮想列を使用すると、JSON 属性のフィルタなど、同じ式を繰り返し評価するカラム型エンジン クエリが高速化されます。この機能は PostgreSQL 16 以降で使用できます。

カラム型エンジンは、頻繁に使用される式の結果をマテリアライズしてキャッシュに保存できます。これらの結果を事前に計算して保存することで、AlloyDB は複数のクエリで同じ式を繰り返し解析して評価することを回避します。このプロセスにより、クエリのパフォーマンスが向上し、CPU 使用量が削減されます。これは、大規模なデータセットに対する分析ワークロードで特に効果的です。

式用の仮想列のユースケースには、次のようなものがあります。

  • JSON 属性のフィルタリング: JSON 列内の特定の Key-Value ペアに基づいてクエリを頻繁にフィルタする場合。
  • 複雑な式の評価: 複雑な式や計算負荷の高い式を含むクエリ。

式スキャンの仮想列を有効にすると、カラム型エンジンを使用し、頻繁に使用される式を含むクエリが自動的に最適化されます。カラム型エンジンは、ベース列を更新するときに、これらの仮想列を自動的に更新します。

仮想列でサポートされている式

AlloyDB は、（プレビュー）リリースで JSON 列と JSONB 列の -> 演算子と ->> 演算子をサポートしています。

始める前に

  1. AlloyDB クラスタのカラム型エンジンを有効にします。詳細については、カラム型エンジンを構成するをご覧ください。

  2. Query Insights を使用して、AlloyDB クエリで頻繁に使用される式を特定します。

  3. 式のベース列をカラム型エンジンに追加します。詳細については、カラム型ストア コンテンツを手動で管理するをご覧ください。

式で仮想列を有効にする

式用の仮想列のサポートは、デフォルトで有効になっています（google_columnar_engine.enable_virtual_columns_scan=on）。このフラグが以前に無効になっていた場合は、gcloud alloydb instances update コマンドを使用してインスタンスで有効にできます。

gcloud CLI を使用するには、Google Cloud CLI をインストールして初期化するか、Cloud Shell を使用します。

gcloud alloydb instances update INSTANCE_ID \
    --region=REGION_ID \
    --cluster=CLUSTER_ID \
    --project=PROJECT_ID \
    --database-flags="google_columnar_engine.enable_virtual_columns_scan=on"

次のように置き換えます。

  • INSTANCE_ID: AlloyDB インスタンス ID。
  • REGION_ID: AlloyDB インスタンスのリージョン。
  • CLUSTER_ID: AlloyDB インスタンスのクラスタ ID。
  • PROJECT_ID: 実際の Google Cloud プロジェクト ID。

このフラグをセッション レベルで設定するには、次のコマンドを実行します。

SET google_columnar_engine.enable_virtual_columns_scan=on;

式用の仮想列を追加する

よく使用される式をカラム型エンジンに追加するには、psql クライアントを使用して google_columnar_engine_add 関数を呼び出します。これらの式は、データベースにすでに存在する列に加えて、列としてカラム型エンジンに入力されます。

複数の式をカンマ区切り値として指定できます。

    SELECT google_columnar_engine_add(
    relation => 'DB.SCHEMA.TABLE_NAME',
    columns => 'COLUMN_NAME, COLUMN_NAME',
    expressions => 'EXP1, EXP2, EXP3'
    );

次のように置き換えます。

  • DB.SCHEMA: テーブルが保存されているデータベース スキーマ。
  • TABLE_NAME: 列が格納されているテーブルの名前。
  • COLUMN_NAME: 式を含む列の名前。

  • EXP1, EXP2, EXP3: 式のカンマ区切りのリスト。サポートされている JSON 抽出式は -> と ->> です。

    たとえば、public スキーマの employee テーブルに user ->> 'email' 式と user ->> 'name' 式を追加するには、次のクエリを使用します。

        SELECT google_columnar_engine_add(
    relation => 'postgres.public.employee',
    expressions => '"user ->> ''email''", "user ->> ''name''"'
    );

    式の構文:

    • 式全体を単一引用符で囲みます（例: expressions => 'EXP1,EXP2,EXP3'）。
    • 複数の式をカンマで区切ります。
    • 個々の式を二重引用符で囲みます。
    • 式内の単一引用符をエスケープするには、別の単一引用符を使用します。

    たとえば、col -> 'level1' 式と col -> 'level1' ->> 'level2' 式を追加するには、次の形式を使用します。

        expressions => '"col -> ''level1''", "col -> ''level1'' ->> ''level2''"'

式の仮想列の例

この例では、式に仮想列を使用する方法を示します。profile JSONB 列を含む users テーブルを作成し、サンプルデータを入力します。次に、クエリ分析に基づいて、頻繁に使用される profile ->> 'email' 式を google_columnar_engine_add 関数を使用してカラム型エンジンに追加します。カラム型エンジンは、この頻出式を使用して、後続のクエリの最適化を支援します。

この頻出式の例をカラム型エンジンに追加する手順は次のとおりです。

  1. Google Cloud コンソールで、[クラスタ] ページに移動します。

    クラスタに移動

  2. [リソース名] 列でクラスタの名前をクリックします。

  3. ナビゲーション パネルで [AlloyDB Studio] をクリックします。

  4. profile JSONB 列を含む users テーブルを作成するには、次のコマンドを実行します。

    CREATE TABLE users (
id int,
username TEXT,
profile JSONB
);

  5. users テーブルにサンプルデータを入力するには、次のコマンドを実行します。

    INSERT INTO users (id, username, profile)
SELECT
    i,
    'user' || i,
    jsonb_build_object(
        'name', 'User ' || i,
        'email', 'user' || i || '@example.com',
        'active', (i % 2 = 0)
    )
FROM generate_series(1, 100000) AS i;

  6. 頻繁に使用される式を含むクエリのパフォーマンスを向上させるには、profile ->> 'email' 式をカラム型エンジンに追加します。

    SELECT google_columnar_engine_add(
    relation => 'users',
    columns => 'username, profile',
    expressions => '"profile ->> ''email''"'
);

  7. 式の仮想列機能を無効にしてパフォーマンスのベースラインを確立し、頻繁に使用される式を使用するクエリを実行して、クエリの完了にかかる時間を観察します。

    SET google_columnar_engine.enable_virtual_columns_scan=off;

SELECT username
FROM users
WHERE profile->>'email' = 'user50000@example.com';

  8. 式機能の仮想列を有効にします。

    SET google_columnar_engine.enable_virtual_columns_scan=on;

  9. 頻繁に使用される式を使用するクエリを再実行し、クエリの完了にかかる時間を観察します。

    SELECT username
FROM users
WHERE profile->>'email' = 'user50000@example.com';

式の仮想列を有効にすると、クエリの実行時間が短縮されます。

式の仮想列を表示する

特定のテーブルに追加されたすべての式を検索するには、g_columnar_virtual_columns ビューをクエリします。

    SELECT * FROM g_columnar_virtual_columns;

出力は次のようになります。description フィールドには、テーブル（リレーション）に追加されたすべての式が表示されます。

category           | expression
database_name      | testdb
schemas            | {public}
relations          | {users}
description        | profile->>'email'
column_data_type   | text
status             | Usable
last_accessed_time | 2026-02-04 06:25:32.499601+00
num_times_accessed | 1

式用の仮想列を削除する

式を削除するには、google_columnar_engine_drop() 関数を呼び出します。

    SELECT google_columnar_engine_drop(
      relation => 'DB.SCHEMA.TABLE_NAME',
      expressions => 'EXP1, EXP2, EXP3'
    );

EXP1, EXP2, EXP3 は、式を追加するときに使用した形式と同じ形式で、テーブル内の式のカンマ区切りのリストに置き換えます。