Accueil › SQL & bases de données › Les index
Les index — entretien SQL & bases de données
6 questions posées en entretien sur 4 sujets, avec la bonne réponse et son explication.
Principes des index
Un index existe mais le plan montre un Seq Scan. Première hypothèse ?
- L'index est corrompu, il faut le reconstruire
- La requête ramène une grande partie de la table, ou les statistiques sont périmées ✓
- PostgreSQL n'utilise jamais les index sur les grandes tables
- Il faut forcer l'index avec un hint
Pourquoi : Un Seq Scan peut être le meilleur plan : au-delà de quelques pour cent des lignes, la lecture séquentielle bat les accès dispersés. Sinon, c'est souvent une estimation fausse — à vérifier en comparant rows= et actual rows= dans EXPLAIN ANALYZE.
Niveau confirme
Index composites
Avec un index sur `(client_id, creee_le)`, quelle requête ne peut pas en profiter efficacement ?
- `WHERE client_id = 42`
- `WHERE client_id = 42 AND creee_le > '2026-01-01'`
- `WHERE creee_le > '2026-01-01'` ✓
- `WHERE client_id = 42 ORDER BY creee_le`
Pourquoi : L'index est trié d'abord par client_id : les entrées d'une date donnée sont dispersées dans tout l'arbre. C'est l'analogie de l'annuaire trié par ville puis par nom.
Niveau confirme
Index spécialisés
Quel type d'index pour une recherche `jsonb @> '{"canal": "mobile"}'` ?
- B-tree
- GIN ✓
- BRIN
- Hash
Pourquoi : GIN indexe chaque élément d'une valeur composée (clés jsonb, éléments de tableau, lexèmes de texte) : c'est lui qui rend l'opérateur « contient » efficace. Un B-tree ne saurait indexer que la valeur entière.
Niveau confirme
Diagnostic
`EXPLAIN ANALYZE` affiche `rows=12` et `actual rows=480000`. Quelle action en premier ?
- Reconstruire l'index avec REINDEX
- Lancer ANALYZE sur la table ✓
- Augmenter work_mem
- Ajouter un index composite
Pourquoi : L'écart d'estimation signifie que l'optimiseur a raisonné sur des statistiques fausses. ANALYZE est gratuit et immédiat. Si l'écart persiste, augmenter l'échantillon ou déclarer des statistiques étendues pour des colonnes corrélées.
Niveau senior
Une requête est rapide en test, lente en production. Cause la plus probable ?
- La latence réseau
- Le volume de données : sur un petit jeu de test, le plan choisi n'est pas le même ✓
- Une version différente du pilote JDBC
- Le niveau d'isolation
Pourquoi : Sur 500 lignes, l'optimiseur choisit un Seq Scan qui est le bon choix ; sur 50 millions, le même plan est désastreux — et les différences d'index n'apparaissent pas non plus. D'où la nécessité de tester sur des volumes réalistes, générés plutôt que copiés de production.
Niveau confirme
Comment créer un index sur une table de production très sollicitée ?
- `CREATE INDEX` classique, l'opération est rapide
- `CREATE INDEX CONCURRENTLY`, qui ne bloque pas les écritures ✓
- En arrêtant l'application le temps de la création
- En créant l'index sur un réplica uniquement
Pourquoi : CREATE INDEX classique prend un verrou qui bloque les écritures pendant toute la construction. CONCURRENTLY parcourt la table en deux passes sans ce verrou : plus lent, mais non bloquant. Il ne peut pas tourner dans une transaction et peut laisser un index INVALID en cas d'échec — à supprimer et relancer.
Niveau senior