Index B-tree : comprendre et choisir

Prérequis : cours précédents ; index et transactions.

Pourquoi

Sans index, chaque requête lit toute la table (seq scan) : 10 ms à 10k lignes, 10 s à 10M. Avec les bons index : millisecondes. C'est le levier perf n°1, devant tout le reste.

Concepts

• B-tree : l'index par défaut, trié : parfait pour =, <, >, BETWEEN, ORDER BY, préfixes LIKE 'abc%'. Inutile pour %abc (full scan) et les recherches plein-texte (voir GIN).
• Composite : (customer_id, created_at) sert WHERE customer_id=? AND created_at>? ET WHERE customer_id=? seul (préfixe gauche !), mais PAS WHERE created_at=? seul. L'ordre des colonnes = l'ordre des questions.
• Partiel : WHERE active dans l'index — petit, rapide, pour les requêtes filtrées récurrentes (WHERE status='pending').
• Coût : chaque index ralentit les écritures + prend du disque. Indexer ce qu'on lit souvent, pas « au cas où ».

Exemple

CREATE INDEX CONCURRENTLY idx_orders_customer_date
  ON orders (customer_id, created_at DESC);
-- CONCURRENTLY : sans verrou en écriture (prod !), mais plus lent à construire

Cas réel

Recherche par email sans index sur 3M d'users : 6 s par login. Un B-tree : 2 ms. Le login « lent depuis des mois » réparé en une migration.

Pièges

• Index sur colonne à faible cardinalité seule (is_active true/false) : quasi inutile seul, utile en composite/partiel.
• Fonctions sur colonne indexée (WHERE lower(email)=) : tue l'index — indexer l'expression ((lower(email))) à la place.
• Doublons d'index (un junior ajoute ce qui existe) : pg_stat_user_indexes + audit régulier des inutilisés (idx_scan = 0).

À retenir

B-tree par défaut, ordre composite = ordre des questions, partiel pour les filtres, CONCURRENTLY en prod.