Lisez le plan d'exécution avant d'ajouter un autre index

L'affirmation

Ajouter un index parce qu'une requête est lente, sans d'abord lire le plan que la base utilise pour l'exécuter, c'est deviner — et cela empire souvent les choses, car chaque index ajouté ralentit chaque écriture et consomme du cache, qu'il aide ou non la lecture. EXPLAIN ANALYZE vous dit exactement ce que fait la base et pourquoi, et apprendre à y lire trois choses remplace la devinette par un diagnostic.

EXPLAIN contre EXPLAIN ANALYZE

EXPLAIN seul montre le plan que la base compte utiliser, avec des coûts estimés. EXPLAIN ANALYZE exécute réellement la requête et montre ce qui s'est vraiment passé — durées réelles, comptes de lignes réels. Utilisez ANALYZE, car l'écart entre l'estimation et la réalité est souvent là où loge le problème, et ajoutez BUFFERS pour voir quelle part des données a été lue du cache plutôt que du disque :

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 8812 AND status = 'shipped'
ORDER BY created_at DESC;

Un avertissement : ANALYZE exécute la requête ; sur un UPDATE ou un DELETE, elle modifiera donc des données. Enveloppez-les dans une transaction que vous annulez.

La première chose à lire : le type de balayage

Vers le bas du plan figure la façon dont la base a accédé à chaque table, et c'est généralement toute l'histoire :

• Seq Scan lit chaque ligne de la table. Correct pour les petites tables et les requêtes qui ont réellement besoin de la plupart des lignes ; un problème quand une requête qui filtre vers quelques lignes sur des millions le fait, car cela signifie qu'aucun index utilisable n'existe pour ce filtre.
• Index Scan utilise un index pour trouver directement les lignes correspondantes. C'est habituellement ce que vous voulez pour un filtre sélectif.
• Bitmap Heap Scan se situe entre les deux — il utilise un index pour trouver efficacement de nombreuses lignes correspondantes, courant et sain pour les filtres qui touchent une fraction modérée de la table.

Un Seq Scan sur une grande table sous une clause WHERE sélective est de loin le constat le plus fréquent, et celui qu'un index corrige réellement.

La deuxième chose : l'écart estimé-réel

Chaque nœud affiche rows= (l'estimation du planificateur) et, sous ANALYZE, le compte réel produit. Quand ces valeurs divergent fortement — le planificateur attendait 10 lignes et en a obtenu 40 000 — la base prend de mauvaises décisions fondées sur des statistiques périmées ou absentes, et aucun index ne corrigera cela pleinement tant que les statistiques ne sont pas corrigées :

ANALYZE orders;   -- rafraichit les statistiques du planificateur

Une grande erreur d'estimation est un diagnostic distinct d'un index manquant, et c'est fréquemment la vraie cause d'une requête devenue lente « soudainement » après un gros changement de données. Ajouter un index pour compenser de mauvaises statistiques traite le symptôme et laisse la maladie.

La troisième chose : où passe réellement le temps

Dans un plan à plusieurs nœuds, le temps n'est pas réparti également. Lisez le actual time de chaque nœud et trouvez celui qui domine — c'est le seul qui vaille la peine d'être optimisé. Un piège courant est d'optimiser un nœud qui prend 3 % du total parce qu'il est facile à comprendre, pendant que le nœud à 90 % reste intact. Le plan est un arbre ; la branche coûteuse est celle à couper.

Surveillez particulièrement une Nested Loop dont le côté interne s'exécute de nombreuses fois sur une grande table — c'est ainsi qu'une jointure d'apparence innocente devient quadratique, et cela signifie d'ordinaire qu'un index manque sur la colonne de jointure, pas sur la colonne de filtre.

Alors, et seulement alors, ajoutez l'index

Une fois que le plan vous a dit quel filtre ou quelle jointure manque d'index, ajoutez celui dont la requête a besoin — et faites-le correspondre à la requête, y compris l'ordre de tri et les colonnes multiples là où la requête les utilise :

CREATE INDEX CONCURRENTLY idx_orders_cust_status_created
  ON orders (customer_id, status, created_at DESC);

L'ordre des colonnes compte : mettez d'abord les colonnes utilisées pour l'égalité, la colonne d'intervalle ou de tri en dernier. Relancez ensuite EXPLAIN ANALYZE et confirmez deux choses — que le balayage est passé de Seq Scan à Index Scan, et que le temps réel a chuté. Si le plan n'a pas changé, l'index n'est pas utilisé, et empiler une seconde supposition sur la première est ainsi que des tables finissent avec une douzaine d'index et aucune requête plus rapide.

La discipline en une phrase

Lisez le plan, trouvez le nœud dominant, identifiez pourquoi il est lent — index manquant, statistiques périmées, ou une quantité réellement grande de travail nécessaire — et agissez sur cette cause précise. Un index ajouté à partir du plan est un correctif ; un index ajouté sur une intuition est un passif qui ralentit chaque insertion pour une lecture qu'il n'aide peut-être même pas. Le plan est gratuit, il est instantané, et il transforme la performance de base de données, d'un folklore en quelque chose que vous pouvez réellement voir.