Otimizar consultas com colunas virtuais para expressões

As colunas virtuais para expressões aceleram as consultas do mecanismo colunar que avaliam repetidamente as mesmas expressões, como filtros em atributos JSON. Esse recurso está disponível para o PostgreSQL 16 e versões mais recentes.

O mecanismo colunar pode materializar e armazenar em cache os resultados de expressões usadas com frequência. Ao pré-computar e armazenar esses resultados, o AlloyDB evita a análise e a avaliação repetidas das mesmas expressões em várias consultas. Esse processo melhora o desempenho da consulta e reduz o consumo de CPU, especialmente para cargas de trabalho analíticas em grandes conjuntos de dados.

Confira alguns casos de uso de colunas virtuais para expressões:

  • Filtragem em atributos JSON:quando você filtra consultas com frequência com base em pares de chave-valor específicos em uma coluna JSON.
  • Avaliação de expressões complexas:para consultas que envolvem expressões complexas ou que exigem muitos recursos de computação.

Quando você ativa a verificação de colunas virtuais para expressões, as consultas que usam o mecanismo colunar e contêm expressões usadas com frequência são otimizadas automaticamente. O mecanismo colunar atualiza automaticamente essas colunas virtuais quando atualiza as colunas de base.

Expressões compatíveis em colunas virtuais

O AlloyDB é compatível com os operadores -> e ->> em colunas JSON e JSONB na versão de pré-lançamento.

Antes de começar

  1. Ativar o mecanismo colunar para o cluster do AlloyDB. Para mais informações, consulte Configurar o mecanismo colunar.

  2. Identifique expressões usadas com frequência nas consultas do AlloyDB usando o Query Insights.

  3. Adicione as colunas de base das expressões ao mecanismo colunar. Para mais informações, consulte Gerenciar manualmente o conteúdo do repositório de colunas.

Ativar colunas virtuais para expressões

O suporte a colunas virtuais para expressões é ativado por padrão (google_columnar_engine.enable_virtual_columns_scan=on). Se essa flag tiver sido desativada anteriormente, será possível ativá-la para uma instância usando o comando gcloud alloydb instances update.

Para usar a CLI gcloud, você pode instalar e inicializar a Google Cloud CLI ou usar o 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"

Substitua:

  • INSTANCE_ID: o ID da instância do AlloyDB.
  • REGION_ID: a região da instância do AlloyDB.
  • CLUSTER_ID: o ID do cluster da instância do AlloyDB.
  • PROJECT_ID: o ID do projeto Google Cloud .

Para definir essa flag no nível da sessão, execute o seguinte:

SET google_columnar_engine.enable_virtual_columns_scan=on;

Adicionar colunas virtuais para expressões

Para adicionar expressões usadas com frequência ao mecanismo colunar, use o cliente psql para chamar a função google_columnar_engine_add. Essas expressões são preenchidas no mecanismo colunar como colunas, além das que já existem no banco de dados.

É possível especificar várias expressões como valores separados por vírgula.

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

Substitua:

  • DB.SCHEMA: o esquema do banco de dados em que a tabela está armazenada.
  • TABLE_NAME: o nome da tabela em que a coluna está armazenada.
  • COLUMN_NAME: o nome das colunas que incluem as expressões.

  • EXP1, EXP2, EXP3: uma lista separada por vírgulas de expressões. As expressões de extração JSON compatíveis são -> e ->>.

    Por exemplo, para adicionar expressões user ->> 'email' e user ->> 'name' para a tabela employee no esquema public, use a seguinte consulta:

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

    Sintaxe da expressão:

    • Coloque todo o valor da expressão entre aspas simples, por exemplo, expressions => 'EXP1,EXP2,EXP3'.
    • Separe várias expressões com vírgulas.
    • Coloque cada expressão entre aspas duplas.
    • Use aspas simples para fazer o escape de qualquer aspa simples em uma expressão.

    Por exemplo, para adicionar a expressão col -> 'level1' e col -> 'level1' ->> 'level2', use o seguinte formato:

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

Exemplo de colunas virtuais para expressões

Este exemplo demonstra como usar o recurso de colunas virtuais para expressões. Crie uma tabela users com uma coluna JSONB profile e preencha-a com dados de amostra. Em seguida, com base na análise de consulta, adicione a expressão profile ->> 'email' usada com frequência ao mecanismo colunar com a função google_columnar_engine_add. Em seguida, o mecanismo colunar usa essa expressão frequente para ajudar a otimizar as consultas subsequentes.

Para adicionar esse exemplo de expressão frequente ao mecanismo colunar, siga estas etapas:

  1. No Google Cloud console, acesse a página Clusters.

    Acessar Clusters

  2. Clique no nome do cluster na coluna Nome do recurso.

  3. No painel de navegação, clique em AlloyDB Studio.

  4. Para criar a tabela users com uma coluna JSONB profile, execute o seguinte:

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

  5. Para preencher a tabela users com dados de amostra, execute o seguinte:

    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. Para melhorar o desempenho da consulta que inclui a expressão usada com frequência, adicione a expressão profile ->> 'email' ao mecanismo colunar:

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

  7. Desativar o recurso de colunas virtuais para expressões para estabelecer uma referência de desempenho, executar uma consulta que use a expressão frequente e observar o tempo que a consulta leva para ser concluída.

    SET google_columnar_engine.enable_virtual_columns_scan=off;

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

  8. Ative o recurso de colunas virtuais para expressões.

    SET google_columnar_engine.enable_virtual_columns_scan=on;

  9. Execute novamente a consulta que usa a expressão frequente e observe o tempo que ela leva para ser concluída.

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

O ambiente de execução da consulta é mais rápido depois que você ativa as colunas virtuais para expressões.

Ver colunas virtuais para expressões

Para encontrar todas as expressões adicionadas a uma tabela específica, consulte a visualização g_columnar_virtual_columns:

    SELECT * FROM g_columnar_virtual_columns;

A saída é semelhante à seguinte, em que o campo description mostra todas as expressões adicionadas para uma tabela (relação):

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

Remover colunas virtuais para expressões

Para remover uma expressão, chame a função google_columnar_engine_drop():

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

Substitua EXP1, EXP2, EXP3 por uma lista separada por vírgulas de expressões na tabela no mesmo formato usado ao adicionar expressões.