Optimiser une requête SQL lente (PostgreSQL) : méthode pas à pas

Une page qui met dix secondes à s'afficher, un rapport qui part en timeout, une API qui ralentit dès que la base grossit : dans la majorité des cas, le coupable est une poignée de requêtes. L'optimisation requête SQL ne consiste pas à deviner ni à ajouter des index au hasard, mais à suivre une méthode : identifier les requêtes qui coûtent vraiment, lire leur plan d'exécution, corriger la cause précise, puis mesurer le gain. Voici cette méthode, appliquée à PostgreSQL, étape par étape.

Étape 1 : trouver les requêtes qui coûtent vraiment

Avant toute optimisation requête SQL, il faut mesurer. La requête la plus lente n'est pas forcément la plus coûteuse : une requête de 50 ms exécutée 200 000 fois par jour pèse bien plus lourd qu'un rapport de 30 secondes lancé une fois par semaine.

Sous PostgreSQL, l'outil de référence est l'extension pg_stat_statements. Une fois activée, elle agrège toutes les requêtes normalisées avec leur nombre d'appels, leur temps total et leur temps moyen :

SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Complétez avec le paramètre log_min_duration_statement (par exemple à 500 ms) pour journaliser les requêtes lentes, et avec votre outil d'observabilité applicatif (Sentry, Grafana, OpenSearch…) pour relier chaque requête à l'écran ou à l'endpoint qui la déclenche. Vous obtenez ainsi une liste courte, triée par impact réel : c'est elle qui guide toute la suite.

Étape 2 : lire le plan d'exécution avec EXPLAIN ANALYZE

Le plan d'exécution montre ce que PostgreSQL fait réellement pour répondre à la requête. Lancez-le sur une copie de données représentative, jamais sur une base de test presque vide, car le planificateur choisit des stratégies différentes selon le volume :

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
  AND o.created_at >= now() - interval '30 days';

Attention : ANALYZE exécute vraiment la requête. Sur un UPDATE ou un DELETE, encadrez-la d'un BEGIN / ROLLBACK.

Dans la sortie, cherchez en priorité :

Seq Scan sur une grosse table avec un filtre qui ne garde que quelques lignes : un index manque probablement.
Écart entre lignes estimées et lignes réelles (rows=10 estimé contre rows=500000 réel) : les statistiques sont faussées et le planificateur se trompe de stratégie.
Nested Loop sur des volumes importants : souvent la conséquence d'une mauvaise estimation.
Sort ou Hash qui débordent sur disque (external merge, Disk:) : mémoire de travail insuffisante ou trop de lignes triées.
Buffers read très élevés : beaucoup de lectures disque, donc de pages chargées pour rien.

Le nœud le plus coûteux du plan est votre cible. Des outils de visualisation de plans facilitent la lecture, mais l'essentiel est de savoir où part le temps.

Étape 3 : optimisation requête SQL, les leviers les plus efficaces

Une fois la cause identifiée, l'optimisation SQL repose sur un nombre limité de leviers. Le tableau ci-dessous résume les symptômes les plus fréquents et la correction associée.

Symptôme dans le plan	Cause probable	Correction
Seq Scan + filtre très sélectif	Index absent	Index B-tree sur la ou les colonnes filtrées
Index présent mais ignoré	Fonction sur la colonne, cast implicite, ordre de colonnes inadapté	Réécrire le filtre ou créer un index sur expression
Estimations très fausses	Statistiques obsolètes ou colonnes corrélées	`ANALYZE`, statistiques étendues (`CREATE STATISTICS`)
Tri sur disque	`work_mem` trop bas ou tri inutile	Index qui fournit l'ordre, ou ajustement ciblé de `work_mem`
Milliers de petites requêtes identiques	Problème N+1 côté ORM	Jointure ou chargement groupé (`select_related`, `prefetch_related`…)

Indexer intelligemment

Un index composite doit suivre l'ordre des filtres : colonnes en égalité d'abord, puis colonne de plage. Pour l'exemple précédent, (status, created_at) est généralement pertinent. Si seule une petite fraction des lignes est concernée, un index partiel (WHERE status = 'pending') est plus compact et plus rapide. Et si la requête ne lit que quelques colonnes, un index couvrant avec INCLUDE permet un Index Only Scan. Sur une table en production, créez l'index avec CREATE INDEX CONCURRENTLY pour éviter de bloquer les écritures.

Mais n'indexez pas tout : chaque index ralentit les insertions et mises à jour et consomme de l'espace. Vérifiez régulièrement les index jamais utilisés via pg_stat_user_indexes.

Réécrire la requête

Évitez SELECT * : ne ramenez que les colonnes utiles.
Remplacez WHERE date(created_at) = '2026-01-15' par une plage created_at >= '2026-01-15' AND created_at < '2026-01-16', qui peut utiliser l'index.
Préférez EXISTS à IN (sous-requête) ou à un DISTINCT sur jointure quand vous testez seulement l'existence.
Remplacez la pagination par OFFSET élevé par une pagination par clé (WHERE id > :dernier_id ORDER BY id LIMIT 50).
Méfiez-vous des OR sur des colonnes différentes, qui empêchent souvent l'usage d'un index unique ; un UNION ALL peut être plus efficace.

Étape 4 : regarder au-delà de la requête

Certaines lenteurs ne viennent pas de la requête elle-même. Une optimisation PostgreSQL complète vérifie aussi l'environnement :

Bloat et autovacuum : une table très modifiée dont l'autovacuum ne suit pas grossit, et chaque lecture parcourt des lignes mortes. Ajustez les seuils d'autovacuum table par table si nécessaire.
Verrous : une requête rapide peut attendre longtemps derrière une transaction ouverte. Consultez pg_stat_activity et pg_locks.
Paramétrage mémoire : shared_buffers, work_mem et effective_cache_size laissés aux valeurs par défaut sont rarement adaptés à un serveur de production.
Cache applicatif : une donnée qui change peu mais qui est relue à chaque page n'a pas besoin d'interroger la base à chaque fois.
Traitements lourds : un rapport sur des millions de lignes gagne souvent à être découpé, pré-agrégé (vue matérialisée, table de synthèse) ou déplacé en tâche asynchrone.

C'est souvent là que se joue l'optimisation base de données à long terme : corriger une requête règle un symptôme, corriger le modèle d'accès aux données évite qu'il revienne.

Étape 5 : mesurer le gain et sécuriser la correction

Une optimisation non mesurée n'est qu'une impression. Relancez EXPLAIN (ANALYZE, BUFFERS) après correction, comparez le temps d'exécution et le volume de buffers lus, puis surveillez la requête en production pendant quelques jours via pg_stat_statements (pensez à réinitialiser les statistiques avant pour comparer proprement).

Avancez par petits changements : un index ou une réécriture à la fois, chacun couvert par des tests pour vérifier que les résultats restent identiques. Cette approche incrémentale est moins spectaculaire qu'une grande refonte, mais beaucoup plus sûre. Sur des bases PostgreSQL de plus de 100 Go, elle a permis de ramener des requêtes qui partaient en timeout sous la seconde, sans réécrire l'application.

Questions fréquentes

Ajouter un index suffit-il à accélérer une requête lente ?

Pas toujours. Un index n'aide que si le planificateur peut l'utiliser et s'il filtre une part réduite de la table. Si la requête retourne une grande proportion des lignes, ou si le filtre applique une fonction sur la colonne, l'index sera ignoré. Le plan d'exécution vous dit ce qu'il en est.

Faut-il abandonner l'ORM pour optimiser les requêtes ?

Non. La plupart des problèmes liés à l'ORM (N+1, colonnes inutiles, comptages répétés) se corrigent avec ses propres outils de chargement groupé et de sélection de champs. Le SQL brut reste utile pour quelques requêtes analytiques complexes, pas comme règle générale.

Combien de temps prend une optimisation requête SQL ?

Une requête isolée avec une cause claire (index manquant, N+1) se corrige souvent en quelques heures, tests compris. Un problème plus diffus, lié au volume, au modèle de données ou à l'architecture, demande d'abord un diagnostic global de quelques jours pour prioriser les corrections par impact et effort.

Vos requêtes ralentissent à mesure que vos données grossissent ? Lanovatic, basée à La Seyne-sur-Mer près de Toulon, réalise des audits de performance et d'optimisation SQL sur PostgreSQL et MySQL : plans d'exécution, index, ORM, cache et traitements batch, avec des mesures avant/après pour chaque gain. Découvrez notre méthode d'audit de performance d'application ou contactez-nous pour en parler.