Parcours séquentiel, parcours par index et parcours par index seul

Lorsque le planificateur a besoin de lignes provenant d’une table, il choisit l’une de trois méthodes d’accès, et les personnes qui vous interrogent s’attendent à ce que vous les nommiez toutes :

• Analyse séquentielle : lire chaque ligne de la table du début à la fin.
• Analyse par index : parcourir un index pour trouver les lignes correspondantes, puis récupérer chacune d’elles dans la table.
• Analyse par index seul : répondre entièrement à partir de l’index sans accéder à la table.

Comprendre pourquoi le planificateur choisit chacune de ces méthodes est le cœur de cette leçon et une question incontournable pour un profil expérimenté.

Une analyse séquentielle lit les pages de la table l’une après l’autre et applique tout filtre à chaque ligne. Aucun index n’est consulté.

Cela semble mauvais, mais c’est souvent le bon choix. Les lectures séquentielles sont rapides sur disque (sans déplacements aléatoires) ; lorsqu’une requête renvoie une grande partie de la table, tout analyser est plus efficace que de parcourir un index des millions de fois.

L’exemple consiste à analyser orders et à conserver les lignes pour lesquelles amount > 100. Si la plupart des commandes dépassent 100, l’analyse séquentielle est le bon choix.

Une analyse par index utilise un arbre B pour accéder directement aux clés correspondantes, puis lit les lignes correspondantes dans le tas de la table.

Elle est particulièrement efficace lorsque le filtre est sélectif et ne renvoie qu’une petite partie de la table. Rechercher 5 lignes au moyen d’un index est plus efficace que d’en lire 10 millions.

Le plan indique le nom de l’index utilisé. Chaque correspondance coûte une recherche dans l’index et une récupération dans le tas (une lecture aléatoire) ; les analyses par index perdent donc leur avantage lorsqu’elles renvoient trop de lignes.

Le concept central qui explique tout cela est la sélectivité : la proportion de lignes conservées par un prédicat.

• Forte sélectivité (peu de lignes correspondent, comme avec un identifiant unique) : elle favorise une analyse par index.
• Faible sélectivité (beaucoup de lignes correspondent, comme avec status IS NOT NULL) : elle favorise une analyse séquentielle.

Une règle empirique courante est qu’à partir du moment où une requête renvoie plus ou moins 5 à 10 % d’une table, le planificateur préfère souvent une analyse séquentielle, car les récupérations aléatoires dans le tas d’un index deviennent plus coûteuses que la lecture ordonnée de l’ensemble.

Une analyse par index seul est la plus rapide des trois. Si toutes les colonnes nécessaires à la requête sont déjà présentes dans l’index, le moteur n’accède jamais au tas de la table.

La requête d’exemple sélectionne uniquement customer_id et filtre sur cette colonne, tandis que l’index porte sur customer_id. Toutes les données nécessaires se trouvent dans l’index ; Postgres indique donc Analyse par index seul.

Cela évite les lectures aléatoires dans le tas qui ralentissent une analyse par index classique, ce qui constitue un gain considérable sur les tables larges.

Les personnes qui vous interrogent apprécient beaucoup cette nuance. Une analyse par index seul doit tout de même confirmer que chaque ligne est visible pour votre transaction (MVCC), et l’index seul ne stocke pas les informations de visibilité.

Postgres utilise la carte de visibilité : si une page est marquée comme entièrement visible, le tas est ignoré ; sinon, la ligne du tas doit tout de même être récupérée. Le plan affiche Heap Fetches: N.

C’est pourquoi une table récemment mise à jour peut afficher de nombreuses récupérations dans le tas et ralentir les analyses par index seul, jusqu’à ce que VACUUM actualise la carte de visibilité.

Une quatrième méthode apparaît souvent : l’analyse du tas par bitmap. Le planificateur la choisit lorsqu’un prédicat correspond à davantage de lignes qu’une analyse par index classique ne le souhaite, mais à moins de lignes qu’une lecture complète de la table.

Il construit d’abord, à partir de l’index, un bitmap des emplacements des lignes correspondantes (analyse de l’index par bitmap), puis récupère les pages du tas dans l’ordre physique plutôt que dans un ordre aléatoire. Les récupérations ordonnées sont beaucoup moins coûteuses que les lectures dispersées d’une analyse par index classique.

Une question classique en entretien : j’ai ajouté un index, mais le plan effectue toujours une analyse séquentielle ; pourquoi ? Voici les raisons courantes :

• Le prédicat n’est pas sélectif : l’analyse séquentielle est réellement moins coûteuse.
• Une fonction entoure la colonne : WHERE lower(email) = ... ne peut pas utiliser un index classique sur email.
• Une incompatibilité de types impose une conversion implicite qui empêche l’utilisation de l’index.
• Les statistiques sont obsolètes : exécutez ANALYZE.
• La table est minuscule : analyser quelques pages est plus efficace que de subir le coût d’un index.

Supposons que orders possède un index sur created_at, mais que cette requête effectue toujours une analyse séquentielle :

Le problème vient de DATE(created_at). Entourer la colonne d’une fonction signifie que l’index sur la valeur brute de created_at ne peut pas être utilisé. La solution consiste à réécrire la requête sous la forme d’un prédicat de plage qui laisse la colonne telle quelle, ou à créer un index d’expression sur DATE(created_at).

Gardez cette comparaison à l’esprit pour l’entretien :

• Analyse séquentielle : préférable lorsqu’une grande partie des lignes est renvoyée ; E/S séquentielles.
• Analyse par index : préférable pour les recherches sélectives ; parcours de l’index suivi de récupérations aléatoires dans le tas.
• Analyse du tas par bitmap : nombre de lignes correspondant situé entre les deux ; index vers bitmap, puis lectures ordonnées du tas.
• Analyse par index seul : la plus rapide lorsque l’index couvre toutes les colonnes nécessaires et que les pages sont entièrement visibles.

Le planificateur choisit en fonction du coût estimé, principalement déterminé par la sélectivité et les statistiques.

Pour vérifier un point en développement, vous pouvez temporairement inciter le planificateur à changer de choix : SET enable_seqscan = off; le force à privilégier les index afin que vous puissiez comparer les plans.

Il s’agit d’une astuce de diagnostic, jamais d’une correction à appliquer en production. Mentionnez en entretien que les véritables solutions sont de meilleures statistiques, un index adapté ou la réécriture du prédicat, et non la désactivation globale des fonctionnalités du planificateur.

Une requête sélectionne uniquement email et filtre sur email, et un index B-tree existe sur email. Le plan affiche Index Only Scan. Pourquoi cette méthode est-elle plus rapide qu’une analyse par index classique ?

Points essentiels concernant les méthodes d’accès :

• L’analyse séquentielle est la meilleure pour les requêtes à faible sélectivité ; l’analyse par index est la meilleure pour les requêtes sélectives.
• L’analyse par index seul évite le tas lorsque l’index couvre toutes les colonnes nécessaires ; surveillez Heap Fetches et la carte de visibilité.
• L’analyse du tas par bitmap constitue le compromis en récupérant les pages du tas dans l’ordre physique.
• Le planificateur décide en fonction de la sélectivité et des statistiques ; les fonctions appliquées aux colonnes, les incompatibilités de types et les statistiques obsolètes expliquent pourquoi un index est ignoré.