CREATE INDEX name ON table (column); construit un index qui permet à PostgreSQL de trouver les lignes correspondantes sans lire toute la table. Le type par défaut est B-tree, qui sert =, <, >, BETWEEN, IN, IS NULL et ORDER BY. Choisissez un autre type avec USING : gin, gist, spgist, brin ou hash.
La première ligne du plan indique Index Scan using users_email_idx on users : PostgreSQL cherche la valeur dans l'index et ne lit que la ligne correspondante. Retirez la ligne CREATE INDEX et relancez pour voir Seq Scan on users, qui lit les 10 000 lignes.
Sur une table de quelques dizaines de lignes, le planificateur choisit souvent un parcours séquentiel même quand un index existe : lire une poignée de pages coûte moins cher. EXPLAIN ANALYZE explique comment lire ces plans.
Choisir un type d'index
| Type | À utiliser pour |
|---|---|
| B-tree (par défaut) | égalités, intervalles, tris, LIKE 'prefix%' (avec la collation C ou text_pattern_ops) |
| GIN | valeurs à plusieurs éléments : jsonb (@>, ?), tableaux (@>, &&), tsvector en plein texte |
| GiST | géométrie, ranges (&&, chevauchement), plein texte, plus proche voisin avec ORDER BY ... <-> |
| SP-GiST | données qui se découpent en zones sans chevauchement : points, adresses IP, préfixes de texte |
| BRIN | très grosses tables dont les valeurs suivent l'ordre physique, comme un created_at qu'on ne fait qu'ajouter |
| Hash | égalité seulement ; un B-tree fait la même chose et plus, donc on le choisit rarement |
Un index GIN sur une colonne jsonb permet aux requêtes de contenance d'utiliser l'index :
Le plan montre un Bitmap Index Scan sur l'index GIN, qui alimente un Bitmap Heap Scan lisant les lignes correspondantes. La page JSONB présente ses opérateurs.
Indexer plusieurs colonnes
Un index B-tree multicolonne trie par la première colonne, puis par la deuxième pour chaque valeur de la première. Il sert les requêtes qui filtrent sur la première colonne, ou sur la première et les suivantes. Un filtre sur la deuxième colonne seule n'y gagne pas grand-chose, donc placez en premier la colonne que vous filtrez toujours avec =.
CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at);
-- served well by the index above
SELECT * FROM orders WHERE customer_id = 7 AND created_at > '2026-01-01';
SELECT * FROM orders WHERE customer_id = 7 ORDER BY created_at DESC LIMIT 10;
INCLUDE (PostgreSQL 11 et plus) stocke des colonnes supplémentaires dans l'index sans trier dessus : CREATE INDEX ON orders (customer_id) INCLUDE (total);. Une requête qui ne lit que ces colonnes peut alors être servie par l'index seul, un Index Only Scan.
Index uniques et partiels : imposer une règle à certaines lignes
CREATE UNIQUE INDEX refuse les valeurs en double. Un index partiel ajoute un WHERE et n'indexe que les lignes qui le vérifient. Il est plus petit, et il peut limiter une règle d'unicité à une partie des lignes :
Les deux lignes sont acceptées, car une seule n'est pas supprimée. Une troisième ligne active avec le même email échoue avec duplicate key value violates unique constraint "accounts_email_live". Une requête n'utilise un index partiel que si son WHERE implique la condition de l'index, ici WHERE NOT deleted.
Indexer une expression
Un index peut stocker le résultat d'une expression. Une requête doit répéter la même expression pour s'en servir, et un index sur email n'aide pas WHERE lower(email) = ... :
Le plan utilise users_email_lower_idx parce que le WHERE répète exactement lower(email). L'expression doit être immutable, donc now() ou une fonction qui dépend d'un paramètre est refusée.
Lister, construire et supprimer des index sur une base en production
pg_indexes liste chaque index avec l'instruction qui le recréerait :
La clé primaire et la contrainte UNIQUE ont créé leurs propres index, users_pkey et users_email_key. Dans psql, \d users affiche la même liste sous les colonnes de la table.
Sur une table qui reçoit des écritures, construisez et supprimez les index avec CONCURRENTLY pour ne pas bloquer les écritures. Ces formes ne peuvent pas s'exécuter dans un bloc de transaction. Une construction concurrente qui échoue laisse un index INVALID, que vous devez supprimer puis recréer :
CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);
DROP INDEX CONCURRENTLY orders_status_idx;
REINDEX INDEX CONCURRENTLY orders_status_idx; -- rebuild a bloated index
Questions fréquentes
PostgreSQL crée-t-il des index automatiquement ?
Seulement pour les contraintes PRIMARY KEY, UNIQUE et d'exclusion. Une colonne de clé étrangère n'a pas d'index, donc ajoutez-en un vous-même quand vous faites des jointures dessus ou supprimez des lignes de la table référencée.
Pourquoi PostgreSQL n'utilise-t-il pas mon index ?
Raisons fréquentes : la table est petite, donc la lire en entier coûte moins cher ; la requête renvoie une grande part des lignes ; le WHERE enveloppe la colonne dans une fonction ou une conversion que l'index n'a pas ; ou les statistiques sont périmées (lancez ANALYZE). EXPLAIN montre quel plan a été choisi.
À partir de combien d'index y en a-t-il trop ?
Il n'y a pas de chiffre fixe. Chaque index accélère certaines lectures et ralentit chaque INSERT et la plupart des UPDATE sur la table, et il prend de la place sur le disque. pg_stat_user_indexes.idx_scan indique combien de fois chaque index sert, ce qui vous permet de supprimer ceux qui restent à zéro.
CREATE INDEX verrouille-t-il la table ?
Un CREATE INDEX simple bloque INSERT, UPDATE et DELETE sur la table jusqu'à la fin, mais les lectures continuent. CREATE INDEX CONCURRENTLY autorise les écritures pendant la construction, prend plus de temps et ne peut pas s'exécuter dans un bloc de transaction.