Aller au contenu
Hugo Delacour
Tous les articles

Pourquoi ta requête est lente : lire un plan, poser le bon index

Une requête lente lit presque toujours trop de lignes. Comment le voir avec EXPLAIN, ce que fait vraiment un index B-tree, et pourquoi l'ordre des colonnes d'un index composite décide de tout — chiffres mesurés à l'appui.

9 septembre 20269 min
  • Bases de données
  • SQL
  • Performance
  • Architecture

Une requête lente n'est presque jamais lente parce qu'elle calcule trop. Elle est lente parce qu'elle lit trop : des lignes dont elle n'a pas besoin, en quantité, et souvent plusieurs fois.

Tous les chiffres de cet article viennent d'une même base de démonstration — 100 000 commandes, 45 000 clients, 250 000 lignes de commande — interrogée avec SQLite 3.54 compilé en WebAssembly. Les durées sont des moyennes sur plusieurs exécutions. Sur PostgreSQL ou MySQL, les mots du plan changent ; la mécanique, non.

Lire le plan avant de toucher à quoi que ce soit

Les commandes d'un client :

SELECT COUNT(*), MIN(passee_le)
  FROM commande
 WHERE client_id = 1234;

Huit commandes, 7,9 ms. Rien d'alarmant. Mais demande au moteur comment il s'y prend :

EXPLAIN QUERY PLAN
SELECT COUNT(*), MIN(passee_le) FROM commande WHERE client_id = 1234;
-- SCAN commande

SCAN commande : il lit la table entière, cent mille lignes, pour en garder huit. Le résultat est juste, le chemin est absurde, et il coûtera dix fois plus le jour où la table sera dix fois plus grosse.

Le plan est le premier réflexe, avant toute réécriture. Chaque moteur a le sien :

Moteur Commande Ce qu'on y cherche
SQLite EXPLAIN QUERY PLAN SCAN (tout lire) ou SEARCH … USING INDEX
PostgreSQL EXPLAIN ANALYZE Seq Scan, ou Index Scan et Index Only Scan
MySQL EXPLAIN ANALYZE Table scan, ou Index lookup

Une précaution : EXPLAIN ANALYZE exécute vraiment la requête pour la chronométrer. Sur un UPDATE ou un DELETE, enveloppe-le dans une transaction que tu annules ensuite.

Un index, c'est un second exemplaire, trié

Un index est une copie d'une ou plusieurs colonnes, triée, où chaque valeur garde l'adresse de sa ligne. Le tri change tout : dans une liste triée, on ne cherche pas, on descend.

SQLite et PostgreSQL rangent cette copie dans un B-tree, un arbre de pages de taille fixe — 4 Ko par défaut chez SQLite, 8 Ko chez PostgreSQL. La racine dit dans quelle page descendre, chaque niveau resserre l'intervalle, et les feuilles portent les valeurs.

CREATE INDEX ix_commande_client ON commande (client_id);
-- SEARCH commande USING INDEX ix_commande_client (client_id=?)

Cet index tient en 265 pages : une racine et 264 feuilles. Trouver le client 1234, c'est lire la racine, une feuille, puis les huit lignes visées. La même requête passe de 7,9 ms à 0,015 ms, cinq cents fois moins, sans qu'on ait changé un caractère.

SANS INDEXSCAN commandeclient 3 018client 412client 1 234client 27 051client 8 800client 15client 41 207client 1 234client 9 332client 30 876client 610client 22 113100 000 LIGNES LUES7,9 msAVEC UN INDEXSEARCH … USING INDEX12 001 · 30 002RACINE1 … 12 00012 001 …30 002 …264 FEUILLES DE 4 KOclient 1 234…client 1 234…2 PAGES D'INDEX0,015 ms
Le client 1234, deux fois. Sans index, le moteur lit chaque ligne et garde celles qui conviennent : 7,9 ms. Avec l'index, il lit la racine du B-tree, une feuille, puis va droit aux lignes du client : 0,015 ms.

L'écart se creuse avec la taille. Un parcours complet coûte deux fois plus quand la table double ; un B-tree ne gagne un niveau que lorsqu'elle est multipliée par quelques centaines.

Quand l'absence d'index se multiplie

Huit millisecondes ne font peur à personne. Le problème commence quand la requête les répète. La date de la dernière commande des 2 000 premiers clients :

SELECT c.id,
       (SELECT MAX(o.passee_le)
          FROM commande o
         WHERE o.client_id = c.id) AS derniere
  FROM client c
 WHERE c.id <= 2000;
SEARCH c USING INTEGER PRIMARY KEY (rowid<?)
CORRELATED SCALAR SUBQUERY 1
  SEARCH o

CORRELATED : la sous-requête dépend de la ligne courante, elle est rejouée pour chacun des 2 000 clients. Et ce SEARCH o, sans USING INDEX, n'a rien d'une recherche : faute d'index, chaque passage relit la table entière. Deux mille passages de cent mille lignes, deux cents millions de lectures : 13,9 secondes.

Avec l'index sur client_id, la même requête répond en 11,7 ms. Mille fois moins. Un index manquant ne coûte pas une constante, il multiplie : par le nombre de lignes lues à chaque passage, puis par le nombre de passages.

L'ordre des colonnes décide de tout

Un index peut porter plusieurs colonnes. (client_id, passee_le) trie d'abord par client, puis par date à l'intérieur de chaque client — comme un annuaire trié par nom, puis par prénom.

Il sert donc tout ce qui commence par le client :

CREATE INDEX ix_client_date ON commande (client_id, passee_le);
 
SELECT COUNT(*) FROM commande
 WHERE client_id = 1234 AND passee_le >= '2024-01-01';
-- SEARCH commande USING COVERING INDEX ix_client_date (client_id=? AND passee_le>?)

Mais pas une condition sur la date seule :

SELECT COUNT(*) FROM commande WHERE passee_le >= '2025-06-01';
-- SCAN commande USING COVERING INDEX ix_client_date

SCAN, encore. Les commandes de juin 2025 sont éparpillées dans les 45 000 groupes de clients : aucune plage continue ne les contient, l'index se lit en entier. 4,4 ms, contre 0,017 ms pour la requête qui commence par le client.

INDEX (CLIENT_ID, PASSEE_LE)CLIENTPASSEE_LE412023-02-11412024-08-03422022-05-19422023-01-04422024-03-27422025-06-02432022-11-30432024-01-15442023-07-21442025-02-09452023-12-30452024-10-08AAWHEREclient_id = 42SEARCHune plage : 4 entréesBBWHEREclient_id = 42AND passee_le >= '2024'SEARCHune plage plus courte : 2 entréesCCWHEREpassee_le >= '2024'SCANpas de plage : tout l'index est lu
Un index trié par client, puis par date. A et B trouvent leurs entrées dans une plage continue de l'index ; C cherche une date à travers tous les clients à la fois, et doit tout lire.

La règle tient en une phrase : les colonnes testées par égalité d'abord, la colonne de plage ensuite, et l'index ne sert plus si sa première colonne manque à la requête. Un annuaire trié par nom ne t'aide pas à trouver tous les « Camille ».

Les moteurs savent parfois contourner la règle — SQLite saute une première colonne qui a très peu de valeurs distinctes quand ANALYZE le lui a appris, et PostgreSQL 18 a ajouté le même « skip scan » —, mais c'est une optimisation d'opportunité, pas une stratégie.

L'index couvrant : ne plus toucher à la table

Dans le plan précédent, un mot de plus : COVERING. L'index contient toutes les colonnes dont la requête a besoin, et la table n'est même pas ouverte.

SELECT passee_le FROM commande WHERE client_id = 1234;
-- SEARCH commande USING COVERING INDEX ix_client_date (client_id=?)
 
SELECT passee_le, statut FROM commande WHERE client_id = 1234;
-- SEARCH commande USING INDEX ix_client_date (client_id=?)

Demander statut en plus oblige à aller chercher chaque ligne dans la table. PostgreSQL appelle le premier cas un Index Only Scan, et permet d'embarquer des colonnes dans un index sans les trier, avec INCLUDE.

La sous-requête corrélée de tout à l'heure en profite doublement : avec (client_id, passee_le), le MAX(passee_le) d'un client est simplement la dernière entrée de sa plage. Elle tombe à 1,1 ms — plus de dix mille fois moins que sans index.

Ce qui empêche un index de servir

L'index existe, et le plan dit SCAN quand même. Le plus souvent, la requête a rendu la colonne méconnaissable.

-- Une fonction sur la colonne : il faut la calculer ligne par ligne.
SELECT COUNT(*) FROM commande WHERE strftime('%Y', passee_le) = '2024';
-- SCAN commande USING COVERING INDEX ix_commande_date              24 ms
 
-- La même question, posée comme une plage : l'index la découpe directement.
SELECT COUNT(*) FROM commande
 WHERE passee_le >= '2024-01-01' AND passee_le < '2025-01-01';
-- SEARCH commande USING COVERING INDEX ix_commande_date (passee_le>? AND passee_le<?)   1,0 ms
Écriture L'index sert ? Pourquoi
strftime('%Y', d) = '2024' Non une fonction appliquée à la colonne
d >= '2024-01-01' AND d < '2025-01-01' Oui une plage sur la valeur brute
nom LIKE '%café' Non le début du motif est inconnu
nom LIKE 'café%' Selon le moteur un préfixe se lit comme une plage, si la collation s'y prête
code = 1234 sur une colonne texte Selon le moteur MySQL convertit alors chaque ligne, pas la valeur cherchée

Quand la fonction est vraiment la question — tu filtres toujours par année —, indexe l'expression elle-même. SQLite accepte CREATE INDEX ix_annee ON commande (strftime('%Y', passee_le)), et son plan devient SEARCH … USING COVERING INDEX ix_annee (<expr>=?). PostgreSQL fait de même avec extract(year from passee_le), sur une colonne timestamp : pas sur un timestamptz, dont l'année dépend du fuseau.

Le planificateur choisit, avec ce qu'il sait

Un index ne sert pas parce qu'il existe. Entre la requête et les données, le planificateur estime ce que coûte chaque chemin et prend le moins cher. Il ne lit pas les données pour décider : il s'appuie sur des statistiques — combien de lignes, combien de valeurs distinctes par colonne.

Requêtetexte SQLStatistiquesANALYZEPlanificateurcoût estiméTablecommandeIndexB-treeSEARCHrowidSCAN
Le planificateur ne lit pas les données : il estime, d'après les statistiques, ce que coûte chaque chemin. L'index n'est qu'un chemin parmi d'autres, et le parcours complet reste parfois le bon.

Un index sert à trouver peu de lignes parmi beaucoup. Sur la colonne statut, 97 % des commandes sont « livrées » : pour elles, passer par l'index oblige à faire un aller-retour entre l'index et la table pour presque chaque ligne. Mesuré :

Filtre Lignes Par l'index Table entière
statut = 'annulée' 2 526 1,2 ms 5,9 ms
statut = 'livrée' 97 053 14,7 ms 9,5 ms

SQLite a pourtant choisi l'index dans les deux cas. Après ANALYZE, sa table sqlite_stat1 ne lui dit qu'une moyenne — 100 000 lignes, quatre valeurs, donc 25 000 lignes par valeur — et rien de la valeur rare ni de la valeur écrasante. PostgreSQL, qui garde la liste des valeurs les plus fréquentes, aurait lu la table. D'où deux habitudes : faire tourner ANALYZE (l'autovacuum s'en charge chez PostgreSQL), et ne jamais croire un plan sans l'avoir chronométré.

Ce que coûte un index

Rien n'est gratuit. Chaque INSERT, UPDATE ou DELETE met à jour tous les index de la table : vingt mille insertions prennent 52 ms sans index, 102 ms avec trois. Et chaque index pèse — un peu plus d'un mégaoctet pour celui de client_id, sur une table de cent mille lignes.

On indexe donc les requêtes qu'on exécute, pas les colonnes qu'on a. Une démarche en quatre temps :

  1. Trouver les requêtes lentes ou fréquentes : pg_stat_statements, le journal des requêtes lentes de MySQL, ou un simple chronomètre.
  2. Lire leur plan.
  3. Indexer ce que le plan réclame : les colonnes du WHERE testées par égalité, puis celle de la plage ou du ORDER BY.
  4. Mesurer à nouveau. Un index que le plan n'utilise pas est un coût sans contrepartie : on le supprime.

En résumé

  1. Une requête lente lit trop : le plan le dit avant le chronomètre.
  2. SCAN lit tout ; SEARCH … USING INDEX descend dans un B-tree trié.
  3. Dans une sous-requête corrélée, un index manquant multiplie le coût : 13,9 s sans, 11,7 ms avec.
  4. Dans un index composite : l'égalité d'abord, la plage ensuite.
  5. Un index couvrant évite jusqu'à la table.
  6. Une fonction sur la colonne désactive l'index : écris une plage, ou indexe l'expression.
  7. Un index sert à trouver peu de lignes ; au-delà, la table entière va plus vite.
  8. Chaque index ralentit les écritures : on indexe des requêtes, pas des colonnes.