Choisir le bon index PostgreSQL

Mis à jour le 5 juillet 2026

PostgreSQL fournit six méthodes d'accès aux index, et CREATE INDEX sans clause USING vous donne toujours un B-tree. Ce choix par défaut est le bon la plupart du temps — ce guide traite de la reconnaissance des cas où il ne l'est pas, et des trois fonctionnalités d'index (partiel, d'expression, couvrant) qui comptent plus que la méthode d'accès dans le travail quotidien.

B-tree : le choix par défaut, à juste titre

Un index B-tree prend en charge les comparaisons d'égalité et de plage (=, <, >, BETWEEN, IN), IS NULL, le préfixe LIKE 'abc%' (avec le bon collationnement ou text_pattern_ops) et — souvent oublié — le tri : un index sur (created_at) peut satisfaire ORDER BY created_at DESC LIMIT 20 sans rien trier.

Pour les B-tree multicolonnes, l'ordre des colonnes fait tout. Un index sur (customer_id, created_at) est excellent pour WHERE customer_id = ? ORDER BY created_at, mais quasiment inutile pour une requête qui filtre uniquement sur created_at. Règle empirique : les colonnes d'égalité d'abord, puis la colonne de plage ou de tri.

Quand le B-tree est le mauvais outil

Index partiels : n'indexer que ce que vous interrogez

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

Si 99 % de vos commandes sont terminées mais que chaque sondage demande celles en attente, un index partiel reste petit, reste chaud en cache et est peu coûteux à maintenir — les lignes terminées ne le touchent jamais. La requête doit inclure la condition WHERE (ou quelque chose dont le planificateur peut prouver qu'elle l'implique) pour que l'index soit pris en compte.

Index d'expression : correspondre à ce que la requête calcule

CREATE INDEX users_email_lower_idx ON users (lower(email));

Une requête qui filtre sur lower(email) = lower($1) ne peut pas utiliser un simple index sur email — l'index stocke les valeurs brutes, pas leur version en minuscules. L'expression dans l'index doit correspondre à l'expression dans la requête. La même technique fonctionne pour date_trunc('day', created_at), l'extraction de champ JSON et toute fonction immuable.

Index couvrants et parcours d'index seul

CREATE INDEX orders_cust_idx
    ON orders (customer_id) INCLUDE (total, created_at);

Si l'index contient toutes les colonnes dont la requête a besoin, PostgreSQL peut ignorer complètement la table — un parcours d'index seul (index-only scan). INCLUDE ajoute des colonnes de charge utile aux feuilles de l'index sans les faire partie de la clé. Deux réserves : l'index grossit, et les parcours d'index seul exigent aussi que la visibility map soit à jour — sur une table fortement modifiée et rarement nettoyée par VACUUM, vous verrez le compteur Heap Fetches grimper et une grande partie du bénéfice disparaître.

Règles pratiques

• Créez les index en production avec CREATE INDEX CONCURRENTLY — il ne bloque pas les écritures (il ne peut pas s'exécuter dans une transaction et prend plus de temps, c'est le prix à payer).
• Chaque index taxe chaque INSERT/UPDATE/DELETE. Vérifiez périodiquement pg_stat_user_indexes.idx_scan et supprimez ceux qui ne sont jamais lus.
• Un index que le planificateur ignore n'a pas forcément des statistiques manquantes — vérifiez que les types correspondent, que les fonctions du prédicat correspondent à l'expression de l'index, et que la fraction filtrée est suffisamment petite pour battre un parcours séquentiel.
• Vérifiez, ne présumez pas : exécutez EXPLAIN (ANALYZE, BUFFERS) avant et après. La preuve est dans le plan.