Les requêtes lentes classiques

La plupart des problèmes de performance en production tiennent en une poignée de motifs récurrents.

1. SELECT *#

Lire toutes les colonnes empêche les index-only scans, transfère des données inutiles (surtout text/jsonb volumineux) et rend le code fragile. Listez les colonnes.

2. Pagination par OFFSET#

SELECT * FROM commandes ORDER BY cree_le DESC, id DESC LIMIT 20 OFFSET 100000;
-- PostgreSQL lit et jette 100 000 lignes avant de renvoyer 20

Pagination par curseur (keyset) : on repart de la dernière ligne vue.

SELECT id, cree_le, total
FROM   commandes
WHERE  (cree_le, id) < ($1, $2)          -- valeurs de la dernière ligne de la page précédente
ORDER  BY cree_le DESC, id DESC
LIMIT  20;

Avec un index sur (cree_le DESC, id DESC), chaque page coûte le même temps, quelle que soit la profondeur.

3. Le problème N+1#

Une requête pour la liste, puis une requête par ligne pour ses relations (souvent causé par un ORM et un chargement paresseux). Solutions : JOIN, chargement groupé (WHERE id = ANY($1)), ou agrégation côté SQL.

4. Fonction ou conversion sur une colonne indexée#

WHERE lower(email) = ..., WHERE cree_le::date = ..., WHERE id::text = ... : indexez l'expression ou réécrivez la condition.

5. count(*) sur une grande table#

À cause de MVCC, PostgreSQL doit parcourir les lignes visibles pour compter : c'est O(n). Pour une estimation rapide : SELECT reltuples::bigint FROM pg_class WHERE relname = 'commandes';, ou maintenez un compteur.

6. Types incompatibles#

Comparer une colonne bigint à un paramètre numeric, ou une colonne text à un paramètre d'un autre type, peut forcer une conversion de la colonne et désactiver l'index. Vérifiez les types des paramètres.

7. Trop de petites requêtes#

Un aller-retour réseau par ligne coûte bien plus que la requête elle-même. Regroupez : INSERT ... VALUES (...), (...), COPY, unnest($1::int[]).

Cas pratique : une page d'accueil met 4 s. EXPLAIN (ANALYZE, BUFFERS) montre un Seq Scan sur 8 millions de lignes avec Rows Removed by Filter: 7 999 000. Le filtre porte sur statut = 'en_attente', vrai pour 0,01 % des lignes : un index partiel WHERE statut = 'en_attente' ramène la requête à 1 ms.