EXPLAIN — Afficher le plan d'exécution d'une instruction
EXPLAIN [ (option
[, ...] ) ]instruction
EXPLAIN [ ANALYZE ] [ VERBOSE ]instruction
oùoption
est : ANALYZE [boolean
] VERBOSE [boolean
] COSTS [boolean
] BUFFERS [boolean
] TIMING [boolean
] SUMMARY [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é en une unité de coût arbitraire bien
que conventionnellement ce sont des lectures de page disque).
Deux nombres sont affichés : le coût de démarrage, écoulé avant que la première
ligne soit renvoyée, et le coût d'exécution total, nécessaire au renvoi
de toutes les lignes.
Pour la plupart des requêtes, le coût 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 coût 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 coûts 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. De ce fait, les statistiques d'exécution réelle sont ajoutées
à l'affichage, en incluant le temps total écoulé à chaque nœud du plan (en
millisecondes) et le nombre total de lignes renvoyées.
C'est utile pour vérifier la véracité des informations fournies par le planificateur.
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.
ANALYZE
Exécute la commande et affiche les temps d'exécution réels et d'autres
statistiques. Ce paramètre est par défaut à FALSE
.
VERBOSE
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
.
COSTS
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
.
BUFFERS
Inclut des informations sur l'utilisation des tampons. Spécifiquement,
inclut le nombre de blocs partagés lus dans la cache, lus en dehors du
cache, modifiés et écrits, le nombre de blocs locaux lus dans le cache,
lus en dehors du cache, modifiés, et écrits, et le nombre de blocs
temporaires lus et écrits.
Le terme hit signifie que la lecture a été évitée
car le bloc se trouvait déjà dans le cache. Les blocs partagés contiennent
les données de tables et index standards ; les blocs locaux
contiennent les tables et index temporaires ; les blocs temporaires
contiennent les données de travail à court terme, comme les tris, les
hachages, les nœuds Materialize, et des cas similaires. Le nombre de blocs
modifiés (dirtied) indique le nombre de blocs
précédemment propres qui ont été modifiés par cette requeête ; le
nombre de blocs écrits (written) indique le nombre
de blocs déjà modifiés qui a été enlevé du cache pour être écrit sur disque
lors de l'exécution de cette requête. Le nombre de blocs affichés pour un
nœud de niveau supérieur inclut ceux utilisés par tous ses enfants. Dans
le format texte, seules les valeurs différentes de zéro sont affichées.
Ce paramètre peut seulement être utilisé si ANALYZE
est aussi activé. Sa valeur par défaut est FALSE
.
TIMING
Inclut le temps réel de démarrage et le temps réel passé dans le nœud en
sortie. La surcharge de la lecture répétée de l'horloge système peut
ralentir la requête de façon significative sur certains systèmes, et donc
il est utile de pouvoir configurer ce paramètre à FALSE
quand seuls le décompte réel des lignes est nécessaire. La durée d'exécution
complète de la commande est toujours mesurée, même si le chonométrage des
nœuds est désactivé avec cette option. Ce paramètre
peut seulement être utilisé quand l'option ANALYZE
est
aussi activée. La valeur par défaut est TRUE
.
SUMMARY
Inclut des informations résumées (par exemple : information de temps
total) après le plan de la requête. Les informations résumées sont
inclues par défaut quand ANALYZE
est utilisé mais
sinon ne sont pas inclues par défaut, mais peuvent être activées avec
cette option. Le temps de planification dans EXPLAIN
EXECUTE
inclue le temps nécessaire pour récupérer le plan du
cache ainsi que le temps nécessaire pour le replanifier, si nécessaire.
FORMAT
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
.
boolean
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
.
instruction
Toute instruction SELECT
, INSERT
,
UPDATE
, DELETE
, VALUES
EXECUTE
, DECLARE
,
CREATE TABLE AS
ou CREATE MATERIALIZED VIEW
AS
dont le plan d'exécution est souhaité.
La sortie de la commande est une description textuelle du plan
sélectionné pour la requête
,
annotée en option des statistiques d'exécution.
Section 14.1 décrit les informations fournies.
Pour permettre au planificateur de requêtes de
PostgreSQL de prendre des décisions en
étant raisonnablement informé pour l'optimisation des requêtes, les
données du catalogue pg_statistic
doivent être à jour pour toutes les tables utilisées dans la requête.
Habituellement, le démon autovacuum
s'en chargera automatiquement. Mais si une table a eu récemment des
changements importants dans son contenu, vous pourriez avoir besoin de
lancer un ANALYZE manuel plutôt que d'attendre
que l'autovacuum s'occupe des modifications.
Pour mesurer le coût d'exécution de chaque nœud dans le plan d'exécution,
l'implémentation actuelle de la commande EXPLAIN
ANALYZE
ajoute une surcharge de profilage à l'exécution de la
requête. En résultat, exécuter EXPLAIN ANALYZE
sur une
requête peut parfois prendre un temps significativement plus long que
l'exécution de la requête. La durée supplémentaire dépend de la nature
de la requête ainsi que de la plateforme utilisée. Le pire des cas survient
pour les nœuds du plan nécessitant en eux-même peu de durée d'exécution par
exécution et sur les machines disposant d'appels systèmes relativement
lents pour obtenir l'heure du jour.
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=9.54..9.54 rows=1 width=8) (actual time=0.156..0.161 rows=11 loops=1) Group Key: foo -> Index Scan using test_pkey on test (cost=0.29..9.29 rows=50 width=8) (actual time=0.039..0.091 rows=99 loops=1) Index Cond: ((id > $1) AND (id < $2)) Planning time: 0.197 ms Execution time: 0.225 ms (6 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é.
L'instruction EXPLAIN
n'est pas définie dans le
standard SQL.