Introduction à l'analyse de la performance des requêtes

Laisse-moi commencer par une histoire vraie. Imagine que tu bosses comme dev sur une grosse plateforme e-commerce. Tout roule, sauf un détail : le rapport des ventes mensuelles est tellement lent à charger que tes utilisateurs ont le temps de passer une autre commande avant qu'il ne s'affiche. Tu stresses, ton manager stresse, le serveur PostgreSQL chauffe comme une bouilloire, et ça sent un peu la fin du monde. Maintenant, imagine que tu pourrais trouver la cause et la corriger d'un coup de baguette magique.

L'analyse de la performance des requêtes, c'est un peu comme un check-up médical pour ton PostgreSQL. Ça t'aide à trouver les "goulots d'étranglement" (les points douloureux) et à les corriger pour améliorer l'expérience utilisateur et économiser les ressources système.

Comment les requêtes fonctionnent sous le capot de PostgreSQL ?

Quand tu écris une requête simple du genre :

SELECT * FROM products WHERE price > 100;

PostgreSQL ne fonce pas direct pour sortir les données. Il commence par analyser ta requête, réfléchit à la meilleure façon de l'exécuter, et seulement après il passe à l'action.

Les étapes principales de l'exécution des requêtes :

1. Parsing. PostgreSQL vérifie ta requête pour les erreurs de syntaxe et la transforme en une représentation intermédiaire.
2. Optimisation. L'optimiseur de requêtes évalue plusieurs façons d'exécuter la requête et choisit la plus "économique" (en temps et en ressources).
3. Exécution. Le serveur suit le plan choisi et va chercher les données.

C'est quoi un "goulot d'étranglement" ?

Un "goulot d'étranglement", c'est la partie de la requête qui ralentit tout le reste. Ça peut être une opération qui, sans prévenir, bouffe le plus de temps ou de ressources. Par exemple, si PostgreSQL fait un scan complet de la table (Seq Scan) au lieu d'utiliser un index rapide, la requête rame. Ou alors, s'il y a beaucoup plus de données que prévu, le serveur passe un temps fou à trier, joindre, filtrer.

Ces moments-là, c'est ce qu'on appelle les goulots d'étranglement — c'est eux qu'il faut traquer et optimiser en priorité.

Outils pour analyser la performance des requêtes

PostgreSQL propose plusieurs outils puissants pour t'aider à identifier les soucis dans tes requêtes :

1. EXPLAIN et EXPLAIN ANALYZE. Ces commandes te montrent comment PostgreSQL compte exécuter la requête, ou même l'exécutent pour mesurer la perf réelle.

• EXPLAIN : affiche le plan d'exécution de la requête sans vraiment l'exécuter.
• EXPLAIN ANALYZE : exécute la requête et affiche le plan réel avec les vraies métriques de temps.

Exemple d'utilisation de EXPLAIN :

EXPLAIN SELECT * FROM products WHERE price > 100;

Sortie :

Seq Scan on products  (cost=0.00..35.50 rows=5 width=72)
  Filter: (price > 100)

Ici, tu vois que la requête fait un "Seq Scan" — un scan complet de la table, ce qui n'est pas top pour les grosses tables.

2. pg_stat_statements. C'est une extension en plus qui garde la trace des requêtes exécutées. Elle te montre :

• Quelles requêtes tournent sur le serveur.
• Combien de temps chaque requête prend.
• Combien de lignes la requête retourne et combien de ressources elle consomme.

Pour activer pg_stat_statements, il faut :

1. Activer l'extension :

CREATE EXTENSION pg_stat_statements;

2. Configurer PostgreSQL : Dans le fichier postgresql.conf, ajoute :

   shared_preload_libraries = 'pg_stat_statements'
   pg_stat_statements.track = all

3. Redémarre PostgreSQL.

Maintenant tu peux analyser les requêtes :

SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

Ça va te montrer les 5 requêtes les plus "lourdes" classées par temps total d'exécution décroissant.

3. Logging des requêtes lentes. Tu peux configurer PostgreSQL pour qu'il enregistre dans un log les requêtes qui prennent trop de temps (genre plus d'une seconde).

Pour ça, dans postgresql.conf mets :

log_min_duration_statement = 1000  # Temps en millisecondes (1 seconde)

Maintenant, les requêtes lentes seront enregistrées dans les logs de PostgreSQL.

Les métriques clés pour analyser la performance

Quand tu analyses la perf des requêtes, mate bien ces métriques principales :

1. Temps d'exécution. C'est le temps que met la requête à s'exécuter. Plus c'est rapide, mieux c'est.
2. Nombre de lignes. Si ta requête retourne ou scanne plus de lignes que prévu, ça peut être un souci.
3. Utilisation des index. Si la requête devrait utiliser un index mais fait un scan séquentiel (Seq Scan), c'est un signal pour optimiser.
4. Buffers et opérations disque. Les requêtes qui tapent beaucoup sur le disque sont plus lentes que celles qui bossent en RAM.

Comment utiliser tout ça en pratique ?

Exemple 1 : Requête lente

Tu écris une requête pour choper tous les produits à plus de 100 :

SELECT * FROM products WHERE price > 100;

Tu remarques que la requête est super lente. Tu utilises EXPLAIN et tu vois :

Seq Scan on products  (cost=0.00..35.50 rows=5 width=72)
  Filter: (price > 100)

Le problème : la requête fait un scan complet de la table parce qu'il n'y a pas d'index sur la colonne price.

Solution :

Crée un index :

CREATE INDEX idx_price ON products(price);

Maintenant la requête utilise un Index Scan :

Index Scan using idx_price on products  (cost=0.15..8.25 rows=5 width=72)
  Index Cond: (price > 100)

Exemple 2 : Trouver les requêtes lentes avec pg_stat_statements

Avec la commande :

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

Tu repères une requête qui prend beaucoup de temps à s'exécuter. Tu l'ouvres avec EXPLAIN ANALYZE, tu la corriges, et elle devient plus rapide.

Quand tu commenceras à utiliser EXPLAIN, pg_stat_statements et les autres outils, tes requêtes vont carburer, et ton serveur PostgreSQL tournera comme une horloge suisse. Dans la prochaine leçon, on va plonger dans les détails des paramètres EXPLAIN comme cost, rows et width, pour que tu puisses lire les plans d'exécution comme un livre ouvert.