Comment optimiser des agrégations lentes sur une grande table ?

Question

IT Interview Practice · Accepted Answer

Pour accélérer une agrégation, identifiez d’abord **le sens du résultat et l’opération qui prend du temps**. Une agrégation résume des lignes, par exemple le chiffre d’affaires par vendeur. Son coût dépend des données lues et combinées.

Prenons un tableau de bord du mois précédent. Il permet de choisir entre améliorer la requête, stocker un résumé ou déplacer le reporting.

## 1. Valider la réponse avant la vitesse

Précisez si les commandes payées ou créées comptent, comment traiter les remboursements et quelles devises inclure. Définissez aussi la fraîcheur. Ces choix changent requête et optimisations.

Une erreur fréquente joint un paiement à plusieurs lignes de commande puis additionne son montant :

```text
[Payment: 100] -> [Join with 3 items] -> [100, 100, 100]
                                               |
                                          [SUM = 300]
```

Ces nombres sont illustratifs. Le paiement est multiplié ; accélérer ne ferait que produire plus vite un total faux. Agrégez au bon niveau métier ou corrigez la jointure avant mesure.

## 2. Trouver l’étape coûteuse

Le plan d’exécution décrit les opérations du résultat. Examinez les étapes chères et leurs entrées au lieu de présumer qu’un index manque.

| Observation | Question à examiner |
| --- | --- |
| Beaucoup de lignes inutiles sont lues | Filtres ou élimination de partitions peuvent-ils les exclure ? |
| Les volumes réels diffèrent fortement des estimations | Les statistiques représentent-elles les données ? |
| Une jointure répète beaucoup de travail | Entrée trop grande ou accès inadapté ? |
| Tri ou regroupement écrit temporairement sur disque | Peut-on réduire l’entrée avant d’augmenter la mémoire ? |

Dans PostgreSQL, EXPLAIN (ANALYZE, BUFFERS) exécute réellement la requête et rapporte son comportement. Faites les analyses coûteuses dans un cadre contrôlé ou avec un budget de production explicite. [PostgreSQL EXPLAIN](https://www.postgresql.org/docs/current/using-explain.html).

## 3. Réduire le travail dans la base actuelle

Sélectionnez mois et colonnes utiles. Vérifiez que le plan ignore réellement les partitions étrangères et que les jointures n’ajoutent rien d’inutile.

Un index aide s’il économise assez de lecture. Si presque toutes les lignes sont nécessaires, un parcours séquentiel peut convenir. Jugez plan et coût mesuré, pas la seule présence d’un index.

Réduire l’entrée peut supprimer un débordement de tri sur disque. Augmenter la mémoire de chaque requête peut au contraire épuiser la RAM avec plusieurs rapports. Testez une concurrence réaliste.

## 4. Décider d’un résultat préparé

Si beaucoup demandent les mêmes totaux et acceptent un délai, relire les commandes brutes gaspille du travail. Une table peut garder les sommes par jour, vendeur et devise, puis le tableau de bord les combine.

```text
[Orders] -> [Scheduled / incremental calculation]
                              |
                       [Daily summaries] -> [Dashboard]
```

Le gain vient d’un calcul partagé effectué une fois. Une vue matérialisée stocke aussi un résultat, avec son propre rafraîchissement.

Cette économie apporte des responsabilités :

- Définir la fréquence et afficher la fraîcheur pertinente.
- Appliquer remboursements, commandes tardives et corrections sans double comptage.
- Sécuriser les reprises et conserver une reconstruction possible.
- Garder somme et effectif pour combiner des moyennes ; moyenner des moyennes peut être faux.

Ici, les résumés ne conviennent que si fraîcheur et corrections respectent le besoin initial.

## 5. Séparer le reporting si les mesures le justifient

Si les grands parcours analytiques restent chers et perturbent les achats, évaluez un stockage analytique adapté. Incluez délai de synchronisation, restauration et exploitation supplémentaire.

Une réplique de lecture isole parfois la charge, mais la requête reste coûteuse. Séparer déplace le coût sans forcément le supprimer.

Comparez chaque changement à un résultat fiable sous la même charge. Mesurez réponse, ressources, fraîcheur et effet sur les écritures.

**À retenir :** résultat juste → limite mesurée → moins de travail inutile → résumés adaptés → analyse séparée si nécessaire.

Observation	Question à examiner
Beaucoup de lignes inutiles sont lues	Filtres ou élimination de partitions peuvent-ils les exclure ?
Les volumes réels diffèrent fortement des estimations	Les statistiques représentent-elles les données ?
Une jointure répète beaucoup de travail	Entrée trop grande ou accès inadapté ?
Tri ou regroupement écrit temporairement sur disque	Peut-on réduire l’entrée avant d’augmenter la mémoire ?

1. Valider la réponse avant la vitesse

2. Trouver l’étape coûteuse

3. Réduire le travail dans la base actuelle

4. Décider d’un résultat préparé

5. Séparer le reporting si les mesures le justifient

Questions connexes