Indexation des tableaux et opérateurs (`@>`, `<@`, `&&`) pour une recherche rapide

Les tableaux dans PostgreSQL te permettent de stocker plein de valeurs dans une seule cellule de table. C’est super pratique quand tu veux regrouper des données liées, genre une liste de tags pour un article ou les catégories d’un produit. Mais dès qu’on commence à chercher, filtrer ou croiser des tableaux, les perfs peuvent vite s’effondrer. C’est là que l’indexation des tableaux sauve la mise. Les index permettent d’accélérer des opérations comme :

• vérifier si un tableau contient un élément précis,
• chercher les tableaux qui contiennent certains éléments,
• vérifier l’intersection entre tableaux.

Opérateurs pour bosser avec les tableaux

Avant de plonger dans la création d’index, voyons les opérateurs de base pour manipuler les tableaux :

@> (contient) — vérifie si un tableau contient tous les éléments d’un autre tableau.

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Ici, on cherche les cours qui ont le tag "SQL".

<@ (est contenu dans) — vérifie si un tableau est contenu dans un autre.

SELECT *
FROM courses
WHERE ARRAY['PostgreSQL', 'SQL'] <@ tags;

Ici, on cherche les cours dont les tags incluent tous les éléments du tableau ARRAY['PostgreSQL', 'SQL'].

&& (overlap/intersection) — vérifie s’il y a une intersection entre deux tableaux.

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

Cette requête trouve les cours qui ont au moins un des tags "NoSQL" ou "Big Data".

Comment l’indexation aide ?

Imagine que tu as une table courses avec des millions de lignes, et tu fais une requête avec un des opérateurs ci-dessus. Sans index, PostgreSQL doit checker chaque ligne une par une — une opération qui peut durer une éternité (surtout si t’as la patience d’un dev qui attend la fin d’une compilation).

Avec des index, tu évites ce genre de galère. PostgreSQL propose deux types d’index adaptés aux tableaux :

1. GIN (Generalized Inverted Index) — le meilleur choix pour les tableaux.
2. BTREE — utilisé pour comparer des tableaux entiers.

Exemple : Créons un index pour les tableaux

On va créer une petite table avec des tableaux pour tester tout ça en pratique.

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    tags TEXT[] NOT NULL
);

Ajoutons quelques lignes :

INSERT INTO courses (name, tags)
VALUES
    ('Bases de SQL', ARRAY['SQL', 'PostgreSQL', 'Bases de données']),
    ('Travailler avec Big Data', ARRAY['Hadoop', 'Big Data', 'NoSQL']),
    ('Développement en Python', ARRAY['Python', 'Web', 'Données']),
    ('Cours sur PostgreSQL', ARRAY['PostgreSQL', 'Advanced', 'SQL']);

Voilà à quoi ressemble la table :

Sans index : recherche lente

Maintenant, imagine qu’on veut trouver tous les cours qui ont le tag SQL.

EXPLAIN ANALYZE
SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

La requête va marcher, mais si t’as beaucoup de données, ça va être super lent. PostgreSQL va faire ce qu’on appelle un scan séquentiel (Sequential Scan), c’est-à-dire checker chaque ligne de la table.

Exemple de résultat :

Création d’un index GIN

Pour accélérer la recherche, créons un index de type GIN :

CREATE INDEX idx_courses_tags
ON courses USING GIN (tags);

On relance la même requête :

EXPLAIN ANALYZE
SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Maintenant PostgreSQL va utiliser l’index GIN qu’on vient de créer, et le temps d’exécution va chuter.

Avant, c’était scan séquentiel (Seq Scan), maintenant dans le plan d’exécution tu verras Bitmap Index Scan :

Comment les opérateurs bossent avec les index ?

Exemple 1 : Opérateur @>

Requête :

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

L’index GIN est parfait pour cet opérateur. Postgres checke vite quelles lignes contiennent l’élément demandé et renvoie le résultat.

Résultat :

Exemple 2 : Opérateur &&

Requête :

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

Cet opérateur vérifie l’intersection des tableaux : il renvoie les lignes où le tableau tags croise au moins un élément du tableau passé.

L’index GIN fait encore sa magie — la recherche est rapide, même avec beaucoup de données.

Résultat :

Indexation et optimisation

Quand tu bosses avec des tableaux, garde ces conseils en tête :

1. Utilise des index GIN pour chercher dans les tableaux. C’est bien plus rapide qu’un scan séquentiel.
2. Ajoute des index seulement sur les colonnes vraiment utilisées dans les requêtes. Les index prennent de la place et ralentissent les insertions, donc évite d’indexer tout et n’importe quoi.
3. Profile tes requêtes avec EXPLAIN et EXPLAIN ANALYZE pour vérifier que ton index est bien utilisé.

Exemples : création d’index pour les tableaux

Voyons comment créer des index pour différents types d’opérations sur les tableaux et pourquoi c’est utile en pratique.

Index pour l’opérateur @>

Imaginons qu’on a déjà cette table courses :

Pour accélérer les requêtes avec l’opérateur @> (le tableau contient un élément), créons un index GIN :

CREATE INDEX idx_courses_tags_gin
ON courses USING GIN (tags);

On lance la requête :

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Résultat :

Index pour les opérateurs @>, <@, et &&

La table de départ est la même que dans l’exemple précédent.

Comme les opérateurs @>, <@ et && marchent tous super bien avec les index GIN, tu peux créer un index universel qui accélérera toutes ces requêtes :

CREATE INDEX idx_tags
ON courses USING GIN (tags);

Exemples de requêtes et leurs résultats :

• @> — vérifier si le tableau contient les éléments donnés :

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

---

• <@ — vérifier si le tableau est contenu dans un autre :

SELECT *
FROM courses
WHERE tags <@ ARRAY['SQL', 'PostgreSQL', 'Advanced', 'Big Data', 'NoSQL', 'Python'];

---

• && — vérifier l’intersection des tableaux :

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

Essayons un truc plus costaud

On va faire une requête qui renvoie les cours où il y a au moins une intersection avec la liste ['Python', 'SQL', 'NoSQL'] :

SELECT *
FROM courses
WHERE tags && ARRAY['Python', 'SQL', 'NoSQL'];

Sortie :

Avec un index GIN, cette requête s’exécute instantanément, même si la table a des millions de lignes.

Erreurs courantes avec les tableaux

Index non utilisé : si dans le résultat de EXPLAIN tu vois Seq Scan, vérifie que l’index existe et que l’opérateur que tu utilises supporte bien l’indexation.

Utilisation rare du tableau : si la colonne avec les tableaux est rarement utilisée dans les requêtes ou souvent modifiée, l’index peut prendre de la place pour rien et ne pas vraiment servir.

Index en trop : les index prennent de la place sur le disque et ralentissent les écritures, donc crée seulement ceux qui sont vraiment utiles et utilisés dans les requêtes.

Maintenant t’as tout ce qu’il faut pour bosser efficacement avec les tableaux dans PostgreSQL — accélère tes requêtes avec les opérateurs @>, <@, && et les index GIN. N’hésite pas à tester tout ça sur tes propres données !