Optimisation des requêtes avec CTE

Aujourd’hui, on plonge dans le monde palpitant (et parfois un peu flippant) de l’optimisation des requêtes avec les CTE (Common Table Expressions). Si tu sais déjà comment créer des CTE (on en a parlé dans les cours précédents), il est temps de voir les subtilités de leur fonctionnement interne, les pièges à éviter et comment en tirer le max d’efficacité.

À première vue, les CTE ont l’air parfaits : c’est propre, facile à écrire, ça permet de découper le code en blocs logiques. Mais il y a un petit (ou pas si petit) détail. PostgreSQL a une façon bien à lui de gérer les CTE, et ça peut impacter les perfs.

Quand PostgreSQL voit WITH, il matérialise généralement le résultat du CTE. Ça veut dire que les données renvoyées par le CTE sont d’abord calculées et stockées comme une table temporaire, qui est ensuite utilisée dans la requête principale. Pratique pour la réutilisation, mais ça peut devenir un souci si :

1. Le volume de données dans le CTE est énorme, mais on n’utilise qu’une partie du résultat.
2. Le CTE est appelé trop de fois, ce qui augmente les overheads.
3. On crée des CTE trop complexes qui ne servent pas vraiment.

Découverte de la matérialisation

La matérialisation, c’est le process où PostgreSQL stocke le résultat du CTE en mémoire ou sur disque (selon la taille des données). Ça veut dire que les données sont extraites une seule fois, mais si tu utilises le CTE qu’à un seul endroit, la matérialisation peut être de trop. Par exemple :

WITH large_set AS (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)
SELECT student_id, grade
FROM large_set
WHERE grade > 90;

Ici, PostgreSQL crée d’abord une table temporaire avec tout le résultat du CTE (grade > 60), puis filtre les lignes où grade > 90. Ça rajoute une étape intermédiaire inutile et ça impacte les perfs.

Comment éviter la matérialisation inutile ?

Depuis PostgreSQL 12, on peut éviter la matérialisation du CTE quand c’est pas nécessaire. Pour ça, on utilise le mot-clé MATERIALIZED (par défaut) ou NOT MATERIALIZED. Exemple :

WITH large_set AS NOT MATERIALIZED (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)
SELECT student_id, grade
FROM large_set
WHERE grade > 90;

Là, on dit à PostgreSQL de ne pas matérialiser les données de large_set, mais d’intégrer la requête direct dans l’expression principale. C’est plus efficace, car il n’y a pas de table intermédiaire créée.

Quand la matérialisation est utile ?

Faut pas croire que la matérialisation c’est toujours mauvais ! Si les données du CTE sont utilisées plusieurs fois dans la requête ou doivent être calculées indépendamment, la matérialisation peut être utile. Exemple :

WITH materialized_example AS (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)

SELECT student_id
FROM materialized_example
WHERE grade > 90

UNION ALL

SELECT student_id
FROM materialized_example
WHERE grade < 70;

Ici, la matérialisation évite de recalculer le filtre grade > 60 à chaque fois.

Optimisation des requêtes avec les index

Pour que les CTE tournent plus vite, il faut utiliser des index sur les tables de base d’où on extrait les données. Par exemple :

CREATE INDEX idx_students_grades_grade ON students_grades(grade);

WITH filtered_students AS (
    SELECT student_id, grade
    FROM students_grades
    WHERE grade > 90
)
SELECT *
FROM filtered_students;

L’index sur la colonne grade permet à PostgreSQL d’extraire plus vite les lignes qui matchent grade > 90. C’est super important quand tu bosses avec de grosses tables.

Découper les gros CTE en plus petits

Si ton CTE renvoie beaucoup de données qui sont ensuite filtrées ou agrégées, c’est mieux de le découper en plusieurs étapes. Au lieu d’un gros CTE compliqué, fais-en plusieurs petits :

Pas top (gros CTE) :

WITH large_query AS (
    SELECT s.student_id, AVG(g.grade) AS avg_grade
    FROM students s
    JOIN grades g ON s.student_id = g.student_id
    WHERE g.subject_id = 101 AND g.grade > 85
    GROUP BY s.student_id
)
SELECT *
FROM large_query
WHERE avg_grade > 90;

Mieux (découpage en étapes) :

WITH filtered_grades AS (
    SELECT student_id, grade
    FROM grades
    WHERE subject_id = 101 AND grade > 85
),
average_grades AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM filtered_grades
    GROUP BY student_id
)
SELECT *
FROM average_grades
WHERE avg_grade > 90;

Cette approche aide PostgreSQL à mieux optimiser l’exécution des requêtes.

Exemple pratique : analyse de la structure et optimisation

Regardons un exemple plus costaud. On a des tables d’étudiants, de cours et de notes. On veut trouver les étudiants avec une moyenne élevée et afficher leur liste avec les cours correspondants :

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
),
student_courses AS (
    SELECT e.student_id, c.course_name
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
)
SELECT ha.student_id, ha.avg_grade, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

On peut optimiser cette requête en ajoutant des index sur les tables grades et enrollments, ce qui accélère le filtrage et les jointures.

Monitoring : analyse des performances

Pour savoir si ta requête est efficace, utilise EXPLAIN ou EXPLAIN ANALYZE. Par exemple :

EXPLAIN ANALYZE
WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

Cette requête te montre combien de temps prend chaque étape, et t’aide à voir où tu peux améliorer les perfs.

On verra EXPLAIN ANALYZE plus en détail dans les prochains niveaux :P

Erreurs fréquentes lors de l’optimisation des CTE

1. Oublier les index. Si tu filtres les données dans le CTE mais qu’il n’y a pas d’index sur la table de base, les perfs vont en prendre un coup.
2. Utiliser des CTE trop gros. Si une requête fait trop de choses, ça peut entraîner la matérialisation de gros volumes de données.
3. Abuser de NOT MATERIALIZED. Parfois, la matérialisation est quand même nécessaire pour éviter de recalculer le CTE plusieurs fois.
4. Ignorer le monitoring. Sans analyse avec EXPLAIN, tu risques de ne pas voir que tes requêtes sont lentes.

Maintenant, t’es prêt à optimiser tes requêtes avec les CTE, à éviter les pièges et à booster les perfs ! Rappelle-toi que les CTE sont un outil, pas une solution miracle. Utilise-les intelligemment, et ils deviendront tes meilleurs potes dans PostgreSQL.