Google utilise la technologie IA pour traduire le contenu dans votre langue préférée. Les traductions générées par IA peuvent contenir des erreurs.

Optimiser les requêtes avec des colonnes virtuelles pour les expressions

Les colonnes virtuelles pour les expressions accélèrent les requêtes du moteur de données en colonnes qui évaluent de manière répétée les mêmes expressions, telles que les filtres sur les attributs JSON. Cette fonctionnalité est disponible pour PostgreSQL 16 et versions ultérieures.

Le moteur de données en colonnes peut matérialiser et mettre en cache les résultats des expressions fréquemment utilisées. En précalculant et en stockant ces résultats, AlloyDB évite d'analyser et d'évaluer de manière répétée les mêmes expressions dans plusieurs requêtes. Ce processus améliore les performances des requêtes et réduit la consommation de processeur, en particulier pour les charges de travail analytiques sur de grands ensembles de données.

Voici quelques cas d'utilisation des colonnes virtuelles pour les expressions :

Filtrage sur les attributs JSON : lorsque vous filtrez fréquemment des requêtes en fonction de paires clé/valeur spécifiques dans une colonne JSON.
Évaluation d'expressions complexes : pour les requêtes impliquant des expressions complexes ou nécessitant beaucoup de ressources de calcul.

Lorsque vous activez l'analyse des colonnes virtuelles pour les expressions, les requêtes qui utilisent le moteur de données en colonnes et contiennent des expressions fréquemment utilisées sont optimisées automatiquement. Le moteur de données en colonnes actualise automatiquement ces colonnes virtuelles lorsqu'il actualise les colonnes de base.

Expressions acceptées dans les colonnes virtuelles

AlloyDB est compatible avec les opérateurs -> et ->> sur les colonnes JSON et JSONB dans la version preview (Preview).

Avant de commencer

Activez le moteur de données en colonnes pour votre cluster AlloyDB. Pour en savoir plus, consultez Configurer le moteur de données en colonnes.
Identifiez les expressions fréquemment utilisées dans vos requêtes AlloyDB à l'aide d'Insights sur les requêtes.
Ajoutez les colonnes de base des expressions au moteur de données en colonnes. Pour en savoir plus, consultez Gérer manuellement le contenu du store orienté colonnes.

Activer les colonnes virtuelles pour les expressions

La compatibilité des colonnes virtuelles pour les expressions est activée par défaut (google_columnar_engine.enable_virtual_columns_scan=on). Si cette option a été désactivée précédemment, vous pouvez l'activer pour une instance à l'aide de la commande gcloud alloydb instances update.

Pour utiliser la gcloud CLI, vous pouvez installer et initialiser la Google Cloud CLI, ou utiliser 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"

Remplacez les éléments suivants :

INSTANCE_ID : ID de votre instance AlloyDB.
REGION_ID : région de votre instance AlloyDB.
CLUSTER_ID : ID du cluster de votre instance AlloyDB.
PROJECT_ID : ID de votre projet Google Cloud .

Pour définir cette option au niveau de la session, exécutez la commande suivante :

SET google_columnar_engine.enable_virtual_columns_scan=on;

Ajouter des colonnes virtuelles pour les expressions

Pour ajouter des expressions couramment utilisées au moteur de stockage en colonnes, utilisez le client psql pour appeler la fonction google_columnar_engine_add. Ces expressions sont renseignées dans le moteur de données en colonnes en tant que colonnes, en plus de celles qui existent déjà dans la base de données.

Vous pouvez spécifier plusieurs expressions sous la forme de valeurs séparées par une virgule.

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

Remplacez les éléments suivants :

DB.SCHEMA : schéma de base de données dans lequel votre table est stockée.
TABLE_NAME : nom de la table dans laquelle votre colonne est stockée.
COLUMN_NAME : nom des colonnes qui incluent les expressions.
EXP1, EXP2, EXP3 : liste d'expressions séparées par une virgule. Les expressions d'extraction JSON acceptées sont -> et ->>.

Par exemple, pour ajouter des expressions user ->> 'email' et user ->> 'name' pour la table employee dans le schéma public, utilisez la requête suivante :
```
    SELECT google_columnar_engine_add(
    relation => 'postgres.public.employee',
    expressions => '"user ->> ''email''", "user ->> ''name''"'
    );
```
Syntaxe des expressions :
- Placez l'intégralité de la valeur de l'expression entre guillemets simples (par exemple, expressions => 'EXP1,EXP2,EXP3').
- Séparez les différentes expressions par une virgule.
- Placez chaque expression entre guillemets doubles.
- Échappez les guillemets simples d'une expression en utilisant un autre guillemet simple.
Par exemple, pour ajouter l'expression col -> 'level1' et col -> 'level1' ->> 'level2', utilisez le format suivant :
```
    expressions => '"col -> ''level1''", "col -> ''level1'' ->> ''level2''"'
```

Exemple de colonnes virtuelles pour les expressions

Cet exemple montre comment utiliser la fonctionnalité de colonnes virtuelles pour les expressions. Créez une table users avec une colonne JSONB profile et remplissez-la avec des exemples de données. Ensuite, en fonction de l'analyse des requêtes, ajoutez l'expression profile ->> 'email' fréquemment utilisée au moteur de données en colonnes avec la fonction google_columnar_engine_add. Le moteur de données en colonnes utilise ensuite cette expression fréquente pour optimiser les requêtes ultérieures.

Pour ajouter cet exemple d'expression fréquente au moteur de données en colonnes, procédez comme suit :

Dans la console Google Cloud , accédez à la page Clusters.

accéder aux clusters
Cliquez sur le nom de votre cluster dans la colonne Nom de la ressource.
Dans le volet de navigation, cliquez sur AlloyDB Studio.
Pour créer la table users avec une colonne JSONB profile, exécutez la commande suivante :
```
CREATE TABLE users (
id int,
username TEXT,
profile JSONB
);
```

Pour insérer des exemples de données dans la table users, exécutez la commande suivante :

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;

Pour améliorer les performances de la requête qui inclut l'expression fréquemment utilisée, ajoutez l'expression profile ->> 'email' au moteur de données en colonnes :
```
SELECT google_columnar_engine_add(
    relation => 'users',
    columns => 'username, profile',
    expressions => '"profile ->> ''email''"'
);
```
Désactivez la fonctionnalité de colonnes virtuelles pour les expressions afin d'établir une référence de performances, puis exécutez une requête qui utilise l'expression fréquente et observez le temps nécessaire à l'exécution de la requête.
```
SET google_columnar_engine.enable_virtual_columns_scan=off;

SELECT username
FROM users
WHERE profile->>'email' = 'user50000@example.com';
```
Activez la fonctionnalité de colonnes virtuelles pour les expressions.
```
SET google_columnar_engine.enable_virtual_columns_scan=on;
```
Réexécutez la requête qui utilise l'expression fréquente et observez le temps nécessaire à son exécution.
```
SELECT username
FROM users
WHERE profile->>'email' = 'user50000@example.com';
```

Le temps d'exécution des requêtes est plus rapide après l'activation des colonnes virtuelles pour les expressions.

Afficher les colonnes virtuelles pour les expressions

Pour trouver toutes les expressions ajoutées pour une table spécifique, interrogez la vue g_columnar_virtual_columns :

    SELECT * FROM g_columnar_virtual_columns;

Le résultat ressemble à ce qui suit, où le champ description affiche toutes les expressions ajoutées pour une table (relation) :

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

Supprimer les colonnes virtuelles pour les expressions

Pour supprimer une expression, appelez la fonction google_columnar_engine_drop() :

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

Remplacez EXP1, EXP2, EXP3 par une liste d'expressions séparées par une virgule dans la table, au même format que celui utilisé lors de l'ajout d'expressions.