Comment fonctionne un index B-tree
AvancéUn index est une structure triée, séparée de la table, qui permet de retrouver des lignes sans parcourir toute la table : c'est le principal levier de performance en lecture.
Le B-tree (type par défaut) est un arbre équilibré : trouver une valeur parmi des millions de lignes demande quelques lectures de pages (O(log n)) au lieu de lire toute la table (O(n)).
Ce qu'un B-tree sait faire#
- Égalité :
WHERE email = 'a@b.fr' - Plages :
WHERE cree_le >= ... AND cree_le < ... - Tri :
ORDER BY cree_le DESC LIMIT 20(sans tri supplémentaire) - Préfixe :
WHERE nom LIKE 'Dup%'(hors collationC, il faut créer l'index avectext_pattern_ops) MIN()/MAX()sur la colonne indexée
Ce que coûte un index#
| Bénéfice | Coût |
|---|---|
| Lectures beaucoup plus rapides | Chaque INSERT, UPDATE, DELETE doit aussi mettre à jour chaque index |
| Tris et contraintes d'unicité | Espace disque et mémoire (cache) |
| Un index inutilisé ralentit quand même les écritures |
Astuce : un index doit répondre à un besoin de requête concret. Créez-les à partir des requêtes réelles (voir
pg_stat_statements), pas « au cas où ».pg_stat_user_indexes.idx_scanvous dit quels index ne servent jamais.