Indexation des tableaux : création des index GIN et BTREE

Imagine que t’as une table avec des millions de lignes, et qu’une des colonnes stocke des tableaux. Par exemple, on a une table products, et chaque produit peut appartenir à plusieurs catégories :

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    categories TEXT[] -- Tableau de chaînes pour stocker les catégories du produit
);

Supposons que tu veuilles trouver tous les produits qui sont dans la catégorie electronics. Utiliser juste l’opérateur @> pour chercher peut te faire faire un scan complet de la table :

SELECT *
FROM products 
WHERE categories @> ARRAY['electronics'];

Un scan complet (Seq Scan) — c’est lent. Surtout si la table est énorme. Les index sont là pour transformer ça en une recherche bien plus rapide.

Types d’index pour les tableaux

PostgreSQL gère deux types d’index principaux que tu peux utiliser pour les tableaux :

1. GIN (Generalized Inverted Index) — parfait pour chercher vite fait des éléments dans un tableau ou checker les intersections.
2. BTREE (Binary Tree) — utile pour d’autres opérations, genre comparer exactement des tableaux.

On va détailler chacun d’eux.

1. Index GIN : à la vitesse de la lumière

GIN (Generalized Inverted Index) — c’est l’index qui déchire pour bosser avec des opérateurs comme :

• @> (le tableau contient un élément ou un autre tableau),
• <@ (le tableau est contenu dans un autre tableau),
• && (les tableaux se croisent).

Voilà comment tu peux créer un index GIN pour notre colonne categories :

CREATE INDEX idx_categories_gin
ON products USING gin(categories);

Après avoir créé l’index, tes requêtes vont aller carrément plus vite. Par exemple, la requête :

SELECT *
FROM products 
WHERE categories @> ARRAY['electronics'];

va utiliser ton index GIN.

Fun fact : L’index GIN marche comme une liste inversée — il garde en mémoire quels éléments (genre des chaînes) sont dans quelles lignes. C’est comme l’index à la fin d’un bouquin pour retrouver un sujet à une page précise. Pratique, non ?

2. Index BTREE : quand l’ordre compte

BTREE (Binary Tree) — c’est l’index standard qu’on retrouve dans la plupart des bases de données. Il est top pour les opérations où tu veux comparer exactement des tableaux, genre :

• Vérifier l’égalité d’un tableau =,
• Comparer des tableaux selon l’ordre des éléments (>, <).

Pour créer un index BTREE sur un tableau, tu fais comme ça :

CREATE INDEX idx_categories_btree
ON products USING btree(categories);

Exemple de requête qui peut utiliser l’index BTREE :

SELECT *
FROM products
WHERE categories = ARRAY['electronics', 'gadgets'];

Mais faut pas oublier que les index BTREE ne sont pas faits pour les opérateurs genre @> ou <@. Pour ça, GIN est ton pote.

Exemples d’utilisation des index

Maintenant, on va mixer la théorie et la pratique avec quelques exemples.

1. Recherche d’intersection de tableaux

Supposons qu’on veuille trouver tous les produits liés aux catégories electronics et smartphones, en utilisant l’opérateur && (intersection de tableaux) :

SELECT *
FROM products
WHERE categories && ARRAY['electronics', 'smartphones'];

Pour ça, l’index GIN qu’on a déjà créé est parfait :

CREATE INDEX idx_categories_gin
ON products USING gin(categories);

Avec cet index, la requête va tourner bien plus vite grâce à la liste inversée.

2. Comparer des tableaux pour l’égalité

Si tu veux trouver les produits qui appartiennent uniquement aux catégories electronics et gadgets (dans cet ordre), là c’est mieux d’utiliser un index BTREE :

SELECT *
FROM products
WHERE categories = ARRAY['electronics', 'gadgets'];

Crée l’index qui va bien :

CREATE INDEX idx_categories_btree
ON products USING btree(categories);

Performance des index

Les index accélèrent les requêtes, mais y’a aussi un revers à la médaille. Par exemple :

• Création de l’index ça prend du temps et des ressources. Si ta table est vraiment grosse, construire l’index peut être un peu long.
• Mise à jour de la table. À chaque fois que tu insères de nouvelles lignes ou que tu modifies des données, les index sont mis à jour aussi. Ça peut ralentir les opérations INSERT et UPDATE.

Mais bon, dans la plupart des cas, le gain sur la rapidité des requêtes vaut largement le coup.

Comment choisir : GIN ou BTREE ?

Voilà un petit tableau pour t’aider à choisir le bon index selon ce que tu veux faire :