EXPLAIN [ ANALYZE ] [ VERBOSE ] instruction
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 ou EXECUTE sans que la commande n'affecte les données, l'approche suivante peut être envisagée :
BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;
Exécute la commande et affiche les temps d'exécution réels.
Affiche la représentation interne complète du plan plutôt qu'un simple résumé. Cette option n'est réellement utile que lors de débogage. Le formatage de la sortie de VERBOSE peut être modifié à l'aide du paramètre de configuration explain_pretty_print.
Toute instruction SELECT, INSERT, UPDATE, DELETE, VALUES EXECUTE ou DECLARE 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 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.
L'optimiseur génétique de requêtes (GEQO) teste des plans d'exécution au hasard. Ainsi, quand le nombre de relations de jointures est supérieur geqo_threshold, ce qui implique son utilisation, le plan d'exécution risque d'être différent à chaque exécution de la requête.
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 :
EXECUTE
EXPLAIN SELECT * FROM foo;
QUERY PLAN
---------------------------------------------------------
Seq Scan on foo (cost=0.00..155.00 rows=10000 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)
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é.