Index dans PostgreSQL : B-tree, GIN et quand les utiliser

1. Aperçu

PostgreSQL prend en charge plusieurs types d’index, mais B-tree et GIN sont les deux que tu rencontres le plus souvent dans ton travail quotidien. Choisir le mauvais type d’index rend tes requêtes lentes ou consomme beaucoup d’espace sans apporter d’avantage.

Cet article explique comment fonctionnent B-tree et GIN à un niveau suffisant, comment créer des index correctement, comment lire EXPLAIN pour savoir si l’index est utilisé, et une checklist pour choisir le type d’index adapté. Après l’avoir lu, tu pourras ajouter en confiance des index aux tables Order, Product sans avoir besoin de deviner.

2. Index B-tree : par défaut et le plus courant

2.1. Comment fonctionne B-tree

B-tree (balanced tree) stocke les valeurs dans l’ordre trié, ce qui permet la recherche, la comparaison (<, >, BETWEEN) et le tri (ORDER BY) très rapides — complexité O(log n). C’est le type d’index par défaut quand tu exécutes CREATE INDEX sans spécifier la méthode.

CREATE TABLE customer_order (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    total_amount NUMERIC(12,2)
);

CREATE INDEX idx_order_customer_id ON customer_order (customer_id);

La ligne CREATE INDEX crée un B-tree sur la colonne customer_id. Quand tu requêtes WHERE customer_id = 123, PostgreSQL traverse l’arbre au lieu de balayer toute la table (sequential scan).

2.2. Vérifier si l’index est utilisé

EXPLAIN ANALYZE
SELECT * FROM customer_order WHERE customer_id = 123;

Index Scan using idx_order_customer_id on customer_order
  Index Cond: (customer_id = 123)
  Planning Time: 0.123 ms
  Execution Time: 0.045 ms

Index Scan signifie que le planner choisit d’utiliser l’index. Si tu vois Seq Scan même avec l’index présent, c’est généralement parce que la table est trop petite (le planner estime que le balayage complet est moins cher) ou que les statistiques sont obsolètes — exécute ANALYZE customer_order; pour mettre à jour.

2.3. Index composé et ordre des colonnes

B-tree supporte plusieurs colonnes, mais l’ordre des colonnes décide de l’efficacité.

CREATE INDEX idx_order_customer_status
    ON customer_order (customer_id, status);

-- Utilise l'index en entier
SELECT * FROM customer_order
WHERE customer_id = 123 AND status = 'PAID';

-- N'utilise que la partie customer_id de l'index
SELECT * FROM customer_order WHERE customer_id = 123;

-- N'UTILISE PAS cet index (manque customer_id)
SELECT * FROM customer_order WHERE status = 'PAID';

Principe : un index composite est comme un carnet d’adresses trié par (customer_id, status) — tu peux chercher d’abord par customer_id, mais tu ne peux pas chercher directement par status seul.

Note : Place la colonne avec une selectivité élevée (beaucoup de valeurs différentes) en premier si les colonnes sont utilisées indépendamment dans les requêtes. Si tu requêtes toujours les deux colonnes ensemble, place la colonne la plus sélective en premier pour que l’arbre réduise la plage plus rapidement.

3. Index GIN : pour les données « plusieurs valeurs dans une ligne »

3.1. Quand B-tree est impuissant

B-tree n’est pas efficace avec les types de données où une ligne contient de nombreux « éléments » à rechercher indépendamment : tableaux (array), JSON (jsonb), recherche full-text (tsvector). GIN (Generalized Inverted Index) a été créé pour ce cas — il indexe chaque élément individuellement, pas la valeur entière.

3.2. GIN pour JSONB

CREATE TABLE product (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    attributes JSONB NOT NULL
);

CREATE INDEX idx_product_attributes ON product USING GIN (attributes);

INSERT INTO product (name, attributes) VALUES
('Laptop Dell', '{"brand": "Dell", "ram_gb": 16, "tags": ["laptop", "office"]}'),
('Laptop HP',   '{"brand": "HP", "ram_gb": 8, "tags": ["laptop", "gaming"]}');

SELECT * FROM product WHERE attributes @> '{"brand": "Dell"}';
SELECT * FROM product WHERE attributes -> 'tags' @> '["gaming"]';

USING GIN (attributes) crée un index sur la colonne JSONB entière. L’opérateur @> (« contains ») est celui que GIN optimise le mieux — il vérifie si le JSON de gauche contient la structure de droite, sans avoir besoin de réanalyser le document entier à chaque fois.

Bitmap Heap Scan on product
  Recheck Cond: (attributes @> '{"brand": "Dell"}'::jsonb)
  ->  Bitmap Index Scan on idx_product_attributes
        Index Cond: (attributes @> '{"brand": "Dell"}'::jsonb)

3.3. GIN pour la recherche full-text

ALTER TABLE product ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('simple', name)) STORED;

CREATE INDEX idx_product_search ON product USING GIN (search_vector);

SELECT name FROM product
WHERE search_vector @@ to_tsquery('simple', 'laptop & dell');

La colonne search_vector est une colonne générée qui crée automatiquement un tsvector à partir de name. L’index GIN sur cette colonne aide l’opérateur @@ (match) à s’exécuter rapidement avec de grandes données texte, au lieu de LIKE '%laptop%' qui doit balayer la table entière.

3.4. GIN pour les tableaux

CREATE TABLE article (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(200),
    tag_list TEXT[]
);

CREATE INDEX idx_article_tags ON article USING GIN (tag_list);

SELECT * FROM article WHERE tag_list @> ARRAY['spring-boot'];

Comme JSONB, GIN indexe chaque élément du tableau tag_list, donc la requête « article contenant le tag X » est beaucoup plus rapide que B-tree avec les grandes tables.

4. Comparaison et coûts

Critère	B-tree	GIN
Données adaptées	scalaire : nombres, chaînes, dates	array, jsonb, tsvector
Opérateurs supportés	=, <, >, BETWEEN, ORDER BY	@>, ?, @@, &&
Vitesse d’écriture (INSERT/UPDATE)	rapide	plus lente, spécialement UPDATE JSONB grand
Taille de l’index	plus petite	plus grande (stocke chaque élément)
Vitesse de construction	rapide	plus lente sur les grandes tables

GIN est plus cher en coût d’écriture car chaque INSERT/UPDATE doit mettre à jour plusieurs entrées (une pour chaque élément du JSON/tableau), pas une seule entrée comme B-tree.

-- Vérifier la taille réelle de l'index
SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass))
FROM pg_indexes
WHERE tablename = 'product';

5. Tester que l’index est utilisé (intégration avec Spring Boot)

Quand tu travailles avec Spring Data JPA, tu veux généralement t’assurer que l’index fonctionne correctement avant de déployer. Utilise Testcontainers pour tester réellement sur PostgreSQL.

<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>postgresql</artifactId>
    <version>1.20.1</version>
    <scope>test</scope>
</dependency>

@Testcontainers
@SpringBootTest
class ProductIndexTest {

    @Container
    static PostgreSQLContainer<?> postgres =
            new PostgreSQLContainer<>("postgres:16-alpine");

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    void shouldUseGinIndexForJsonbQuery() {
        jdbcTemplate.execute("""
            INSERT INTO product (name, attributes)
            VALUES ('Laptop Dell', '{"brand": "Dell"}')
        """);

        String plan = jdbcTemplate.queryForList(
                "EXPLAIN SELECT * FROM product WHERE attributes @> '{\"brand\": \"Dell\"}'"
        ).stream()
         .map(row -> row.get("QUERY PLAN").toString())
         .reduce("", (a, b) -> a + b);

        assertThat(plan).containsIgnoringCase("idx_product_attributes");
    }
}

Ce test exécute EXPLAIN (pas besoin d’ANALYZE), fusionne les lignes de résultat en une chaîne, puis vérifie que le plan mentionne le nom de l’index attendu. C’est la façon de confirmer que l’index participe réellement au plan de requête, pas seulement qu’il « existe dans le schéma ».

Note : EXPLAIN n’exécute pas la requête réellement, il interroge simplement le planner. Si tu veux connaître le temps d’exécution réel, utilise EXPLAIN ANALYZE — mais rappelle-toi qu’il exécute la déclaration, évite de l’utiliser avec DELETE/UPDATE sauf si tu la composes dans une transaction rollback.

6. Checklist pour choisir le type d’index

• Requête filtrée par colonne scalaire (nombre, chaîne, date) avec =, <, >, ou besoin de ORDER BY → B-tree.
• Requête filtrée sur plusieurs colonnes à la fois en style « AND » → B-tree composite, colonne à haute sélectivité ou colonne utilisée indépendamment en premier.
• Requête sur colonne JSONB utilisant @>, ?, ?&, ?| → GIN.
• Recherche full-text (opérateur @@ avec tsvector) → GIN.
• Requête sur array utilisant @>, &&, <@ → GIN.
• Table avec beaucoup d’écritures, peu de lectures, données JSONB changeant fréquemment → considère de ne pas créer un index GIN sur la colonne entière, crée seulement sur une expression (expression index) si nécessaire.

-- Expression index : indexe seulement une clé spécifique dans JSONB, plus léger que GIN en colonne entière
CREATE INDEX idx_product_brand ON product ((attributes ->> 'brand'));

Cet expression index est en fait un B-tree sur le résultat de attributes ->> 'brand' (valeur texte), adapté quand tu interroges toujours sur exactement une clé fixe, sans avoir besoin de la flexibilité de GIN.

7. Erreurs courantes

• Créer un index sur une colonne avec une faible cardinalité (par exemple status n’a que 3 valeurs sur une table de millions de lignes) : le planner ignore généralement l’index et choisit Seq Scan parce que c’est moins cher. Un index existant ne signifie pas qu’il sera utilisé.
• Oublier ANALYZE après une insertion de grandes données : le planner s’appuie sur les anciennes statistiques, estime mal le nombre de lignes, choisit un mauvais plan.
• Utiliser GIN pour une colonne rarement modifiée mais écrite très souvent par petits morceaux : le coût cumulatif des mises à jour d’index surpasse l’avantage des requêtes — mesure avec EXPLAIN ANALYZE avant de décider.
• Composite B-tree avec un mauvais ordre de colonnes : placer la colonne moins utilisée en premier rend l’index quasi inutile pour les requêtes réelles.
• Créer trop d’index sur une table : chaque INSERT/UPDATE doit mettre à jour tous les index concernés, ralentissant le write path sans que tous les index ne soient réellement utilisés.

8. Conclusion

B-tree est le choix par défaut, adapté à la plupart des conditions de filtre et de tri sur les données scalaires. GIN est réservé aux données « plusieurs valeurs » comme JSONB, array, full-text — où B-tree ne peut pas le représenter efficacement.

Avant d’ajouter un index, vérifie toujours avec EXPLAIN ANALYZE que le planner l’utilise réellement, et mesure le coût d’écriture si la table a un taux d’INSERT/UPDATE élevé. Un index n’est pas quelque chose qu’on ajoute « pour faire bonne mesure » — chaque index est un compromis entre la vitesse de lecture et la vitesse d’écriture.