Créer les bons index
AvancéL'efficacité d'un index dépend de son ordre de colonnes, de sa sélectivité et de la façon dont la requête l'utilise.
CREATE INDEX commandes_client_idx ON commandes (client_id);
-- Composite : utile pour WHERE client_id = ? ORDER BY cree_le DESC
CREATE INDEX commandes_client_date_idx ON commandes (client_id, cree_le DESC);
Index composite : la règle du préfixe gauche#
Un index sur (a, b, c) sert pour les recherches sur a, sur a, b et sur a, b, c, mais pas (efficacement) sur b seul ou c seul.
| Requête | Index (client_id, statut, cree_le) utilisable ? |
|---|---|
WHERE client_id = 1 | Oui |
WHERE client_id = 1 AND statut = 'paye' | Oui |
WHERE client_id = 1 AND statut = 'paye' AND cree_le > ... | Oui, entièrement |
WHERE statut = 'paye' | Peu efficace (pas de préfixe) |
Règle pratique : colonnes d'égalité d'abord, puis la colonne de plage ou de tri en dernier.
Index partiels#
On n'indexe qu'un sous-ensemble : plus petit, plus rapide, adapté à un filtre stable.
CREATE INDEX commandes_a_traiter_idx ON commandes (cree_le) WHERE statut = 'en_attente';
Index sur expression#
CREATE INDEX clients_email_lower_idx ON clients (lower(email));
-- utilisé par : WHERE lower(email) = 'alice@exemple.fr'
Index couvrants (INCLUDE) et index-only scans#
CREATE INDEX commandes_client_couvrant_idx
ON commandes (client_id) INCLUDE (total, statut);
Si toutes les colonnes demandées sont dans l'index, PostgreSQL répond sans lire la table (index-only scan), à condition que la table soit suffisamment « vacuumée » (carte de visibilité à jour).
Piège : appliquer une fonction ou un calcul à la colonne dans le
WHERE(WHERE lower(email) = ...,WHERE cree_le::date = ...,WHERE prix * 2 > 10) empêche d'utiliser un index classique sur cette colonne. Soit vous indexez l'expression, soit vous réécrivez la condition.