Analyse des requêtes lentes avec pg_stat_statements

Quand tu bosses sur des projets réels, ton appli peut avoir des milliers d’utilisateurs en même temps. Ils balancent des requêtes à la base, ajoutent des données, lisent, mettent à jour... Et là, tu remarques que ton serveur commence à “gémir”. C’est le signal que tes requêtes sont loin d’être optimisées. Parfois, une requête qui avait l’air cool “sur le papier” peut devenir un vrai cauchemar pour tes perfs. C’est là que pg_stat_statements entre en scène.

pg_stat_statements te permet de :

1. Suivre les requêtes lentes.
2. Voir combien de fois chaque requête a été exécutée.
3. Savoir combien de temps elles prennent.
4. Voir le temps moyen d’exécution d’une requête.
5. Éviter la grosse boulette de tout réécrire pour rien !

Explorer la structure de pg_stat_statements

Une fois l’extension activée, tu as une vue spéciale pg_stat_statements dans ta base. Elle stocke toutes les infos sur les requêtes exécutées. On va voir ce qu’il y a dedans :

SELECT * FROM pg_stat_statements LIMIT 1;

Le résultat peut ressembler à ça (version simplifiée) :

Petites explications :

• requête — la requête SQL elle-même.
• exécutions — combien de fois cette requête a été exécutée.
• temps_total — le temps total passé sur cette requête.
• lignes — le nombre de lignes retournées par la requête.
• blocs_partagés_lus — combien de blocs ont été lus (on tape sur le disque si tu n’utilises pas le cache).

Analyse des résultats

Maintenant que pg_stat_statements est activé, voyons comment trouver les requêtes lentes.

Les requêtes les plus lentes

Pour repérer celles qui prennent le plus de temps, tu peux utiliser :

SELECT query, total_time, calls, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

Ici :

• mean_time — c’est le temps moyen d’exécution d’une requête (total_time / calls).
• ORDER BY total_time DESC — on trie par temps total décroissant.

Les requêtes les plus fréquentes

Parfois, le souci ce n’est pas la lenteur, mais le fait qu’une requête tourne trop souvent. Par exemple :

SELECT query, calls
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 5;

Optimisation des requêtes

1. Utilise les index

Si tu vois que certaines requêtes sur des colonnes précises sont lentes, vérifie s’il y a un index dessus. Par exemple, tu as une table étudiants avec plein de lignes, et tu fais souvent des requêtes sur le champ nom_de_famille. Tu devrais créer un index :

CREATE INDEX idx_etudiants_nom_de_famille ON étudiants (nom_de_famille);

2. Réécris la requête

Imaginons que tu vois une requête du genre SELECT * FROM commandes WHERE montant > 1000 qui prend trop de temps. Souvent, au lieu de “tout prendre”, il vaut mieux ne sélectionner que les colonnes utiles :

SELECT id_commande, montant FROM commandes WHERE montant > 1000;

Réinitialiser les stats

Parfois, pour voir seulement les nouveaux résultats (genre après une optimisation), il faut remettre à zéro les données de pg_stat_statements. Ça se fait avec :

SELECT pg_stat_statements_reset();

C’est comme le bouton “Reset” sur ta calculette. Après ça, les stats repartent de zéro.

Détecter les requêtes problématiques

Imagine que tu es admin de la base d’une université, et que les étudiants râlent parce que leur espace perso rame à mort. Tu décides de checker pg_stat_statements :

Étape 1 : Trouver la requête la plus lente

SELECT query, total_time, calls, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 1;

Tu vois qu’une requête du genre SELECT * FROM étudiants WHERE statut = 'actif' prend 30 secondes. Ouch. Faut agir vite.

Étape 2 : Vérifier l’indexation En analysant la table étudiants, tu remarques que la colonne statut n’a pas d’index. Tu corriges ça :

CREATE INDEX idx_etudiants_statut ON étudiants (statut);

Étape 3 : Vérifier le résultat Après l’optimisation, tu re-check pg_stat_statements et tu vois que la requête passe à 0,5 seconde. Victoire !

Erreurs fréquentes avec pg_stat_statements

Parfois, les admins font des boulettes en analysant les requêtes :

1. Extension non activée. Si tu oublies d’ajouter pg_stat_statements dans shared_preload_libraries, aucune stat ne sera collectée.
2. Ignorer l’indexation. Même si les requêtes sont lentes, parfois il suffit d’ajouter les bons index pour régler le problème.
3. Ne pas réinitialiser les stats. Si tu ne fais pas pg_stat_statements_reset(), les vieilles données polluent ton analyse.

Utiliser pg_stat_statements dans ton taf, c’est comme avoir un GPS pour ta base : il te dit exactement où tu es bloqué dans les “embouteillages”, et te file même des pistes pour contourner. Bien configuré, cet outil peut vraiment booster les perfs de tes bases de données.