Types principaux d’index : B-TREE, HASH, GIN, GiST

Alors, dans le monde de PostgreSQL, il existe plusieurs types d’index, et chacun a son rôle bien à lui. C’est un peu comme choisir un moyen de transport : tu peux prendre un vélo pour une balade au parc, mais pour traverser toute la ville, tu vas sûrement préférer une voiture. Pareil pour les index : chaque type est fait pour un usage précis.

Dans PostgreSQL, les types d’index principaux sont :

• B-TREE index : l’index passe-partout pour la plupart des besoins.
• HASH index : optimisé pour la comparaison exacte.
• GIN index : parfait pour la recherche dans les tableaux et le JSONB.
• GiST index : utilisé pour les types de données complexes, genre les données géographiques.

Les index sont faits pour accélérer la recherche de lignes. Il y a 4 types d’optimisation différents : chaque type accélère certaines actions et gère mieux certains types de données.

Tu ne peux pas vraiment gérer les index. Tout ce que tu peux faire, c’est choisir le type d’index : aucun ou un de ceux listés ci-dessus. On va voir chacun d’eux pour piger quand et comment les utiliser.

Index B-TREE

B-TREE (abréviation de "balanced tree") — c’est le type d’index le plus courant, la base de PostgreSQL. Cet index crée une structure en arbre où les données sont organisées pour accélérer la recherche, le tri et le filtrage.

Imagine une bibliothèque avec des étagères, chaque étagère ayant des livres rangés par ordre alphabétique. Si tu cherches un livre qui commence par "M", pas besoin de tout feuilleter — tu commences direct au milieu. Les arbres équilibrés fonctionnent sur ce principe.

Quand les utiliser ?

Presque tout le temps ! Les index B-TREE sont surtout utiles pour :

• Recherche par intervalle : WHERE price > 100.
• Tri : ORDER BY name ASC.
• Recherche par égalité : WHERE id = 42.

Exemple de création :

-- On crée un index B-TREE sur la colonne price de la table products :
CREATE INDEX idx_price ON products(price);

Quand tu fais une requête du genre WHERE price > 100, PostgreSQL va utiliser cet index et ne va pas scanner toute la table.

Index HASH

Les index HASH utilisent des hash tables pour une recherche ultra rapide. Leur point fort, c’est la comparaison exacte des valeurs. Mais attention, les index HASH ont une limite : ils ne gèrent pas la recherche par intervalle ni le tri.

C’est comme un fichier de fiches où chaque fiche a un numéro précis. Tu cherches la fiche numéro 42, le bibliothécaire te la sort direct. Mais si tu demandes : « montre-moi les fiches de 40 à 50 », il va te dire non.

Les index HASH sont faits uniquement pour la recherche exacte :

• WHERE email = 'user@example.com'.
• SELECT ... WHERE id = 123.

Si tu veux faire des recherches par intervalle ou trier, HASH n’est pas le bon choix.

Exemple de création :

-- On crée un index hash sur la colonne email de la table users :
CREATE INDEX idx_email_hash ON users USING HASH (email);

Maintenant, PostgreSQL va utiliser cet index pour les requêtes du genre WHERE email = 'user@example.com'.

À noter : les index HASH sont faits pour des cas très spécifiques et sont moins utilisés que les B-TREE.

Index GIN (Generalized Inverted Index)

GIN — c’est un index spécialisé qui fait de la vraie magie avec les tableaux, le JSONB et les données texte. Imagine que tu as une armoire avec des milliers de tiroirs, chaque tiroir a une étiquette. Par exemple, dans le tiroir "pommes" il y a toutes les pommes, dans "bananes" toutes les bananes. Pour trouver des pommes ou des bananes, pas besoin de fouiller partout — tu vas direct au bon tiroir.

Les index GIN servent à :

• Recherche dans les tableaux : @> (contient), <@ (est contenu).
• Données JSONB : WHERE jsonb_data @> '{"clé": "valeur"}'.

Exemple de création

-- On crée un index GIN sur la colonne tags qui contient des tableaux :
CREATE INDEX idx_tags_gin ON products USING GIN (tags);

Maintenant, PostgreSQL pourra trouver efficacement les produits dont les tags sont, par exemple, "électronique" et "recommandé".

Index GiST (Generalized Search Tree)

Les index GiST — c’est l’outil puissant pour gérer des types de données plus complexes, comme les coordonnées géographiques ou les intervalles. Ils construisent des arbres optimisés pour la recherche spatiale et la recherche par intervalle.

Imagine une carte de la ville où chaque point est marqué selon ses coordonnées. Tu peux vite trouver tous les points dans un rayon de 5 km autour de toi.

GiST est adapté pour :

• Données géographiques : SELECT ... FROM locations WHERE ST_DWithin(geom, point, distance).
• Recherche par intervalle : WHERE date_range && '[2023-01-01, 2023-12-31]'.

Exemple :

-- On crée un index GiST sur la colonne location qui contient des données géographiques :
CREATE INDEX idx_location_gist ON places USING GiST (location);

Tu peux maintenant faire des requêtes géographiques complexes, genre chercher les points les plus proches.

Tableau comparatif des index

Voilà, tu connais maintenant les types d’index principaux dans PostgreSQL et comment les utiliser. Retiens bien : choisir le bon index, c’est un move stratégique qui va booster la vitesse de tes requêtes. Échec et mat, les lenteurs !