Accueil › SQL & bases de données › Index partiels, sur expression et spécialisés
Index partiels, sur expression et spécialisés
Les indexintermediaire
Le B-tree classique couvre l'essentiel, mais quatre variantes résolvent des problèmes qu'il ne sait pas traiter.
Index partiel — n'indexer que les lignes qui comptent :
-- 95 % des commandes sont archivées, on ne cherche que les autres
CREATE INDEX commande_en_cours_idx ON commande (creee_le)
WHERE statut = 'en_cours';
Vingt fois plus petit, donc bien plus souvent en mémoire, et sans maintenance pour les lignes archivées.
Index sur expression — rendre indexable un prédicat calculé :
CREATE INDEX client_email_lower_idx ON client (lower(email));
SELECT * FROM client WHERE lower(email) = 'jean@example.fr'; -- index utilisé
Index unique partiel — l'unicité sur un sous-ensemble :
CREATE UNIQUE INDEX ON adresse (client_id) WHERE principale;
Types d'index spécialisés :
| Type | Pour quoi |
|---|---|
GIN |
jsonb, tableaux, recherche plein texte — « contient » |
GiST |
intervalles, géométrie, contraintes EXCLUDE |
BRIN |
très grosses tables dont les données suivent l'ordre physique |
Le point commun de ces outils : ils rendent indexable ce qui ne l'était pas, ou réduisent l'index à ce qui sert vraiment.
Les termes à connaître
- Index partiel
Un index qui ne couvre qu'une partie des lignes :
CREATE INDEX … WHERE retire_le IS NULL. Plus petit, plus rapide, et il n'est maintenu que pour les lignes concernées.- Index sur expression
Un index construit sur le résultat d'un calcul :
CREATE INDEX ON client (lower(email)). Il rend indexable un prédicat qui enveloppe la colonne dans une fonction.- Index unique partiel
Combinaison des deux :
CREATE UNIQUE INDEX … (client_id) WHERE principaleimpose l'unicité sur un sous-ensemble seulement.- GIN
Generalized Inverted Index : pour les valeurs composées — tableaux,
jsonb, recherche plein texte. Il indexe chaque élément, ce qui permet « contient ».- GiST
Index généralisé pour les données géométriques, les intervalles (
tstzrange) et la recherche par similarité. C'est lui qui soutient les contraintesEXCLUDE.- BRIN
Block Range Index : stocke min/max par bloc de pages. Minuscule, efficace uniquement si les données sont physiquement ordonnées selon la colonne — typiquement une date d'insertion sur une table d'historique.
Cette page présente la partie essentielle de la leçon. Les exemples commentés, les pièges courants et les trois questions de quiz sont dans l'application.