Tu l'as déjà fait sans y penser : tu colles un EXPLAIN ANALYZE devant une requête pour déboguer un plan d'exécution. Résultat ? La requête s'exécute pour de vrai. Si c'est un UPDATE sur une table de production, tu viens potentiellement de modifier des données en direct. Panique à bord.
La différence entre EXPLAIN et EXPLAIN ANALYZE est simple : le premier se contente d'estimer le plan sans rien exécuter, le second exécute réellement la requête pour collecter les statistiques réelles (temps, lignes, boucles). C'est un outil puissant, mais dangereux si tu oublies qu'il a des effets de bord.
Le pire ennemi ? Les requêtes DML (INSERT, UPDATE, DELETE). Un EXPLAIN ANALYZE sur un DELETE supprime les lignes. Même avec un ROLLBACK, tu risques des verrous, des séquenceurs incrémentés, ou des effets secondaires (triggers, contraintes). J'ai déjà vu un collègue exécuter un EXPLAIN ANALYZE sur un UPDATE en production sans le wrapper dans une transaction. Il a changé 300 000 lignes. Le rollback a traîné 20 minutes.
Alors comment l'utiliser en sécurité ? Toujours dans une transaction avec ROLLBACK. Exemple :
Ça te donne les vrais temps et lignes sans persister les modifications. Mais attention : même ainsi, la requête s'exécute, ce qui peut déclencher des triggers, occuper des verrous, ou générer du WAL. Utilise-le avec parcimonie.
Parfois, un simple EXPLAIN suffit. Si tu veux juste la structure du plan, les coûts estimés, ou que la requête est trop lourde pour être exécutée (même rollbackée), ne mets pas ANALYZE. C'est souvent le cas sur des grosses tables ou des requêtes complexes où le coût d'exécution dépasse le bénéfice du debug.
En bref : EXPLAIN ANALYZE est un couteau suisse. Bien utilisé, il te sauve la mise. Mal utilisé, il peut te coûter une nuit blanche. La règle d'or : toujours en transaction, jamais sur une DML sans protection, et préfère EXPLAIN seul quand l'estimation suffit.
EXPLAIN vs EXPLAIN ANALYZE : Quelles différences ?
Si tu penses qu'EXPLAIN exécute ta requête, tu vas tout droit dans le mur. EXPLAIN ne fait qu'estimer le plan d'exécution sans toucher aux données. EXPLAIN ANALYZE, lui, exécute vraiment. C'est la différence entre lire une carte et marcher sur le terrain.
Concrètement, EXPLAIN te montre ce que PostgreSQL pense qu'il va faire : les coûts estimés, le nombre de lignes estimé, les opérateurs. Mais ces estimations peuvent être fausses, surtout si tes statistiques sont obsolètes. EXPLAIN ANALYZE, en exécutant, te donne les vrais temps et les vraies lignes. Exemple typique : un EXPLAIN peut prédire 1000 lignes, mais l'ANALYZE en montre 10 000. Là, tu sais que tes stats sont à jour. Voici la différence en pratique :
En résumé, EXPLAIN c'est pour une vue macroscopique rapide, sans risque. EXPLAIN ANALYZE c'est pour le diagnostic précis, mais avec des effets de bord. Mon conseil : commence toujours par un EXPLAIN seul. Si l'estimation te semble bizarre, passe à l'ANALYZE dans une transaction rollbackée. Et ne fais jamais confiance à un plan sans vérifier les vraies valeurs.
Les effets de bord de EXPLAIN ANALYZE sur les données
Tu crois qu'EXPLAIN ANALYZE se contente d'observer ? Erreur fatale : il exécute réellement ta requête. Si c'est un UPDATE, il modifie les lignes. Si c'est un DELETE, il les supprime. Même dans une transaction avec ROLLBACK, les séquenceurs avancent — les valeurs de SERIAL ou IDENTITY sautent. Et les triggers se déclenchent, laissant des traces (logs, envois d'emails, mises à jour d'autres tables).
Exemple vécu : un collègue lance un EXPLAIN ANALYZE sur un INSERT dans une table de production. Pas de transaction. Résultat : 50 000 lignes dupliquées. Le rollback a duré 15 minutes et les séquenceurs étaient déjà incrémentés. Le bordel. Même si tu penses être protégé par un ROLLBACK, les effets secondaires persistent : les verrous sont acquis pendant toute la durée de l'exécution, le WAL est rempli, et la contention grimpe.
Je te le dis franchement : ne fais jamais ça en production sans blindage. Toujours une transaction explicite, toujours un ROLLBACK, et vérifie qu'il n'y a pas de triggers non réversibles. Et si tu veux juste le plan, EXPLAIN seul suffit. Moins risqué, moins de trafic, et tu évites de transformer ton analyse en incident.
- Modification effective des données (UPDATE, DELETE, INSERT).
- Avancement des séquenceurs (gap d'IDs).
- Exécution des triggers (logs, envois).
- Verrous et contention sur la table.
- Génération de WAL inutile.
Exécuter EXPLAIN ANALYZE sur une DML sans modifier les données
Tu veux analyser l'exécution d'une DML sans risquer de modifier les données ? La solution est connue : enveloppe tout dans une transaction et rollback juste après. Exemple typique :
Ça te donne les temps réels sans toucher aux données. Mais ne te méprends pas : la transaction exécute vraiment l'opération. PostgreSQL verrouille les lignes, déclenche les triggers, et incrémente les séquences. Tout ça pendant le temps de l'EXPLAIN ANALYZE.
Si ta requête modifie des millions de lignes, les verrous restent actifs jusqu'au ROLLBACK. Sur une table en production, c'est l'assurance de bloquer d'autres sessions. Sans parler des effets secondaires comme l'envoi de mails via un trigger ou la consommation d'IDs de séquence. J'ai déjà vu un EXPLAIN ANALYZE sur un INSERT générer un trou de 10 000 dans une séquence.
La règle d'or : garde cette technique pour des DML légères ou des environnements isolés. Pour du lourd, utilise EXPLAIN seul ou des outils comme auto_explain avec pg_stat_statements. Si tu dois absolument analyser une grosse DML, fais-le sur une copie de la base. Le bénéfice de l'information ne vaut pas toujours le risque.
Quand éviter EXPLAIN ANALYZE en production et utiliser EXPLAIN seul
Le premier réflexe, c'est de vouloir tout mesurer avec ANALYZE. Mauvaise idée. Surtout en production, où chaque exécution a un coût et un risque.
Le problème numéro un : les effets de bord. Même enrobé dans une transaction avec ROLLBACK, EXPLAIN ANALYZE exécute réellement la requête. Les triggers sautent, les verrous sont pris, le Write-Ahead Log gonfle. J'ai vu un EXPLAIN ANALYZE sur un INSERT ... SELECT verrouiller une table pendant 15 secondes en pleine charge. Le ROLLBACK a libéré les lignes, mais pas le temps perdu.
Un simple EXPLAIN t'évite tous ces tracas. Il ne fait qu'estimer, sans rien exécuter. Tu obtiens le plan, les coûts, les opérateurs. C'est souvent suffisant pour détecter un mauvais index, un seq scan inattendu ou un mauvais join.
Alors quand passer en ANALYZE ? Seulement quand l'estimation ne colle pas avec la réalité et que tu as besoin des métriques réelles. Et même là, préfère un replica ou clone de prod. Sur une table de 10 millions de lignes, un EXPLAIN ANALYZE peut prendre 30 secondes pendant lesquelles ta base trinque.
Ma règle : je commence toujours par un EXPLAIN (option (FORMAT JSON) pour plus de détails). Si les estimations divergent, je passe en ANALYZE sur un environnement isolé. Jamais de DML en production sans une transaction, et uniquement si je suis prêt à assumer le stress.
Exemple concret :
Tu vois la différence ? Le premier peut tout foutre en l'air. Le second te donne une estimation sans risque. Le troisième est le compromis acceptable, mais pas en pic de charge.
En bref : EXPLAIN t'évite 90% des problèmes. EXPLAIN ANALYZE est réservé au diagnostic précis, pas au réflexe quotidien.
Conclusion
Ne fais pas la confusion : EXPLAIN ne touche à rien, EXPLAIN ANALYZE exécute pour de vrai. En production, les DML avec ANALYZE sont une bombe à retardement. Même avec un ROLLBACK, tu n'es pas à l'abri d'effets de bord : verrous longue durée, incrémentation de séquences, déclenchement de NOTIFY, écritures WAL. J'ai déjà vu un EXPLAIN ANALYZE INSERT déclencher une cascade de triggers qui a envoyé des emails à tout le client. Pas ouf.
Mon conseil : garde EXPLAIN ANALYZE pour les requêtes SELECT en dev ou staging. Pour les DML, préfère EXPLAIN seul. Si tu as vraiment besoin des stats réelles, utilise un snapshot de la base ou une transaction temporaire avec ROLLBACK, mais en pleine connaissance des risques. Voici la seule pattern acceptable :
Mais honnêtement, dans 80% des cas, un EXPLAIN te donne assez d'infos pour optimiser. Évite la tentation d'ajouter ANALYZE par réflexe. Ton cerveau, ta base et ton équipe te remercieront.