Suivi des requêtes lentes avec pg_stat_statements

pg_stat_statements — c’est une extension native de PostgreSQL qui te permet de voir ce qui se passe vraiment dans ta base et comment les requêtes se comportent. En gros, c’est un assistant discret mais super attentif qui note chaque mouvement : quelles requêtes SQL ont été exécutées, combien de temps elles ont pris, à quelle fréquence elles tournent et à quel point elles chargent le système.

Pourquoi c’est utile ? D’abord, pour trouver les requêtes problématiques. Parfois, ta base rame pas à cause d’un seul méchant, mais à cause d’une dizaine de requêtes lourdes identiques qui tournent trop souvent. Ensuite, les stats t’aident à voir quelles requêtes bouffent le plus de ressources — CPU, RAM, disque. Et tu peux aussi checker si tes indexes bossent comme prévu : peut-être qu’ils sont pas utilisés du tout à certains endroits, ou au contraire, il en manque.

pg_stat_statements te permet d’arrêter de deviner et de voir les vrais chiffres — et de t’appuyer dessus pour tirer des conclusions et optimiser.

Comment trouver les requêtes lentes ?

Là, ça devient fun ! En utilisant la table pg_stat_statements, on peut chasser les requêtes qui prennent trop de temps ou qui mettent le serveur à genoux.

L’idée principale :

Chaque ligne dans la table pg_stat_statements représente les stats d’une requête. Les requêtes sont groupées par leur texte (champ query), et pour chacune, on compte les métriques suivantes :

• total_time — temps total d’exécution de la requête, en millisecondes.
• calls — nombre d’exécutions de la requête.
• mean_time — temps moyen d’exécution (total_time / calls).
• rows — nombre de lignes retournées par la requête.

Exemple d’analyse simple

Essayons de trouver les requêtes les plus lentes en moyenne :

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

Cette requête va te sortir le TOP-5 des requêtes les plus longues à s’exécuter. Mate bien la colonne mean_time : si tu vois des valeurs en millisecondes qui dépassent 500-1000, c’est un signal qu’il faut optimiser ces requêtes.

Exemple d’analyse de requêtes lentes

Voyons un exemple :

Voici le résultat de la requête précédente :

Qu’est-ce qu’on voit ?

Requête sur la table orders : elle s’exécute très rarement (seulement 10 appels), mais à chaque fois elle ramène 10 000 lignes. Probablement, la table est énorme et la requête n’utilise pas d’index.

Requête sur la table products : elle est appelée des milliers de fois, peut-être dans une boucle côté appli. Chaque sélection ne retourne que 100 lignes, mais vu la fréquence, ça peut aussi être un souci.

Requête sur la table customers : elle est rapide (543 ms), mais elle est appelée beaucoup trop souvent.

Optimisation des requêtes lentes

Maintenant qu’on a trouvé les requêtes problématiques, il faut regarder leur plan d’exécution avec EXPLAIN ANALYZE. Par exemple, pour la requête sur la table orders :

EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'nouveau';

Qu’est-ce qu’on peut voir ?

Seq Scan : si la requête fait un scan séquentiel, il faut ajouter un index :

CREATE INDEX idx_orders_status ON orders (status);

Problèmes de filtrage : si la requête ramène trop de lignes, revois le texte de la requête. Peut-être qu’il faut ajouter des conditions ou limiter les résultats :

SELECT * FROM orders WHERE status = 'nouveau' LIMIT 100;

Afficher les stats sur le temps d’exécution

Parfois, les requêtes problématiques ne sont pas si évidentes. Par exemple, celles qui appellent souvent des fonctions ou des sous-requêtes. Dans ces cas-là, c’est pratique de regarder la colonne total_time :

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

Cette requête va te montrer les requêtes les plus “chères” en temps total d’exécution.

Optimisation de l’indexation

Souvent, les requêtes lentes sont dues à l’absence d’index nécessaires. Utilise pg_stat_statements pour voir quelles requêtes n’utilisent pas d’index. Si tu vois plein de requêtes avec les mêmes filtres (genre sur le champ status), mais qu’elles sont lentes, ajoute l’index qui va bien :

CREATE INDEX idx_orders_status ON orders (status);

Après ça, vérifie à nouveau les perfs de la requête avec EXPLAIN ANALYZE.

En utilisant pg_stat_statements, tu peux suivre efficacement les perfs de tes requêtes, repérer les “goulots d’étranglement” et améliorer les performances de ta base. Rappelle-toi : plus tu commences tôt à analyser les requêtes, plus ce sera simple d’optimiser tout le système.