Optimisation des requêtes avec les fonctions window

Il reste encore un détail important dont on n’a pas parlé : la perf des requêtes avec les fonctions window. Même la requête la plus élégante peut devenir un escargot si tu ne penses pas à l’optimisation. Aujourd’hui, on va s’attaquer à ça !

Les fonctions window sont ultra flexibles et puissantes. Mais cette flexibilité, c’est à la fois un cadeau et un piège pour la perf. PostgreSQL, malheureusement, ce n’est pas de la magie, il a besoin de ressources pour traiter les données. Et si tu balances des fonctions window sur des tables énormes, ta requête va ressembler à un marathon sur place.

L’optimisation va te permettre de :

• Accélérer les requêtes qui bossent sur des gros volumes de données.
• Réduire la charge sur la base de données.
• Rendre tes requêtes plus cool pour le serveur (et pour tes collègues qui bossent aussi sur la base !).

Allez, on plonge et on voit comment faire pour que tes requêtes filent comme une Formule 1 sur un circuit.

Bases du fonctionnement des fonctions window

Avant de commencer à optimiser, c’est bien de piger ce qui ralentit la requête. PostgreSQL gère les fonctions window comme ça :

1. Il trie les données si la requête a un ORDER BY dans le OVER().
2. Il traite chaque ligne dans la fenêtre ou le groupe défini.
3. Il renvoie le résultat pour chaque ligne.

Imagine maintenant qu’on a une table sales avec 10 millions de lignes. Si ta requête n’a pas de filtres, PostgreSQL va traiter chacune de ces lignes. Là, c’est plus un marathon, c’est carrément un tapis de course sans fin.

Comment accélérer les fonctions window ?

1. Utiliser des indexes pour accélérer le tri

La plupart des fonctions window utilisent ORDER BY dans OVER() pour gérer l’ordre des lignes. Ça veut dire que PostgreSQL doit trier tes données avant d’exécuter la fonction window.

Si tu as un index sur la colonne (ou les colonnes) utilisées dans le ORDER BY, PostgreSQL pourra trier beaucoup plus vite.

Exemple

CREATE INDEX idx_sales_date ON sales (sale_date);

Maintenant, si tu fais une requête triée par sale_date, l’index va servir :

SELECT
    sale_date,
    product_id,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

Sans index sur sale_date, chaque exécution de la requête va demander un tri coûteux — PostgreSQL va paniquer pour trouver comment ordonner les lignes plus vite.

2. Appliquer des filtres avec WHERE

Réduire le volume de données, c’est la base de l’optimisation. Si tu n’as pas besoin de traiter les 10 millions de lignes, mais juste la dernière année — réduis la plage de données avec WHERE !

Exemple

SELECT
    sale_date,
    product_id,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales
WHERE sale_date >= '2023-01-01';

C’est comme filtrer de l’eau sale avec une passoire pour ne garder que ce qui t’intéresse.

3. Choisir le bon frame window

Quand tu bosses avec des fonctions window d’agrégation, genre SUM(), c’est important de choisir le bon frame window. Si tu utilises le frame par défaut (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), PostgreSQL va inclure toutes les lignes jusqu’à la ligne courante. Pas top pour les grosses tables.

Exemple : utiliser ROWS

Si tu veux inclure juste quelques lignes avant la ligne courante, précise-le avec ROWS :

SELECT
    sale_date,
    product_id,
    SUM(amount) OVER (
        PARTITION BY product_id 
        ORDER BY sale_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_sum
FROM sales;

Là, PostgreSQL ne traite que trois lignes (deux avant + la courante) pour chaque ligne. C’est bien plus efficace que de traiter des centaines de lignes par défaut.

4. Minimiser le nombre de fonctions window

Chaque fonction window est traitée par PostgreSQL séparément. Si tu en utilises plusieurs, PostgreSQL peut trier pour chacune, ce qui ralentit tout. Mais si les paramètres window (genre PARTITION BY et ORDER BY) sont les mêmes, PostgreSQL peut optimiser ça.

Exemple : optimisation avec la même fenêtre

SELECT
    product_id,
    sale_date,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total,
    ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_date) AS row_num
FROM sales;

Les deux fonctions (SUM() et ROW_NUMBER()) utilisent la même fenêtre. PostgreSQL ne trie qu’une fois — et ça, c’est cool.

5. Partitionnement des tables

Si ta table est trop grosse, pense à la découper physiquement en morceaux plus petits. PostgreSQL permet de créer des tables partitionnées, pour que les données soient dans différents segments. Ça peut vraiment accélérer le traitement.

Exemple de création d’une table partitionnée

CREATE TABLE sales_partitioned (
    sale_date DATE NOT NULL,
    product_id INT NOT NULL,
    amount NUMERIC NOT NULL
) PARTITION BY RANGE (sale_date);

Après, tu crées des partitions, par exemple par année :

CREATE TABLE sales_2022 PARTITION OF sales_partitioned
FOR VALUES FROM ('2022-01-01') TO ('2022-12-31');

CREATE TABLE sales_2023 PARTITION OF sales_partitioned
FOR VALUES FROM ('2023-01-01') TO ('2023-12-31');

Maintenant, si tu utilises WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31', PostgreSQL va automatiquement taper dans la bonne partition.

Tu verras plus de détails sur le partitionnement des tables vers la fin du cours :P

6. Évite les données inutiles (SELECT juste ce qu’il faut)

Sélectionne seulement les colonnes dont tu as besoin pour la fonction et le résultat. Si ta fonction window n’a besoin que de product_id, sale_date et amount, ne traîne pas tout le zoo de la table avec les bio-données du client.

Exemple de requête "économe"

SELECT
    product_id,
    sale_date,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

Moins de données = moins de boulot pour PostgreSQL.

7. Utiliser la matérialisation (MATERIALIZED VIEW)

Si tu fais souvent les mêmes calculs avec des fonctions window, tu peux stocker les résultats dans une vue matérialisée. Materialized View garde les données sur disque, pour éviter de refaire les requêtes lourdes à chaque fois.

Exemple de création d’une vue matérialisée

CREATE MATERIALIZED VIEW sales_running_total AS
SELECT 
    product_id,
    sale_date,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

Maintenant, tu peux juste requêter les données :

SELECT * FROM sales_running_total WHERE product_id = 10;

8. Planifier les requêtes avec EXPLAIN et EXPLAIN ANALYZE

Comme pour le reste du SQL, tu peux utiliser EXPLAIN ou EXPLAIN ANALYZE pour voir comment PostgreSQL exécute ta requête — et où ça coince.

Exemple d’analyse de requête

EXPLAIN ANALYZE
SELECT 
    product_id,
    sale_date,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

Cet outil te montre où PostgreSQL passe le plus de temps, et tu peux optimiser les points chauds.

Les fonctions window, c’est un outil d’analyse de données super puissant, mais il faut les manier avec précaution. Tu veux de la vitesse ? Prends des indexes, ajoute des filtres, pense aux partitions et n’hésite pas à utiliser les vues matérialisées. PostgreSQL adore quand tu réfléchis à son utilisation !