Comment EXPLAIN guide la création d'index

L'un des usages les plus directs d'EXPLAIN est de confirmer qu'un index est bien utilisé — ou de comprendre pourquoi il ne l'est pas. Prenons un exemple concret.

Après avoir ajouté un index sur client_id :

CREATE INDEX idx_commandes_client ON commandes(client_id);
EXPLAIN SELECT * FROM commandes WHERE client_id = 42;

Le plan devient :

Index Scan using idx_commandes_client on commandes
  (cost=0.43..12.7 rows=18 width=96)
  Index Cond: (client_id = 42)

Le coût est passé de 4 523 à 12,7 — soit une réduction de 99 %. C'est le type de démonstration concrète attendu lors d'un entretien sur la performance et l'optimisation SQL.

Il arrive cependant que l'optimiseur ignore un index existant. Cela se produit notamment quand :

• La table est trop petite (un Seq Scan est plus rapide)
• La colonne a une faible sélectivité (ex. une colonne booléenne)
• Les statistiques sont obsolètes (relancez ANALYZE en PostgreSQL)
• La requête applique une fonction sur la colonne indexée : WHERE UPPER(nom) = 'MARTIN' ne peut pas utiliser un index sur nom

EXPLAIN sur les requêtes avec jointures

Les jointures sont souvent le point de départ des problèmes de performance. EXPLAIN vous montre exactement quelle stratégie le moteur choisit pour assembler deux tables. Considérez cette requête :

EXPLAIN ANALYZE
SELECT c.nom, COUNT(o.id) AS nb_commandes
FROM clients c
LEFT JOIN commandes o ON c.id = o.client_id
GROUP BY c.nom;

Si le plan révèle un Hash Join avec un grand nombre de lignes côté commandes et un Sort final pour le GROUP BY, vous avez 2 pistes d'optimisation immédiates : un index sur commandes.client_id (pour éviter un full scan) et éventuellement un index sur clients.nom si le tri devient coûteux.

Pour aller plus loin sur les jointures, consultez notre guide INNER JOIN vs LEFT JOIN qui détaille la logique de chaque type et ses implications sur les performances.

Différences entre PostgreSQL, MySQL et SQL Server

La commande EXPLAIN existe dans les 3 grands SGBD, mais sa syntaxe et son niveau de détail varient.

En MySQL, EXPLAIN renvoie un tableau avec des colonnes comme type, key, rows et Extra. La valeur ALL dans la colonne type équivaut à un Sequential Scan : c'est le pire cas. La colonne Extra avec Using filesort ou Using temporary signale des opérations coûteuses.

En SQL Server, l'équivalent s'appelle SET SHOWPLAN_TEXT ON ou, graphiquement, "Afficher le plan d'exécution estimé" dans SSMS. Le plan est rendu sous forme de graphe avec des pourcentages de coût relatif par nœud.

En PostgreSQL, EXPLAIN (FORMAT JSON) ou EXPLAIN (FORMAT TEXT, BUFFERS, ANALYZE) donne accès aux informations les plus riches, notamment le nombre de blocs lus depuis le disque vs depuis le cache (shared hit vs read).

EXPLAIN en entretien technique : ce qu'on attend de vous

En entretien data, EXPLAIN apparaît souvent dans des questions du type : "Cette requête est lente, comment diagnostiqueriez-vous le problème ?". Les recruteurs attendent une réponse structurée en 3 temps.

D'abord, isoler la requête et lancer EXPLAIN ANALYZE pour obtenir le plan réel. Ensuite, identifier le nœud coûteux : Sequential Scan inattendu, Nested Loop sur un grand jeu, Sort sans index. Enfin, proposer une action corrective : création d'index, réécriture de la requête, mise à jour des statistiques.

Montrer que vous savez lire un plan d'exécution prouve que vous comprenez ce qui se passe à l'intérieur du moteur — bien au-delà de la syntaxe SQL de surface. C'est exactement ce que valorisent les équipes data engineering et analytics dans les profils qu'elles recrutent.

---

Questions fréquentes

Quelle est la différence entre EXPLAIN et EXPLAIN ANALYZE ?

EXPLAIN génère le plan d'exécution estimé sans exécuter la requête. EXPLAIN ANALYZE exécute réellement la requête et affiche en parallèle les estimations de l'optimiseur et les mesures réelles (temps, nombre de lignes). C'est EXPLAIN ANALYZE qui permet de détecter les écarts entre prévisions et réalité.

Pourquoi l'optimiseur n'utilise-t-il pas mon index ?

Plusieurs raisons sont possibles : la table est petite (un Seq Scan est plus rapide), la colonne a peu de valeurs distinctes (faible sélectivité), les statistiques sont obsolètes, ou la requête applique une transformation sur la colonne (ex. LOWER(email)) qui empêche l'utilisation de l'index standard. Créez un index fonctionnel dans ce dernier cas.

EXPLAIN ANALYZE modifie-t-il les données ?

Non, sur une SELECT. Mais sur une UPDATE, DELETE ou INSERT, la requête est réellement exécutée. Enveloppez-la dans une transaction : BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK; pour annuler les modifications après diagnostic.

Que signifie le coût affiché par EXPLAIN ?

Le coût est une unité abstraite interne à l'optimiseur (en PostgreSQL, 1 unité ≈ lecture séquentielle d'une page disque). Il ne correspond pas à des millisecondes. Il sert uniquement à comparer des plans entre eux : un plan à coût 500 est jugé plus efficace qu'un plan à coût 5 000.

Existe-t-il des outils pour visualiser les plans d'exécution ?

Oui. explain.dalibo.com est un outil en ligne qui transforme la sortie JSON d'EXPLAIN PostgreSQL en graphe interactif coloré. pgAdmin intègre un visualiseur graphique natif. Pour MySQL, MySQL Workbench offre un affichage visuel similaire avec des indicateurs de coût par nœud.

---

Conclusion

Maîtriser EXPLAIN et EXPLAIN ANALYZE transforme votre rapport aux performances SQL. Plutôt que de modifier des requêtes au hasard en espérant un gain, vous diagnostiquez avec précision, vous comprenez les choix de l'optimiseur, et vous agissez là où c'est vraiment utile. C'est une compétence directement valorisée en entretien et en production.

Pour aller plus loin dans votre préparation, entraînez-vous sur des requêtes réelles avec des jeux de données significatifs. La plateforme SQL Pratique met à votre disposition des exercices corrigés avec environnement d'exécution intégré — commencez votre préparation dès aujourd'hui.