EXPLAIN ANALYZE, c'est l'outil qui transforme un problème de performance en protocole d'expérimentation. Là où la plupart des devs voient une requête lente, moi je vois une hypothèse à tester. Et c'est exactement cette discipline qui me permet d'innover sans casser le schéma.
Quand je lance EXPLAIN ANALYZE sur une requête qui rame, je ne cherche pas la solution directe. Je cherche le goulot d'étranglement : un Sequential Scan sur une table de 10M lignes, un Nested Loop qui boucle sur une sous-requête, un tri disque qui explose les temps. Chaque ligne du plan est un indice, et chaque indice est une expérience à mener. Dans ce billet, je te montre comment lire un plan, identifier les goulots, traiter le plan comme une série d'hypothèses itératives — et surtout, ne pas te laisser piéger par les coûts estimés.
Crois-moi, cette approche n'est pas du simple tuning SQL. C'est une méthode d'innovation technique : on ne modifie pas le schéma à l'aveugle, on itère. On mesure, on ajuste, on recommence. Exactement comme dans une startup lean.
EXPLAIN ANALYZE : un outil de diagnostic, pas un verdict
Un plan d'exécution ne te dit pas ce que tu dois faire. Il te dit ce que PostgreSQL a fait, et encore, seulement si tu utilises ANALYZE. Sans lui, tu as juste une prédiction basée sur des statistiques parfois périmées — une hypothèse, pas un fait.
Regarde la différence avec une requête simple :
Les coûts te semblent raisonnables ? Maintenant, ajoute le mot magique :
185 millisecondes pour cinq lignes. Est-ce que le coût estimé reflétait ça ? Non. C'est exactement pour ça que je traite EXPLAIN ANALYZE comme un outil de diagnostic, pas comme un verdict. Le plan n'est jamais la vérité absolue ; c'est une photographie de ce que le moteur a choisi de faire à un instant T.
Pour le lire, je ne regarde pas la première ligne. Je remonte les nœuds les plus profonds, du bas vers le haut, et je cherche la ligne avec le plus gros actual time. C'est là que se cache ton goulot d'étranglement. Un Seq Scan sur une grosse table, un Nested Loop qui s'exécute des milliers de fois, un Sort qui passe en disque... Chacun de ces nœuds est une piste d'amélioration.
Un piège classique : oublier que EXPLAIN ANALYZE exécute réellement la requête. Pour une UPDATE ou une INSERT, encapsule dans une transaction et fais ROLLBACK si tu ne veux pas modifier tes données. C'est une fausse manip ultra fréquente, même chez les devs expérimentés.
Ton travail ne commence pas quand tu obtiens le plan. Il commence quand tu compares les coûts estimés aux temps réels. Cet écart est une anomalie à creuser, une hypothèse à tester. Tu nettoies les statistiques avec ANALYZE, tu ajoutes un index, tu reformules la jointure... puis tu relances. Chaque itération est une expérience, et c'est exactement comme ça que l'innovation technique avance : par séries d'expériences mesurées, pas par intuition.
Identifier les goulots d'étranglement avec EXPLAIN ANALYZE
Première leçon : ne regarde jamais la première ligne du plan. Les devs débutants scrutent le nœud racine et concluent que tout va bien. Les devs expérimentés remontent du bas vers le haut, ligne par ligne, et traquent le plus gros actual time. C'est là que se planque le goulot.
Prenons un cas réel. Tu as une table commandes avec 500 000 lignes et tu veux filtrer sur client_id :
284 ms pour 8 lignes. Le plan estime 0.00 de coût de démarrage, mais en réel, le scan balaie toutes les lignes. Diagnostique : un Seq Scan sur une grosse table avec un filtre sélectif. Le remède classique ? Un index sur client_id. Mais avant de créer l'index, tu dois vérifier qu'il sera utilisé. Je lance la même requête avec SET enable_seqscan = off pour tester l'hypothèse. Si le temps réel chute, mon hypothèse est correcte. C'est de l'expérimentation pure.
Le deuxième piège, ce sont les mauvaises estimations. Quand les coûts estimés et les temps réels divergent trop, PostgreSQL s'est basé sur des statistiques périmées. Un ANALYZE sur la table devrait être ton premier réflexe. Mais attention : même avec des stats à jour, certains opérateurs mentent. Un Nested Loop avec des milliers de loops, c'est souvent un drapeau rouge. Un Sort qui passe en disque (work_mem trop petit), pareil.
Ma règle : je traite chaque plan comme une hypothèse. Je modifie un paramètre, je relance, je compare. EXPLAIN ANALYZE n'est pas un compte rendu, c'est un protocole d'expérience. Et c'est comme ça que tu peux innover sans casser le schéma.
Itérer sur les plans d'exécution : une méthode d'innovation technique
Un plan d'exécution ne contient jamais la solution directe. Il te donne des faits mesurés, et c'est à toi de décider quoi en faire. Moi, j'ai arrêté de chercher la réponse en une seule lecture. Depuis que j'applique le cycle Build-Measure-Learn d'Eric Ries à mes requêtes, je tiens tous mes gains de performance. Chaque plan est une hypothèse, chaque modification de schéma ou de requête est une expérience, et chaque nouvelle sortie d'EXPLAIN ANALYZE est la mesure qui valide ou invalide l'hypothèse.
Prenons un cas réel. J'ai une table commandes avec 5 millions de lignes. La requête qui filtre sur client_id met 185 ms à cause d'un Seq Scan. Ma première hypothèse : un index sur cette colonne va transformer ce scan en Index Scan. Je lance l'expérience :
Résultat : 185 ms à 0.092 ms. L'hypothèse est confirmée, je garde l'index. Quand ce n'est pas le cas — et ça arrive souvent — je droppe l'index sans état d'âme. J'essaie autre chose : un index composite, un JOIN différent, une modification de la configuration du moteur. Chaque échec m'apprend quelque chose sur la distribution des données, sur les statistiques, sur les limites du matériel. C'est exactement comme ça qu'on innove en technique : pas en appliquant des recettes toutes faites, mais en enchaînant des expériences rapides et mesurées. Au bout de quelques itérations, tu obtiens un plan que tu n'aurais jamais trouvé en restant sur ta première intuition.
Pièges courants et limites de l'interprétation
Le premier piège, c'est de croire qu'EXPLAIN ANALYZE donne une vérité stable. Faux. Je l'ai vu des centaines de fois : la même requête lancée deux fois, les temps réels varient du simple au triple. Cache buffer, données en mémoire, locks en cours... tout influence le plan. Tu ne captures pas ta requête, tu captures un instant de ton serveur.
Le piège le plus sournois, c'est le coût estimé. Les coûts du plan ne sont que des prédictions basées sur les statistiques de PostgreSQL. Si elles sont périmées, tu vas droit dans le mur. Regarde cet exemple typique :
Le plan te promet 890 lignes, tu en obtiens 42. Les coûts annoncent 5234, la réalité est 38 millisecondes. Si tu t'étais fié aux estimations, tu serais en train de créer un index inutile. Toujours croiser les dossiers avec les actual time, jamais avec les cost.
Et le classique des classiques : EXPLAIN ANALYZE exécute la requête. Pour une UPDATE ou un DELETE, tu vas modifier les données. J'ai vu un collègue perdre une heure à cause de ça. Encapsule dans une transaction et fais ROLLBACK. Et si tu veux tester un nouvel index, fais-le sur une copie de la table en staging, pas sur la prod.
Conclusion
Après cette lecture, tu ne regardes plus un EXPLAIN ANALYZE de la même façon. Le coût estimé ne vaut rien sans le temps réel. Et quand une requête passe de 185 ms à 2 ms après un index, tu sais que tu viens de faire de l'innovation.
Cette discipline, c'est exactement le cycle Build-Measure-Learn du Lean Startup. Tu n'ajoutes pas un index au hasard : tu écris une prédiction, tu exécutes la requête, tu compares le temps réel au coût estimé. Chaque itération te rapproche d'un schéma plus robuste.
Alors la prochaine fois qu'un collègue te dit "la requête est lente", réponds "on va faire une expérience". Tu verras, après quelques EXPLAIN ANALYZE, ton équipe ne parlera plus de tuning, mais d'expérimentation systématique. Et ça, c'est ce qui transforme un DBA en innovateur.