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
] SETTINGS [boolean
] GENERIC_PLAN [boolean
] BUFFERS [boolean
] WAL [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
, MERGE
, 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. L'identifiant de
requête sera aussi affiché si une requête a été évaluée, voir compute_query_id pour plus de détails. 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
.
GENERIC_PLAN
Autorise la requête à contenir des caractères joker de placement
comme $1
, et génère un plan générique qui ne dépend
pas de ces paramètres.
Voir PREPARE
pour les détails sur les plans génériques et les types de requêtes qui
acceptent des paramètres.
Ce paramètre ne peut pas être utilisé en même temps que
ANALYZE
. Il est à FALSE
par
défaut.
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, et le temps passé à lire et écrire des blocs
dans les fichiers de données et dans les fichiers temporaires (en millisecondes) si le paramètre track_io_timing est activé. 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 requê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.
La valeur par défaut de ce paramètre est FALSE
.
SETTINGS
Inclut des informations sur les paramètres de configuration. Plus
précisément, inclut les options affectant l'optimisation des requêtes et
qui ont des valeurs différentes de leur valeur par défaut. Sa valeur par
défaut est FALSE
.
WAL
Inclut des informations sur les génération des enregistrements dans les
WAL. Spécifiquement, inclut le nombre d'enregistrements, le nombre
d'images complète de bloc (FPI), et la quantité d'octets générés dans
les WAL. Dans le format texte, seules les valeurs différentes de zéro
seront affichées. Ce paramètre peut seulement aussi être utilisé quand
ANALYZE
est aussi activé. Il vaut par défaut
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 chronomé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
inclut 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
,
MERGE
,
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=10.77..10.87 rows=10 width=12) (actual time=0.043..0.044 rows=10 loops=1) Group Key: foo Batches: 1 Memory Usage: 24kB -> Index Scan using test_pkey on test (cost=0.29..10.27 rows=99 width=8) (actual time=0.009..0.025 rows=99 loops=1) Index Cond: ((id > 100) AND (id < 200)) Planning Time: 0.244 ms Execution Time: 0.073 ms (7 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é.
Notez que l'exemple précédent montrait un plan « personnalisé »
pour les valeurs spécifiques des paramètres données dans
EXECUTE
. Nous pourrions aussi souhaiter voir le plan
générique pour une requête à paramètre, ce qui peut se faire avec
GENERIC_PLAN
:
EXPLAIN (GENERIC_PLAN) SELECT sum(bar) FROM test WHERE id > $1 AND id < $2 GROUP BY foo; QUERY PLAN ------------------------------------------------------------------------------- HashAggregate (cost=26.79..26.89 rows=10 width=12) Group Key: foo -> Index Scan using test_pkey on test (cost=0.29..24.29 rows=500 width=8) Index Cond: ((id > $1) AND (id < $2)) (4 rows)
Dans ce cas, l'analyseur détecte correctement que $1
et $2
devraient avoir le même type de données que
id
, donc la manque d'information sur le type de
paramètre par PREPARE
n'était pas un problème. Dans
d'autres cas, il pourrait être nécessaire de spécifier explicitement les
types pour les symboles des paramètres, ce qui peut se faire en les
convertissant. Par exemple :
EXPLAIN (GENERIC_PLAN) SELECT sum(bar) FROM test WHERE id > $1::integer AND id < $2::integer GROUP BY foo;
L'instruction EXPLAIN
n'est pas définie dans le standard
SQL.