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.5.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.