Le problème N+1 : cent une requêtes pour afficher une page

Ce que vous saurez faire

Reconnaître un N+1 dans des journaux SQL, écrire la version en lot, et choisir entre une jointure et deux requêtes sur la base d’une mesure plutôt que d’une intuition. Travaillez sur une base d’exercice vide.

1. Préparer un cas reproductible

Contexte : psql, base d’exercice vide. Les deux tables sont créées par l’atelier lui-même.

CREATE TABLE users (
  id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);
CREATE TABLE orders (
  id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id integer NOT NULL REFERENCES users(id),
  total integer NOT NULL
);
INSERT INTO users (name)
SELECT 'Client ' || g FROM generate_series(1, 100) AS g;
INSERT INTO orders (user_id, total)
SELECT u.id, 10 * g FROM users AS u, generate_series(1, 5) AS g;

Résultat attendu : 100 utilisateurs et 500 commandes. C’est assez pour observer un motif, pas pour mesurer une charge de production.

2. Le lot, une requête au lieu de cent

La version fautive exécute SELECT * FROM orders WHERE user_id = ? cent fois, une par utilisateur. La version en lot demande les mêmes lignes d’un seul coup, puis regroupe côté application.

SELECT user_id, id, total
FROM orders
WHERE user_id IN (1, 2, 3, 4, 5)
ORDER BY user_id;

Résultat attendu : les commandes des cinq utilisateurs demandés dans une seule réponse. En production, la liste d’identifiants provient du premier SELECT ; passez-la en paramètre de requête, jamais par concaténation de chaînes. Une liste très longue mérite d’être découpée en plusieurs lots plutôt que d’être envoyée en une fois.

3. Jointure ou lot : cela se mesure

Un JOIN sur une relation « un vers plusieurs » duplique les colonnes du côté « un ». Un utilisateur ayant cinq commandes fait revenir son nom cinq fois. Avec des colonnes larges, une adresse ou une description par exemple, la jointure peut transporter beaucoup plus d’octets que deux requêtes en lot, alors même qu’elle fait un aller-retour de moins.

EXPLAIN ANALYZE
SELECT u.name, o.total
FROM users AS u
JOIN orders AS o ON o.user_id = u.id;

EXPLAIN ANALYZE exécute réellement la requête et affiche le plan retenu, le nombre de lignes traitées et le temps passé par étape. Comparez ce plan avec celui des deux requêtes en lot, sur vos volumes et vos colonnes réelles. C’est le même outil que celui utilisé pour vérifier qu’un index est bien employé : le choix se mesure, il ne se devine pas.

Références : utiliser EXPLAIN et les jointures.

Pièges et limites

• Prendre les ordres de grandeur pour des mesures. « 3 ms par requête » est une illustration. Le coût dépend surtout de la latence par aller-retour, donc de la distance entre l’application et la base, pas du travail SQL lui-même.
• Remplacer un N+1 par une jointure démesurée. Joindre deux relations « un vers plusieurs » à partir de la même table multiplie les lignes entre elles. Deux ou trois requêtes en lot sont alors généralement plus rapides.
• Croire que « tout charger » supprime le problème. Un chargement anticipé systématique déplace le coût vers la mémoire, la sérialisation et la bande passante.
• Ne pas regarder les journaux. Sans trace des requêtes réellement exécutées, un ORM ne vous signalera pas qu’il en a lancé cent une pour afficher une page.

À vous de jouer

Sur l’atelier ci-dessus, une page doit afficher les 100 utilisateurs avec le nombre de commandes de chacun. Écrivez une requête unique qui renvoie, pour chaque utilisateur, son nom et son nombre de commandes, en incluant les utilisateurs qui n’en ont aucune.

Correction commentée

SELECT u.id, u.name, count(o.id) AS nb_commandes
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
GROUP BY u.id, u.name
ORDER BY u.id;

Résultat attendu : 100 lignes, chacune portant un compte.

Trois raisons en font la bonne réponse. Le LEFT JOIN conserve les utilisateurs sans commande, qui obtiennent 0, car count(o.id) ignore les valeurs NULL, contrairement à count(*) qui aurait renvoyé 1 pour ces lignes. L’agrégation est faite par la base : une seule réponse traverse le réseau au lieu de cent une. Enfin, rien n’est dupliqué, puisque le regroupement ramène chaque utilisateur à une ligne, ce qui évite l’effet de gonflement décrit plus haut.

Si vous aviez besoin des commandes elles-mêmes et pas seulement de leur nombre, la question redeviendrait ouverte : jointure ou deux requêtes en lot, à départager avec EXPLAIN ANALYZE sur vos données.