Vos dix requêtes les plus lentes tiennent en un rapport de vingt minutes

L'affirmation

Avant d'agrandir l'instance de base de données, d'ajouter une réplique en lecture ou de réécrire un point d'accès, exécutez une requête. Dans presque toutes les applications sous-performantes qu'on nous demande d'examiner, une poignée d'instructions — souvent moins de dix — représente la majorité du temps total de base de données, et elles apparaissent dans un rapport qui prend vingt minutes à activer et à lire. Acheter du matériel pour accélérer des requêtes lentes, c'est payer pour faire le mauvais travail plus vite.

Activez la seule extension qui compte

pg_stat_statements est fournie avec Postgres et enregistre les statistiques d'exécution de chaque forme de requête, agrégées sur toutes ses exécutions. Activez-la :

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
# puis redemarrer, et une fois :
CREATE EXTENSION pg_stat_statements;

Laissez-la collecter sur une période représentative — une journée ouvrable normale suffit — puis posez-lui la seule question qui compte d'abord : où passe le temps ?

SELECT
  round(total_exec_time)          AS total_ms,
  calls,
  round(mean_exec_time, 2)        AS mean_ms,
  left(query, 80)                 AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Lisez le rapport correctement

La colonne qui compte est total_ms, pas mean_ms, et c'est là que l'intuition trompe. Une requête de 2 millisecondes exécutée quatre millions de fois par jour consomme bien plus votre base qu'un rapport de cinq secondes lancé deux fois. Optimiser le rapport de cinq secondes semble productif et ne change rien ; grignoter la requête de 2 millisecondes qui tourne sans cesse, c'est de là que vient réellement la capacité.

Vous cherchez deux coupables distincts. Un total_ms élevé avec un calls élevé et un mean_ms faible est une requête individuellement correcte mais appelée trop souvent — fréquemment le motif N+1, où l'affichage d'une page de 50 éléments émet 50 requêtes séparées. Un mean_ms élevé est une requête lente à chaque fois — d'ordinaire un index manquant.

Diagnostiquez la requête lente à chaque fois

Prenez le pire fautif et demandez au planificateur ce qu'il fait :

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

Le mot à chercher dans la sortie est Seq Scan. Un balayage séquentiel sur une grande table, dans une requête qui filtre sur une valeur précise, signifie qu'aucun index n'est utilisable et que la base lit chaque ligne pour trouver les correspondantes. Le correctif tient souvent en une ligne :

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

Cet index composite sert à la fois le filtre et le tri. Relancez le EXPLAIN et vous voulez voir un Index Scan là où était le Seq Scan, avec un temps d'exécution radicalement plus bas. Un index, une requête, souvent une amélioration de dix à cent fois sur cette instruction.

Diagnostiquez la requête appelée trop souvent

Quand le problème est le nombre d'appels plutôt que le coût par appel, le correctif est dans l'application, pas dans la base. La page qui émet 50 requêtes pour afficher 50 lignes devrait en émettre une qui récupère les 50 lignes, ou deux s'il lui faut des données liées. C'est le défaut de performance le plus courant dans les applications bâties sur un ORM, car l'ORM rend les requêtes supplémentaires invisibles — chacune ressemble à un simple accès d'attribut dans le code. Journalisez le nombre de requêtes par requête HTTP en développement et traitez toute page en émettant plus d'une poignée comme un défaut à examiner.

L'index à chercher aussi : l'inutilisé

Les index ne sont pas gratuits — chacun ralentit les écritures et consomme espace et cache. Pendant que les statistiques sont ouvertes, trouvez les index que personne n'utilise :

SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Un index à zéro balayage depuis la dernière remise à zéro des statistiques est de la pure surcharge — ajouté pour une requête qui ne tourne plus, ou qui n'a jamais tourné. Le supprimer accélère les écritures et rend du cache aux index qui gagnent leur place.

L'ordre des opérations

La discipline consiste à mesurer avant de dépenser. Activez l'extension, collectez une journée, lisez le top dix, et agissez selon ce que montrent les chiffres :

1. Ajoutez les index manquants que pointe la sortie d'EXPLAIN. La victoire la moins chère et la plus rapide.
2. Corrigez les motifs N+1 que révèle le nombre d'appels. Cela exige du code, mais aucune infrastructure neuve.
3. Supprimez les index inutilisés pour récupérer du débit d'écriture.
4. Seulement maintenant, si le top dix est réellement tout du travail nécessaire tournant aussi vite que possible, envisagez une instance plus grosse ou une réplique.

La plupart des équipes constatent que les étapes un et deux retirent la pression qui les envoyait magasiner une plus grosse base, au prix d'un après-midi et d'aucune dépense mensuelle supplémentaire. Le rapport est gratuit, il est déjà installé, et il vous dira la vérité sur l'endroit où passe réellement le temps de votre base — qui n'est presque jamais celui que l'équipe supposait avant de regarder.