0Pricing
Coding Interview Prep · Leçon

Quand les index nuisent : écritures et sélectivité

L’amplification des écritures et pourquoi un index sur une colonne peu sélective est inutile

Quand les index nuisent : écritures et sélectivité est une leçon Coding Interview Prep gratuite sur CoddyKit. Ceci est la leçon 4 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage Coding Interview Prep, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours Coding Interview Prep comprend 4 leçons au total.

La question qui se cache derrière la question

Après trois leçons sur les raisons pour lesquelles les index sont utiles, les recruteurs renversent la question : « Pourquoi ne pas indexer chaque colonne ? » Un candidat solide explique que les index ont de vrais coûts, au niveau des écritures ainsi que du cache et du stockage, et que certains index ne seront jamais utilisés par l’optimiseur.

Cette leçon présente les deux grandes raisons pour lesquelles un index peut nuire : l’amplification des écritures et la faible sélectivité.

Chaque index ralentit les écritures

Un index doit rester synchronisé avec la table. Chaque INSERT, chaque DELETE et chaque UPDATE d’une colonne indexée doit également mettre à jour la structure de l’index. C’est l’amplification des écritures : une modification de ligne devient une écriture dans la table, plus une écriture pour chaque index concerné.

Une table comportant huit index doit fournir environ neuf fois plus de travail d’écriture qu’une table sans index. Pour les tables soumises à de nombreuses écritures ou à un débit élevé, c’est un coût important.

Exemple détaillé : le coût des écritures

Imaginez une table d’événements qui ingère des milliers de lignes par seconde. Chaque index supplémentaire oblige chaque insertion à effectuer davantage de travail : fractionner les pages de l’index, mettre à jour les feuilles et entrer en concurrence pour le cache.

Pour une table alimentée uniquement par ajouts et dominée par les écritures, la bonne réponse consiste souvent à conserver peu d’index, voire aucun, en dehors de la clé primaire, et à effectuer plutôt les lectures lourdes sur une réplique ou dans un entrepôt de données.

-- Each of these indexes adds cost to EVERY insert below
CREATE INDEX ix_events_user ON events (user_id);
CREATE INDEX ix_events_type ON events (event_type);
CREATE INDEX ix_events_ts   ON events (created_at);

INSERT INTO events (user_id, event_type, created_at)
VALUES (42, 'click', now());  -- now updates table + 3 indexes

Ce que signifie la sélectivité

La sélectivité mesure la capacité d’une colonne à distinguer les lignes, c’est-à-dire la proportion de lignes correspondant à une valeur courante. Une sélectivité élevée signifie peu de lignes par valeur (comme pour une adresse e-mail ou un UUID). Une faible sélectivité signifie beaucoup de lignes par valeur (comme pour un booléen ou un statut comportant trois options).

Les index sont rentables sur les colonnes à forte sélectivité, lorsqu’une recherche élimine presque toutes les lignes. Sur les colonnes à faible sélectivité, ils ne sont souvent pas avantageux.

Pourquoi un index à faible sélectivité est inutile

Supposons que is_active vaille true pour 90 % des utilisateurs. Une recherche dans l’index renverrait 90 % de la table et, pour autant de lignes, le moteur effectuerait une lecture du tas par ligne, ce qui serait plus lent qu’un simple parcours séquentiel de la table en un seul passage.

L’optimiseur ignore donc correctement l’index et effectue un parcours séquentiel. L’index ne représente alors qu’un coût d’écriture et de stockage, sans apporter le moindre bénéfice en lecture.

-- 90% of rows match: the planner will likely skip this index
CREATE INDEX ix_users_active ON users (is_active);
SELECT * FROM users WHERE is_active = true;

Le seuil approximatif

Voici une règle générale utile à énoncer : lorsqu’un prédicat correspond à plus ou moins 5 à 20 % d’une table, un parcours séquentiel est généralement plus performant qu’un parcours d’index, car les lectures aléatoires du tas coûtent plus cher que le chargement continu des pages dans l’ordre.

Le point de bascule exact dépend de la taille des lignes, de la mise en cache et de la vitesse du stockage. C’est pourquoi l’optimiseur utilise les statistiques, et non un nombre fixe, pour prendre sa décision.

Les index partiels à la rescousse

Si vous n’interrogez que les valeurs rares d’une colonne asymétrique, un index partiel (PostgreSQL) indexe uniquement ces lignes : il est compact, sélectif et peu coûteux à maintenir.

Si 1 % des commandes sont pending et que ce sont celles que vous interrogez constamment, indexez uniquement celles-ci. L’index reste petit et l’optimiseur l’utilisera volontiers.

-- Index only the rare, frequently-queried rows
CREATE INDEX ix_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

Des statistiques obsolètes induisent l’optimiseur en erreur

L’optimiseur décide entre index et parcours à partir des statistiques des colonnes. Si celles-ci sont obsolètes, après un chargement massif ou une mise à jour importante, il peut mal évaluer la sélectivité et choisir le mauvais plan.

Lorsqu’un recruteur dit : « l’index existe mais n’est pas utilisé », une excellente réponse consiste notamment à actualiser les statistiques avec ANALYZE avant d’accuser l’index lui-même.

ANALYZE orders;  -- refresh planner statistics

D’autres façons dont les index nuisent

Complétez votre réponse avec les coûts moins connus :

  • Stockage et cache : les index occupent de l’espace disque et se disputent la mémoire, expulsant les pages de données utiles.
  • Les index redondants ou qui se chevauchent : ils sont entretenus, mais jamais choisis.
  • Gonflement : lors de mises à jour importantes, les arbres B se fragmentent et nécessitent REINDEX.
  • Confusion de l’optimiseur : un trop grand nombre d’index similaires ralentit la planification et la rend moins prévisible.

Trouver les index inutilisés

Pour justifier un nettoyage en situation réelle, mentionnez que PostgreSQL suit l’utilisation des index. Les index avec idx_scan = 0 sont des candidats à la suppression : ils coûtent des écritures et de l’espace sans jamais servir une lecture.

SELECT relname AS table_name, indexrelname AS index_name, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

Comment le formuler en entretien

Un résumé complet et équilibré :

« Les index entraînent une amplification des écritures : chaque insertion, mise à jour ou suppression les entretient, et ils exercent en plus une pression sur le stockage et le cache. Ils ne sont rentables qu’avec des prédicats à forte sélectivité ; sur une colonne correspondant à la plupart des lignes, l’optimiseur préfère à juste titre un parcours séquentiel, l’index ne constituant donc qu’un pur surcoût. Pour les colonnes asymétriques, je choisis un index partiel, je maintiens les statistiques à jour avec ANALYZE et je supprime les index inutilisés. »

Vérification rapide

Déterminez quel index a le moins de chances de justifier son coût.

Récapitulatif : quand les index nuisent

Points essentiels :

  • Chaque index ajoute une amplification des écritures, ainsi qu’un coût de stockage et de cache.
  • Les index sont utiles sur les colonnes à forte sélectivité ; sur celles à faible sélectivité, l’optimiseur préfère un parcours séquentiel.
  • Au-delà d’environ 5 à 20 % des lignes correspondantes, un parcours est généralement plus performant.
  • Utilisez un index partiel pour les colonnes asymétriques que vous n’interrogez qu’avec leurs valeurs rares.
  • Maintenez les statistiques à jour avec ANALYZE et supprimez les index inutilisés (idx_scan = 0).

Le cours sur la stratégie d’indexation est ainsi terminé : créez des index là où ils sont réellement utiles et prouvez-le avec le plan.

Questions Fréquemment Posées

La leçon « Quand les index nuisent : écritures et sélectivité » est-elle gratuite ?

Oui — le texte complet de « Quand les index nuisent : écritures et sélectivité » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours Coding Interview Prep, passe à CoddyKit PRO. Le cours Coding Interview Prep comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « Quand les index nuisent : écritures et sélectivité » ?

L’amplification des écritures et pourquoi un index sur une colonne peu sélective est inutile Tu pratiques Coding Interview Prep avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer Coding Interview Prep ?

Aucune expérience préalable n'est requise. Coding Interview Prep sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 4 sur 4.

Combien de temps prend la leçon « Quand les index nuisent : écritures et sélectivité » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon Coding Interview Prep ?

Oui. Chaque leçon Coding Interview Prep inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

  1. Index B-tree et leur utilité
  2. Ordre des colonnes d’un index composite
  3. Index couvrants et parcours par index seul
  4. Quand les index nuisent : écritures et sélectivité
← Retour à Coding Interview Prep