使用運算式的虛擬資料欄最佳化查詢

運算式的虛擬資料欄可加快資料欄引擎查詢的速度，重複評估相同的運算式，例如 JSON 屬性的篩選器。這項功能適用於 PostgreSQL 16 以上版本。

資料欄引擎可以具體化及快取常用運算式的結果。AlloyDB 會預先計算並儲存這些結果，避免在多個查詢中重複剖析及評估相同的運算式。這項程序可提升查詢效能並減少 CPU 消耗，特別是大型資料集的分析工作負載。

運算式虛擬資料欄的用途如下：

  • 篩選 JSON 屬性：經常根據 JSON 資料欄中的特定鍵/值組合篩選查詢。
  • 複雜運算式評估：適用於涉及複雜或運算密集運算式的查詢。

啟用運算式虛擬資料欄掃描功能後，系統會自動最佳化使用資料欄引擎且包含常用運算式的查詢。資料欄引擎重新整理基本資料欄時，會自動重新整理這些虛擬資料欄。

虛擬資料欄支援的運算式

AlloyDB 支援在 JSON 和 JSONB 資料欄上使用 -> 和 ->> 運算子 (預先發布版)。

事前準備

  1. 為 AlloyDB 叢集啟用資料欄引擎。詳情請參閱「設定資料欄引擎」。

  2. 使用查詢洞察，找出 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''"'

運算式虛擬資料欄範例

這個範例說明如何使用運算式虛擬資料欄功能。建立 users 資料表，其中包含 profile JSONB 資料欄，並填入範例資料。接著，根據查詢分析，使用 google_columnar_engine_add 函式將常用的 profile ->> 'email' 運算式新增至資料欄引擎。接著，資料欄引擎會使用這個常用運算式，協助最佳化後續查詢。

如要將這個範例常用運算式新增至資料欄引擎，請按照下列步驟操作：

  1. 前往 Google Cloud 控制台的「Clusters」(叢集) 頁面。

    前往「Clusters」(叢集) 頁面

  2. 在「Resource name」(資源名稱) 欄中，按一下叢集名稱。

  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 替換為表格中以半形逗號分隔的運算式清單，格式與新增運算式時使用的格式相同。