Formations — 1 037 leçons en français

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) = '[email protected]';   -- 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 principale impose 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 contraintes EXCLUDE.

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.

Lire l'explication détaillée et faire le quiz →

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.