05 - Les index : comprendre, créer et optimiser

Ce que tu vas apprendre

• Ce qu'est un index et comment il fonctionne
• B-tree, GIN, GiST, BRIN : quel type pour quel usage
• Index partiel, multi-colonnes, sur expression
• Lire un EXPLAIN ANALYZE
• Quand NE PAS indexer

Prerequisites

Avoir lu les articles précédents, surtout l'article 01 sur WHERE et ORDER BY.

---

C'est quoi un index

Imagine un livre de 500 pages sans index a la fin. Pour trouver le mot "transaction", tu dois lire chaque page. C'est un Seq Scan (parcours sequentiel).

Maintenant imagine un index alphabetique a la fin du livre : "transaction -> pages 42, 187, 301". Tu vas directement aux bonnes pages. C'est un Index Scan.

En base de donnees, c'est pareil. Un index est une structure de donnees triee qui permet a PostgreSQL de trouver les lignes sans parcourir toute la table.

B-tree : l'index par défaut

Quand tu ecris CREATE INDEX, tu créés un B-tree. C'est un arbre balance qui permet les recherches en O(log n) :

sqlCREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL,
  name TEXT NOT NULL,
  status TEXT DEFAULT 'active',
  created_at TIMESTAMP DEFAULT NOW()
);

-- Cree un index B-tree sur email
CREATE INDEX idx_users_email ON users (email);

Le B-tree est bon pour : =, <, >, <=, >=, BETWEEN, IN, IS NULL, ORDER BY. C'est l'index polyvalent par excellence.

PRIMARY KEY et UNIQUE creent automatiquement un index. Tu n'as pas besoin d'en ajouter un manuellement.

Index multi-colonnes

L'ordre des colonnes est determinant :

sqlCREATE INDEX idx_users_status_created ON users (status, created_at);

Cet index est utilise pour :

• WHERE status = 'active' (première colonne, oui)
• WHERE status = 'active' AND created_at > '2025-01-01' (les deux colonnes, oui)
• WHERE created_at > '2025-01-01' (deuxieme colonne seule, non !)

C'est le principe du "leftmost prefix". L'index est comme un annuaire trie par nom puis prenom. Tu peux chercher par nom seul. Par nom + prenom. Mais pas par prenom seul (il faudrait scanner tout l'annuaire). Le site Use The Index, Luke explique ca tres bien avec des schemas.

Index partiel

Un index qui ne couvre qu'un sous-ensemble des lignes :

sqlCREATE INDEX idx_active_users ON users (email) WHERE status = 'active';

Si 90% de tes utilisateurs sont actifs et que tu ne requêtes quasiment jamais les inactifs, cet index est plus petit et plus rapide qu'un index complet. C'est aussi utile pour les soft deletes :

sqlCREATE INDEX idx_not_deleted ON orders (created_at) WHERE deleted_at IS NULL;

Index sur expression

Si tu cherches toujours par email en minuscule :

sql-- Cette requete ne peut PAS utiliser un index standard sur email
SELECT * FROM users WHERE LOWER(email) = 'alice@mail.com';

-- Cree un index sur l'expression
CREATE INDEX idx_lower_email ON users (LOWER(email));

-- Maintenant cette requete utilise l'index
SELECT * FROM users WHERE LOWER(email) = 'alice@mail.com';

L'expression dans le WHERE doit correspondre exactement a celle de l'index. LOWER(email) dans l'index, LOWER(email) dans la requête.

UNIQUE index

sqlCREATE UNIQUE INDEX idx_unique_email ON users (email);
-- Ou en partiel :
CREATE UNIQUE INDEX idx_unique_active_email ON users (email) WHERE status = 'active';

L'index unique partiel est un pattern genial. Tu veux qu'il n'y ait qu'un seul email actif par utilisateur, mais tu autorises les doublons chez les comptes supprimes.

GIN : pour JSONB, arrays et full-text

sql-- Index GIN sur une colonne JSONB
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- Permet les requetes avec @> (contient)
SELECT * FROM events WHERE payload @> '{"type": "order_created"}';

-- Index GIN pour le full-text search
CREATE INDEX idx_fts ON articles USING GIN (to_tsvector('french', content));

GIN (Generalized Inverted Index) indexé chaque élément d'une valeur composite. Pour un JSONB, il indexé chaque paire clé/valeur. Pour un array, chaque élément. C'est ce qui rend les requêtes JSONB performantes (on en parle dans l'article 10).

GiST : pour la geometrie et les ranges

sql-- Index GiST sur des ranges de dates
CREATE TABLE reservations (
  id SERIAL PRIMARY KEY,
  room TEXT NOT NULL,
  period DATERANGE NOT NULL,
  EXCLUDE USING GIST (room WITH =, period WITH &&) -- pas de chevauchement !
);

GiST (Generalized Search Tree) gere les types "non lineaires" : geometrie (PostGIS), ranges de dates, intervalles. La contrainte EXCLUDE ci-dessus empeche deux reservations de la meme salle de se chevaucher. C'est PostgreSQL qui gere ca tout seul, pas ton code applicatif.

BRIN : pour les grosses tables ordonnees

sql-- Table de logs avec des millions de lignes, inserees chronologiquement
CREATE INDEX idx_logs_created ON logs USING BRIN (created_at);

BRIN (Block Range INdex) est minuscule en taille. Il stocke le min/max de chaque bloc de pages. Si tes donnees sont inserees dans l'ordre (logs, time-series, events), BRIN est incroyablement efficace : il fait quelques Ko au lieu de centaines de Mo pour un B-tree. Le compromis : il est moins precis, donc PostgreSQL lit parfois quelques blocs de trop.

Lire un EXPLAIN ANALYZE

C'est l'outil numero un pour comprendre ce que fait PostgreSQL avec ta requête :

sqlEXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@mail.com';

Index Scan using idx_users_email on users  (cost=0.29..8.30 rows=1 width=72)
  (actual time=0.025..0.026 rows=1 loops=1)
  Index Cond: (email = 'alice@mail.com'::text)
Planning Time: 0.085 ms
Execution Time: 0.042 ms

Les infos a regarder :

• Index Scan vs Seq Scan : Index Scan = index utilise, Seq Scan = parcours complet de la table
• cost : estimation du planificateur (unités arbitraires)
• actual time : temps réel en millisecondes
• rows : nombre de lignes estimees vs reelles. Si l'ecart est énorme (estime 1, réel 50000), les statistiques sont perimees, lance un ANALYZE users
• Bitmap Index Scan : un compromis entre Seq Scan et Index Scan, utilise quand beaucoup de lignes matchent

Un plan plus complexe :

sqlEXPLAIN ANALYZE
SELECT c.name, COUNT(o.id)
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

HashAggregate  (cost=... rows=4 width=...)
  (actual time=0.045..0.047 rows=2 loops=1)
  Group Key: c.name
  -> Hash Join  (cost=... rows=5 width=...)
    (actual time=0.030..0.035 rows=4 loops=1)
    Hash Cond: (o.customer_id = c.id)
    -> Seq Scan on orders o  (cost=... rows=5 width=...)
    -> Hash  (cost=... rows=4 width=...)
      -> Seq Scan on customers c  (cost=... rows=4 width=...)

Ici, PostgreSQL fait un Seq Scan sur les deux tables (elles sont petites), un Hash Join, puis un HashAggregate pour le GROUP BY. Sur des tables plus grosses avec des index, tu verrais des Index Scan et des Nested Loop Join.

Quand NE PAS indexer

Les index ne sont pas gratuits. Chaque INSERT, UPDATE ou DELETE doit aussi mettre à jour les index. Voici quand éviter d'indexer :

• Tables de moins de 1000 lignes (un Seq Scan est plus rapide que l'overhead de l'index)
• Colonnes rarement utilisees dans les WHERE ou ORDER BY
• Colonnes avec peu de valeurs distinctes (un boolean is_active avec 50% true/50% false, l'index ne filtre rien)
• Tables avec beaucoup d'ecritures et peu de lectures (les index ralentissent les ecritures)

Pour voir quels index sont réellement utilises :

sqlSELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

Si idx_scan = 0 depuis des semaines, l'index ne sert a rien. Supprime-le, ca libéré de l'espace et accéléré les ecritures.

L'index bloat (fragmentation) est aussi un problème réel en production. REINDEX CONCURRENTLY permet de reconstruire un index sans bloquer les requêtes. On couvre ca dans l'article 13 sur la performance.

Sur paltemps.fr, j'ai vu des projets avec 30 index sur une table de 5000 lignes. C'est du gaspillage pur.

---

Résumé

• B-tree : l'index par défaut, bon pour =, <, >, ORDER BY
• GIN : pour JSONB, arrays, full-text search
• GiST : pour geometrie, ranges, contraintes d'exclusion
• BRIN : pour les grosses tables inserees dans l'ordre chronologique
• Les index multi-colonnes suivent le "leftmost prefix"
• Les index partiels economisent de l'espace et sont plus rapides
• EXPLAIN ANALYZE est ton outil numero un pour diagnostiquer les performances
• Trop d'index ralentissent les ecritures

---

Article précédent : 04 - Sous-requêtes et CTEs Article suivant : 06 - Transactions et ACID

Sources

• PostgreSQL Documentation - Indexes
• Use The Index, Luke par Markus Winand
• pganalyze - Efficient Use of PostgreSQL Indexes
• Citus Data - Index Types in PostgreSQL