Index et plans d'exécution : faire travailler l'optimiseur pour vous

Tout moteur relationnel embarque un optimiseur qui décide comment exécuter votre requête, et tout optimiseur est un modèle de coût alimenté par des statistiques. Face à une requête lente, la question intéressante n'est pas « quel index dois-je ajouter ? » mais « qu'est-ce que l'optimiseur croyait, et où s'est-il trompé ? ». Le plan d'exécution est le seul endroit où se trouve la réponse.

Ce guide part du principe que vous écrivez déjà des prédicats exploitables par l'optimiseur — pas de fonction appliquée à la colonne indexée, pas de conversion implicite, bref les réflexes présentés dans les bonnes pratiques SQL. Ce qui suit est la couche en dessous : ce qu'est physiquement un index, ce qui le rend utilisable, et comment lire un plan avec assez de précision pour agir. Les exemples utilisent la syntaxe de PostgreSQL 17, avec les équivalents Oracle signalés au passage.

Ce qu'un B-tree sait faire, et ce qu'il ne sait pas faire

Presque tous les index que vous créez sont des arbres équilibrés de clés triées. Les pages internes contiennent des clés de séparation qui orientent la recherche ; les pages feuilles contiennent les clés dans l'ordre, chacune accompagnée d'un pointeur vers la ligne (un identifiant de tuple dans le heap pour PostgreSQL, un ROWID pour Oracle). Les pages feuilles sont chaînées entre elles : une fois la recherche arrivée à destination, le moteur peut avancer ou reculer sans repasser par la racine.

Cette structure permet exactement quatre choses, et les comprendre éclaire presque toutes les questions sur les index :

1. Recherche par égalité. On descend jusqu'à la première clé correspondante. Le coût est logarithmique en nombre de lignes — trois ou quatre lectures de pages sur une grosse table.
2. Parcours d'intervalle. On descend une fois, puis on parcourt les feuilles tant que la clé respecte la borne. >, <, BETWEEN et un LIKE 'abc%' sur préfixe se ramènent tous à ce cas.
3. Tri. Les feuilles sont déjà triées, donc un index peut fournir l'ORDER BY sans étape de tri, ce qui change tout lorsqu'il est combiné à un LIMIT.
4. Unicité. C'est par un index unique que le moteur fait respecter les clés primaires et les contraintes d'unicité.

En revanche, il ne peut rien pour une recherche qui ne démarre pas à un point connu de l'ordre des clés. LIKE '%invoice%' n'a pas de préfixe de départ. Un prédicat sur la deuxième colonne d'un index composite non plus, quand la première n'est pas contrainte — même si PostgreSQL 18 a assoupli ce point avec le skip scan, qui sonde chaque valeur distincte de la colonne de tête lorsqu'elle en compte peu. Voyez-y un filet de sécurité, pas un principe de conception.

Index composites : l'ordre des colonnes, c'est la conception

Un index sur (a, b, c) trie les lignes par a, puis par b à a égal, puis par c. Conséquence : l'index est utilisable pour une requête qui contraint un préfixe de tête de ces colonnes. WHERE a = ? l'utilise, WHERE a = ? AND b = ? l'utilise mieux, WHERE b = ? seul ne peut pratiquement pas s'en servir.

La règle qui en découle mérite d'être retenue : d'abord les colonnes testées par égalité, puis la colonne sur laquelle vous faites un parcours d'intervalle ou un tri. Dès que le parcours rencontre un prédicat d'intervalle, toutes les colonnes qui le suivent dans l'index ne servent plus à positionner la recherche — seulement à filtrer les lignes que l'intervalle renvoie.

-- Query: recent unpaid invoices for one customer
SELECT id, issued_at, amount
FROM   invoice
WHERE  customer_id = $1 AND status = 'UNPAID' AND issued_at >= $2
ORDER  BY issued_at DESC
LIMIT  50;

-- Equalities first, range/sort column last
CREATE INDEX invoice_cust_status_issued
  ON invoice (customer_id, status, issued_at DESC);

Cet index répond à toute la requête avec un seul parcours d'intervalle et sans tri. Inversez les colonnes en (issued_at, customer_id, status) et le moteur doit parcourir toutes les factures depuis $2 puis filtrer : sans conséquence sur une petite table, désastreux sur une grosse.

Un index couvrant va un cran plus loin : s'il contient toutes les colonnes que touche la requête, le moteur n'a jamais besoin de visiter la table. PostgreSQL l'affiche comme un Index Only Scan, qui n'est possible que si la visibility map indique que les pages concernées sont entièrement visibles — une raison de plus pour laquelle l'autovacuum compte, comme l'explique la comparaison entre Oracle et PostgreSQL. Les colonnes dont on n'a besoin qu'en sortie, jamais pour filtrer ni trier, vont dans INCLUDE plutôt que dans la clé, ce qui garde l'arbre plus compact :

CREATE INDEX invoice_cust_issued_covering
  ON invoice (customer_id, issued_at DESC) INCLUDE (amount, status);

Sélectivité et statistiques

L'optimiseur choisit entre un parcours d'index et un parcours séquentiel en fonction de la sélectivité estimée : la fraction de lignes qu'un prédicat conserve. Un index vaut la peine quand cette fraction est faible, car chaque ligne retenue peut coûter une lecture de page aléatoire. Au-delà de quelques pour cent d'une grosse table, tout lire séquentiellement revient moins cher. C'est pourquoi une colonne status à deux valeurs ne profite presque jamais d'un B-tree classique, et pourquoi personne ne devrait s'étonner qu'un index sur cette colonne reste inutilisé.

Les estimations de sélectivité reposent sur les statistiques collectées par ANALYZE (PostgreSQL, lancé automatiquement par l'autovacuum) ou DBMS_STATS (Oracle) : le nombre de valeurs distinctes par colonne, la proportion de NULL, la liste des valeurs les plus fréquentes avec leur fréquence, et un histogramme pour le reste. Trois situations les mettent en défaut :

• Des statistiques périmées après un chargement massif ou une mise à jour de masse. La solution consiste à lancer ANALYZE en dernière étape de tout job ETL.
• Des colonnes corrélées. Le planificateur suppose leur indépendance : WHERE city = 'Lyon' AND country = 'FR' est estimé en multipliant deux sélectivités alors que la première implique la seconde, ce qui produit une estimation largement sous-évaluée. La réponse de PostgreSQL, ce sont les statistiques étendues : CREATE STATISTICS city_country (dependencies, ndistinct) ON city, country FROM address;
• Une distribution asymétrique. Une colonne dont une seule valeur couvre 90 % des lignes a besoin d'un histogramme et, sous Oracle, tire parti de l'adaptive cursor sharing pour que des valeurs de bind différentes puissent obtenir des plans différents.

Augmenter default_statistics_target (ou son équivalent par colonne) affine les histogrammes au prix d'une analyse plus lente. Faites-le pour les quelques colonnes dont la distribution est réellement difficile, pas globalement.

Au-delà du B-tree : les autres types d'index

Deux variantes s'appliquent à tous ces types. Un index partiel ne stocke que les lignes qui satisfont un prédicat, ce qui est parfait quand les requêtes visent toujours un petit sous-ensemble : CREATE INDEX ON job (created_at) WHERE status = 'PENDING'; vous donne un index minuscule sur une table de file d'attente, quel que soit le volume d'historique terminé qui s'accumule. Un index sur expression stocke le résultat d'une fonction et rend l'index de nouveau utilisable pour un prédicat qui, sinon, ne serait pas sargable : CREATE INDEX ON person (lower(email)); est utilisé par WHERE lower(email) = $1, à condition que la requête reprenne exactement la même expression.

PostgreSQL construit aussi à la volée des bitmap heap scans : il rassemble dans un bitmap en mémoire les identifiants de tuples correspondants issus d'un ou plusieurs index, les trie par emplacement physique, puis lit le heap dans cet ordre. C'est ainsi que deux index mono-colonne distincts peuvent coopérer sur un AND ou un OR — utilement, mais jamais aussi bien qu'un index composite aux colonnes bien ordonnées.

Lire EXPLAIN ANALYZE

Demandez la version mesurée, avec les buffers, et lisez-la comme un arbre qui s'exécute du nœud le plus interne vers l'extérieur :

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT c.name, sum(i.amount)
FROM   customer c
JOIN   invoice i ON i.customer_id = c.id
WHERE  i.issued_at >= date '2026-01-01'
GROUP  BY c.name;

HashAggregate  (cost=18422.10..18560.42 rows=11066 width=40)
               (actual time=214.883..218.014 rows=10871 loops=1)
  Group Key: c.name
  ->  Hash Join  (cost=612.00..17250.44 rows=234332 width=36)
                 (actual time=6.201..168.442 rows=233980 loops=1)
        Hash Cond: (i.customer_id = c.id)
        ->  Index Scan using invoice_issued_at_idx on invoice i
              (cost=0.43..14887.02 rows=234332 width=12)
              (actual time=0.038..92.117 rows=233980 loops=1)
              Index Cond: (issued_at >= '2026-01-01'::date)
              Buffers: shared hit=4210 read=8891
        ->  Hash  (cost=474.00..474.00 rows=11040 width=32)
                  (actual time=6.104..6.105 rows=11040 loops=1)
              ->  Seq Scan on customer c  (cost=0.00..474.00 rows=11040 width=32)
Planning Time: 0.412 ms
Execution Time: 219.774 ms

Lisez-le dans cet ordre :

1. Comparez le rows= estimé et le rows= réel sur chaque nœud. Un facteur dix ou plus, c'est le signal que vous cherchez, et le nœud le plus bas où il apparaît en est la cause ; tout ce qui se trouve au-dessus hérite de l'erreur. Ici, les estimations sont excellentes : la forme du plan est donc fiable.
2. Multipliez par loops. Dans une boucle imbriquée, les nombres de lignes et les temps réels sont donnés par itération. Un nœud qui affiche 3 lignes et 0,2 ms avec loops=50000 a consommé dix secondes.
3. Regardez Buffers. shared hit provient du cache, read provient du système d'exploitation, et temp read/written signifie qu'une opération a débordé sur disque — en général un tri ou un hash qui a dépassé work_mem.
4. Distinguez Index Cond de Filter. Une condition d'index restreint le parcours ; un filtre s'applique aux lignes que le parcours a déjà renvoyées. Rows Removed by Filter: 480000 signifie que vous avez récupéré un demi-million de lignes pour les jeter.
5. Vérifiez les temps globaux. Un temps de planification élevé par rapport au temps d'exécution évoque trop de partitions ou une table surindexée.

Les opérateurs de jointure racontent leur propre histoire. Le Nested Loop sonde la relation interne une fois par ligne externe : optimal quand le côté externe est minuscule et que la recherche interne est indexée, catastrophique quand l'estimation du côté externe était fausse. Le Hash Join construit une table de hachage à partir de l'entrée la plus petite et fait défiler la plus grande devant elle — le choix habituel pour les équijointures volumineuses. Le Merge Join consomme les deux côtés dans l'ordre trié, ce qui est peu coûteux quand des index fournissent déjà cet ordre et cher quand il impose deux tris. La sortie des plans Oracle nomme les trois mêmes formes et y ajoute, en exécution parallèle, des méthodes de distribution pour le HASH JOIN.

Quand le planificateur se trompe

Un mauvais plan remonte presque toujours à une mauvaise estimation, et la liste des causes est courte :

• Une estimation de lignes erronée, due à des statistiques périmées, à des colonnes corrélées ou à un prédicat que l'optimiseur ne sait pas analyser — un appel de fonction, une valeur cachée derrière un bind opaque, ou une jointure sur une expression.
• La sensibilité aux paramètres. Le plan générique d'une requête préparée peut être construit pour une valeur moyenne, puis réutilisé pour une valeur très atypique. PostgreSQL recalcule des plans spécifiques lors des premières exécutions et peut ensuite basculer sur un plan générique ; positionner plan_cache_mode = force_custom_plan sur la session permet de diagnostiquer ce cas. Oracle appelle ce phénomène le bind peeking et l'atténue avec l'adaptive cursor sharing.
• Un index manquant, ou un index dont l'ordre des colonnes ne correspond pas au prédicat.
• Un modèle de coût inadapté. La valeur par défaut de random_page_cost, 4.0, suppose des disques rotatifs. Sur SSD, l'abaisser à 1.1 déplace légitimement l'équilibre en faveur des parcours d'index.
• Un work_mem insuffisant, qui force un hash ou un tri à déborder sur disque ; le plan est bon, mais pas l'exécution.

Un ORM ajoute un niveau d'indirection mais ne change rien à tout cela : le SQL généré a toujours un plan, et le lazy loading transforme toujours une requête en mille — voir Spring Data JPA pour charger les données de façon ensembliste.

Le coût d'un index

Les index ne sont pas gratuits, et la facture arrive côté écriture. Chaque insertion et chaque suppression met à jour tous les index de la table ; une mise à jour touche les index dont elle modifie les colonnes, et tous si la ligne ne peut pas rester sur sa page. Les index occupent aussi du cache que les pages de données utiliseraient sinon, et ils gonflent : un index très sollicité en mise à jour accumule des entrées mortes jusqu'à sa reconstruction. Sous PostgreSQL, REINDEX INDEX CONCURRENTLY reconstruit un index sans poser de verrou bloquant, et CREATE INDEX CONCURRENTLY en ajoute un sur une table en pleine activité au prix de deux passes sur la table.

Avant d'ajouter un index, vérifiez si un index existant peut être étendu à la place — un index sur (customer_id) devient redondant dès qu'existe (customer_id, issued_at). Avant d'en conserver un, vérifiez que quelque chose l'utilise :

SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM   pg_stat_user_indexes
WHERE  idx_scan = 0
ORDER  BY pg_relation_size(indexrelid) DESC;

Zéro scan depuis la dernière remise à zéro des statistiques, sur une table effectivement interrogée, signifie que l'index n'est que du surcoût — sauf s'il porte une contrainte. Concevez d'abord correctement le schéma, comme l'explique la conception de bases de données et la normalisation : les index s'adaptent aux modes d'accès, ils ne sauvent pas un modèle défaillant.

Points clés à retenir

• Un B-tree sert l'égalité, les intervalles, le tri et l'unicité à partir d'une structure triée, et ne peut démarrer un parcours qu'à partir d'un préfixe connu de sa clé.
• Dans un index composite, placez d'abord les colonnes testées par égalité et en dernier la colonne d'intervalle ou de tri ; ajoutez avec INCLUDE les colonnes de sortie non filtrées pour permettre les index-only scans.
• L'optimiseur décide sur la base de la sélectivité estimée : des statistiques fausses — périmées, corrélées ou asymétriques — sont la cause habituelle d'un mauvais plan.
• Au-delà des B-trees, utilisez GIN pour les données multivaluées, BRIN pour les très grosses tables corrélées à l'ordre physique, les index bitmap pour les entrepôts surtout en lecture, et les index partiels ou sur expression pour rendre un petit sous-ensemble peu coûteux.
• Lisez EXPLAIN ANALYZE du nœud le plus interne vers l'extérieur, comparez lignes estimées et lignes réelles, multipliez par les boucles, et surveillez les lignes éliminées par un filtre et les débordements dans des fichiers temporaires.
• Chaque index taxe les écritures et consomme du cache : étendez un index existant plutôt que d'ajouter un quasi-doublon, et supprimez ce que pg_stat_user_indexes montre comme inutilisé.