Otimizar consultas com colunas virtuais para expressões

Nesta página, descrevemos como usar colunas virtuais para expressões no mecanismo colunar e acelerar as consultas.

O mecanismo colunar pode materializar e armazenar em cache os resultados de expressões usadas com frequência. Ao pré-calcular 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 a performance 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 computacionalmente intensivas.

Quando você ativa as colunas virtuais para a verificação de 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 (Prévia).

Antes de começar

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

  2. Identifique as expressões usadas com frequência nas suas consultas do AlloyDB usando os insights de consultas.

  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

É possível ativar o suporte a colunas virtuais para expressões em uma instância usando o comando gcloud beta alloydb instances update.

Para usar a gcloud CLI, você pode instalar e inicializar a CLI do Google Cloud ou usar o Cloud Shell.

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

Substitua:

  • INSTANCE_ID: o ID da sua instância do AlloyDB.
  • REGION_ID: a região da sua instância do AlloyDB.
  • CLUSTER_ID: o ID do cluster da sua 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 de 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 com 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' à 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 individual entre aspas duplas.
    • Use outra aspa simples para evitar aspas simples em uma expressão.

    Por exemplo, para adicionar as expressões 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 profile JSONB e preencha-a com dados de amostra. Em seguida, com base na análise de consultas, 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 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 comando:

    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. Execute uma consulta que use 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';

  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 necessário para a conclusão.

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

O tempo 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 a uma tabela (relação).

SELECT * FROM g_columnar_virtual_columns;
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.