Utilisation de EXPLAIN

Postgres imagine un plan de requête pour chaque requête donnée. Choisir le bon plan pour apparier la structure d'une requête et les propriétés des données est absolument essentiel pour de bonnes performances. Vous pouvez utiliser la commande EXPLAIN pour voir quel plan de requête le système crée pour chaque requête. La lecture de plan est un art qui nécessite un tutoriel à part entière, lequel n'existe pas; mais nous verrons ici certaines informations basiques.

Les aspects couramment exploités par EXPLAIN sont :

• Le coût de démarrage estimé (le temps passé avant l'affichage, ex., le temps pour faire un tri avec SORT).

• Le coût total estimé (si tous les tuples sont retrouvés, ce qui peut ne pas être le cas --- un requête avec un LIMIT raccourcira le temps nécessaire, par exemple).

• L'estimation du nombre de lignes affichées par le plan (de nouveau sans inclure aucun LIMIT).

• La taille moyenne estimée (en octets) des lignes affichées par le plan.

Les coûts sont mesurés en unités d'accès aux pages disque. (Le travail processeur estimé est converti en unités d'accès disque selon certains facteurs arbitraires. Si vous voulez tenter l'expérience avec ces facteursn voir la liste des paramètres de configuration de lancement dans le Guide de l'administrateur).

Il est important de noter que le coût d'un niveau supérieur inclut le coût de tous ses descendants. Il est aussi important de réaliser que le coût ne reflète que ce que l'optimiseur prend en compte. En particulier, le coût ne prend pas en compte le temps passé à transmettre les résultats des tuples au client --- lequel peut être un facteur important dans le critère temps-dépensé, mais l'optimiseur l'ignore car il ne peut pas le changer en modifiant son plan.

Les sorties lignes sont un peu délicates car elles ne représentent pas le nombre de lignes scannées par la requête --- c'est habituellement moins, reflétant la sélectivité estimée des contraintes de certaines clauses WHERE qui sont appliquées. Idéalement, les lignes de niveau supérieur estimeront de manière approximative le nombre de lignes actuellement renvoyées, mises à jour ou supprimées par la requête.

Ici quelques exemples (utilisant le test de régression sur la base après une analyse VACUUM) :

regression=# EXPLAIN SELECT * FROM tenk1;
NOTICE:  QUERY PLAN:

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

Si vous faites :

SELECT * FROM pg_class WHERE relname = 'tenk1';
    

Maintenant modifions la requête en ajoutant une clause de qualification :

regression=# EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 1000;
NOTICE:  QUERY PLAN:

Seq Scan on tenk1  (cost=0.00..358.00 rows=1007 width=148)
    

Le nombre actuel de lignes que cette requête sélectionnera est de 1000, mais l'estimation est seulement approximative. Si vous essayez de dupliquer cette expérimentation, vous obtiendrez probablement une estimation un peu différente; de plus, elle changera après chaque commande ANALYZE, car les statistiques produites par ANALYZE sont effectuées depuis des exemples pris au hasard de la table.

Modifions la requête pour restreindre la qualification :

regression=# EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 50;
NOTICE:  QUERY PLAN:

Index Scan using tenk1_unique1 on tenk1  (cost=0.00..181.09 rows=49 width=148)
    

Ajoutons une autre condition à la qualification :

regression=# EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 50 AND
regression-# stringu1 = 'xxx';
NOTICE:  QUERY PLAN:

Index Scan using tenk1_unique1 on tenk1  (cost=0.00..181.22 rows=1 width=148)
    

Essayons de joindre deux tables, en utilisant les champs dont nous avons parlé :

regression=# EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50
regression-# AND t1.unique2 = t2.unique2;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..330.41 rows=49 width=296)
  ->  Index Scan using tenk1_unique1 on tenk1 t1
               (cost=0.00..181.09 rows=49 width=148)
  ->  Index Scan using tenk2_unique2 on tenk2 t2
               (cost=0.00..3.01 rows=1 width=148)
    

Dans cette jointure de boucle-imbriquée, le scan externe est le même scan d'index que nous avons dans l'exemple avant-dernier, et ainsi les coûts et le comptage de ligne sont les mêmes car nous avons appliqué la clause WHERE unique1 < 50 à ce noeud. La clause t1.unique2 = t2.unique2 n'est pas en rapport cependant, ainsi elle n'affecte pas le comptage de ligne du scan externe. Pour le scan interne, la valeur unique2 du tuple de scan externe courant est connecté au scan interne d'indexes pour produire une qualification d'index comme t2.unique2 = constant. Ainsi, nous obtenons le même plan de scan interne que nous avons obtenus en faisant, explain select * from tenk2 where unique2 = 42. Les coûts de la boucle imbriquée sont alors placés sur la base du scan externe, plus une répétition du scan interne pour chaque tuple externe, plus une répétition du scan interne pour chaque tuple (49 * 3.01, ici), plus un peu de temps CPU pour joindre le traitement.

Dans cet exemple le compte de sortie ligne est le même que le produit des dexu scans de compte ligne, mais ce n'est pas vrai en général, car en général vous pouvez avoir des clauses WHERE qui mentionnent les deux relations et ne peuvent ainsi être appliquées qu'au point de jointure, pas à chaqeu scan d'entrée. Par exemple, si nous ajoutons WHERE ... AND t1.hundred < t2.hundred, qui abaissera le total des sorties ligne du noeud de jointure, mais ne changera pas les scans d'entrée.

Un moyen de voir sous un angle différent est de forcer le planner à ne pas faire attention à quelle stratégie est la meilleure, en utilisant les drapeaux actifs/inactifs pour chaque type de plan. (Voir aussi the section called Contrôle du planner avec des JOIN explicites).

regression=# set enable_nestloop = off;
SET VARIABLE
regression=# EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50
regression-# AND t1.unique2 = t2.unique2;
NOTICE:  QUERY PLAN:

Hash Join  (cost=181.22..564.83 rows=49 width=296)
  ->  Seq Scan on tenk2 t2
               (cost=0.00..333.00 rows=10000 width=148)
  ->  Hash  (cost=181.09..181.09 rows=49 width=148)
        ->  Index Scan using tenk1_unique1 on tenk1 t1
               (cost=0.00..181.09 rows=49 width=148)
    

Il est possible de vérifier l'exactitude des coûts estimés par le planner en utilisant EXPLAIN ANALYZE. Cette commande exécute la requête, et affiche le temps de lancement réel accumulé dans chaque plan avec les mêmes coûts extimés que EXPLAIN peut le faire. Par exemple, nous pourrions obtenir un résultat comme ceci :

regression=# EXPLAIN ANALYZE
regression-# SELECT * FROM tenk1 t1, tenk2 t2
regression-# WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..330.41 rows=49 width=296) (actual time=1.31..28.90 rows=50 loops=1)
  ->  Index Scan using tenk1_unique1 on tenk1 t1
               (cost=0.00..181.09 rows=49 width=148) (actual time=0.69..8.84 rows=50 loops=1)
  ->  Index Scan using tenk2_unique2 on tenk2 t2
               (cost=0.00..3.01 rows=1 width=148) (actual time=0.28..0.31 rows=1 loops=50)
Total runtime: 30.67 msec

Dans certains plans de requête, il est possible pour un sous-plan d'être exécuté plus d'une fois. Par exemple, le scan d'index interne est exécuté une fois pour le tuple externe dans le plan ci-dessus. Dans certains cas, la valeur "loops" affiche le nombre total d'exécutions, et les valeurs temps et lignes indiquées comme moyennes par exécution. Ceci est destiné à rendre les nombres comparables avec ce que les coûts estimés ont indiqué. Multipliez par la valeur "loops" pour obtenir le temps total dépensé actuellement.

Le "temps de lancement total" indiqué par EXPLAIN ANALYZE inclut les temps de démarrage et arrêt, aussi bien que le temps de calcul de résultats de tuples. Il n'inclut pas les temps d'analyse, réécriture ou planning. Pour une requête SELECT, le temps total de lancement sera normalement juste un peu plus grand que le temps total rapporté pour le plan de noeud de haut niveau. Pour les requêtes INSERT, UPDATE, et DELETE, le temps total de lancement peut être considérablement plus important, car il inclut le temps passé en calcul de tuples externes. Dans ces requêtes, le temps pour le plan de noueud de haut niveau est essentiellement le temps passé en calcul de nouveaux tuples et/ou en localisation des anciens, mais il n'inclut pas le temps dépensé à faire ces modifications.

Il vaut d'être noté que les résultats de EXPLAIN ne seront pas extrapolés pour les situations autres que celles que nous testons actuellement; par exemple, les résultats d'une petite table ne pourront pas être supposés s'appliquer à de grosses tables. Les coûts estimés du planner sont non linéaires et il peut choisir un plan différent pour une grosse table ou une plus petite. Un exemple extrême est que sur une table qui occupe seulement une page disque, vous obtiendrez presque toujours un plan de scan séquentiel que les indexes soient disponibles ou non.