Analyse des performances des fonctions et procédures : utilisation de EXPLAIN ANALYZE

EXPLAIN ANALYZE t'aide à piger comment PostgreSQL "réfléchit" quand il exécute ta requête :

• Quelles étapes sont effectuées pour traiter les données.
• Combien de temps prend chaque étape.
• Pourquoi une requête est lente — genre un scan complet de la table (Seq Scan en anglais) ou un index ignoré.

La commande EXPLAIN ANALYZE exécute vraiment la requête et te montre comment PostgreSQL optimise l'exécution. Imagine que tu démontes une montre pour comprendre comment elle marche. C'est pareil avec EXPLAIN ANALYZE, mais pour tes requêtes SQL.

Syntaxe de EXPLAIN ANALYZE

On commence simple. Voilà à quoi ressemble la commande de base :

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Cette requête va exécuter le SELECT et te montrer comment PostgreSQL traite les données.

Le résultat de EXPLAIN ANALYZE est un arbre d'exécution de la requête. Chaque niveau de l'arbre décrit une étape que PostgreSQL effectue :

• Type d'opération — genre Seq Scan, Index Scan, etc.
• Cost — combien PostgreSQL estime que cette opération va coûter.
• Rows — combien de lignes sont attendues et combien il y en a vraiment au final.
• Time — combien de temps l'opération a pris.

Exemple de sortie :

Seq Scan on students  (cost=0.00..35.50 rows=1000 width=64) (actual time=0.023..0.045 rows=250 loops=1)
  Filter: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.234 ms

Regarde bien le Seq Scan on students. Ça veut dire que PostgreSQL parcourt TOUTES les lignes de la table students. Si la table est grosse, ça peut être VRAIMENT LENT.

Exemples d'utilisation de EXPLAIN ANALYZE

Allez, on va voir quelques exemples pratiques pour apprendre à repérer et corriger les soucis dans les requêtes.

Exemple 1 : scan complet de la table

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Sortie :

Seq Scan on students  (cost=0.00..35.50 rows=1000 width=64) (actual time=0.023..0.045 rows=250 loops=1)
  Filter: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.234 ms

Le souci ici, c'est que PostgreSQL fait un Seq Scan, donc il parcourt toutes les lignes de la table. Si t'as des millions de lignes, ça va devenir un vrai goulot d'étranglement.

La solution : on crée un index sur la colonne age.

CREATE INDEX idx_students_age ON students(age);

Maintenant, relance la même requête :

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Sortie :

Index Scan using idx_students_age on students  (cost=0.29..12.30 rows=250 width=64) (actual time=0.005..0.014 rows=250 loops=1)
  Index Cond: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.045 ms

On voit maintenant un Index Scan au lieu d'un Seq Scan. Nickel, la requête est super rapide !

Exemple 2 : requête complexe avec JOIN

Imaginons qu'on a deux tables : students et courses. On veut connaître les noms des étudiants et les noms des cours auxquels ils sont inscrits.

EXPLAIN ANALYZE
SELECT s.name, c.course_name
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id;

La sortie pourrait ressembler à ça :

Nested Loop  (cost=1.23..56.78 rows=500 width=128) (actual time=0.123..2.345 rows=500 loops=1)
  -> Seq Scan on students s  (cost=0.00..12.50 rows=1000 width=64) (actual time=0.023..0.045 rows=1000 loops=1)
  -> Index Scan using idx_enrollments_student_id on enrollments e  (cost=0.29..2.345 rows=3 width=64) (actual time=0.005..0.014 rows=3 loops=1000)
  -> Index Scan using idx_courses_id on courses c  (cost=0.29..2.345 rows=3 width=64) (actual time=0.005..0.014 rows=3 loops=1000)
Execution Time: 2.456 ms

Comme tu vois, PostgreSQL gère bien : il utilise les index sur les tables enrollments et courses, et l'exécution est rapide. Mais si un index manque, tu pourrais voir un Seq Scan qui ralentit tout.

Optimisation des performances des fonctions

Maintenant, imaginons qu'on a une fonction qui retourne la liste des étudiants plus âgés qu'un certain âge :

CREATE OR REPLACE FUNCTION get_students_older_than(min_age INT)
RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
  RETURN QUERY
  SELECT id, name
  FROM students
  WHERE age > min_age;
END;
$$ LANGUAGE plpgsql;

On peut analyser les perfs de cette fonction avec EXPLAIN ANALYZE :

EXPLAIN ANALYZE
SELECT * FROM get_students_older_than(20);

Accélérer l'exécution de la fonction

Si la fonction est lente, c'est sûrement à cause d'un scan complet de la table. Pour corriger ça :

1. Vérifie que la colonne utilisée dans les filtres (age) est bien indexée.
2. Regarde combien de lignes il y a dans la table et pense à faire du partitionnement si t'as trop de données.

Goulots d'étranglement et comment les corriger

1. Scan complet des tables (Seq Scan). Utilise des index pour accélérer la recherche de lignes. Mais attention, trop d'index peut ralentir les insertions.

2. Beaucoup de lignes dans le résultat. Si ta requête retourne des millions de lignes, pense à ajouter des filtres (WHERE, LIMIT) ou à faire de la pagination (OFFSET).

3. Opérations "chères". Certaines opérations, comme le tri, l'agrégation ou les gros JOIN, peuvent bouffer pas mal de ressources. Utilise des index ou découpe tes requêtes en plusieurs étapes.