Maîtrise d'EXPLAIN ANALYZE : Guide d'optimisation des plans de requêtes PostgreSQL

EXPLAIN ANALYZE est l'outil que j'utilise lorsqu'une requête PostgreSQL semble lente et que les suppositions habituelles ne suffisent plus. Peut-être que la requête semble anodine dans le code de l'application. Peut-être que la table a un index et que tout le monde suppose que la base de données l'utilise. Peut-être que la requête est rapide en préproduction mais lente en production. Le plan est l'endroit où ces hypothèses se confirment ou s'effondrent.

L'habitude utile est de lire le plan comme une histoire du travail effectué par PostgreSQL : quelles lignes il s'attendait à toucher, quelles lignes il a réellement touchées, où il a fait des jointures, où il a trié, s'il est resté en mémoire et s'il a dû lire depuis le disque. Vous n'avez pas besoin de mémoriser chaque nœud du plan pour que cela devienne utile. Vous devez ralentir et comparer les estimations avec la réalité.

Comprendre EXPLAIN vs. EXPLAIN ANALYZE

La différence entre EXPLAIN et EXPLAIN ANALYZE est importante car l'un est une prédiction et l'autre une mesure.

EXPLAIN

Lorsque vous exécutez une requête préfixée par EXPLAIN, PostgreSQL génère le plan d'exécution prévu sans réellement exécuter la requête. Cela est utile pour :

• Aperçu du plan : Vous pouvez voir ce que PostgreSQL considère comme le moyen le moins coûteux d'exécuter votre requête.
• Estimation des coûts : Il fournit des estimations de coût pour chaque nœud du plan, vous donnant une idée relative de l'utilisation des ressources.

Exemple :

EXPLAIN SELECT * FROM users WHERE registration_date > '2023-01-01';

EXPLAIN ANALYZE

EXPLAIN ANALYZE va plus loin. Il vous montre non seulement l'exécution planifiée mais exécute également la requête, puis rapporte les statistiques d'exécution réelles. Cela signifie que vous obtenez :

• Temps d'exécution réels : Combien de temps chaque étape a réellement pris.
• Nombres de lignes réels : Combien de lignes ont été réellement traitées à chaque nœud.
• Confirmation des estimations : Vous pouvez comparer les nombres de lignes estimés avec les réels pour voir si le planificateur de PostgreSQL fait des prédictions précises.

Cela fait de EXPLAIN ANALYZE le meilleur outil pour un réglage réel, mais il a un inconvénient : il exécute la requête. Un SELECT peut encore être coûteux car il peut analyser beaucoup de données, prendre des verrous ou entrer en compétition pour le cache. Un UPDATE, DELETE ou INSERT modifiera réellement les données à moins que vous ne l'enveloppiez dans une transaction et que vous l'annuliez :

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE accounts SET status = 'archived' WHERE last_seen_at < now() - interval '2 years';
ROLLBACK;

Ce modèle est utile dans une fenêtre de maintenance ou une copie de préproduction. Ce n'est pas un laissez-passer pour exécuter des instructions dangereuses sur une base de données de production chargée.

Exemple :

EXPLAIN ANALYZE SELECT * FROM users WHERE registration_date > '2023-01-01';

Décoder la sortie d'EXPLAIN ANALYZE

La sortie d'EXPLAIN ANALYZE peut sembler dense au premier abord, mais comprendre ses composants clés est fondamental.

Composants principaux :

• Type de nœud : Identifie l'opération effectuée (par exemple, Seq Scan, Index Scan, Hash Join, Nested Loop, Sort, Aggregate).
• Coût : Présenté comme (startup_cost .. total_cost).
  • startup_cost : Le coût pour récupérer la première ligne.
  • total_cost : Le coût pour récupérer toutes les lignes.
  • Remarque : Les coûts sont des unités arbitraires utilisées pour la comparaison, pas directement le temps ou la mémoire.
• Lignes : Le nombre estimé de lignes que le planificateur s'attend à renvoyer de ce nœud.
• Largeur : La largeur moyenne estimée (en octets) des lignes renvoyées par ce nœud.
• Temps réel : Présenté comme (startup_time .. total_time). C'est le temps réel en millisecondes pour exécuter ce nœud.
  • startup_time : Temps réel pour renvoyer la première ligne.
  • total_time : Temps réel pour renvoyer toutes les lignes.
• Lignes réelles : Le nombre réel de lignes renvoyées par ce nœud.
• Boucles : Le nombre de fois que ce nœud a été exécuté. Pour les nœuds de niveau supérieur, c'est généralement 1. Pour les opérations imbriquées, cela peut être plus élevé.

Exemple d'interprétation de sortie :

Considérons un exemple simplifié d'un Seq Scan (Analyse séquentielle) sur une grande table :

Seq Scan on users  (cost=0.00..15000.00 rows=1000000 width=100) (actual time=0.020..150.500 rows=950000 loops=1)
  Filter: (registration_date > '2023-01-01')
  Rows Removed by Filter: 50000

Interprétation :

• Seq Scan on users : La base de données lit chaque ligne de la table users.
• cost=0.00..15000.00 : Le planificateur a estimé le coût total à environ 15000 unités.
• rows=1000000 : Le planificateur a estimé qu'il y avait 1 million de lignes dans la table.
• actual time=0.020..150.500 : Cela a réellement pris 150,5 millisecondes pour terminer l'analyse et le filtrage.
• rows=950000 : Cela a réellement renvoyé 950 000 lignes (après filtrage).
• loops=1 : Cette analyse a été effectuée une fois.
• Filter: (registration_date > '2023-01-01') : C'est la condition appliquée pour filtrer les lignes.
• Rows Removed by Filter: 50000 : 50 000 lignes ont été écartées par le filtre.

Identification des goulots d'étranglement : Ne cherchez pas seulement le plus grand actual time. Cherchez aussi un nœud qui s'exécute plusieurs fois. Une analyse interne en boucle imbriquée qui prend 0,2 ms peut sembler inoffensive jusqu'à ce que loops=50000. Dans ce cas, le coût réel est approximativement le temps par boucle multiplié par le nombre de boucles.

Lire de l'intérieur vers l'extérieur

Les plans PostgreSQL sont des arbres. Le nœud supérieur renvoie le résultat final au client, mais le travail commence généralement plus profondément dans le plan. Lorsqu'une requête joint orders, customers et order_items, la première ligne pourrait être une Aggregate, mais la vraie douleur pourrait être une analyse ou une jointure en dessous.

Je lis généralement un plan dans cet ordre :

1. Commencez par les nœuds d'analyse les plus profonds et demandez : PostgreSQL a-t-il lu beaucoup plus de lignes que la requête n'en renvoie ?
2. Comparez les rows estimées avec les rows réelles.
3. Vérifiez si les nœuds coûteux ont des loops élevées.
4. Cherchez les nœuds Sort, Hash ou Materialize qui débordent sur le disque.
5. Utilisez BUFFERS pour décider si la requête est principalement un travail CPU/cache ou des E/S disque.

Voici un exemple courant :

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Si vous voyez une analyse séquentielle sur des millions de lignes orders, puis un tri, puis une limite, la base de données fait trop de travail avant de pouvoir renvoyer les 20 lignes demandées. Un index pratique pourrait être :

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

Après cela, un bon plan peut utiliser l'index pour aller directement aux commandes les plus récentes pour ce client et s'arrêter après 20 lignes. Le plan exact dépend de la taille de la table, des statistiques, de la version de PostgreSQL et de la distribution des données, mais le principe est stable : faites correspondre l'index au modèle de filtrage et de tri que vous utilisez réellement.

Nœuds de plan de requête courants et stratégies d'optimisation

Comprendre les différents types de nœuds et comment les optimiser est essentiel pour maîtriser les performances des requêtes.

1. Analyse séquentielle (Seq Scan)

• Ce que c'est : Lit chaque ligne de la table. C'est souvent inefficace pour les grandes tables, surtout lors du filtrage sur des conditions spécifiques.
• Quand c'est acceptable : Pour les petites tables, ou lorsque vous devez récupérer un grand pourcentage des lignes de la table. Une analyse séquentielle n'est pas automatiquement mauvaise.
• Optimisation : Créez un index sur les colonnes de filtre sélectives, mais vérifiez-le avec le plan. Si un prédicat renvoie la plupart de la table, PostgreSQL peut correctement continuer à utiliser une analyse séquentielle.

2. Analyse d'index (Index Scan)

• Ce que c'est : Utilise un index pour trouver les lignes qui correspondent à la clause WHERE. PostgreSQL parcourt l'index puis récupère les lignes correspondantes de la table.
• Optimisation : Assurez-vous que l'index correspond à la forme de la requête. Pour un index composé, l'ordre des colonnes est important. Un index sur (tenant_id, created_at) aide une requête qui filtre par tenant_id et trie par created_at ; il peut ne pas beaucoup aider pour une requête qui filtre uniquement par created_at.

3. Analyse uniquement d'index (Index Only Scan)

• Ce que c'est : Un Index Scan optimisé où toutes les données requises par la requête sont disponibles directement dans l'index. PostgreSQL n'a pas besoin de visiter le tas de la table.
• Quand c'est efficace : Lorsque toutes les colonnes sélectionnées sont disponibles dans l'index et que la carte de visibilité permet à PostgreSQL d'éviter de nombreuses vérifications du tas.
• Optimisation : Envisagez un index couvrant avec INCLUDE pour les chemins à lecture intensive, mais n'ajoutez pas chaque colonne "au cas où". Des index plus grands coûtent plus cher à maintenir lors des écritures.

4. Opérations de jointure (Nested Loop, Hash Join, Merge Join)

• Nested Loop : Pour chaque ligne de la relation externe, PostgreSQL analyse la relation interne. Efficace pour les petites relations externes ou lorsque la relation interne peut être rapidement accédée via un index.
• Hash Join : Construit une table de hachage à partir d'une relation (le côté de construction) et la sonde avec les lignes de l'autre relation (le côté de sonde). Efficace pour les grandes tables où les index ne sont pas bénéfiques pour la condition de jointure.
• Merge Join : Nécessite que les deux relations soient triées sur les clés de jointure. Fusionne les listes triées. Efficace pour les grandes entrées déjà triées.
• Optimisation :
  • Assurez-vous que des index existent sur les colonnes de jointure.
  • Vérifiez si de mauvaises estimations de lignes ont conduit à un mauvais choix de jointure. PostgreSQL ne prend pas en charge les indicateurs d'optimisation natifs de la même manière que certaines bases de données, donc les correctifs habituels sont de meilleures statistiques, de meilleurs index ou une réécriture de la requête.
  • Vérifiez EXPLAIN ANALYZE pour des comptes loops élevés ou un actual time élevé sur les nœuds de jointure.

5. Tri (Sort)

• Ce que c'est : Ordonne les lignes. Peut être coûteux en calcul, surtout sur de grands ensembles de données.
• Optimisation :
  • Ajoutez un index dont l'ordre des colonnes correspond au modèle ORDER BY lorsque la requête est suffisamment sélective.
  • Réduisez le nombre de lignes à trier en ajoutant des clauses WHERE plus restrictives.
  • Assurez-vous que work_mem est configuré de manière suffisante pour permettre au tri de se faire en mémoire plutôt que sur disque.

6. Agrégations (Aggregate)

• Ce que c'est : Effectue des opérations comme COUNT(), SUM(), AVG(), GROUP BY.
• Optimisation :
  • Assurez-vous que les clauses WHERE sont efficaces, réduisant le nombre de lignes avant l'agrégation.
  • Envisagez d'utiliser des vues matérialisées pour les données pré-agrégées si l'agrégation est une opération fréquente et lente.
  • Indexez les colonnes utilisées dans les clauses GROUP BY.

Utiliser EXPLAIN ANALYZE avec des options

EXPLAIN ANALYZE a plusieurs options utiles qui peuvent fournir des informations encore plus détaillées.

VERBOSE

• Ce que ça fait : Affiche des informations supplémentaires sur le plan de requête, comme les noms de table qualifiés par le schéma et les noms de colonnes de sortie.

EXPLAIN (ANALYZE, VERBOSE) SELECT u.name FROM users u WHERE u.id = 1;

COSTS

• Ce que ça fait : Inclut les coûts estimés dans la sortie. C'est le comportement par défaut, mais vous pouvez explicitement le désactiver.

EXPLAIN (ANALYZE, COSTS FALSE) SELECT COUNT(*) FROM orders;

BUFFERS

• Ce que ça fait : Rapporte des informations sur l'utilisation des tampons (partagés, temporaires et locaux). Cela aide à identifier les goulots d'étranglement d'E/S.
  • shared hit : Blocs trouvés dans le cache de tampons partagés de PostgreSQL.
  • shared read : Blocs lus depuis le disque dans les tampons partagés.
  • temp read/written : Blocs lus/écrits dans des fichiers temporaires (souvent pour les tris ou les hachages qui dépassent work_mem).

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM products WHERE category = 'Electronics';

TIMING

• Ce que ça fait : Inclut le temps de démarrage réel et le temps total pour chaque nœud. C'est le comportement par défaut pour ANALYZE.

EXPLAIN (ANALYZE, TIMING FALSE) SELECT * FROM logs LIMIT 10;

Combinaison d'options

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT o.order_date, COUNT(oi.product_id)
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= '2023-01-01'
GROUP BY o.order_date;

Conseils pratiques et bonnes pratiques

• Commencez par EXPLAIN ANALYZE : Utilisez toujours EXPLAIN ANALYZE pour une analyse de performance réelle. EXPLAIN seul est insuffisant.
• Concentrez-vous sur actual time : Priorisez l'optimisation des nœuds avec le actual time le plus élevé.
• Comparez rows (estimé vs réel) : De grandes divergences indiquent que le planificateur de requêtes de PostgreSQL pourrait faire des hypothèses inexactes. Cela peut souvent être corrigé en mettant à jour les statistiques de la table avec ANALYZE <table_name>; ou en créant des index appropriés.
• Utilisez BUFFERS : Analysez l'utilisation des tampons pour comprendre si votre requête est liée aux E/S.
• Testez avec des données réalistes : Exécutez EXPLAIN ANALYZE sur une base de données qui a une quantité de données représentative et une distribution de données similaire à votre environnement de production.
• Optimisez par étapes : N'essayez pas d'optimiser tout à la fois. Traitez d'abord le plus grand goulot d'étranglement.
• Considérez work_mem : Si vous voyez des lectures disque importantes pour le tri ou le hachage (temp read/written dans BUFFERS), augmenter work_mem (par session ou globalement) pourrait aider, mais soyez conscient de l'utilisation de la mémoire.
• Indexez judicieusement : Créez uniquement des index qui sont réellement utilisés et bénéfiques. Trop d'index peuvent ralentir les écritures et consommer de l'espace disque.
• Vérifiez la version de PostgreSQL : Les versions plus récentes ont souvent des planificateurs de requêtes améliorés et de nouvelles fonctionnalités qui peuvent affecter les performances.

Un passage de réglage pratique

Prenez cette requête :

SELECT id, email, created_at
FROM users
WHERE lower(email) = lower('[email protected]');

Si le plan montre une analyse séquentielle, un index sur email seul peut ne pas aider car la requête applique lower(email). PostgreSQL ne peut pas toujours utiliser un index simple lorsque l'expression dans la requête diffère de la valeur indexée. Une meilleure option pourrait être un index d'expression :

CREATE INDEX CONCURRENTLY users_lower_email_idx
ON users (lower(email));

Puis réexécutez :

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email, created_at
FROM users
WHERE lower(email) = lower('[email protected]');

Vous cherchez moins de lignes analysées, moins de tampons lus et un temps d'exécution plus faible. Si le plan n'utilise toujours pas l'index, vérifiez si la table est minuscule, si les statistiques sont obsolètes ou si la requête n'est pas écrite comme vous pensez que l'application l'envoie.

Un autre cas courant est une jointure qui semble correcte en SQL mais explose dans le plan :

SELECT o.id, p.sku
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.created_at >= current_date - interval '7 days';

Les index utiles pourraient inclure orders(created_at), order_items(order_id) et la clé primaire sur products(id). Mais si les sept derniers jours incluent la plupart de la table orders, orders(created_at) peut ne pas être le correctif principal. Le plan vous dit si le vrai problème est le filtre de date, l'éclatement de la jointure ou un index manquant sur la table enfant.

Un bon réglage des requêtes PostgreSQL n'est pas "ajouter un index jusqu'à ce que le plan change". C'est une boucle : mesurez le plan réel, faites un changement défendable, mesurez à nouveau, et ne conservez le changement que s'il améliore la charge de travail qui vous intéresse réellement.