Au-delà du B-tree : GIN, GiST, BRIN, hash
AvancéPostgreSQL propose plusieurs types d'index pour des données et des opérateurs différents.
| Type | Idéal pour | Exemple |
|---|---|---|
| B-tree | Égalité, plages, tri (défaut) | La grande majorité des colonnes |
| GIN | Valeurs composites : jsonb, tableaux, texte intégral, pg_trgm | CREATE INDEX ON docs USING gin (donnees) |
| GiST | Géométrie, plages (tstzrange), recherche de proximité, exclusions | EXCLUDE USING gist (...), PostGIS |
| BRIN | Très grosses tables dont la colonne est corrélée à l'ordre physique (horodatage d'un journal) | Index minuscule sur cree_le d'une table de logs |
| Hash | Égalité uniquement (rarement préférable au B-tree) |
Exemples#
-- jsonb : recherche de documents contenant un fragment
CREATE INDEX evenements_donnees_idx ON evenements USING gin (donnees);
SELECT * FROM evenements WHERE donnees @> '{"type": "connexion"}';
-- Texte : recherche « contient » avec pg_trgm
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX clients_nom_trgm_idx ON clients USING gin (nom gin_trgm_ops);
SELECT * FROM clients WHERE nom ILIKE '%dupont%';
-- BRIN : quelques ko pour une table de plusieurs centaines de Go
CREATE INDEX journal_cree_le_brin ON journal USING brin (cree_le);
Astuce :
LIKE '%mot%'(joker au début) est inutilisable avec un B-tree classique. C'est le cas d'usage depg_trgm(index GIN ou GiST), ou du texte intégral (voir chapitre suivant).