Comment choisir le bon type d’index

On a déjà plongé dans la théorie des index, vu leurs types, appris à les créer et supprimer, et aussi pigé comment indexer des types de données complexes comme les tableaux et JSONB. Maintenant, il est temps de discuter de comment choisir l’index qui va vraiment booster tes requêtes — parce qu’un mauvais choix peut te causer pas mal de galères.

Imagine que ta base de données, c’est une bibliothèque, et les requêtes, ce sont les visiteurs qui cherchent des bouquins. Si les livres sont juste éparpillés par terre, la recherche devient une errance sans fin. Les index, c’est les étagères et les catalogues bien rangés qui t’aident à trouver vite ce qu’il te faut, sans tout fouiller.

Mais si tu mets la mauvaise étagère ou le mauvais catalogue, genre tu utilises un index HASH là où il faudrait un index pour les recherches par plage, c’est comme si le bibliothécaire devait chercher les livres par titre alors qu’il n’a qu’un catalogue par année de publication — ça va traîner, et tout le monde va râler. Dans la base, ça se traduit par des requêtes lentes et une charge système qui explose.

Aujourd’hui, on va voir comment bien choisir son index pour que tes requêtes volent et que ta base ne s’essouffle pas. Sinon, le résultat sera catastrophique : les requêtes rament, les ressources sont bouffées, le bibliothécaire (PostgreSQL) déprime.

Critères pour choisir un index : checklist

Quand tu choisis un index, pose-toi quelques questions :

1. Quel type de données t’as dans cette colonne ?
   • Par exemple, les nombres INTEGER, FLOAT demandent souvent un index B-TREE, les tableaux — GIN, les champs texte — ça dépend de la tâche.

2. Quelles requêtes tu fais le plus souvent ?

   • WHERE field = value ? Recherche directe ? Tu veux sûrement un B-TREE ou un HASH.
   • Recherche dans des tableaux ou du JSONB ? Regarde du côté de GIN.
   • Données géo, plages ? Pense à GiST.

3. Qu’est-ce qui se passe avec tes données ?

   • Si t’as une table avec beaucoup d’inserts et updates, évite de trop indexer, ça va alourdir les perfs.

4. Faut-il garantir l’unicité ?

   • Dans ce cas, tu dois utiliser un index avec l’attribut UNIQUE.

Cas pratiques : exemples réels de choix d’index

Regardons quelques scénarios concrets.

1. Recherche simple par égalité

Tu bosses sur une base d’étudiants et tu veux trouver vite un étudiant par son email :

SELECT * FROM students WHERE email = 'student@example.com';

Qu’est-ce qui compte ici ? On cherche par égalité. Le meilleur choix, c’est l’index B-TREE, il gère super bien les recherches de correspondance exacte.

CREATE INDEX idx_students_email ON students (email);

Ou, si l’email doit être unique :

CREATE UNIQUE INDEX idx_students_email_unique ON students (email);

2. Recherche par plage

Maintenant, imaginons que tu veux trouver les étudiants de plus de 18 ans :

SELECT * FROM students WHERE age > 18;

Pour la recherche par plage, B-TREE marche aussi très bien, sa structure est faite pour ça.

CREATE INDEX idx_students_age ON students (age);

3. Filtrage sur des tableaux

T’as une table courses où une colonne stocke un tableau avec les IDs des étudiants inscrits au cours. Tu veux trouver tous les cours où l’étudiant avec l’ID 123 est inscrit.

SELECT * FROM courses WHERE student_ids @> ARRAY[123];

Pour ce genre de requêtes, l’index GIN est parfait, il est optimisé pour les tableaux.

CREATE INDEX idx_courses_students_ids ON courses USING gin (student_ids);

4. Extraction de données depuis JSONB

Disons que t’as une table avec des données JSONB, où tu stockes des infos sur les commandes. Tu veux trouver toutes les commandes où le client vient de la ville "Moscow" :

SELECT * FROM orders WHERE data->>'city' = 'Moscow';

Ici, un index GIN est adapté, il permet de chercher efficacement dans les clés et valeurs JSONB.

CREATE INDEX idx_orders_data ON orders USING gin (data);

5. Données géographiques

Si tu bosses avec des infos géographiques, genre tu veux trouver tous les points dans un rayon donné, utilise un index GiST. Ce type d’index gère super bien la géométrie et les plages.

CREATE INDEX idx_locations_geom ON locations USING gist (geom);

Comparaison des perfs des différents index

Prenons un exemple réel avec la recherche d’étudiants par email. La table a 1 million de lignes. On va tester la requête avec différents index et sans index :

Conclusion : ici, utiliser un index B-TREE accélère la requête plus de 500 fois.

Erreurs fréquentes dans le choix des index

L’erreur la plus courante, c’est de créer des index "au cas où". Genre tu décides d’indexer chaque colonne de ta table, puis tu te rends compte que les insertions rament. Retiens bien : un index, ce n’est pas une baguette magique qui marche partout. C’est un outil puissant si tu sais t’en servir, mais mal utilisé, il peut faire plus de mal que de bien.

Autre erreur classique : choisir le mauvais type d’index. Par exemple, tu utilises un index HASH pour une recherche par plage, et tes requêtes deviennent super lentes. Tout ça parce que HASH ne sert qu’aux recherches exactes.

Recommandations pour choisir un index

• Si tu fais souvent des recherches par égalité ou des tris, prends un B-TREE.
• Pour des correspondances exactes avec peu de mémoire, tu peux utiliser HASH.
• Si tu bosses avec des tableaux ou du JSONB, ton choix c’est GIN.
• Pour les plages ou données géographiques, utilise GiST.

Et surtout, le conseil ultime : analyse toujours tes requêtes ! Utilise la commande EXPLAIN et EXPLAIN ANALYZE pour voir comment PostgreSQL utilise les index et ce que tu peux améliorer.

EXPLAIN ANALYZE
SELECT * FROM students WHERE email = 'student@example.com';

Voilà, c’est tout pour aujourd’hui ! Maintenant t’as les bases pour choisir tes index comme un Jedi choisit son sabre laser. Fais gaffe à ne pas en mettre partout, et vérifie toujours leur impact sur les perfs.