Repérer et corriger les requêtes lentes

Il s'agit de la consigne d'entretien finale : le recruteur vous présente une requête lente et un plan EXPLAIN ANALYZE, puis vous demande de la diagnostiquer. Il évalue une méthode, et non des astuces mémorisées.

Une réponse solide suit à voix haute une liste de contrôle : mesurer, lire le plan, repérer le coût dominant, formuler une hypothèse, proposer une correction, puis vérifier. Cette leçon construit cette liste étape par étape.

Restez méthodique et explicitez votre raisonnement : c'est ce qui vous permet d'obtenir une évaluation senior.

Ne vous fiez jamais au SQL seul. Obtenez le plan réel avec EXPLAIN (ANALYZE, BUFFERS).

ANALYZE fournit les temps réels et le nombre réel de lignes ; BUFFERS indique si vous utilisez le cache ou si vous lisez depuis le disque. Ensemble, ces informations vous indiquent si la requête est limitée par le CPU, par les entrées-sorties ou si elle effectue simplement trop de travail.

Exécutez-la plusieurs fois ; la première exécution peut subir la pénalité d'un cache froid, ce qui fausse la mesure du temps.

Ne parcourez pas le plan de haut en bas au hasard. Repérez le nœud où le plus de temps est réellement consommé.

Calculez le temps propre de chaque nœud : son actual time total moins le temps de ses enfants, multiplié par loops. Le nœud qui représente la plus grande part est votre cible ; tout le reste est secondaire.

Lors d'un entretien, dites : 80 % du temps d'exécution se trouve dans ce balayage séquentiel, c'est donc là que je me concentre. Optimiser quoi que ce soit d'autre serait un effort inutile.

Sur le nœud dominant, comparez le nombre de lignes estimé au nombre de lignes réel. Un écart important signifie que le planificateur avance à l'aveugle et a probablement choisi un mauvais plan, avec un algorithme de jointure ou une méthode d'accès inadapté.

L'exemple montre une sous-estimation d'un facteur 1000. Avant de revoir la conception, actualisez les statistiques : cette simple commande corrige souvent le plan gratuitement.

ANALYZE recalcule les statistiques des colonnes ; VACUUM ANALYZE nettoie également les tuples obsolètes et met à jour la carte de visibilité.

Le problème corrigeable le plus fréquent est le suivant : une fonction ou une conversion enveloppe la colonne dans WHERE, de sorte que l'index ne peut pas être utilisé et que le moteur effectue un balayage séquentiel.

L'exemple impose un parcours complet, car DATE() est appliquée à chaque ligne. Réécrivez la condition sous forme de prédicat d'intervalle portant directement sur la colonne, afin de permettre l'utilisation de l'index, et l'index sur created_at devient utilisable.

La même idée s'applique à WHERE lower(email)=... : stockez des données normalisées, interrogez directement la colonne ou créez un index d'expression.

Si le nœud dominant est un balayage séquentiel avec un filtre très sélectif, ou une boucle imbriquée dont la valeur de loops est très élevée sur une clé interne non indexée, la solution est généralement d'ajouter un index.

Ajoutez un index sur la colonne filtrée ou jointe. L'exemple en crée un sur customer_id, afin que la jointure puisse passer de balayages séquentiels à des balayages d'index ; le planificateur pourra alors choisir un plan bien moins coûteux.

Vérifiez le résultat en réexécutant EXPLAIN ANALYZE : ne supposez pas que l'index a été utile.

SELECT * récupère toutes les colonnes depuis le disque et les transfère sur le réseau, et empêche les balayages utilisant uniquement l'index, car celui-ci contient rarement toutes les colonnes.

Ne sélectionnez que les colonnes dont vous avez besoin. Cela réduit la largeur des lignes, diminue les entrées-sorties et peut permettre un balayage couvert utilisant uniquement l'index.

Un recruteur qui glisse SELECT * dans l'exemple veut que vous le remarquiez. Réduire la liste des colonnes constitue souvent un gain rapide et réel sur les tables larges.

Si un nœud Sort ou Hash signale une utilisation du disque (Sort Method: external merge Disk: 25000kB ou Batches: > 1), l'opération a dépassé work_mem et écrit des données sur le disque.

Possibilités : augmenter work_mem pour la session, réduire le nombre de lignes qui atteignent le tri ou le hachage en filtrant plus tôt, ou ajouter un index qui fournit l'ordre trié afin de supprimer complètement l'étape de tri.

Il s'agit d'un diagnostic précis, de niveau senior, que les recruteurs apprécient.

Surveillez la présence de Rows Removed by Filter: 9500000. La requête a lu dix millions de lignes et en a éliminé presque toutes : c'est un gaspillage classique de travail.

Solutions : ajoutez un index afin que le filtre soit appliqué pendant l'accès, et non après ; rendez le prédicat plus sélectif ; ou poussez le filtrage plus tôt dans la requête afin que moins de lignes remontent dans l'arbre.

Le principe est simple : effectuez le moins de travail possible et filtrez aussi tôt et aussi efficacement que possible.

Récitez cette liste lors de l'entretien et vous ne vous égarerez pas :

• Mesurez avec EXPLAIN (ANALYZE, BUFFERS).
• Repérez le nœud qui consomme le plus de temps.
• Comparez le nombre de lignes estimé au nombre réel et corrigez d'abord les statistiques obsolètes.
• Vérifiez l'indexabilité et retirez les fonctions des colonnes filtrées.
• Indexez les filtres sélectifs et les clés de jointure.
• Réduisez le nombre de colonnes et évitez SELECT *.
• Surveillez les écritures temporaires sur le disque et la récupération excessive de lignes.
• Vérifiez en réexécutant le plan.

Parcourez à voix haute un exemple complet. Le plan affiche un Seq Scan sur une table orders de 50 millions de lignes, avec le filtre customer_id = 42 et Rows Removed by Filter proche de 50 millions, tandis que l’estimation correspond approximativement aux valeurs réelles.

Diagnostic : filtre sélectif, aucun index, le coût dominant est le balayage. Correction : CREATE INDEX ON orders(customer_id). Relancez la requête : le plan bascule vers un Index Scan et le temps passe de plusieurs secondes à moins d’une milliseconde.

Cette boucle mesurer-diagnostiquer-corriger-vérifier constitue le modèle de réponse à toute question sur une requête lente.

Vous disposez maintenant d’une méthode reproductible pour répondre aux questions sur les requêtes lentes :

• Commencez toujours par mesurer avec EXPLAIN (ANALYZE, BUFFERS) et concentrez-vous sur le nœud dominant.
• Corrigez d’abord les statistiques obsolètes lorsque les estimations et les valeurs réelles divergent.
• Rendez les prédicats compatibles avec une recherche par index, ajoutez des index pour les filtres sélectifs et les clés de jointure, et réduisez SELECT *.
• Traitez les débordements sur disque et la récupération excessive de données, puis vérifiez le nouveau plan.

Énoncez la liste de vérifications, proposez une modification concrète et relancez le plan pour la démontrer : c’est la réponse attendue d’un ingénieur expérimenté.