Index, statistiques et lecture de plans

Comprendre le mécanisme

Un index accélère certains accès au prix d’espace et de maintenance lors des écritures. L’ordre des colonnes d’un index composite compte selon filtres et tri. Le planificateur utilise des statistiques ; une estimation erronée de cardinalité peut conduire à un choix inadapté. Un Sequential Scan n’est pas automatiquement mauvais, notamment lorsque la requête lit une grande part d’une petite table.

Raisonner et vérifier

EXPLAIN décrit le plan estimé. EXPLAIN ANALYZE exécute réellement l’instruction et mesure les étapes : faites attention aux mutations et aux effets déclenchés, même dans un laboratoire. Comparez lignes estimées et réelles, boucles, tri et lectures de buffers. Une optimisation doit être évaluée sur des données représentatives et sur la charge d’écriture, pas sur une seule mesure après création d’index.

Lire une estimation avant une mesure

EXPLAIN décrit le plan que le moteur envisage, avec ses estimations. Ce document aide à raisonner sur les accès et cardinalités sans constituer une mesure universelle de performance réelle. Repérez les nœuds, les lignes estimées et les prédicats utilisés. Un coût estimé n’est pas une durée directement interchangeable avec une mesure d’horloge ; il faut garder le contexte du planificateur et des données.

ANALYZE implique une exécution

EXPLAIN ANALYZE mesure une exécution réelle de l’instruction. Cette différence est essentielle pour un UPDATE ou un DELETE : le diagnostic peut produire les effets de l’opération. Utilisez un laboratoire et une procédure qui maîtrise explicitement les données. Lire le mot EXPLAIN ne suffit pas à conclure que l’action est inoffensive ; les options choisies font partie du contrat de l’outil.

Comparer estimations et observations

Le planificateur utilise des statistiques pour estimer les cardinalités. Une divergence importante entre lignes estimées et observées peut expliquer un choix mal adapté. Recherchez distribution de données, corrélations et fraîcheur des informations avant de forcer un plan au hasard. La mesure doit conserver la version et la fixture utilisées ; une amélioration sur un autre jeu ne corrige pas nécessairement le problème initial.

Le coût d’écriture d’un index

Un index offre un accès supplémentaire mais occupe du stockage et doit être maintenu lors des écritures pertinentes. Ajouter tous les index imaginables peut dégrader d’autres parcours. Évaluez le gain de lecture avec le coût de mise à jour et d’exploitation. Le choix doit correspondre à des requêtes observées et à leur fréquence, sans transformer une accélération locale en promesse de performance globale.

Un parcours séquentiel peut être rationnel

Lorsque la table est petite ou qu’une grande part des lignes est utile, lire séquentiellement peut être un choix adapté. Le seul nom Seq Scan ne démontre donc pas un défaut. Examinez volumes, sélectivité et coût observé du parcours. Une optimisation doit conserver le bon résultat et viser le goulot réel, plutôt que remplacer systématiquement toute lecture séquentielle par un accès indexé.

L’ordre des colonnes répond à une requête

Un B-tree composite doit être étudié avec ses prédicats et son ordre de tri. Pour une sélection par tenant puis un tri chronologique limité, la position des colonnes et les directions pertinentes peuvent permettre un parcours utile. Le choix d’un index ne promet pas que le planificateur le prendra toujours. Comparez les plans et mesures sur une distribution représentative, avec des égalités et bornes proches de la production.

Le prédicat d’un index partiel

Un index partiel ne contient que les lignes satisfaisant sa condition. Son bénéfice dépend donc du rapport entre ce prédicat et les requêtes à accélérer. Il ne constitue pas un index universel de la table. Définissez le sous-ensemble utile et les parcours qui doivent encore fonctionner autrement. Les tests doivent inclure des lignes dedans et dehors pour vérifier coût et résultat.

Mesurer sur des données représentatives

Trois lignes uniformes ne reproduisent pas une table volumineuse avec quelques tenants dominants. La distribution, les doublons et la sélectivité peuvent influencer le plan. Préparez une fixture adaptée au problème et consignez sa composition. Une mesure de laboratoire n’est utile que si ses conditions expliquent les conclusions ; elle ne doit pas être extrapolée sans limite à toute taille et tout trafic.

Comparer à contexte constant

Caches, concurrence et état du stockage modifient les temps observés. Un résultat chaud isolé ne démontre pas la latence à froid ou sous charge. Définissez le scénario de mesure et répétez seulement ce qui permet d’évaluer sa variabilité utile. Conservez le plan et les données avec le résultat afin que le reviewer sache quelles conditions ont changé entre deux versions.

La vitesse ne corrige pas la sémantique

Un index peut accélérer une requête qui calcule un mauvais total ou divulgue des lignes d’un autre tenant. Validez d’abord les prédicats, jointures et permissions. Les assertions de résultat doivent rester présentes pendant l’optimisation. Séparez la preuve de justesse de la mesure de coût : un temps plus court ne rend pas conforme une sélection qui ne respectait pas le contrat métier.

Atelier guidé : lire, expliquer et vérifier le code

EXPLAIN (ANALYZE, BUFFERS)
DELETE FROM lab_events WHERE created_at < DATE '2020-01-01';

L’instruction de l’atelier est un DELETE accompagné de l’option ANALYZE. Le moteur doit donc exécuter la requête pour produire les observations, ce qui la distingue d’un simple plan estimé. BUFFERS ajoute des informations de diagnostic ; cette option ne transforme pas la suppression en lecture et ne garantit aucune annulation automatique.

Étudiez ce cas uniquement sur des données de laboratoire prévues pour être modifiées. Comparez ensuite un EXPLAIN sans ANALYZE et relevez la différence entre estimation et exécution observée. Pour une requête de lecture, confrontez les lignes estimées et réelles avant de proposer un index. Le diagnostic doit rester une expérience contrôlée dont le périmètre, les effets et les conditions de restauration sont connus.

Index composite : égalité puis ordre et limite

SELECT id, created_at FROM orders
WHERE tenant_id = :tenant
ORDER BY created_at DESC, id DESC
LIMIT 50;

La consultation fixe un tenant puis demande les lignes les plus récentes, avec id comme départage déterministe. Un B-tree commençant par tenant_id puis created_at DESC et id DESC peut isoler le préfixe d’égalité et parcourir directement le suffixe dans l’ordre demandé.

L’ordre des colonnes n’est pas décoratif. Un index qui commence uniquement par la date peut devoir examiner des lignes de nombreux tenants ; un index sur tenant_id seul ne fournit pas automatiquement le tri demandé. L’index retenu doit correspondre aux prédicats et à l’ordre complet, y compris le départage des dates identiques.

Cette correspondance rend un chemin possible, sans imposer le choix du planificateur. Mesurez avec EXPLAIN et, sur un environnement approprié, EXPLAIN ANALYZE en tenant compte de l’exécution réelle. Comparez volumes, statistiques et coût d’écriture de l’index. Un test fonctionnel vérifie également que deux lignes de même date restent ordonnées de façon stable.

Points de vigilance : Cet index ne regroupe pas les lignes du tenant ni l’ordre principal sur created_at. Le préfixe d’égalité isole le tenant et le suffixe suit les deux clés de tri. Mesurer ensuite avec EXPLAIN. Intercaler tenant_id après created_at ne fournit pas un intervalle d’index exclusivement dédié à ce tenant. Après le tenant, id est prioritaire : cet ordre ne correspond pas au tri demandé.