Analyse comparative de EXPLAIN ANALYZE et pg_stat_statements

À ce stade, tu peux te demander logiquement : pourquoi on a besoin de deux outils différents pour l’analyse ? Lequel est le plus utilisé : EXPLAIN ANALYZE ou pg_stat_statements ? On va voir ensemble ces deux approches, leurs points forts et faibles, et aussi où et quand chacun s’utilise.

Les problèmes que ces outils résolvent

EXPLAIN ANALYZE : c’est l’outil pour analyser à fond une requête précise. Si tu veux savoir comment PostgreSQL exécute une requête, quels nodes il utilise, combien de lignes sont traitées, et combien de temps chaque opération prend, c’est celui qu’il te faut. Il répond à la question : "Pourquoi ma requête précise rame ?"

pg_stat_statements : c’est l’outil pour monitorer à un niveau plus global, il te donne des infos sur les perfs de toutes les requêtes qui tournent dans la base. C’est le bon choix si tu veux voir la big picture des perfs : "Quelles requêtes sont les plus lentes dans ma base ?" ou "Quelles requêtes mettent le plus la pression sur le serveur ?"

Quand utiliser EXPLAIN ANALYZE

EXPLAIN ANALYZE — c’est ton outil de debug pour piger comment PostgreSQL exécute une requête précise. Utilise-le dans ces cas-là :

Optimisation ciblée d’une requête Si quelqu’un râle parce qu’une page de ton appli met trois plombes à charger, la première chose à faire c’est de trouver la requête qui pose souci et de passer un EXPLAIN ANALYZE dessus. Ça va te montrer le plan d’exécution et les vraies métriques, genre le temps d’exécution et le nombre de lignes traitées.

Choisir le bon index Quand tu crées un nouvel index ou que tu modifies un existant, utilise EXPLAIN ANALYZE pour voir si PostgreSQL l’utilise vraiment. Sinon, c’est peut-être que ton index ne sert à rien pour optimiser les requêtes.

Débug de requêtes complexes Si tu codes une requête bien velue avec plein de JOIN ou de WHERE, analyser le plan d’exécution réel avec EXPLAIN ANALYZE va t’aider à trouver les goulots d’étranglement, genre les scans séquentiels inutiles (coucou Seq Scan).

Exemple : Optimiser une requête avec EXPLAIN ANALYZE

-- Requête qui rame
SELECT *
FROM students
WHERE name = 'Alice';

-- On analyse le plan d’exécution
EXPLAIN ANALYZE
SELECT *
FROM students
WHERE name = 'Alice';

Si tu vois que c’est un Seq Scan, t’as sûrement oublié de créer un index :

-- On crée un index sur la colonne name
CREATE INDEX idx_students_name ON students(name);

-- On vérifie à nouveau
EXPLAIN ANALYZE
SELECT *
FROM students
WHERE name = 'Alice';

Quand utiliser pg_stat_statements

Ce tool est indispensable pour analyser les perfs de tout le système. Utilise-le dans ces situations :

Monitoring en prod pg_stat_statements te montre les stats d’exécution des requêtes sur une période donnée. Tu peux facilement trouver les requêtes les plus lentes grâce à la colonne total_time, qui affiche le temps total passé sur chaque requête.

Trouver les requêtes "lourdes" Tu veux savoir quelles requêtes mettent le plus ta base à genoux ? Trie les requêtes par nombre de lectures mémoire (shared_blks_hit) ou par nombre de lignes traitées (rows).

Détecter les requêtes très fréquentes Parfois, c’est pas juste une requête lente qui pose problème, mais celles qui tournent tout le temps. Genre, si une requête s’exécute 100 fois par minute, même une petite optimisation peut vraiment soulager ton serveur.

Exemple : Trouver les requêtes lentes avec pg_stat_statements

-- Voir les stats des requêtes
SELECT query,
       calls,
       total_time,
       rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

Cette requête te sort le top 5 des requêtes qui bouffent le plus de temps.

Comparaison des approches : c’est quoi la diff ?

Utiliser les deux outils ensemble

Comme souvent en dev, y’a pas de bouton magique qui règle tout. Le mieux, c’est d’utiliser les deux outils ensemble. Par exemple :

1. Utilise pg_stat_statements pour repérer les requêtes les plus lentes ou les plus fréquentes dans ton système.

2. Puis analyse ces requêtes avec EXPLAIN ANALYZE pour piger pourquoi elles posent problème.

Exemple pratique : approche complète

-- Étape 1 : Trouver la requête la plus lente
SELECT query, total_time, calls
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 1;

-- Étape 2 : Analyser cette requête
EXPLAIN ANALYZE
<copie la requête de l’étape précédente>;

Erreurs classiques à éviter

Quand tu bosses avec EXPLAIN ANALYZE et pg_stat_statements, y’a quelques pièges dans lesquels tombent souvent les débutants :

1. Oublier la pertinence des données. Si tu analyses une requête sur une table vide, le résultat de EXPLAIN ANALYZE peut être trompeur. Vérifie que ta base de test reflète bien les volumes réels.

2. Ignorer la conso de ressources du monitoring. Si l’extension pg_stat_statements est activée en prod, assure-toi qu’elle est bien configurée et qu’elle ne surcharge pas le serveur.

3. Lire le plan théorique au lieu du réel. Rappelle-toi que EXPLAIN tout court donne juste le plan théorique. Utilise EXPLAIN ANALYZE pour avoir les vraies données.

Maintenant t’as toutes les clés pour non seulement te battre contre les requêtes lentes, mais aussi pour éviter qu’elles apparaissent. PostgreSQL te file des outils puissants, et bien les combiner te permet d’avoir des perfs au top même sur des systèmes bien chargés.

1

Étude/Quiz

Optimisation des requêtes, niveau 42, leçon 4

Indisponible

Optimisation des requêtes

Optimisation des requêtes

Ouvrir