13.1. Utiliser EXPLAIN

PostgreSQL réalise un plan de requête pour chaque requête qu'il reçoit. Choisir le bon plan pour correspondre à la structure de la requête et aux propriétés des données est absolument critique pour de bonnes performances. Vous pouvez utiliser la commande EXPLAIN pour voir quel plan de requête le système crée pour une requête particulière. La lecture du plan est un art qui mérite un tutoriel complet, ce que vous n'aurez pas là ; ici ne se trouvent que des informations de base.

Les nombres actuellement donnés par EXPLAIN sont :

• Le coût estimé du lancement (temps passé avant que l'affichage de la sortie ne commence, c'est-à-dire pour faire le tri dans un nœud de tri.)

• Coût total estimé (si toutes les lignes doivent être récupérées, ce qui pourrait ne pas être le cas : une requête avec une clause LIMIT ne paiera pas le coût total par exemple.)

• Nombre de lignes estimé en sortie par ce nœud de plan (encore une fois, seulement si exécuté jusqu'au bout)

• Largeur moyenne estimée (en octets) des lignes en sortie par ce nœud de plan

Les coûts sont mesurés en unités de récupération de page disque. (Les estimations de l'effort CPU sont converties en unités de page disque en utilisant quelques facteurs assez arbitraires. Si vous voulez expérimenter avec ces facteurs, voir la liste des paramètres de configuration en exécution dans Section 16.4.2.)

Il est important de noter que le coût d'un nœud de haut niveau inclut le coût de tous les nœuds fils. Il est aussi important de réaliser que le coût reflète seulement les éléments d'importance pour le planificateur/optimiseur. En particulier, le coût ne considère pas le temps dépensé dans la transmission des lignes de résultat à l'interface, qui pourrait être un facteur dominant dans le temps réellement passé ; mais le planificateur l'ignore parce qu'il ne peut pas le changer en modifiant le plan. (Chaque plan correct sortira le même ensemble de lignes.)

La sortie des lignes est un peu difficile car il ne s'agit pas du nombre de lignes traitées/parcourues par la requête, c'est habituellement moins, reflétant la sélectivité estimée des conditions de la clause WHERE qui sont appliquées à ce nœud. Idéalement, les estimations des lignes de haut niveau sera une approximation des nombres de lignes déjà renvoyés, mis à jour, supprimés par la requête.

Voici quelques exemples (utilisant la base de données des tests de régression après un VACUUM ANALYZE et les sources de développement de la 7.3) :

EXPLAIN SELECT * FROM tenk1;

                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..333.00 rows=10000 width=148)

C'est aussi direct que ce que nous obtenons. Si vous faîtes :

SELECT * FROM pg_class WHERE relname = 'tenk1';

vous trouverez que tenk1 a 233 pages disque et 10000 lignes. Donc, le coût est estimé à 233 lectures de page, dont le coût individuel est estimé à 1,0, plus 10000 * cpu_tuple_cost qui vaut actuellement 0,01 (essayez SHOW cpu_tuple_cost).

Maintenant, modifions la requête pour ajouter une condition WHERE :

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 1000;

                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..358.00 rows=1033 width=148)
   Filter: (unique1 < 1000)

L'estimation des lignes en sortie a baissé à cause de la clause WHERE. Néanmoins, le parcours devra toujours visiter les 10000 lignes, donc le coût n'a pas baissé ; en fait, il a un peu augmenté pour refléter le temps CPU supplémentaire dépensé pour vérifier la condition WHERE.

Le nombre réel de lignes que cette requête sélectionnera est 1000 mais l'estimation est approximative. Si vous tentez de dupliquer cette expérience, vous obtiendrez probablement une estimation légèrement différente ; de plus, elle changera après chaque commande ANALYZE parce que les statistiques produites par ANALYZE sont prises à partir d'un extrait au hasard de la table.

Modifiez la requête pour restreindre encore plus la condition :

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 50;

                                   QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using tenk1_unique1 on tenk1  (cost=0.00..179.33 rows=49 width=148)
   Index Cond: (unique1 < 50)

et vous verrez que si nous faisons une condition WHERE assez sélective, le planificateur décidera éventuellement qu'un parcours d'index est moins cher qu'un parcours séquentiel. Ce plan ne visitera que 50 lignes grâce à l'index, donc il gagnera malgré le fait que chaque récupération individuelle est plus chère que la lecture séquentielle d'une page de disque complète.

Ajoutez une autre condition à la clause WHERE :

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 50 AND stringu1 = 'xxx';

                                  QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using tenk1_unique1 on tenk1  (cost=0.00..179.45 rows=1 width=148)
   Index Cond: (unique1 < 50)
   Filter: (stringu1 = 'xxx'::name)

La condition ajoutée stringu1 = 'xxx' réduit l'estimation du nombre de lignes en sortie mais pas le coût car nous devons toujours visiter le même ensemble de lignes. Notez que la clause stringu1 ne peut pas être appliqué à une condition d'index (car cet index est seulement sur la colonne unique1). À la place, il est appliqué comme un filtre sur les lignes récupérées par l'index. Du coup, le coût a un peu augmenté pour refléter cette vérification supplémentaire.

Maintenant, essayons de joindre deux tables, en utilisant les colonnes dont nous avons discuté :

EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;

                               QUERY PLAN
----------------------------------------------------------------------------
 Nested Loop  (cost=0.00..327.02 rows=49 width=296)
   ->  Index Scan using tenk1_unique1 on tenk1 t1
                                      (cost=0.00..179.33 rows=49 width=148)
         Index Cond: (unique1 < 50)
   ->  Index Scan using tenk2_unique2 on tenk2 t2
                                      (cost=0.00..3.01 rows=1 width=148)
         Index Cond: ("outer".unique2 = t2.unique2)

Dans cette jointure en boucle imbriquée, le parcours externe utilise le même parcours d'index que celui utilisé dans l'avant-dernier exemple et donc son coût et le nombre de lignes sont les mêmes parce que nous appliquons la clause WHERE unique1 < 50 à ce nœud. La clause t1.unique2 = t2.unique2 n'a pas encore d'intérêt donc elle n'affecte pas le nombre de lignes du parcours externe. Pour le parcours interne, la valeur unique2 de la ligne courante du parcours externe est connectée dans le parcours d'index interne pour produire une condition d'index identique à t2.unique2 = constante. Donc, nous obtenons le même plan de parcours interne et les coûts que nous obtenons de, disons, EXPLAIN SELECT * FROM tenk2 WHERE unique2 = 42. Les coûts du nœud correspondant à la boucle sont ensuite initialisés sur la base du coût du parcours externe, avec une répétition du parcours interne pour chaque ligne externe (ici, 49 * 3.01), plus un petit temps CPU pour traiter la jointure.

Dans cet exemple, le nombre de lignes en sortie de la jointure est identique aux nombres de lignes des deux parcours mais ce n'est pas vrai en règle générale car vous pouvez avoir des clauses WHERE mentionnant les deux tables et qui, donc, peuvent seulement être appliquées au point de jointure, non pas aux parcours d'index. Par exemple, si nous avions ajouté WHERE ... AND t1.hundred < t2.hundred, cela aurait diminué le nombre de lignes en sortie du nœud de jointure mais n'aurait pas changé les parcours d'index.

Une façon de rechercher des plans différents est de forcer le planificateur à oublier certaines stratégies qu'il aurait donné vainqueur en utilisant les options d'activation (enable)/désactivation (disable) pour chaque type de plan. (C'est un outil brut mais utile. Voir aussi Section 13.3.)

SET enable_nestloop = off;
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;

                               QUERY PLAN
--------------------------------------------------------------------------
 Hash Join  (cost=179.45..563.06 rows=49 width=296)
   Hash Cond: ("outer".unique2 = "inner".unique2)
   ->  Seq Scan on tenk2 t2  (cost=0.00..333.00 rows=10000 width=148)
   ->  Hash  (cost=179.33..179.33 rows=49 width=148)
         ->  Index Scan using tenk1_unique1 on tenk1 t1
                                    (cost=0.00..179.33 rows=49 width=148)
               Index Cond: (unique1 < 50)

Ce plan propose d'extraire les 50 lignes intéressantes de tenk1 en utilisant le même parcours d'index, de les placer dans une table de hachage en mémoire puis de faire un parcours séquentiel de tenk2, en cherchant dans la table de hachage des correspondances possibles de la ligne t1.unique2 = t2.unique2 at each tenk2. Le coût pour lire tenk1 et pour initialiser la table de hachage correspond au coût de lancement complet pour la jointure hachée car nous n'obtiendrons pas de lignes jusqu'à avoir lu tenk2. Le temps total estimé pour la jointure inclut aussi une charge importante du temps CPU pour requêter la table de hachage 10000 fois. Néanmoins, notez que nous ne chargeons pas 10000 fois 179,33 ; la configuration de la table de hachage n'est exécutée qu'une fois dans ce type de plan.

Il est possible de vérifier la précision des coûts estimés par le planificateur en utilisant EXPLAIN ANALYZE. Cette commande exécute réellement la requête puis affiche le vrai temps d'exécution accumulé par chaque nœud du plan, avec les mêmes coûts estimés que ceux affichés par un simple EXPLAIN. Par exemple, nous pourrions obtenir un résultat comme celui-ci :

EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;

                                   QUERY PLAN
-------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..327.02 rows=49 width=296)
                                 (actual time=1.181..29.822 rows=50 loops=1)
   ->  Index Scan using tenk1_unique1 on tenk1 t1
                  (cost=0.00..179.33 rows=49 width=148)
                                 (actual time=0.630..8.917 rows=50 loops=1)
         Index Cond: (unique1 < 50)
   ->  Index Scan using tenk2_unique2 on tenk2 t2
                  (cost=0.00..3.01 rows=1 width=148)
                                 (actual time=0.295..0.324 rows=1 loops=50)
         Index Cond: ("outer".unique2 = t2.unique2)
 Total runtime: 31.604 ms

Notez que les valeurs << temps réel >> sont en millisecondes alors que les estimations de << coût >> sont exprimées dans des unités arbitraires de récupération de page disque ; donc il y a peu de chances qu'elles correspondent. L'important est de faire attention aux ratios.

Dans certains plans de requête, il est possible qu'un nœud de sous-plan soit exécuté plus d'une fois. Par exemple, le parcours d'index interne est exécuté une fois par ligne externe dans le plan de boucle imbriquée ci-dessus. Dans de tels cas, la valeur << loops >> renvoie le nombre total d'exécution du nœud, et le temps réel et les valeurs des lignes affichées sont une moyenne par exécution. Ceci est fait pour que les nombres soient comparables avec la façon dont les estimations de coûts sont affichées. Multipliez par la valeur de << loops >> pour obtenir le temps total réellement passé dans le nœud.

Le Total runtime (temps total d'exécution) affiché par EXPLAIN ANALYZE inclut les temps de lancement et d'arrêt de l'exécuteur ainsi que le temps passé lors du traitement des lignes de résultat. Il n'inclut pas le temps passé pour l'analyse, la réécriture ou la planification. Pour une requête SELECT, le temps total d'exécution sera juste un peu plus important que le temps total indiqué par le noœud du plan de haut niveau. Pour les commandes INSERT, UPDATE et DELETE, le temps total d'exécution pourrait être considérablement plus important parce qu'il inclut le temps passé au traitement des lignes de résultat. Dans ces commandes, le temps pour le nœud du plan principal est essentiellement le temps passé à calculer les nouvelles lignes et/ou l'emplacement des anciennes mais il n'inclut pas le temps passé à faire des modifications.

Il est bon de noter que les résultats de EXPLAIN ne devraient pas être extrapolés pour des situations autres que celles de vos tests en cours ; par exemple, les résultats sur une petite table ne peuvent être appliqués à des tables bien plus importantes. Les estimations de coût du planificateur ne sont pas linéaires et, du coup, il pourrait bien choisir un plan différent pour une table plus petite ou plus grande. Un exemple extrême est celui d'une table occupant une page disque. Vous obtiendrez pratiquement toujours un parcours séquentiel que des index soient disponibles ou non. Le planificateur réalise que cela va nécessiter la lecture d'une seule page disque pour traiter la table dans ce cas, il n'y a donc pas d'intérêt à étendre des lectures de pages supplémentaires pour un index.