Cette page a été traduite par l'API Cloud Translation.

Obtenir et analyser les plans d'explication AlloyDB

Vous pouvez identifier les goulots d'étranglement des performances et optimiser vos opérations de base de données AlloyDB pour PostgreSQL en obtenant et en analysant les plans d'exécution. Un plan d'exécution ou un plan EXPLAIN est une représentation détaillée de la façon dont votre moteur de base de données AlloyDB prévoit d'exécuter une requête SQL. Le plan d'exécution comprend un arbre de nœuds qui décrit la séquence d'opérations (analyses de tables, jointures, tris et agrégations, par exemple) que la base de données AlloyDB effectue pour récupérer les données demandées. Chaque étape de ce plan est appelée nœud.

Un plan d'exécution est obtenu à l'aide de la commande EXPLAIN, qui renvoie le plan que le planificateur de requêtes AlloyDB génère pour une instruction SQL donnée. Un planificateur de requêtes, également appelé optimiseur, détermine la manière la plus efficace d'exécuter une requête SQL donnée.

Les plans d'exécution comprennent les composants suivants :

Nœuds du plan : ils représentent les différentes étapes de l'exécution de la requête, comme une opération d'analyse, de jointure ou de tri.
Temps d'exécution : le plan EXPLAIN inclut le temps d'exécution estimé ou réel pour chaque étape, ce qui vous aide à identifier les goulots d'étranglement de la base de données.
Utilisation du tampon : indique la quantité de données lues à partir du disque par rapport au cache, ce qui permet d'identifier les problèmes de lecture de disque.
Paramètres : le plan indique les paramètres qui sont effectifs lors de l'exécution de la requête.

PostgreSQL, et par extension AlloyDB, sont compatibles avec les plans d'exécution pour les instructions suivantes :

SELECT
INSERT
UPDATE
DECLARE CURSOR
CREATE AS
CREATE MATERIALIZED VIEW
REFRESH MATERIALIZED VIEW
EXECUTE

Avant de commencer

Vous devez disposer d'un cluster et d'une instance AlloyDB. Pour en savoir plus, consultez Créer un cluster et son instance principale.

Générer un plan d'exécution

Vous générez un plan d'exécution à partir d'une application cliente telle que psql, pgAdmin ou DBeaver. AlloyDB permet de générer des plans d'exécution au format texte ou JSON.

Pour générer un plan d'exécution, procédez comme suit :

Connectez un client psql à une instance.

Pour générer un plan d'exécution au format texte, exécutez la commande suivante :

SET enable_ultra_fast_cache_explain_output TO ON; /* AlloyDB Specific Command */
EXPLAIN (analyze, verbose, columnar_engine, costs, settings, buffers, wal, timing, summary, format text)

Pour générer un plan d'exécution au format JSON, exécutez la commande suivante :
```
SET enable_ultra_fast_cache_explain_output TO ON; /* AlloyDB Specific Command */
EXPLAIN (analyze, verbose, columnar_engine, costs, settings, buffers, wal, timing, summary, format json)
```
La commande EXPLAIN inclut toutes les options disponibles (analyze, verbose, columnar_engine, costs, settings, buffers, wal, timing et summary) pour générer un plan d'exécution détaillé pour une requête donnée au format texte ou JSON. L'option analyze signifie que la requête est exécutée pour fournir des statistiques d'exécution réelles ainsi que les estimations du planificateur de requêtes.

Afficher et analyser les données du plan EXPLAIN

Une fois que vous avez obtenu un plan d'exécution, vous pouvez afficher et analyser les résultats.

Par défaut, la sortie EXPLAIN affiche l'activité des requêtes côté serveur. Pour mesurer le temps d'aller-retour de bout en bout, utilisez l'option /timing dans psql et exportez les résultats vers /dev/null.

Pour afficher le plan d'exécution que vous avez généré, utilisez la commande EXPLAIN avant votre requête SQL.

EXPLAIN SELECT... : affiche le plan que l'optimiseur choisirait sans exécuter la requête.
EXPLAIN ANALYZE SELECT... : exécute la requête et affiche à la fois le plan prédit et les statistiques d'exécution réelles, y compris les durées d'exécution réelles et le nombre de lignes.

EXPLAIN sans ANALYZE

Pour afficher les coûts de requête estimés du planificateur de requêtes, exécutez une instruction EXPLAIN sans l'option ANALYZE .

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27)
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483
(3 rows)

Le résultat du plan inclut les données suivantes :

cost = 0.00..1735481.00 : le premier nombre indique le coût de récupération de la première ligne. Le deuxième chiffre indique le coût de récupération de la dernière ligne.
rows = 100000000 : il s'agit du nombre de lignes estimé renvoyé par la requête.
width = 27 : il s'agit de la largeur estimée de la ligne renvoyée, ce qui vous aide à comprendre les blocs auxquels vous avez accédé.

Option ANALYSER

Pour afficher les statistiques d'exécution réelles ainsi que les estimations d'exécution, ajoutez l'option ANALYZE.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE) select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.165..9342.424 rows=100000001 loops=1)
 Planning Time: 0.025 ms
 Execution Time: 13674.794 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483
(5 rows)

Le résultat du plan inclut les données suivantes :

actual time (in ms) = 0.165..9342.424 : indique le temps réel nécessaire pour renvoyer la première ligne et le temps total nécessaire pour renvoyer toutes les lignes.
rows = 100000001 : il s'agit du nombre réel de lignes renvoyées.
loops = 1 : cette valeur est importante pour les nœuds de boucle imbriquée. Il indique la durée moyenne par boucle si loops est supérieur à 1.
Temps de planification : 0,025 ms : indique le temps nécessaire au planificateur pour déterminer le chemin d'exécution.
Temps d'exécution : 13 674,794 ms : indique le temps d'exécution après que le planificateur a déterminé le chemin d'accès.
Temps d'exécution total : somme de Planning Time et de Execution Time. (0,025 + 13 674,794 = 13 674,819)

Option VERBOSE

Pour ajouter des informations au plan d'exécution, utilisez l'option VERBOSE. Dans l'exemple suivant, l'utilisation de VERBOSE ajoute des qualifications de schéma aux noms de tables et affiche un identifiant de requête interne que vous pouvez utiliser pour corréler votre requête avec d'autres outils de surveillance.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE) select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.164..6568.938 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
 Query Identifier: -5175350694118300766
 Planning Time: 0.027 ms
 Execution Time: 10875.894 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483

Le résultat du plan inclut les données suivantes :

Nœud de sortie : liste les colonnes incluses dans la requête. Il arrive que le planificateur inclue plus de colonnes que demandé s'il estime que les supprimer serait plus coûteux.
Identifiant de la requête : identifiant PostgreSQL qui correspond à pg_stat_statements.
ID de requête AlloyDB : identifiant de requête AlloyDB pouvant être utilisé pour corréler les informations sur les insights sur les requêtes

Option COLUMNAR ENGINE

Pour afficher des informations sur le moteur en colonnes AlloyDB, ajoutez l'option COLUMNAR_ENGINE.

Si la table n'est pas présente dans le moteur columnar, consultez la colonne Vérification columnar dans le plan d'explication suivant pour connaître l'état.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE) select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.009..6328.154 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
   Columnar Check: table is not in the columnar store
 Query Identifier: -5175350694118300766
 Planning Time: 0.027 ms
 Execution Time: 10673.310 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483

Si la table est présente dans le moteur de données en colonnes et que le format en colonnes est utilisé, une analyse personnalisée est indiquée, ainsi que des statistiques sur l'utilisation du moteur de données en colonnes.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE) select * from public.index_advisor_test where product_id = 1;
                                   QUERY PLAN
---------------------------------------------------------------------------------
Append  (cost=20.00..27438.78 rows=1166668 width=27) (actual time=0.066..377.029 rows=1000290 loops=1)
   ->  Custom Scan (columnar scan) on public.index_advisor_test  (cost=20.00..27437.66 rows=1166667 width=27) (actual time=0.065..296.904 rows=1000290 loops=1)
         Output: id, value, product_id, effective_date
         Filter: (index_advisor_test.product_id = 1)
         Rows Removed by Columnar Filter: 98999711
         Bytes fetched from storage cache: 774835915
         Columnar cache search mode: native
         Swap-in Time: 92.708 ms
   ->  Seq Scan on public.index_advisor_test  (cost=0.00..1.11 rows=1 width=27) (never executed)
         Output: id, value, product_id, effective_date
         Filter: (index_advisor_test.product_id = 1)
 Query Identifier: -4660018746142248761
 Planning Time: 0.217 ms
 Execution Time: 421.114 ms
 AlloyDB query id: 13855683355620344431
 AlloyDB plan id: 2126918133221480510

Le résultat du plan inclut les données suivantes :

Filtre de requête (prédicat) : affiche le filtre appliqué, le cas échéant.
Lignes supprimées par le filtre en colonnes : indique le nombre de lignes supprimées par le filtre en colonnes.
Octets extraits du cache de stockage : indique le nombre d'octets extraits du cache de stockage.
Temps d'échange : temps nécessaire pour échanger les données du cache de déversement en colonnes (SSD) si la relation ne tient pas dans la mémoire.

Option "PARAMÈTRES"

Pour afficher les paramètres de session, de base de données ou généraux non définis par défaut que le planificateur utilise, ajoutez l'option SETTINGS.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE,COSTS,SETTINGS) select * from public.index_advisor_test;

                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.007..6366.249 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
   Columnar Check: table is not in the columnar store
 Settings: effective_cache_size = '19690120kB', random_page_cost = '1.1', work_mem = '256MB'
 Query Identifier: -5175350694118300766
 Planning Time: 0.027 ms
 Execution Time: 10727.068 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483

Option BUFFERS

Pour afficher des informations sur la source de données, utilisez le mot clé BUFFERS. Le nombre BUFFERS est cumulatif pour toutes les étapes, et pas seulement pour une étape spécifique du plan.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE,COSTS,SETTINGS, BUFFERS) select * from public.effective_io_concurrency_test;

                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.effective_io_concurrency_test  (cost=0.00..1844356.96 rows=100058296 width=31) (actual time=0.111..10007.193 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
   Buffers: shared hit=1185 read=842625 dirtied=1, ultra fast cache hit=842625
   I/O Timings: shared read=2588.597
   Columnar Check: table is not in the columnar store
 Settings: effective_cache_size = '19690120kB', random_page_cost = '1.1', work_mem = '256MB'
 Query Identifier: -5987221491374921441
 Planning:
   Buffers: shared hit=58 read=2, ultra fast cache hit=2
   I/O Timings: shared read=0.215
 Planning Time: 0.410 ms
 Execution Time: 14825.271 ms
 AlloyDB query id: 12754549874439071326
 AlloyDB plan id: 13656575252012920931

Les informations sur le tampon incluent les éléments suivants :

shared hit : nombre de blocs de données de 8 ko trouvés dans le cache de tampon partagé principal de PostgreSQL.
shared read : nombre de blocs de données de 8 ko lus à partir du système d'exploitation. Cela indique souvent des E/S disque.
dirtied : nombre de blocs précédemment non modifiés que la requête a modifiés (modifications de la carte de visibilité).
written : nombre de blocs précédemment modifiés et supprimés du cache par ce backend lors du traitement des requêtes, généralement en raison de modifications apportées aux bits d'indication ou à la carte de visibilité, et vidés sur le disque.
ultra fast cache hit : nombre de blocs récupérés à partir du cache ultrarapide.
Timing des E/S : durée des E/S de disque ou de SSD en millisecondes.
Planification : activité de mise en mémoire tampon pendant la phase de planification, comme la lecture des métadonnées ou des statistiques des tables du catalogue.
Temps passé sur les E/S lors de la planification : affiche le temps d'E/S si des métadonnées doivent être lues à partir du disque.

Option WAL

Pour fournir des informations sur l'activité de journalisation WAL, utilisez l'option WAL .

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE,COSTS,SETTINGS, BUFFERS, WAL) select * from public.effective_io_concurrency_test;

                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.effective_io_concurrency_test  (cost=0.00..1844356.96 rows=100058296 width=31) (actual time=0.010..10147.314 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
   Buffers: shared hit=1185 read=842625 dirtied=1, ultra fast cache hit=842625
   I/O Timings: shared read=2590.410
   WAL: records=18 bytes=5178
   Columnar Check: table is not in the columnar store
 Settings: effective_cache_size = '19690120kB', random_page_cost = '1.1', work_mem = '256MB'
 Query Identifier: -5987221491374921441
 Planning Time: 0.030 ms
 Execution Time: 15033.004 ms
 AlloyDB query id: 12754549874439071326
 AlloyDB plan id: 13656575252012920931

Les informations sur le WAL incluent les éléments suivants :

Enregistrements WAL : nombre d'enregistrements WAL lus pour assurer la cohérence.
Octets WAL : nombre d'octets lus à partir du journal WAL pour assurer la cohérence.
Enregistrements modifiés, mais pas encore enregistrés : cela indique les enregistrements qui ont été modifiés, mais pas encore enregistrés.

Étapes suivantes

En savoir plus sur les types de nœuds du plan d'exécution