Lire un plan EXPLAIN

Lorsque vous atteignez un entretien technique senior, les recruteurs cessent de demander écrivez une requête et commencent à demander pourquoi cette requête est-elle lente ? L'outil qui permet d'y répondre est EXPLAIN.

EXPLAIN affiche le plan d'exécution de la base de données : la stratégie étape par étape choisie par le planificateur pour exécuter votre SQL. Il révèle quelles tables sont parcourues, dans quel ordre elles sont jointes et, approximativement, le coût de chaque étape.

Savoir lire un plan montre que vous comprenez le moteur, et pas seulement la syntaxe. C'est précisément le critère utilisé par les recruteurs pour distinguer un profil intermédiaire d'un profil senior.

Il existe deux variantes, et les recruteurs aiment que vous sachiez les distinguer.

• EXPLAIN affiche le plan estimé par le planificateur sans exécuter la requête. C'est rapide et sûr.
• EXPLAIN ANALYZE exécute réellement la requête et indique les nombres de lignes et les durées d'exécution réels en regard des estimations.

Le point le plus instructif consiste à comparer le nombre de lignes estimé au nombre de lignes réel. Un écart important signifie que le planificateur dispose de mauvaises statistiques et risque de faire un mauvais choix.

Attention : EXPLAIN ANALYZE exécute réellement la requête ; il effectuera donc toute opération INSERT ou UPDATE, sauf si celle-ci est placée dans une transaction annulée.

Un plan est un arbre, et non une liste. Les nœuds les plus indentés sont les feuilles exécutées en premier ; les résultats remontent vers la racine, qui produit la sortie finale.

Lisez-le de l'intérieur vers l'extérieur : trouvez le nœud le plus profond, car c'est là que l'exécution commence. Chaque parent consomme les lignes émises par ses enfants.

En entretien, décrivez-le de cette manière : nous parcourons d'abord cette table, ces lignes alimentent ensuite cette jointure, la jointure alimente le tri, puis le tri alimente la limite. C'est cette description de bas en haut que les recruteurs veulent entendre.

Chaque nœud d’un plan Postgres contient les mêmes chiffres clés :

• cost=0.00..35.50 coût de démarrage..coût total dans des unités arbitraires du planificateur
• rows=1000 nombre estimé de lignes produites
• width=64 taille moyenne estimée d’une ligne, en octets

Le premier coût est le coût de démarrage (travail effectué avant l’apparition de la première ligne, comme la construction d’une table de hachage). Le second est le coût total pour renvoyer toutes les lignes. Un coût total plus élevé correspond, selon le planificateur, à une dépense relative plus importante.

Considérez une requête simple avec filtre. Le plan ci-dessous raconte toute l’histoire en une seule ligne.

Il s’agit d’une analyse séquentielle (lecture complète de la table) sur orders, qui applique le filtre status = 'shipped'. Le planificateur estime que 1000 lignes correspondent.

Si orders contient 10 millions de lignes et que seulement 1000 correspondent, la personne qui vous interroge attend que vous répondiez : une analyse séquentielle est ici coûteuse, un index sur le statut (ou sur une colonne plus sélective) permettrait d’éviter de lire toute la table.

Avec EXPLAIN ANALYZE, vous obtenez également les chiffres réels entre parenthèses.

Observez l’exemple : le planificateur avait estimé 1000 lignes, mais en a réellement obtenu 480000. Il s’agit d’une sous-estimation d’un facteur 480. Le planificateur a choisi sa stratégie en supposant qu’il y aurait peu de lignes ; son choix est donc probablement inadapté aux données réelles.

En entretien, cet écart constitue votre diagnostic principal : les statistiques sont obsolètes, exécutez ANALYZE sur la table, puis le planificateur choisira probablement un meilleur plan.

La valeur loops compte davantage que ne le pensent les candidats. Il s’agit du nombre de fois où un nœud a été exécuté.

Cette valeur apparaît du côté interne d’une jointure par boucles imbriquées : le nœud interne s’exécute pour chaque ligne externe. Si loops=480000, cette étape interne s’est exécutée 480 000 fois.

Important : le temps et le nombre de lignes affichés par ligne sont indiqués par boucle. Pour obtenir le véritable total, vous devez multiplier ces valeurs par loops. Un nœud qui semble peu coûteux à 0.004ms par boucle atteint presque 2 secondes sur 480000 boucles.

Un piège fréquent : les candidats lisent cost=18334 et disent cela prend 18 secondes. C’est faux.

Le coût est exprimé en unités arbitraires du planificateur, étalonnées de sorte qu’une lecture séquentielle de page vaille 1.0. Il n’est utile que pour comparer des plans entre eux, et non comme mesure de la durée réelle.

Pour mesurer la durée réelle, vous avez besoin de EXPLAIN ANALYZE et de ses valeurs actual time, exprimées en millisecondes. Dites-le clairement en entretien : cela montre que vous comprenez réellement cette mesure.

Voici un plan portant sur deux tables. Lisez-le de bas en haut.

Les deux premières analyses recueillent des lignes dans orders et customers. Elles alimentent une jointure par hachage : les données d’un côté sont hachées et l’autre côté interroge la table de hachage. La sortie de la jointure alimente ensuite le résultat final.

Remarquez que l’indentation montre la structure : les deux analyses se trouvent sous la jointure par hachage. La personne qui vous interroge veut que vous identifiiez la méthode de jointure (par hachage ici) et la table qui est hachée (généralement la plus petite).

Habituez votre œil à repérer ces signes avant-coureurs dans n’importe quel plan :

• Analyse séquentielle sur une table volumineuse avec un filtre sélectif : un index pourrait aider.
• Lignes estimées très éloignées du nombre réel : statistiques obsolètes.
• Jointure par boucles imbriquées avec beaucoup d’itérations sur une grande table : il manque souvent un index sur la clé de jointure interne.
• Tri ou hachage déversé sur le disque (indiqué par l’utilisation de Disk) : work_mem est trop petit.
• Lignes supprimées par le filtre en très grand nombre : vous avez lu et écarté la majeure partie de la table.

Les plans se présentent sous plusieurs formats. Le format par défaut, TEXT, est celui que vous lisez à voix haute en entretien. Mais vous pouvez également demander une sortie structurée.

EXPLAIN (FORMAT JSON) ou FORMAT YAML produit des plans lisibles par une machine, que les outils et les tableaux de bord analysent. Vous aurez rarement besoin de les examiner manuellement, mais savoir qu’ils existent est un détail appréciable pour un profil expérimenté.

Ajoutez les options entre parenthèses : EXPLAIN (ANALYZE, BUFFERS). L’option BUFFERS indique les accès réussis au cache par rapport aux lectures sur disque, ce qui est très utile pour diagnostiquer les requêtes limitées par les entrées-sorties.

La personne qui vous interroge vous montre un nœud EXPLAIN ANALYZE avec rows=1000 dans la section du coût, mais actual ... rows=480000. Quel est le diagnostic le plus probable ?

Vous savez maintenant lire un plan comme une personne expérimentée :

• EXPLAIN effectue des estimations, tandis que EXPLAIN ANALYZE exécute et mesure.
• Lisez l’arbre de bas en haut : les feuilles s’exécutent en premier et la racine produit la sortie.
• Chaque nœud indique le coût (en unités relatives), le nombre de lignes et la largeur ; actual time correspond à la durée réelle en millisecondes.
• loops multiplie les valeurs par boucle ; soyez attentif aux boucles imbriquées.
• L’écart entre le nombre de lignes estimé et réel constitue votre principal signal de diagnostic.

Décrivez le plan à voix haute et signalez les signaux d’alerte : c’est le comportement qui fait la différence en entretien.