Pourquoi Postgres n’utilise pas votre index : sept causes, avec des sorties EXPLAIN reproductibles

Vous avez exécuté CREATE INDEX, lancé la requête, et EXPLAIN affiche toujours Seq Scan. Avant d’accuser le planificateur, sachez que dans la plupart des cas il a raison sur ce que vous avez demandé et tort seulement sur ce que vous vouliez dire. Cet article présente les sept raisons les plus fréquentes pour lesquelles PostgreSQL ignore un index : une fonction appliquée à la colonne, une incohérence de types, un joker en début de motif, un filtre qui correspond à la majeure partie de la table, des statistiques périmées, un index multicolonne dans le mauvais ordre, et une table tout simplement trop petite. Chaque cas est accompagné d’une reproduction sur PostgreSQL 17.11, du plan exact obtenu et du correctif. Collez la configuration dans psql et, avec les paramètres par défaut, vous obtiendrez les mêmes plans.

Placez EXPLAIN devant la requête. Un nœud de plan nommé Index Scan, Index Only Scan ou Bitmap Index Scan indique qu’un index est utilisé. Seq Scan (ou Parallel Seq Scan) signifie que le planificateur lit toute la table. EXPLAIN ne fait que planifier la requête ; EXPLAIN ANALYZE l’exécute aussi et affiche le nombre réel de lignes, dont vous aurez besoin pour le cas des statistiques périmées plus bas.

Deux habitudes rendent les plans plus faciles à lire et à comparer :

• EXPLAIN (COSTS OFF) supprime les coûts, qui varient avec la taille de la table et les paramètres.
• EXPLAIN (ANALYZE, BUFFERS) indique combien de pages une requête a réellement touchées, et pas seulement combien le planificateur en attendait.

Les sept cas utilisent une même table de 200 000 utilisateurs. Les données sont générées, il n’y a donc rien à télécharger :

SET TimeZone = 'UTC';
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE users (
  id         bigint PRIMARY KEY,
  email      text NOT NULL,
  status     text NOT NULL,
  org_id     int  NOT NULL,
  created_at timestamptz NOT NULL
);

INSERT INTO users
SELECT g,
       'User' || g || '@Example.com',
       CASE WHEN g % 100 = 0 THEN 'banned' ELSE 'active' END,
       (g % 500) + 1,
       timestamptz '2026-01-01' + (g || ' seconds')::interval
FROM generate_series(1, 200000) g;

CREATE INDEX users_email_idx ON users (email);
ANALYZE users;

Comme référence, une égalité exacte sur la colonne indexée utilise l’index :

EXPLAIN (COSTS OFF) SELECT * FROM users WHERE email = 'User42@Example.com';

 Index Scan using users_email_idx on users
   Index Cond: (email = 'User42@Example.com'::text)

Tout ce qui suit consiste à modifier légèrement cette requête et à perdre l’index.

Un index sur email stocke les valeurs de email. Une condition sur lower(email) porte sur d’autres valeurs, que l’index ne peut donc pas retrouver :

EXPLAIN (COSTS OFF) SELECT * FROM users WHERE lower(email) = 'user42@example.com';

 Gather
   Workers Planned: 1
   ->  Parallel Seq Scan on users
         Filter: (lower(email) = 'user42@example.com'::text)

Le plan est parallèle, mais il reste un sequential scan. Le correctif est un index d’expression, c’est-à-dire un index sur la même expression que celle de la requête :

CREATE INDEX users_lower_email_idx ON users (lower(email));
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE lower(email) = 'user42@example.com';

 Bitmap Heap Scan on users
   Recheck Cond: (lower(email) = 'user42@example.com'::text)
   ->  Bitmap Index Scan on users_lower_email_idx
         Index Cond: (lower(email) = 'user42@example.com'::text)

L’expression de la requête doit correspondre à celle de l’index. lower(email) et lower(trim(email)) sont deux expressions différentes. La même règle s’applique à coalesce(col, 0), à l’arithmétique comme price * 100 et aux fonctions de date. Ces dernières ont un piège supplémentaire : sur une colonne timestamptz, date_trunc('day', created_at) et created_at::date dépendent du fuseau horaire de la session, et Postgres refuse donc de les indexer directement (functions in index expression must be marked IMMUTABLE). Indexez plutôt une expression qui fixe le fuseau, comme ((created_at AT TIME ZONE 'UTC')::date), et utilisez la même expression dans la requête.

Quand c’est possible, réécrivez plutôt la condition. created_at >= '2026-01-02' AND created_at < '2026-01-03' peut utiliser un simple index sur created_at, alors que created_at::date = '2026-01-02' ne le peut pas.

Quand les deux côtés d’une comparaison ont des types différents, Postgres doit convertir l’un d’eux. S’il convertit la colonne, un index ordinaire sur cette colonne ne s’applique plus. Ici, id est un bigint et le littéral 42.0 est un numeric :

EXPLAIN (COSTS OFF) SELECT * FROM users WHERE id = 42;
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE id = 42.0;
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE id::text = '42';

 Index Scan using users_pkey on users
   Index Cond: (id = 42)

 Gather
   Workers Planned: 1
   ->  Parallel Seq Scan on users
         Filter: ((id)::numeric = 42.0)

 Gather
   Workers Planned: 1
   ->  Parallel Seq Scan on users
         Filter: ((id)::text = '42'::text)

Regardez la ligne Filter : (id)::numeric montre que c’est la colonne qui est convertie. Ce cast est l’indice révélateur, et c’est lui qu’il faut chercher chaque fois qu’un plan affiche Seq Scan sur une colonne indexée.

Le correctif consiste à faire correspondre le littéral ou le paramètre à la colonne. Dans le code applicatif, la source habituelle est un pilote qui envoie un nombre sous forme de numeric, ou une valeur qui arrive sous forme de chaîne. Castez le paramètre dans la requête (WHERE id = $1::bigint) ou corrigez le type à la source. Si c’est la colonne elle-même qui a le mauvais type, par exemple un identifiant stocké en text et comparé à des nombres, le vrai correctif est de changer le type de la colonne.

Un index B-tree est trié : il peut donc retrouver rapidement tout ce qui commence par User4. Il ne peut pas retrouver tout ce qui se termine par 1234@Example.com, car les valeurs ne sont pas triées selon leur fin :

EXPLAIN (COSTS OFF) SELECT * FROM users WHERE email LIKE '%1234@Example.com';

 Seq Scan on users
   Filter: (email ~~ '%1234@Example.com'::text)

L’extension pg_trgm indexe des fragments de trois caractères, ce qui permet à Postgres d’utiliser un index pour les motifs avec un joker en début de chaîne, pour ILIKE et pour les recherches de similarité avec %. pg_trgm fait partie des extensions disponibles sur Relyt One. L’index est un index GIN avec la classe d’opérateurs gin_trgm_ops :

CREATE INDEX users_email_trgm_idx ON users USING gin (email gin_trgm_ops);
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE email LIKE '%1234@Example.com';

 Bitmap Heap Scan on users
   Recheck Cond: (email ~~ '%1234@Example.com'::text)
   ->  Bitmap Index Scan on users_email_trgm_idx
         Index Cond: (email ~~ '%1234@Example.com'::text)

Les index trigrammes sont plus volumineux que les index B-tree et plus lents à mettre à jour, et ils sont surtout utiles quand la chaîne recherchée est assez longue pour contenir au moins un trigramme complet. Un motif de moins de trois caractères leur laisse peu de prise. Si votre besoin réel est de rechercher des mots dans un texte, utilisez la recherche plein texte (tsvector) plutôt que LIKE.

Un index est rentable quand il permet à la base de passer outre la majeure partie de la table. Si une condition correspond à 99 % des lignes, lire toute la table dans l’ordre coûte moins cher que de passer par l’index 198 000 fois, et le planificateur le sait :

CREATE INDEX users_status_idx ON users (status);
ANALYZE users;

EXPLAIN (COSTS OFF) SELECT * FROM users WHERE status = 'active';
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE status = 'banned';

 Seq Scan on users
   Filter: (status = 'active'::text)

 Index Scan using users_status_idx on users
   Index Cond: (status = 'banned'::text)

C’est l’index qui fonctionne comme prévu : le même index est utilisé pour 1 % des lignes et ignoré pour les 99 %. Il n’y a rien à corriger côté planificateur. Si la requête sur la valeur fréquente compte, la question est de savoir si elle doit vraiment renvoyer autant de lignes (ajoutez un LIMIT, un filtre supplémentaire ou une agrégation).

C’est sur la valeur rare que vous pouvez améliorer les choses. Un index partiel ne couvre que les lignes que vous recherchez : il est donc petit et peu coûteux à maintenir :

DROP INDEX users_status_idx;
CREATE INDEX users_banned_idx ON users (id) WHERE status = 'banned';
EXPLAIN (COSTS OFF) SELECT id FROM users WHERE status = 'banned';

 Index Only Scan using users_banned_idx on users

La clause WHERE de la requête doit impliquer celle de l’index. Une requête qui demande status = 'banned' peut utiliser cet index. Une requête sur un autre statut ne le peut pas.

Le planificateur n’examine pas vos données quand il planifie une requête. Il s’appuie sur les statistiques collectées par ANALYZE et fait ses estimations à partir d’elles. Quand la table change beaucoup après le dernier ANALYZE, les estimations ne correspondent plus à la réalité et le plan peut être faux dans les deux sens, y compris en choisissant un index alors que l’index est une mauvaise idée.

L’autovacuum rafraîchit normalement les statistiques lorsqu’environ 10 % d’une table a changé (autovacuum_analyze_scale_factor = 0.1, plus une base de 50 lignes). Pour montrer l’effet de façon déterministe, nous le désactivons pour une table, l’analysons alors qu’elle contient cinq lignes, puis y chargeons 300 000 lignes en masse :

CREATE TABLE events (id bigint, kind text, created_at timestamptz);
ALTER TABLE events SET (autovacuum_enabled = false);
CREATE INDEX events_kind_idx ON events (kind);

INSERT INTO events SELECT g, 'click', now() FROM generate_series(1, 5) g;
ANALYZE events;
INSERT INTO events SELECT g, 'view', now() FROM generate_series(1, 300000) g;

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM events WHERE kind = 'view';

 Aggregate  (cost=4.31..4.32 rows=1 width=8) (actual rows=1 loops=1)
   ->  Index Only Scan using events_kind_idx on events  (cost=0.29..4.30 rows=1 width=0) (actual rows=300000 loops=1)
         Index Cond: (kind = 'view'::text)
         Heap Fetches: 300000

Le planificateur attendait une ligne (rows=1) et en a obtenu 300 000 (actual rows=300000). Il a choisi un index pour une recherche qui lit toute la table, et Heap Fetches: 300000 signifie que chaque ligne a aussi nécessité une visite de la table. Après ANALYZE, la même requête change de plan :

ANALYZE events;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM events WHERE kind = 'view';

 Finalize Aggregate  (cost=5558.22..5558.23 rows=1 width=8) (actual rows=1 loops=1)
   ->  Gather  (cost=5558.11..5558.22 rows=1 width=8) (actual rows=2 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         ->  Partial Aggregate  (cost=4558.10..4558.11 rows=1 width=8) (actual rows=1 loops=2)
               ->  Parallel Seq Scan on events  (cost=0.00..4116.92 rows=176474 width=0) (actual rows=150000 loops=2)
                     Filter: (kind = 'view'::text)
                     Rows Removed by Filter: 2

Les coûts exacts varient un peu d’une exécution à l’autre : ne les comparez donc pas chiffre à chiffre. La leçon porte sur la façon de lire un plan. Comparez rows= et actual rows= sur chaque nœud. Quand ils diffèrent de plusieurs ordres de grandeur, le problème vient de l’estimation, pas de l’index. Exécutez ANALYZE après un chargement en masse ou un gros backfill, et consultez last_autoanalyze dans pg_stat_user_tables si vous soupçonnez l’autovacuum de prendre du retard. Pour une colonne dont la distribution est asymétrique, augmenter la cible de statistiques (ALTER TABLE … ALTER COLUMN … SET STATISTICS 500) donne au planificateur une image plus fine qu’avec la valeur par défaut de 100.

Un index B-tree multicolonne est trié selon sa première colonne, puis selon la deuxième pour chaque valeur de la première, et ainsi de suite. Une requête qui filtre sur la colonne de tête peut bien l’utiliser. Une requête qui ignore la colonne de tête ne le peut pas :

CREATE INDEX users_org_created_idx ON users (org_id, created_at);
ANALYZE users;

-- colonne de tête contrainte : l'index est utilisé
EXPLAIN (COSTS OFF)
SELECT * FROM users WHERE org_id = 7 AND created_at >= '2026-01-02';

-- colonne de tête absente : il ne l'est pas
EXPLAIN (COSTS OFF)
SELECT * FROM users
WHERE created_at >= '2026-01-02 00:00:00' AND created_at < '2026-01-02 00:05:00';

 Bitmap Heap Scan on users
   Recheck Cond: ((org_id = 7) AND (created_at >= '2026-01-02 00:00:00+00'::timestamp with time zone))
   ->  Bitmap Index Scan on users_org_created_idx

 Gather
   Workers Planned: 1
   ->  Parallel Seq Scan on users
         Filter: ((created_at >= '2026-01-02 00:00:00+00'::timestamp with time zone) AND (created_at < ...

Si vous devez filtrer sur created_at seul, il vous faut un index qui commence par cette colonne. Placer en premier la colonne qui a des conditions d’égalité, et en dernier la colonne de plage, est la règle habituelle pour les index qui servent les deux types de requêtes :

CREATE INDEX users_created_org_idx ON users (created_at, org_id);
EXPLAIN (COSTS OFF)
SELECT * FROM users
WHERE created_at >= '2026-01-02 00:00:00' AND created_at < '2026-01-02 00:05:00';

 Index Scan using users_created_org_idx on users
   Index Cond: ((created_at >= '2026-01-02 00:00:00+00'::timestamp with time zone) AND (created_at < ...

Ce cas dépend de votre version. PostgreSQL 18 a ajouté les skip scans pour les index B-tree, qui permettent dans certains cas à une requête qui omet la colonne de tête d’utiliser l’index. Nous avons exécuté la même table et le même index (org_id, created_at) sur PostgreSQL 18.6, et la requête de plage sur created_at seul y a utilisé l’index (org_id, created_at). Sur PostgreSQL 17, la version qu’exécute Relyt One, ce n’est pas le cas. Un skip scan ne vaut le coup que lorsque le planificateur s’attend à sauter peu de valeurs distinctes de la colonne de tête ou que la condition suivante est sélective, comme ici ; un index conçu pour la requête reste donc le choix le plus fiable.

Chaque index a aussi un coût à chaque écriture. Avant d’en ajouter un, vérifiez qu’un index existant ne couvre pas déjà la requête.

Une table de vingt lignes tient dans une seule page. Lire cette page coûte moins cher que lire une page d’index puis une page de table, et le planificateur parcourt donc la table :

CREATE TABLE plans (id int PRIMARY KEY, name text);
INSERT INTO plans SELECT g, 'plan' || g FROM generate_series(1, 20) g;
ANALYZE plans;

EXPLAIN (COSTS OFF) SELECT * FROM plans WHERE id = 7;

 Seq Scan on plans
   Filter: (id = 7)

C’est le comportement correct, et c’est un piège quand vous testez sur une petite base de développement en attendant le plan que vous obtiendrez en production, ou l’inverse. Testez vos décisions d’indexation sur des données qui ressemblent à la production, en taille comme en distribution.

Il existe aussi un interrupteur de diagnostic pour les moments où vous ne savez pas si l’index est possible ou simplement non choisi. Désactivez les sequential scans le temps d’une transaction et regardez le plan :

BEGIN;
SET LOCAL enable_seqscan = off;
EXPLAIN (COSTS OFF) SELECT * FROM plans WHERE id = 7;
ROLLBACK;

 Index Scan using plans_pkey on plans
   Index Cond: (id = 7)

Si l’index apparaît, il était utilisable et le planificateur a jugé le parcours de table moins coûteux : examinez alors les statistiques, la sélectivité et la taille de la table. S’il n’apparaît toujours pas, l’index ne peut pas servir la requête telle qu’elle est écrite ; reprenez alors les cas 1, 2, 3 et 6. N’utilisez cet interrupteur que pour le diagnostic. Ne le laissez pas activé et ne le mettez pas dans le code applicatif.

Le problème inverse, ce sont les index que rien n’utilise. Ils ralentissent chaque insertion et chaque mise à jour, et ils occupent de l’espace. pg_stat_user_indexes compte les parcours d’index depuis la dernière réinitialisation des statistiques :

SELECT count(*) FROM users WHERE email = 'User42@Example.com';
SELECT count(*) FROM users WHERE email = 'User43@Example.com';

SELECT pg_sleep(1.5);  -- les statistiques sont rapportées avec un court délai

SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'users'
ORDER BY idx_scan DESC, indexrelname;

Après avoir exécuté deux fois la requête d’égalité exacte sur email dans la table de test, et attendu un instant que les statistiques soient rapportées, le résultat était :

     indexrelname      | idx_scan
-----------------------+----------
 users_email_idx       |        2
 users_banned_idx      |        0
 users_created_org_idx |        0
 users_email_trgm_idx  |        0
 users_lower_email_idx |        0
 users_org_created_idx |        0
 users_pkey            |        0

Deux précautions. EXPLAIN seul ne compte pas comme un parcours : seules les exécutions réelles apparaissent. Et un index sans aucun parcours est un candidat à la suppression, pas une preuve : il peut exister pour faire respecter une contrainte d’unicité, ou servir un rapport mensuel qui n’a pas tourné depuis la réinitialisation des statistiques.

1. Exécutez EXPLAIN (ANALYZE, BUFFERS) et trouvez le nœud qui domine.
2. Dans sa ligne Filter, cherchez une fonction ou un cast appliqué à la colonne (cas 1 et 2).
3. Si le motif commence par %, envisagez un index trigramme (cas 3).
4. Comparez rows= et actual rows=. Un grand écart appelle un ANALYZE (cas 5).
5. Vérifiez combien de lignes la condition sélectionne. Si c’est la majeure partie de la table, le parcours est correct (cas 4).
6. Pour un index multicolonne, vérifiez si la requête contraint la colonne de tête (cas 6).
7. Testez sur des données réalistes, et utilisez SET LOCAL enable_seqscan = off pour distinguer « inutilisable » de « non choisi » (cas 7).

Sur une grande table, la construction d’un index est l’étape coûteuse : un CREATE INDEX simple bloque les écritures pendant son exécution, et CREATE INDEX CONCURRENTLY l’évite au prix d’une construction plus lente. Sur Relyt One, chaque branche dispose de son propre calcul et d’un stockage en copie sur écriture : vous pouvez donc créer une branche de votre base de données, y construire l’index, exécuter EXPLAIN (ANALYZE) sur vos données réelles, et jeter la branche si le plan ne s’améliore pas. La branche principale ne construit jamais l’index et ne voit jamais l’expérience. Comparez les plans et les estimations de lignes entre la branche et la production, pas les durées : le calcul de la branche peut être dimensionné différemment et démarre avec un cache froid.

Commencer gratuitement