Optimisation des requêtes basée sur l’analyse du plan : EXPLAIN ANALYZE

Voilà le moment de vérité : les requêtes SQL, c’est pas juste des lignes de code, c’est carrément un dialogue avec ta base de données. Si tu lui murmures un gentil "SELECT *", la base va sûrement piger et exécuter la commande sans broncher. Mais si tu balances un roman SQL mal structuré, la base va cogiter… et peut-être commencer à ramer.

L’optimisation des requêtes, c’est l’art de parler à la base dans un langage clair et concis. Quand ta requête est propre et efficace, elle s’exécute vite, ne surcharge pas le système et ne gêne pas les autres process. Mais une requête foireuse peut ralentir tout le système : la base va pomper plus de CPU et de RAM, le disque va mouliner pour rien, et même les applis qui utilisent la base vont se mettre à laguer.

EXPLAIN ANALYZE t’aide à repérer ces points chauds et à comprendre où ta requête galère. C’est comme un diagnostic — sans ça, dur de soigner les perfs.

Problèmes typiques dans les requêtes et comment les détecter

Il est temps de faire connaissance avec les suspects qui flinguent les perfs. Pour ça, on sort l’arme fatale : EXPLAIN ANALYZE.

Problème 1 : Scan séquentiel (Seq Scan)

Seq Scan (scan séquentiel), c’est quand PostgreSQL cherche des données en lisant chaque ligne de la table. Ça passe si la table est petite, mais sur une grosse table, c’est la galère assurée.

Comment savoir si c’est un Seq Scan ? Fais juste un EXPLAIN ANALYZE. Exemple :

EXPLAIN ANALYZE
SELECT * 
FROM students 
WHERE student_id = 123;

Le résultat peut ressembler à ça (mate le Seq Scan) :

Seq Scan on students  (cost=0.00..35.50 rows=1 width=72) (actual time=0.010..0.015 rows=1 loops=1)

Comment régler le souci ?

Crée un index sur student_id si t’en as pas déjà :

CREATE INDEX idx_student_id ON students(student_id);

Après ça, relance EXPLAIN ANALYZE. Tu devrais voir Index Scan à la place de Seq Scan.

Problème 2 : faible sélectivité des conditions

La sélectivité, c’est combien de lignes il faut traiter pour trouver ce que tu veux. Si ton filtre touche quasi toute la table, l’index ne servira à rien.

Exemple de requête avec faible sélectivité :

EXPLAIN ANALYZE
SELECT * 
FROM students 
WHERE program = 'Computer Science';

Si 90% des étudiants sont en Computer Science, la requête risque de faire un Seq Scan, même si t’as un index sur program.

Comment améliorer la requête ?

1. Revois la logique de ta requête : peut-être qu’il faut préciser le filtre avec d’autres conditions.
2. Vérifie que les stats de la table sont à jour (ça aide PostgreSQL à bien estimer la sélectivité) :

ANALYZE students;

3. Si la requête utilise l’index alors qu’il faudrait un scan séquentiel, tu peux forcer PostgreSQL à l’utiliser :

SET enable_seqscan = OFF;

Problème 3 : opérations de tri inutiles

Le tri (Sort), ça peut coûter cher, surtout si les données tiennent pas en RAM. Typiquement, un ORDER BY déclenche un tri.

Exemple de souci :

EXPLAIN ANALYZE
SELECT * 
FROM students
ORDER BY last_name;

Tu peux voir un truc du genre :

Sort  (cost=123.00..126.00 rows=300 width=45) (actual time=1.123..1.234 rows=300 loops=1)

Comment accélérer le tri ? Si tu tries souvent sur une colonne, crée un index :

CREATE INDEX idx_last_name ON students(last_name);

Maintenant PostgreSQL peut utiliser l’index pour sortir les données déjà triées, sans faire de tri en plus.

Problème 4 : Pas de limite (LIMIT)

Quand tu fais un SELECT sans limiter le nombre de lignes retournées, la requête peut scanner toute la table, même si tu veux juste la première ligne.

Ça donne quoi :

EXPLAIN ANALYZE
SELECT * 
FROM students
WHERE gpa > 3.5;

Si la base a un million de lignes et que le filtre gpa > 3.5 renvoie 80% de la table, tu risques d’attendre longtemps.

Si tu veux juste les 10 meilleurs étudiants, utilise LIMIT :

SELECT *
FROM students
WHERE gpa > 3.5
ORDER BY gpa DESC
LIMIT 10;

En plus, tu peux utiliser LIMIT avec OFFSET pour faire de la pagination.

Gestion des paramètres d’exécution : SET

La commande SET dans PostgreSQL sert à changer les paramètres de la session ou d’une requête. C’est comme un réglage temporaire qui influence le comportement de la base juste pour la connexion en cours.

En gros, SET c’est un moyen de gérer "l’humeur" de PostgreSQL à la volée, sans toucher aux réglages globaux.

Où ça sert ?

• Changer la langue ou le format de date avant de lancer un rapport.
• Augmenter la mémoire pour une requête lourde.
• Désactiver les logs pendant un import massif.
• Changer temporairement le chemin de recherche des schémas (search_path).
• Gérer la sécurité (genre baisser les droits d’un user temporairement).

Syntaxe générale

SET paramètre = valeur;

Pour voir la valeur actuelle d’un paramètre :

SHOW paramètre;

Pour remettre la valeur par défaut :

RESET paramètre;

Exemple d’optimisation complète

Imaginons qu’on a une mission : trouver les 10 derniers étudiants avec le meilleur GPA (GPA), qui sont en Computer Science. Voilà la requête de base :

SELECT *
FROM students
WHERE program = 'Computer Science'
ORDER BY gpa DESC
LIMIT 10;

1. Analyse de la requête : Commence par faire un EXPLAIN ANALYZE :

   EXPLAIN ANALYZE
   SELECT * 
   FROM students
   WHERE program = 'Computer Science'
   ORDER BY gpa DESC
   LIMIT 10;
   

   Si tu vois un scan séquentiel et un tri, c’est le signal pour optimiser.

2. Index sur le filtre et le tri :

   Crée un index composite sur les deux colonnes :

   CREATE INDEX idx_program_gpa
   ON students(program, gpa DESC);
   

3. Vérifie les améliorations :

   Refais un EXPLAIN ANALYZE. Maintenant la requête doit utiliser l’index créé, sans tri ni scan séquentiel.

Méthodologie d’optimisation des requêtes

1. Commence par analyser le plan d’exécution actuel. Utilise EXPLAIN ANALYZE pour repérer les opérations problématiques.

2. Identifie les goulots d’étranglement. Cherche les nœuds du plan qui prennent le plus de temps ou de ressources.

3. Mets des index. Regarde quelles colonnes servent aux filtres et tris, et crée les index nécessaires.

4. Réduis le volume de données. Utilise LIMIT, OFFSET, et des filtres précis.

5. Mets à jour les stats. Fais un ANALYZE pour que PostgreSQL ait des infos fraîches sur la répartition des données.

6. Teste les changements. Après l’optimisation, refais un EXPLAIN ANALYZE pour vérifier les perfs.

Et après ?

Tu viens de te faire un crash course sur l’optimisation des requêtes. GG ! Plus tu vas jouer avec EXPLAIN ANALYZE, plus tu vas piger les rouages internes de PostgreSQL. Et retiens : aucun index magique ne te sauvera si ta requête est trop complexe ou mal fichue. SQL, comme n’importe quelle langue, aime la clarté.