Introduction

Les requêtes lentes sont le symptôme le plus visible d'un système backend sous charge. Dans PostgreSQL, la différence entre une requête qui parcourt des millions de lignes et une qui n'en récupère que dix se résume généralement à une seule chose : le bon index. Cette leçon passe en revue les trois types d'index que vous utiliserez le plus souvent en backend — B-tree, composite et partiel — et vous montre comment lire un plan d'EXPLAIN ANALYZE pour savoir lequel vous avez besoin.

Index B-tree : le cheval de bataille par défaut

Un index B-tree est ce que PostgreSQL crée lorsque vous écrivez simplement CREATE INDEX. Il maintient les valeurs de la colonne triées, ce qui rend rapides les recherches par égalité (=) et par plage (>, <, BETWEEN). Utilisez-le dès qu'une colonne apparaît fréquemment dans des clauses WHERE ou dans des conditions de JOIN.

CREATE INDEX idx_users_email ON users (email);

SELECT * FROM users WHERE email = 'alice@example.com';

Sans l'index, PostgreSQL effectue un sequential scan (parcours séquentiel) — il lit chaque ligne. Avec l'index, il saute directement à l'entrée correspondante. Sur une table de dix millions de lignes, cette différence représente l'écart entre 200 ms et 2 ms.

Index composites : l'ordre compte

Lorsqu'une requête filtre ou trie sur plusieurs colonnes, un index composite peut servir les deux à la fois. Le détail crucial est l'ordre des colonnes : l'index ne peut être utilisé efficacement que lorsque les colonnes de tête sont contraintes.

CREATE INDEX idx_orders_user_date ON orders (user_id, created_at);

SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

Ici, l'index sur (user_id, created_at) permet à PostgreSQL de filtrer par user_id tout en ayant déjà les lignes triées par created_at — aucune étape de tri supplémentaire n'est nécessaire. Une erreur courante consiste à créer l'index avec les colonnes dans le mauvais ordre. Un index sur (created_at, user_id) n'aiderait pas cette requête, car user_id n'est pas la colonne de tête.

Index partiels : n'indexez que ce qui compte

Un index partiel ajoute une clause WHERE à l'index lui-même, de sorte qu'il ne stocke que les lignes qui correspondent à une condition. Cela permet de garder l'index petit et rapide lorsque la plupart de vos requêtes ne s'intéressent qu'à un sous-ensemble de la table.

CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at
LIMIT 50;

Si 99 % des commandes sont déjà terminées et que seulement 1 % sont en attente, un index complet sur status serait inutile. L'index partiel pourrait être 1000× plus petit et tout aussi utile pour les requêtes qui comptent vraiment.

Lire EXPLAIN ANALYZE

EXPLAIN ANALYZE exécute la requête et renvoie le plan réel choisi par PostgreSQL, avec les temps et les nombres de lignes effectifs. Les signaux les plus importants à surveiller :

- Sequential Scan sur une grande table → vous avez probablement besoin d'un index. - Lignes supprimées par le filtre nettement supérieur au nombre de lignes estimé par le planner → vos statistiques sont obsolètes ; exécutez ANALYZE table_name. - Nœud Sort au-dessus d'un grand ensemble → envisagez un index qui correspond à l'ORDER BY, ou ajoutez un LIMIT plus tôt.

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

Recherchez un Index Scan utilisant idx_orders_user_date dans la sortie. Si vous voyez Seq Scan à la place, l'index n'est pas utilisé — généralement parce que la table est trop petite pour que le planner s'en soucie, ou parce que vos statistiques sont obsolètes.

Pièges courants

Indexer chaque colonne est pire que n'en indexer aucune : chaque index ralentit les écritures (INSERT, UPDATE, DELETE) car PostgreSQL doit aussi mettre à jour l'index. Une bonne règle : indexez les colonnes qui apparaissent dans WHERE, ORDER BY ou JOIN de requêtes fréquentes qui touchent beaucoup de lignes.

Une autre erreur fréquente consiste à appliquer une fonction sur la colonne indexée dans le WHERE, ce qui empêche l'utilisation de l'index :

-- This WILL use an index on users.email
SELECT * FROM users WHERE email = 'alice@example.com';

-- This WILL NOT use that index
SELECT * FROM users WHERE lower(email) = 'alice@example.com';

Si vous avez besoin d'une recherche insensible à la casse, stockez soit une colonne normalisée, soit créez un index fonctionnel : CREATE INDEX ON users (lower(email)).

Résumé

Une indexation efficace dans PostgreSQL revient à adapter le type d'index et l'ordre des colonnes aux véritables patterns de requêtes. Le B-tree gère la plupart des recherches par égalité et par plage. Les index composites combinent filtrage et tri lorsque la colonne de tête est contrainte. Les index partiels réduisent l'index aux seules lignes qui comptent. EXPLAIN ANALYZE est votre boucle de rétroaction — exécutez-le toujours avant et après l'ajout d'un index pour confirmer que le plan a réellement changé.