Décision : quel type d'index ?
Le bon type d'index dépend du schéma d'accès de la requête (égalité, plage ou recherche texte/JSON), de la cardinalité de la colonne et de l'équilibre lecture/écriture. Un mauvais index gaspille de l'espace et ralentit les écritures. Cet arbre part de la forme de la requête et vous oriente parmi les options B-tree, Hash, Bitmap, GIN/GiST, index partiel et index couvrant, et vous rappelle que l'ordre des colonnes est critique dans un index composite.
Comment votre requête utilise-t-elle cette colonne ?
Le schéma d'accès est le facteur le plus décisif dans le choix d'un index. Comparez-vous seulement la colonne avec '=', effectuez-vous des plages/tris (>, <, BETWEEN, ORDER BY), ou recherchez-vous du texte intégral, un champ JSON ou des données géométriques/tableaux ? Commencez ici ; ne choisissez pas de type d'index tant que le besoin n'est pas clair.
- Égalité et plage/tri mélangés (=, >, <, BETWEEN, ORDER BY)
- Égalité seule (=), ni plage ni tri
- Recherche plein texte, champ JSON/JSONB, tableau ou géométrique
- La requête cible toujours le même petit sous-ensemble de lignes (par ex. WHERE status='active')
Quelle est la cardinalité de la colonne (nombre de valeurs distinctes) ?
Si une plage/un tri est nécessaire, le choix par défaut est le B-tree ; toutefois, pour des colonnes avec très peu de valeurs distinctes (faible cardinalité : sexe, statut, oui/non) dans un data warehouse à forte lecture, un index Bitmap peut être préférable. La colonne a-t-elle de nombreuses valeurs uniques, ou quelques valeurs répétées ?
- Forte cardinalité (nombreuses valeurs uniques) ou charge OLTP
- Faible cardinalité et forte lecture / data warehouse (DWH)
La requête ne lit-elle que les colonnes de l'index ?
Le schéma d'accès convient à un B-tree. Une dernière optimisation : si toutes les colonnes renvoyées et filtrées par la requête sont dans l'index, le moteur peut répondre à partir de l'index seul sans visiter la table (index-only / covering scan). Votre requête lit-elle quelques colonnes, ou a-t-elle besoin de la majeure partie de la ligne ?
- Oui, quelques colonnes ; réponse possible sans visiter la table
- Non, un accès standard suffit
Recommandation : B-tree (par défaut)
Un B-tree est une structure arborescente équilibrée qui prend en charge de nombreux schémas d'accès : égalité, plage, tri (ORDER BY), correspondance de préfixe avec LIKE 'abc%' et MIN/MAX. C'est le type d'index par défaut de la plupart des moteurs relationnels et le bon point de départ en cas de doute.
Recommandation : index Hash (égalité uniquement)
Un index hash stocke le hachage de la valeur de la colonne et est très rapide uniquement pour les recherches d'égalité exacte ('='). Il ne prend pas en charge les recherches par plage, tri ou préfixe, son domaine d'usage est donc restreint.
Recommandation : index Bitmap (faible cardinalité / DWH)
Un index bitmap conserve un tableau de bits pour chaque valeur distincte et combine très rapidement plusieurs conditions à faible cardinalité par des AND/OR bit à bit. Il excelle dans les charges à forte lecture comme le data warehouse et le reporting.
Recommandation : GIN / GiST (texte, JSON, tableau, géométrie)
PostgreSQL dispose de familles d'index spécialisées pour les types d'accès qu'un B-tree standard ne peut pas traiter. GIN pointe vers de nombreux composants d'une valeur (mots, clés JSONB, éléments de tableau) ; GiST convient aux requêtes géométriques/de plage/de plus proche voisin.
Recommandation : index partiel / filtré
Un index partiel n'indexe que les lignes satisfaisant une condition (WHERE ...) au lieu de toute la table. Si les requêtes ciblent toujours le même petit sous-ensemble, l'index devient beaucoup plus petit et rapide, et le coût d'écriture diminue.
Recommandation : index couvrant (INCLUDE)
Un index couvrant contient toutes les colonnes dont la requête a besoin, ce qui permet au moteur de répondre à partir de l'index seul sans jamais visiter la table (heap) (index-only scan). Il apporte une accélération importante pour les requêtes fréquentes renvoyant peu de colonnes.