Optimiza las consultas con columnas virtuales para expresiones

Las columnas virtuales para expresiones aceleran las consultas del motor de columnas que evalúan de forma repetida las mismas expresiones, como los filtros en los atributos JSON. Esta función está disponible para PostgreSQL 16 y versiones posteriores.

El motor de columnas puede materializar y almacenar en caché los resultados de las expresiones de uso frecuente. Al procesar previamente y almacenar estos resultados, AlloyDB evita el análisis y la evaluación repetidos de las mismas expresiones en varias consultas. Este proceso mejora el rendimiento de las consultas y reduce el consumo de CPU, en especial para las cargas de trabajo analíticas en conjuntos de datos grandes.

Estos son algunos casos de uso de las columnas virtuales para expresiones:

  • Filtrado por atributos JSON: Cuando filtras consultas con frecuencia según pares clave-valor específicos dentro de una columna JSON.
  • Evaluación de expresiones complejas: para consultas que involucran expresiones complejas o que requieren un uso intensivo de recursos de procesamiento.

Cuando habilitas las columnas virtuales para el análisis de expresiones, las consultas que usan el motor de columnas y contienen expresiones de uso frecuente se optimizan automáticamente. El motor de columnas actualiza automáticamente estas columnas virtuales cuando actualiza las columnas base.

Expresiones admitidas en columnas virtuales

AlloyDB admite los operadores -> y ->> en las columnas JSON y JSONB en la versión preliminar (Preview).

Antes de comenzar

  1. Habilitar el motor de columnas para tu clúster de AlloyDB Para obtener más información, consulta Configura el motor de columnas.

  2. Identifica las expresiones que se usan con frecuencia en tus consultas de AlloyDB con las estadísticas de consultas.

  3. Agrega las columnas base de expresiones al motor de columnas. Para obtener más información, consulta Administra el contenido del almacén de columnas de forma manual.

Habilita columnas virtuales para expresiones

La compatibilidad con columnas virtuales para expresiones está habilitada de forma predeterminada (google_columnar_engine.enable_virtual_columns_scan=on). Si esta marca se inhabilitó anteriormente, puedes habilitarla para una instancia con el comando gcloud alloydb instances update.

Para usar gcloud CLI, puedes instalar e inicializar Google Cloud CLI, o bien puedes usar 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"

Reemplaza lo siguiente:

  • INSTANCE_ID: Es el ID de tu instancia de AlloyDB.
  • REGION_ID: Es la región de tu instancia de AlloyDB.
  • CLUSTER_ID: Es el ID del clúster de tu instancia de AlloyDB.
  • PROJECT_ID: Es el ID del proyecto de Google Cloud .

Para establecer esta marca a nivel de la sesión, ejecuta lo siguiente:

SET google_columnar_engine.enable_virtual_columns_scan=on;

Agrega columnas virtuales para expresiones

Para agregar expresiones de uso frecuente al motor de columnas, usa el cliente psql para llamar a la función google_columnar_engine_add. Estas expresiones se propagan en el motor de columnas como columnas, además de las que ya existen en la base de datos.

Puedes especificar varias expresiones como valores separados por comas.

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

Reemplaza lo siguiente:

  • DB.SCHEMA: Es el esquema de la base de datos en el que se almacena la tabla.
  • TABLE_NAME: Es el nombre de la tabla en la que se almacena tu columna.
  • COLUMN_NAME: Es el nombre de las columnas que incluyen las expresiones.

  • EXP1, EXP2, EXP3: Es una lista de expresiones separadas por comas. Las expresiones de extracción de JSON admitidas son -> y ->>.

    Por ejemplo, para agregar expresiones user ->> 'email' y user ->> 'name' para la tabla employee en el esquema public, usa la siguiente consulta:

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

    Sintaxis de expresiones:

    • Encierra todo el valor de la expresión entre comillas simples; por ejemplo, expressions => 'EXP1,EXP2,EXP3'.
    • Separa las distintas expresiones con comas.
    • Encierra cada expresión individual entre comillas dobles.
    • Agrega otra comilla simple para marcar como escape cualquier comilla simple en una expresión.

    Por ejemplo, para agregar la expresión col -> 'level1' y col -> 'level1' ->> 'level2', usa el siguiente formato:

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

Ejemplo de columnas virtuales para expresiones

En este ejemplo, se muestra cómo usar la función de columnas virtuales para expresiones. Crea una tabla users con una columna JSONB profile y propágala con datos de muestra. Luego, según el análisis de consultas, agrega la expresión profile ->> 'email' que se usa con frecuencia al motor de columnas con la función google_columnar_engine_add. Luego, el motor de columnas usa esta expresión frecuente para ayudar a optimizar las consultas posteriores.

Para agregar esta expresión frecuente de ejemplo al motor de columnas, sigue estos pasos:

  1. En la consola de Google Cloud , ve a la página Clústeres.

    Ir a los clústeres

  2. Haz clic en el nombre de tu clúster en la columna Nombre del recurso.

  3. En el panel de navegación, haz clic en AlloyDB Studio.

  4. Para crear la tabla users con una columna JSONB profile, ejecuta lo siguiente:

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

  5. Para propagar la tabla users con datos de muestra, ejecuta lo siguiente:

    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 mejorar el rendimiento de la consulta que incluye la expresión de uso frecuente, agrega la expresión profile ->> 'email' al motor de columnas:

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

  7. Inhabilita la función de columnas virtuales para expresiones para establecer un modelo de referencia de rendimiento, ejecuta una consulta que use la expresión frecuente y observa el tiempo que tarda en completarse.

    SET google_columnar_engine.enable_virtual_columns_scan=off;

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

  8. Habilita la función de columnas virtuales para expresiones.

    SET google_columnar_engine.enable_virtual_columns_scan=on;

  9. Vuelve a ejecutar la consulta que usa la expresión frecuente y observa el tiempo que tarda en completarse.

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

El tiempo de ejecución de la consulta es más rápido después de habilitar las columnas virtuales para las expresiones.

Visualiza columnas virtuales para expresiones

Para encontrar todas las expresiones agregadas para una tabla específica, consulta la vista g_columnar_virtual_columns:

    SELECT * FROM g_columnar_virtual_columns;

El resultado es similar al siguiente, en el que el campo description muestra todas las expresiones agregadas para una tabla (relación):

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

Quita las columnas virtuales para las expresiones

Para quitar una expresión, llama a la función google_columnar_engine_drop():

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

Reemplaza EXP1, EXP2, EXP3 por una lista separada por comas de expresiones en la tabla en el mismo formato que se usa cuando se agregan expresiones.