使用表达式的虚拟列优化查询

表达式虚拟列可加快列式引擎查询的速度，这些查询会重复评估相同的表达式，例如对 JSON 属性的过滤条件。此功能适用于 PostgreSQL 16 及更高版本。

列式引擎可以具体化并缓存常用表达式的结果。通过预先计算并存储这些结果，AlloyDB 可避免在多个查询中重复解析和评估相同的表达式。此过程可提高查询性能并减少 CPU 消耗，尤其适用于大型数据集上的分析工作负载。

表达式虚拟列的一些应用场景如下：

  • 过滤 JSON 属性：当您经常根据 JSON 列中的特定键值对过滤查询时。
  • 复杂表达式求值：适用于涉及复杂或计算密集型表达式的查询。

启用表达式扫描的虚拟列后，使用列式引擎且包含常用表达式的查询会自动优化。列式引擎在刷新基列时会自动刷新这些虚拟列。

虚拟列中支持的表达式

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 表，并使用示例数据填充该表。然后，根据查询分析，使用 google_columnar_engine_add 函数将常用的 profile ->> 'email' 表达式添加到列式引擎。然后，列式引擎会使用此频繁表达式来帮助优化后续查询。

如需将此示例频繁表达式添加到列式引擎，请按照以下步骤操作：

  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 替换为表中以英文逗号分隔的表达式列表，格式与添加表达式时使用的格式相同。