pg_stat_statements : repérer les requêtes PostgreSQL coûteuses

pg_stat_statements aide à repérer les formes de requêtes PostgreSQL qui cumulent du temps sur plusieurs exécutions. Commencez par le temps total, puis examinez ensemble le nombre d’appels et la durée moyenne. Une requête rapide mais fréquente peut cumuler plus de temps qu’une requête lente rarement exécutée.

Ce guide active l’extension dans une base PostgreSQL 18 jetable, classe les requêtes et mesure les variations sur un intervalle sans remettre les compteurs partagés à zéro. Neuf contrôles de fonctionnement ont réussi sur un serveur local PostgreSQL 18.6 avec l’extension 1.12. La charge est synthétique : ces résultats ne mesurent ni le surcoût en production, ni la capacité, ni les performances d’une instance Hivenet.

Activer la collecte et les vues de la base

Il vous faut un serveur de test PostgreSQL 18 avec les fichiers de l’extension installés, un accès administrateur pour la configuration et une base jetable existante. Connectez-vous directement avec psql et conservez son mode autocommit habituel. Exécutez chaque bloc après la réussite du précédent. N’utilisez pas les données de démonstration dans une base de production.

Sur un nouveau serveur de test, ajoutez ce fragment à postgresql.conf :

# For a fresh disposable server. Preserve other libraries on an existing server.
shared_preload_libraries = 'pg_stat_statements'
compute_query_id = on
pg_stat_statements.track = top
pg_stat_statements.track_planning = off

Sur un serveur existant, ajoutez le module à la liste de préchargement sans remplacer les autres entrées. Son chargement nécessite un redémarrage du serveur, pas un simple rechargement de la configuration. Un service managé peut proposer ses propres paramètres et contrôles de maintenance. Vérifiez la disponibilité des extensions et la responsabilité des redémarrages lors du choix d’un hébergement PostgreSQL (en anglais).

La documentation des bibliothèques préchargées explique cette exigence au démarrage. Ici, compute_query_id est activé explicitement et le suivi des temps de planification reste désactivé. PostgreSQL propose aussi une activation automatique des identifiants ; consultez les paramètres de statistiques avant de modifier une configuration existante.

Après le redémarrage, connectez-vous à la base jetable avec le rôle administrateur prévu pour l’installation et exécutez :

CREATE EXTENSION pg_stat_statements;
SELECT extversion FROM pg_extension WHERE extname = 'pg_stat_statements';

CREATE EXTENSION installe les objets SQL dans la base courante. Le préchargement concerne le serveur. Notre test a confirmé que créer les objets seuls ne rendait pas la vue utilisable sans préchargement. Si l’extension existe déjà, examinez sa version et son schéma au lieu de la supprimer pour reproduire l’exemple. Les requêtes ci-dessous supposent que ses vues sont accessibles dans votre chemin de recherche.

SELECT version(), current_database(), current_user;
SHOW shared_preload_libraries;
SHOW compute_query_id;
SHOW pg_stat_statements.track;
SHOW pg_stat_statements.track_planning;

Vérifiez le serveur, la base, le rôle et les paramètres. Dans notre test, les identifiants de requête étaient activés, le suivi était réglé sur top et le suivi de planification était désactivé. Si vous utilisez le guide PostgreSQL avec Compose (en anglais), appliquez le préchargement avant le redémarrage et vérifiez ensuite les valeurs dans la base. Les limites de validation de ce guide concernant les conteneurs restent applicables.

Enregistrer la période d’observation

SELECT clock_timestamp() AS observed_at, stats_reset, dealloc
FROM pg_stat_statements_info;

Conservez ce résultat avec le rapport. stats_reset indique la dernière remise à zéro complète et dealloc signale l’éviction d’entrées. Chaque ligne possède également un champ stats_since. Des compteurs couvrant des périodes différentes ne permettent pas une comparaison avant-après fiable. Un redémarrage ne définit pas, à lui seul, une nouvelle période de mesure.

La référence pg_stat_statements de PostgreSQL 18 définit ces champs et les clés de regroupement : base, rôle exécutant, identifiant de requête et statut de premier niveau. Les exemples utilisent les colonnes de PostgreSQL 18 ; vérifiez votre version majeure et celle de l’extension avant de les adapter.

Repérer les requêtes au temps cumulé élevé

SELECT s.userid, s.queryid, s.calls,
       round(s.total_exec_time::numeric, 2) AS total_exec_ms,
       round(s.mean_exec_time::numeric, 2) AS mean_exec_ms,
       s.rows, s.shared_blks_hit, s.shared_blks_read,
       s.temp_blks_written, s.stats_since,
       left(s.query, 160) AS query
FROM pg_stat_statements AS s
WHERE s.dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
  AND s.toplevel AND s.calls > 0
ORDER BY s.total_exec_time DESC
LIMIT 10;

Ce rapport limite les résultats à la base connectée et aux requêtes de premier niveau ayant des exécutions terminées. Les temps cumulés sont exprimés en millisecondes. Examinez la période d’observation avant de décider si la première ligne mérite une investigation.

Exemple illustratif	Appels	Moyenne par appel	Temps total
Recherche fréquente	1 000	2 ms	2 000 ms
Rapport occasionnel	10	100 ms	1 000 ms

Ces chiffres illustrent un calcul, pas des mesures. La recherche cumule plus de temps, tandis que le rapport prend davantage de temps par exécution. La priorité dépend du problème rencontré par les utilisateurs. Une moyenne ne décrit pas les exécutions les plus lentes ; le temps cumulé n’est ni une mesure d’utilisation CPU ni la latence applicative de bout en bout.

Les compteurs de blocs partagés et temporaires orientent les vérifications suivantes. Une lecture hors du cache de PostgreSQL ne prouve pas une lecture physique sur disque : le système d’exploitation peut encore fournir les données depuis son cache. Des écritures temporaires peuvent justifier une recherche de débordement sur disque, sans en donner seules la cause. Un temps de planification nul est également peu informatif lorsque son suivi est désactivé.

Vérifier le regroupement avec une petite charge

Exécutez une seule fois ces instructions dans la même base jetable. Si diag_lab existe déjà, arrêtez-vous et choisissez une nouvelle base de test, sans écraser des objets inconnus.

CREATE SCHEMA diag_lab;
CREATE TABLE diag_lab.sales (id integer PRIMARY KEY, amount integer NOT NULL);
INSERT INTO diag_lab.sales
SELECT n, n % 100 FROM generate_series(1, 10000) AS n;
ANALYZE diag_lab.sales;
SELECT amount FROM diag_lab.sales WHERE id = 101;
SELECT amount FROM diag_lab.sales WHERE id = 202;
SELECT amount FROM diag_lab.sales WHERE id = 303;
SELECT sum(amount) FROM diag_lab.sales;

Relancez le rapport et retrouvez les recherches dans diag_lab.sales. Dans notre test, les trois valeurs d’identifiant ont produit une seule entrée avec calls = 3, rows = 3 et un texte représentatif se terminant par id = $1. La requête d’agrégation est restée distincte.

Le texte représentatif décrit un groupe, pas chaque exécution originale. Conservez la clé complète pour collecter les résultats ; le texte seul constitue une mauvaise clé de jointure. Ne considérez pas l’identifiant comme permanent entre versions majeures ou serveurs indépendants. Les requêtes de supervision peuvent aussi apparaître dans les statistiques.

Mesurer le nouveau travail sans effacer les compteurs

Gardez cette connexion directe psql ouverte. Après la charge initiale, créez une table temporaire de référence :

CREATE TEMP TABLE pgss_before AS
SELECT statement_timestamp() AS captured_at,
       s.dbid, s.userid, s.queryid, s.toplevel, s.stats_since,
       s.calls, s.total_exec_time, i.stats_reset, i.dealloc
FROM pg_stat_statements AS s
CROSS JOIN pg_stat_statements_info AS i
WHERE s.dbid = (SELECT oid FROM pg_database WHERE datname = current_database());

Cette table appartient à la session. Une autre connexion ne la retrouve pas et un pool en mode transaction peut déplacer les requêtes entre sessions du serveur. Utilisez une connexion directe pour l’exercice ; notre guide PgBouncer (en anglais) explique ces contraintes.

Exécutez maintenant trois recherches supplémentaires :

SELECT amount FROM diag_lab.sales WHERE id = 404;
SELECT amount FROM diag_lab.sales WHERE id = 505;
SELECT amount FROM diag_lab.sales WHERE id = 606;

Comparez les compteurs actuels à la référence :

SELECT s.userid, s.queryid,
       s.calls - b.calls AS interval_calls,
       round((s.total_exec_time - b.total_exec_time)::numeric, 3) AS interval_exec_ms,
       round(((s.total_exec_time - b.total_exec_time) /
              NULLIF(s.calls - b.calls, 0))::numeric, 3) AS interval_mean_ms,
       b.captured_at, statement_timestamp() AS observed_at,
       left(s.query, 160) AS query
FROM pg_stat_statements AS s
JOIN pgss_before AS b
  ON (s.dbid, s.userid, s.queryid, s.toplevel) =
     (b.dbid, b.userid, b.queryid, b.toplevel)
CROSS JOIN pg_stat_statements_info AS i
WHERE s.stats_since = b.stats_since
  AND i.stats_reset = b.stats_reset
  AND i.dealloc = b.dealloc
  AND s.calls > b.calls
  AND s.total_exec_time >= b.total_exec_time
ORDER BY interval_exec_ms DESC;

Notre résultat indiquait trois nouvelles exécutions de recherche, et non les six cumulées. La moyenne de l’intervalle correspond à la variation du temps total divisée par la variation du nombre d’appels. Soustraire deux moyennes cumulées ne donnerait pas cette valeur.

La requête joint les quatre champs d’identité, vérifie que le début de collecte de la ligne n’a pas changé, rejette une remise à zéro complète ou une variation du compteur d’évictions, et exclut les compteurs décroissants. Dans nos tests isolés, les remises à zéro d’une entrée et de l’ensemble ont invalidé l’ancienne référence, même lorsque les nouveaux appels dépassaient le nombre précédent. La procédure de l’article ne remet aucun compteur à zéro.

Cette comparaison prudente ne couvre que les entrées présentes dans les deux échantillons. Elle ne reconstitue ni les nouvelles entrées ni celles qui ont été évincées ; toute variation de dealloc rejette l’intervalle entier. Un résultat vide peut donc signaler une référence invalide, pas une absence d’activité. Après avoir identifié la cause, prenez une nouvelle référence. Le test ne couvre pas les remises à zéro concurrentes et l’exemple ne crée pas d’instantané atomique du serveur. Pour une collecte continue, utilisez un système de supervision dont les règles d’échantillonnage et de remise à zéro sont définies.

Maîtriser l’accès aux statistiques

Notre contrôle a confirmé qu’un rôle ordinaire ne pouvait pas consulter les identifiants des requêtes d’un autre rôle. Un rôle de test doté de pg_read_all_stats pouvait consulter leurs textes et identifiants. La documentation des rôles prédéfinis décrit la visibilité plus large accordée.

Faites approuver le rôle de supervision et son périmètre par l’administrateur. N’accordez pas cet accès étendu à une application pour faciliter un rapport. Traitez les textes collectés comme potentiellement sensibles : le regroupement des constantes ne garantit pas l’anonymisation. Le test utilisait des valeurs synthétiques et un rôle temporaire, sans modifier les accès d’un serveur existant.

Choisir l’investigation suivante

Avec beaucoup d’appels, examinez les répétitions côté application. Avec une moyenne élevée, reproduisez des paramètres représentatifs sur des données de test adaptées, puis examinez le plan. EXPLAIN ANALYZE exécute la requête : son utilisation exige donc un périmètre et des précautions propres. Ce guide s’arrête au choix de la requête à examiner.

Pour un incident en cours, consultez aussi l’activité actuelle et les attentes. La documentation de pg_stat_activity présente cette vue. Les totaux historiques ne suffisent pas à identifier la session qui bloque actuellement une demande.

Les erreurs de configuration ont des causes distinctes : une relation absente suggère les objets de l’extension ou le chemin de schéma ; une erreur de préchargement concerne le démarrage ; un texte masqué suggère les droits. En l’absence de lignes utiles, vérifiez la base connectée, le mode de suivi, la charge exécutée et la période avant de modifier les paramètres.

Nettoyer uniquement les objets du test

DROP TABLE pgss_before;
DROP TABLE diag_lab.sales;
DROP SCHEMA diag_lab;

Exécutez ces commandes dans la session et la base jetable utilisées précédemment. La table et le schéma nommés sont ceux de l’exercice. L’extension et les statistiques partagées restent en place. Nous avons aussi arrêté le serveur local séparé après validation. Le test ne couvrait aucune base de production, aucun conteneur Docker, aucun déploiement cloud ni aucune mesure de performance réaliste.