EXPLAIN — Afficher le plan d'exécution d'une instruction
EXECUTE
EXPLAIN [ ( option [, ...] ) ] instruction
EXPLAIN [ ANALYZE ] [ VERBOSE ] instruction
où option est :
ANALYZE [ boolean ]
VERBOSE [ boolean ]
COSTS [ boolean ]
BUFFERS [ boolean ]
FORMAT { TEXT | XML | JSON | YAML }
Cette commande affiche le plan d'exécution que l'optimiseur de PostgreSQL™ engendre pour l'instruction fournie. Le plan d'exécution décrit le parcours de la (des) table(s) utilisée(s) dans la requête -- parcours séquentiel, parcours d'index, etc. -- . Si plusieurs tables sont référencées, il présente également les algorithmes de jointures utilisés pour rassembler les lignes issues des différentes tables.
La partie la plus importante de l'affichage concerne l'affichage des coûts estimés d'exécution. Ils représentent l'estimation faite par le planificateur des temps d'exécution de la requête (mesurés en unités de récupération de pages sur le disque). Deux nombres sont affichés : le temps de démarrage, écoulé avant que la première ligne soit renvoyée, et le temps d'exécution total, nécessaire au renvoi de toutes les lignes. Pour la plupart des requêtes, le temps qui importe est celui d'exécution totale. Mais dans certains cas, tel que pour une sous-requête dans la clause EXISTS, le planificateur choisira le temps de démarrage le plus court, et non celui d'exécution totale (car, de toute façon, l'exécuteur s'arrête après la récupération d'une ligne). De même, lors de la limitation des résultats à retourner par une clause LIMIT, la planificateur effectue une interpolation entre les deux temps limites pour choisir le plan réellement le moins coûteux.
L'option ANALYZE impose l'exécution de la requête en plus de sa planification. Le temps total d'exécution de chaque nœud du plan (en millisecondes) et le nombre total de lignes effectivement retournées sont ajoutés à l'affichage. C'est utile pour vérifier la véracité des informations fournies par le planificateur.
![[Important]](images/important.png)
Il ne faut pas oublier que l'instruction est réellement exécutée avec l'option ANALYZE. Bien qu'EXPLAIN inhibe l'affichage des retours d'une commande SELECT, les autres effets de l'instruction sont présents. Si EXPLAIN ANALYZE doit être utilisé sur une instruction INSERT, UPDATE, DELETE CREATE TABLE AS ou EXECUTE sans que la commande n'affecte les données, l'approche suivante peut être envisagée :
BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;
Seules les options ANALYZE et VERBOSE peuvent être utilisées et dans cet ordre seulement si la liste d'options entre parenthèses n'est pas utilisé. Avant PostgreSQL™ 9.0, la seule syntaxe supportée était celle sans parenthèses. Les nouvelles options ne seront supportées que par la nouvelle syntaxe, celle avec les parenthèses.
Exécute la commande et affiche les temps d'exécution réels. Ce paramètre est par défaut à FALSE.
Affiche des informations supplémentaires sur le plan. Cela inclut la liste des colonnes en sortie pour chaque nœud du plan, les noms des tables et fonctions avec le nom du schéma, les labels des variables dans les expressions avec des alias de tables et le nom de chaque trigger pour lesquels les statistiques sont affichées. Ce paramètre est par défaut à FALSE.
Inclut des informations sur le coût estimé au démarrage et au total de chaque nœud du plan, ainsi que le nombre estimé de lignes et la largeur estimée de chaque ligne. Ce paramètre est par défaut à TRUE.
Inclut des informations sur l'utilisation des blocs. Cela inclut le nombre de lecture de blocs partagés en cache, sur le disque ainsi que le nombre de blocs écrits. Cela inclut aussi le nombre de blocs locaux lus dans le cache, sur le disque et le nombre de blocs locaux écrits. Enfin, cela inclut le nombre de blocs temporaires lus et écrits. Les blocs partagés, les blocs locaux et les blocs temporaires contiennent respectivement des tables et des index, des tables temporaires et des index temporaires, et les blocs disques utilisés dans les tris et les plans matérialisés. Le nombre de blocs affichés pour un nœud de haut niveau inclut ceux utilisés par tous ses enfants. Dans le format texte, seuls les valeurs différentes de zéro sont affichés. Ce paramètre pourrait seulement être utilisé avec le paramètre ANALYZE. Il vaut par défaut FALSE.
Indique le format de sortie. Il peut valoir TEXT, XML, JSON ou YAML. Toutes les sorties contiennent les mêmes informations, mais les programmes pourront plus facilement traiter les sorties autres que TEXT. Ce paramètre est par défaut à TEXT.
Spécifie si l'option sélectionnée doit être activée ou désactivée. Vous pouvez écrire TRUE, ON ou 1 pour activer l'option, et FALSE, OFF ou 0 pour la désactiver. La valeur de type boolean peut aussi être omise, auquel cas la valeur sera TRUE.
Toute instruction SELECT, INSERT, UPDATE, DELETE, VALUES EXECUTE, DECLARE ou CREATE TABLE AS dont le plan d'exécution est souhaité.
La documentation sur l'utilisation faite par l'optimiseur des informations de coût est assez réduite dans PostgreSQL™. On peut se référer à Section 14.1, « Utiliser EXPLAIN » pour plus d'informations.
Pour que le planificateur de requêtes de PostgreSQL™ puisse prendre des décisions en connaissance de cause, l'instruction ANALYZE(7) doit avoir été exécutée afin d'enregistrer les statistiques de distribution des données dans la table. Si cela n'a pas été fait, (ou si la distribution statistique des données dans la table a changé de manière significative depuis la dernière exécution de la commande ANALYZE) les coûts estimés risquent de ne pas refléter les propriétés réelles de la requête. De ce fait, un plan de requête inférieur risque d'être choisi.
Pour mesurer le coût d'exécution du plan d'exécution, l'implémentation actuelle de EXPLAIN ANALYZE peut ajouter un délai considérable à l'exécution de la requête à cause du profilage. De ce fait, exécuter EXPLAIN ANALYZE sur une requête peut prendre bien plus de temps que d'exécuter la requête seule. Ce délai dépend de la nature de la requête.
Afficher le plan d'une requête simple sur une table d'une seule colonne de type integer et 10000 lignes :
EXPLAIN SELECT * FROM foo;
QUERY PLAN
---------------------------------------------------------
Seq Scan on foo (cost=0.00..155.00 rows=10000 width=4)
(1 row)
Voici le même plan, mais formaté avec JSON :
EXPLAIN (FORMAT JSON) SELECT * FROM foo;
QUERY PLAN
--------------------------------
[ +
{ +
"Plan": { +
"Node Type": "Seq Scan",+
"Relation Name": "foo", +
"Alias": "foo", +
"Startup Cost": 0.00, +
"Total Cost": 155.00, +
"Plan Rows": 10000, +
"Plan Width": 4 +
} +
} +
]
(1 row)
S'il existe un index et que la requête contient une condition WHERE indexable, EXPLAIN peut afficher un plan différent :
EXPLAIN SELECT * FROM foo WHERE i = 4;
QUERY PLAN
--------------------------------------------------------------
Index Scan using fi on foo (cost=0.00..5.98 rows=1 width=4)
Index Cond: (i = 4)
(2 rows)
Voici le même plan, mais formaté avec YAML :
EXPLAIN (FORMAT YAML) SELECT * FROM foo WHERE i='4';
QUERY PLAN
-------------------------------
- Plan: +
Node Type: "Index Scan" +
Scan Direction: "Forward"+
Index Name: "fi" +
Relation Name: "foo" +
Alias: "foo" +
Startup Cost: 0.00 +
Total Cost: 5.98 +
Plan Rows: 1 +
Plan Width: 4 +
Index Cond: "(i = 4)"
(1 row)
L'obtention du format XML est laissé en exercice au lecteur.
Voici le même plan avec les coûts supprimés :
EXPLAIN (COSTS FALSE) SELECT * FROM foo WHERE i = 4;
QUERY PLAN
----------------------------
Index Scan using fi on foo
Index Cond: (i = 4)
(2 rows)
Exemple de plan de requête pour une requête utilisant une fonction d'agrégat :
EXPLAIN SELECT sum(i) FROM foo WHERE i < 10;
QUERY PLAN
---------------------------------------------------------------------
Aggregate (cost=23.93..23.93 rows=1 width=4)
-> Index Scan using fi on foo (cost=0.00..23.92 rows=6 width=4)
Index Cond: (i < 10)
(3 rows)
Exemple d'utilisation de EXPLAIN EXECUTE pour afficher le plan d'exécution d'une requête préparée :
PREPARE query(int, int) AS SELECT sum(bar) FROM test
WHERE id > $1 AND id < $2
GROUP BY foo;
EXPLAIN ANALYZE EXECUTE query(100, 200);
QUERY PLAN
-------------------------------------------------------------------------------------
HashAggregate (cost=39.53..39.53 rows=1 width=8) (actual time=0.661..0.672 rows=7 loops=1)
-> Index Scan using test_pkey on test (cost=0.00..32.97 rows=1311 width=8) (actual time=0.050..0.395 rows=99 loops=1)
Index Cond: ((id > $1) AND (id < $2))
Total runtime: 0.851 ms
(4 rows)
Il est évident que les nombres présentés ici dépendent du contenu effectif des tables impliquées. De plus, les nombres, et la stratégie sélectionnée elle-même, peuvent différer en fonction de la version de PostgreSQL™ du fait des améliorations apportées au planificateur. Il faut également savoir que la commande ANALYZE calcule les statistiques des données à partir d'extraits aléatoires ; il est de ce fait possible que les coûts estimés soient modifiés après l'exécution de cette commande, alors même la distribution réelle des données dans la table n'a pas changé.