Index SQL et plan d exécution : accélérer ses requêtes

Une requête peut être parfaitement correcte et pourtant insupportablement lente. Sur dix lignes, tout va vite ; sur dix millions, la même requête peut prendre plusieurs secondes et bloquer un écran. La différence tient presque toujours à deux choses : la présence d index bien placés, et la capacité à lire le plan d exécution pour comprendre ce que fait réellement le moteur. C est la compétence qui sépare celui qui écrit des requêtes de celui qui les maîtrise.

Ce tutoriel vous apprend à diagnostiquer une lenteur et à la corriger. Vous découvrirez EXPLAIN, la création d index, et la lecture d un plan avant et après optimisation. Toujours sur la boutique TechStock, cette fois remplie de données pour rendre les effets visibles.

🎯 Ce que vous allez apprendre

• Comprendre pourquoi une requête sans index est lente (parcours complet).
• Lire un plan d exécution avec EXPLAIN et EXPLAIN ANALYZE.
• Créer un index et mesurer son effet sur une requête.
• Indexer les colonnes de filtre, de tri et de jointure.
• Concevoir un index composite et comprendre l ordre des colonnes.
• Savoir quand un index est inutile, voire nuisible.

🛠️ Ce que vous allez construire

Vous allez prendre une requête lente sur une grande table, lire son plan, ajouter l index qui change tout, puis vérifier l accélération mesurée. À la fin, vous saurez transformer une recherche de plusieurs secondes en une réponse quasi instantanée, méthode à l appui.

Prérequis

• Connaître SELECT et WHERE (interroger une table) et les jointures.
• Comprendre les clés étrangères (modélisation).
• PostgreSQL, qui offre un excellent outil d analyse de plan.
• ⏱️ Temps estimé : environ 50 minutes.

Pour observer de vrais écarts de performance, il faut du volume. Insérons cent mille produits de test d un seul coup grâce à generate_series :

INSERT INTO produits (nom, prix, stock, categorie_id)
SELECT 'Produit ' || g,
       (random() * 200)::numeric(10,2),
       (random() * 100)::int,
       1 + (random() * 3)::int
FROM generate_series(1, 100000) AS g;

Cette commande remplit la table en quelques instants. Nous avons maintenant de quoi mettre le moteur à l épreuve.

Étape 1 — Pourquoi une requête est lente

Sans index, pour trouver les lignes correspondant à un critère, le moteur n a pas le choix : il lit la table entière, ligne par ligne, et teste chacune. C est le parcours séquentiel (Seq Scan). Sur cent mille lignes, cela reste rapide ; sur cent millions, c est rédhibitoire. L index est l équivalent de l index alphabétique d un livre : au lieu de feuilleter toutes les pages, on saute directement à la bonne.

SELECT nom, prix
FROM produits
WHERE nom = 'Produit 73456';

Telle quelle, sur notre table sans index sur nom, cette requête force un parcours complet : le moteur examine les cent mille lignes pour en trouver une seule. C est exactement le genre de gaspillage qu un index élimine. Mais avant d indexer à l aveugle, apprenons à confirmer le diagnostic.

Étape 2 — Lire le plan avec EXPLAIN

La commande EXPLAIN, placée devant n importe quelle requête, demande au moteur de dévoiler sa stratégie sans exécuter réellement la requête. Elle révèle l opération choisie, le coût estimé et le nombre de lignes attendu.

EXPLAIN
SELECT nom, prix
FROM produits
WHERE nom = 'Produit 73456';

Le plan affichera quelque chose comme Seq Scan on produits, accompagné d un coût et d une estimation de lignes. Le mot Seq Scan est le signal d alerte : le moteur prévoit de tout lire. Le coût est une unité abstraite, sans dimension, qui sert à comparer des plans entre eux : plus il est élevé, plus l opération est chère. Apprendre à repérer un Seq Scan sur une grande table est le premier réflexe d optimisation.

✅ Point d étape — Vous devez voir Seq Scan dans le plan. Notez le coût estimé affiché : il servira de point de comparaison après l ajout de l index.

Étape 3 — Mesurer réellement avec EXPLAIN ANALYZE

EXPLAIN seul donne des estimations. EXPLAIN ANALYZE va plus loin : il exécute vraiment la requête et rapporte les temps réels, en millisecondes. C est l outil de mesure de vérité, à utiliser pour comparer un avant et un après.

EXPLAIN ANALYZE
SELECT nom, prix
FROM produits
WHERE nom = 'Produit 73456';

La sortie ajoute des informations comme actual time et le temps total d exécution. Sur un parcours séquentiel de cent mille lignes, vous lirez un temps mesurable, souvent quelques dizaines de millisecondes. Retenez ce chiffre : nous allons le faire chuter spectaculairement. Attention, EXPLAIN ANALYZE exécute réellement la requête, donc à éviter sur une commande de modification en production sans précaution.

✅ Point d étape — Relevez le temps d exécution réel. C est la mesure objective que toute optimisation devra améliorer.

Étape 4 — Créer l index

L index se crée avec CREATE INDEX, en précisant la table et la colonne concernée. Le moteur construit alors une structure ordonnée, par défaut un arbre équilibré (B-tree), qui lui permet de localiser une valeur sans tout parcourir.

CREATE INDEX idx_produits_nom ON produits (nom);

La création peut prendre un instant sur une grande table, car le moteur doit trier toutes les valeurs existantes. Une fois l index en place, il est maintenu automatiquement à chaque insertion, mise à jour ou suppression. Donnez à vos index des noms explicites, par convention préfixés, pour les retrouver et les gérer facilement.

✅ Point d étape — L index est créé sans erreur. Il ne change rien aux résultats des requêtes, seulement à leur vitesse : c est un accélérateur transparent.

Étape 5 — Constater l accélération

Relançons exactement le même EXPLAIN ANALYZE qu à l étape trois. Le plan a changé : là où il y avait Seq Scan, on lit maintenant Index Scan using idx_produits_nom, et le temps d exécution s effondre.

EXPLAIN ANALYZE
SELECT nom, prix
FROM produits
WHERE nom = 'Produit 73456';

Le moteur emprunte désormais l index pour aller droit à la ligne cherchée, sans parcourir le reste. Le passage de Seq Scan à Index Scan, et la chute du temps mesuré de plusieurs dizaines de millisecondes à une fraction de milliseconde, est la démonstration la plus parlante de l intérêt d un index. Vous tenez là le cycle complet de l optimisation : mesurer, indexer, mesurer de nouveau.

✅ Point d étape — Comparez les deux temps relevés. L accélération doit être de plusieurs ordres de grandeur. Si le plan reste en Seq Scan, c est souvent que la table est trop petite pour que l index soit rentable.

Étape 6 — Indexer les jointures et les tris

Les index ne servent pas qu aux filtres d égalité. Ils accélèrent aussi les jointures et les tris. Un point capital, souvent oublié : les colonnes de clé étrangère ne sont pas indexées automatiquement sur la plupart des moteurs. Or elles servent constamment aux jointures. Indexer client_id dans commandes accélère toute requête qui relie un client à ses commandes.

CREATE INDEX idx_commandes_client ON commandes (client_id);
CREATE INDEX idx_lignes_commande ON lignes_commande (commande_id);
CREATE INDEX idx_lignes_produit ON lignes_commande (produit_id);

De même, un index sur une colonne fréquemment utilisée dans ORDER BY permet au moteur de renvoyer les lignes déjà triées, sans opération de tri coûteuse. Règle pratique : indexez les colonnes qui apparaissent souvent dans les WHERE, les conditions ON de jointure, et les ORDER BY sur de gros volumes.

✅ Point d étape — Après avoir indexé les clés étrangères, relancez un EXPLAIN sur une requête joignant commandes et clients : le plan doit préférer un parcours d index aux jointures.

Étape 7 — Index composite et ordre des colonnes

Quand une requête filtre sur plusieurs colonnes à la fois, un index composite, portant sur plusieurs colonnes, est souvent plus efficace que deux index séparés. L ordre des colonnes dans l index compte énormément : l index est utilisable pour les filtres qui commencent par sa première colonne.

CREATE INDEX idx_produits_cat_prix
  ON produits (categorie_id, prix);

Cet index sert pleinement une requête filtrant sur categorie_id seul, ou sur categorie_id et prix ensemble. En revanche, il n aide pas une requête filtrant uniquement sur prix, car prix n est pas la première colonne : c est la règle dite du préfixe gauche. Placez donc en première position la colonne la plus souvent filtrée seule, généralement la plus sélective.

Quand ne pas indexer

Un index n est pas gratuit. Il occupe de l espace disque et, surtout, il doit être mis à jour à chaque modification de la table : chaque INSERT, UPDATE ou DELETE coûte un peu plus cher. Sur une table très sollicitée en écriture, multiplier les index ralentit les modifications. De même, indexer une colonne à faible diversité, comme un statut ne prenant que deux ou trois valeurs, apporte peu. La bonne démarche n est jamais d indexer à l aveugle, mais d indexer en réponse à un besoin mesuré par le plan d exécution.

🐞 Pièges fréquents

🌍 Réalités du terrain

Sur un serveur modeste, l optimisation n est pas un luxe mais une nécessité : c est souvent ce qui permet à une petite machine de tenir une charge honorable sans monter en gamme. Trois réflexes économes font la différence. D abord, indexez systématiquement les clés étrangères : c est le gain le plus rentable, car les jointures sont partout. Ensuite, mesurez avant d agir : un EXPLAIN ANALYZE vaut mieux que mille intuitions, et il évite d ajouter des index inutiles qui alourdiraient les écritures. Enfin, n indexez que ce qui est réellement interrogé : chaque index économisé est de l espace disque et du temps d écriture préservés, ce qui compte sur un hébergement à ressources comptées. Une base bien indexée reste rapide même quand le trafic augmente.

✅ Récapitulatif

Vous savez désormais optimiser : reconnaître un parcours séquentiel coûteux, lire un plan avec EXPLAIN et le mesurer avec EXPLAIN ANALYZE, créer un index et constater l accélération, indexer filtres, jointures et tris, concevoir un index composite en respectant l ordre des colonnes, et savoir quand s abstenir. Avec cette dernière brique, vous tenez le cycle complet du SQL, de l interrogation à la conception jusqu à la performance. Vous êtes prêt à traiter de vraies bases avec assurance.

🧾 Aide-mémoire

💪 À vous de jouer

1. Mesurez avec EXPLAIN ANALYZE une requête filtrant les produits par categorie_id, avant et après index.
2. Créez un index composite adapté à une recherche par catégorie puis tri par prix.
3. Identifiez un index inutile sur une colonne à deux valeurs et expliquez pourquoi.

-- 1
EXPLAIN ANALYZE
SELECT nom FROM produits WHERE categorie_id = 2;
CREATE INDEX idx_produits_categorie ON produits (categorie_id);
EXPLAIN ANALYZE
SELECT nom FROM produits WHERE categorie_id = 2;

-- 2
CREATE INDEX idx_cat_prix ON produits (categorie_id, prix);
SELECT nom, prix FROM produits
WHERE categorie_id = 2 ORDER BY prix;

-- 3 : un index sur statut (2 valeurs) est peu utile
-- car le filtre ramène une grande part de la table :
-- le parcours séquentiel reste souvent plus rapide.

Tutoriels associés

• Modéliser une base avec les clés étrangères
• Interroger une table avec SELECT, WHERE et tri

Pour aller plus loin

• 🔝 Retour au guide : Apprendre le SQL
• Index PostgreSQL : postgresql.org
• Utiliser EXPLAIN : documentation EXPLAIN

FAQ

Un index accélère-t-il toutes les requêtes ? Non. Il accélère les lectures qui filtrent ou trient sur ses colonnes, mais ralentit légèrement les écritures et occupe de l espace. On indexe en fonction des requêtes réellement exécutées.

Pourquoi mon index n est-il pas utilisé ? Souvent parce qu une fonction est appliquée à la colonne dans le WHERE, ou parce que le filtre ramène une grande part de la table, rendant le parcours complet plus rentable aux yeux du moteur.

Faut-il indexer la clé primaire ? Elle l est automatiquement : une clé primaire crée implicitement un index unique. Ce sont les clés étrangères qu il faut penser à indexer soi-même.

EXPLAIN ou EXPLAIN ANALYZE ? EXPLAIN estime sans exécuter, utile pour un aperçu rapide. EXPLAIN ANALYZE exécute et mesure réellement, indispensable pour comparer un avant et un après.

Mots-clés : index SQL, plan d exécution, EXPLAIN ANALYZE, optimisation requête, B-tree, performance base de données.