Réglage des requêtes PostgreSQL

Vérification des affirmations — CHECKS.md. Tout ce qui suit concerne PostgreSQL standard. Commencez par connaître la version (SHOW server_version) : les valeurs par défaut des BUFFERS (PG18), le skip scan (PG18) et la valeur par défaut de hash_mem_multiplier (2.0 depuis PG15) en dépendent.

Règle principale

Ne traitez pas au hasard. L'ordre est le suivant :

où le temps est réellement passé ;
quelle requête est en cause et combien de fois elle est appelée ;
dans quel nœud l'estimation des lignes du plan s'est écartée ;
correction ;
vérification par le nombre de pages sur des données de volume de production.

Ne touchez l'index, work_mem et random_page_cost que si le diagnostic les a identifiés. Pourquoi : sans profilage, on corrige une requête « effrayante » qui ne représente qu'une fraction de 1/60 et on ne gagne rien, tandis qu'un index ajouté au hasard nuit à une requête longue.

Où le temps est passé

Plainte de lenteur : comparez d'abord le temps en base de données avec ce que voit l'utilisateur. Si les requêtes totalisent environ 100 ms et qu'on se plaint d'une minute, la base n'est pas en cause. Ne touchez pas aux requêtes, cherchez dans l'application, le réseau, le frontend.
Opération composée de nombreuses requêtes : faites un profil via pg_stat_statements.
1. Configurez pg_stat_statements.track = all. Par défaut, c'est top, et les requêtes à l'intérieur des fonctions ne sont pas visibles.
2. Appelez pg_stat_statements_reset(). Nécessite un superutilisateur ou un droit accordé sur la fonction. La réinitialisation efface les statistiques de tout le monde, donc faites-la uniquement sur un banc d'essai ou en période calme.
3. Exécutez l'opération une fois et triez par total_exec_time.
4. Regardez calls, shared_blks_hit + shared_blks_read et toplevel. Le temps des lignes avec toplevel = true inclut déjà les requêtes imbriquées. Si vous additionnez tout à la suite, le temps sera compté deux fois.
Ne corrigez que ce qui occupe la part principale. Le gain ne dépasse pas cette part : une sous-tâche de 1 s sur 60 donnera au maximum 1/60.
Les requêtes dans pg_stat_statements sont normalisées : les constantes sont remplacées par $1, les lignes ne sont pas séparées par session, seuls les agrégats sont stockés (moyenne, min/max, stddev). Si vous pensez que le plan dépend de la valeur (gros client contre petit client), prenez des valeurs spécifiques et analysez EXPLAIN séparément. Avec pg_stat_activity et EXPLAIN VERBOSE, les lignes sont liées par queryid, ce qui nécessite compute_query_id (depuis PG14 ; auto l'active lorsque pg_stat_statements est chargé).
Via le journal : dans log_line_prefix, il faut %p ou %c et le temps %m. Les requêtes imbriquées ne sont visibles que via auto_explain. Définissez log_min_duration (par défaut -1, donc désactivé), activez log_analyze et log_nested_statements (par défaut off ; modification par superutilisateur ou GRANT SET, depuis PG15). Analysez le journal avec pgBadger. Pour une session unique, SET log_min_duration_statement (superutilisateur ou GRANT SET ON PARAMETER) et LOAD 'auto_explain' (superutilisateur uniquement) suffisent.

Sur quelles données mesurer

Ne tirez pas de conclusions sur une base de dev avec des données factices. Le plan dépend de la taille des tables et des statistiques, et une petite base tient entièrement dans le cache. Si la requête est rapide sur une petite base, mentionnez que cela ne prouve rien.
Volume et distribution identiques à la production. Si la production contient des données personnelles, ne copiez pas sur le banc d'essai. Générez des données synthétiques de même forme : combien de lignes dans les tables principales et comment elles sont réparties par client. En production, n'accédez qu'aux statistiques (pg_stat_statements, pg_class).
N'insérez pas les plans avec des constantes de production dans des visualiseurs externes : les constantes peuvent contenir des données personnelles. Utilisez des outils locaux.

Nombreux appels rapides (N+1)

Si une requête est déjà rapide, mais qu'elle est appelée des dizaines ou centaines de fois pour une seule opération, ne l'accélérez pas. Réécrivez la tâche en une seule requête pour tout le lot :
- au lieu d'une boucle de requêtes par liste d'ids dans le code — WHERE id = ANY($1) ou un batch ;
- les découpes par catégorie — count(*) FILTER (WHERE …) ;
- pour ne pas perdre les lignes sans données — LEFT JOIN.
Pourquoi : une requête en 1 ms ne peut pas être accélérée, mais un millier de tels appels ralentit. Par exemple, 27 appels réduits à une seule requête lisent environ 60 000 pages au lieu d'environ 900 000.
Si une seule requête ne suffit pas, revoyez l'accès aux données dans le service, pas les réglages de la base.

Comment comparer les variantes

Métrique principale : les pages. Exécutez EXPLAIN (ANALYZE, BUFFERS) et regardez shared hit + read dans le nœud supérieur : la somme des nœuds enfants y est déjà incluse. Additionnez hit et read, car leur rapport dépend du cache. Dans PG18, BUFFERS est activé automatiquement avec ANALYZE.
Le temps n'est qu'une indication. Il est décalé par le cache, la charge, le matériel et la mesure ANALYZE elle-même. Une deuxième exécution est toujours plus rapide et ne prouve rien. N'écrivez « c'est devenu plus rapide » qu'après plusieurs exécutions.
Les pages ne voient pas la charge CPU : tri en mémoire, fonctions, JIT, plans parallèles. Si la requête calcule plus qu'elle ne lit, vérifiez le temps séparément.
Si vous n'avez besoin que des lignes et des buffers, ajoutez TIMING FALSE : cela évite les frais généraux de mesure.

Analyse du plan

Classe de requête.
- Courte : renvoie peu de lignes, une personne attend la réponse. Exemple : fiche d'objet, page de liste, recherche par clé. Attendez-vous à un accès indexé et nested loop. Pour l'analyse, mettez SET max_parallel_workers_per_gather = 0.
- Longue : lit les tables presque entièrement, avec une agrégation au-dessus. Exemple : rapports, analyses, exportations. Attendez-vous à seq scan et hash join, le parallélisme est normal ici.
C'est une attente, pas une règle absolue. Si le plan ne correspond pas à l'attente, cherchez la cause dans les estimations, n'ajoutez pas d'index.
Estimation vs réalité. Comparez rows avec actual rows par nœud. Quand loops > 1, la valeur actual rows est donnée pour un seul lancement, multipliez-la par loops. Corrigez le nœud le plus imbriqué où l'écart est apparu pour la première fois. L'erreur remonte dans le plan et conduit à une méthode d'accès incorrecte et à une jointure incorrecte.
Pages vs taille des tables (pour les requêtes longues). Faites ANALYZE. Comparez ensuite hit + read du nœud supérieur avec la somme des relpages des tables de la requête dans pg_class. Ajoutez les index et TOAST séparément si la requête les lit.
- Comparable ou inférieur : les données sont lues une fois, le plan est raisonnable.
- Plusieurs fois supérieur : les données sont lues plusieurs fois, par exemple Index Scan dans un nested loop.
Pour les requêtes courtes utilisant un index, ce critère ne s'applique pas.
Mémoire. Si dans BUFFERS il y a temp read/written, que le Sort se fait sur le disque ou que le Hash a Batches > 1, la mémoire est insuffisante. Que faire — dans la section « Que corriger ».

Que corriger

Corrigez d'abord les estimations et la requête elle-même, puis la mémoire. Modifiez les coûts en dernier.

Fonction sur une colonne dans la condition (starts_at::date = …, date_trunc('day', starts_at) = …, round(col) …). Réécrivez en intervalle semi-ouvert starts_at >= $1 AND starts_at < $2. Calculez les limites pour timestamptz dans le fuseau horaire de l'utilisateur, pas du serveur. Pourquoi : pour une expression sans statistiques, l'optimiseur prend une sélectivité fixe (pour =, c'est 0,5 % des lignes) et se trompe fortement. La plage sur la colonne elle-même est évaluée par les statistiques et utilise l'index. Créez un index ou des statistiques sur l'expression seulement si vous ne pouvez pas réécrire.
Vérifiez quel index a été choisi. Si l'index est composite et que la colonne filtrée n'est pas la première, alors avant PG18, il est lu entièrement. Dans PG18, le skip scan aide (Index Searches: N dans le plan), mais il n'est avantageux que lorsque la première colonne a peu de valeurs différentes. Sinon, il faut un index où cette colonne est la première.
Déclarez les FK composites (account_id, user_id), même si l'application vérifie tout elle-même, et écrivez la jointure sur toutes les colonnes de la FK avec des égalités simples. L'optimiseur évalue la jointure sur plusieurs colonnes grâce à la FK. Sans FK, il considère les colonnes comme indépendantes et se trompe fortement (1000 lignes au lieu de 100 000), puis choisit nested loop au lieu de hash. Si la jointure ne couvre pas la FK entièrement (une colonne dans une expression ou dans WHERE), l'évaluation par FK ne s'applique pas. La table cible doit avoir une clé unique sur les mêmes colonnes. Le prix : des coûts d'écriture supplémentaires.
Jointure inutile dans une requête longue. Si la table ne filtre rien et que la relation est garantie par FK et NOT NULL, supprimez-la : c'est souvent le plus grand gain. Ne comptez pas sur l'optimiseur pour supprimer lui-même une inner join sur une autre table, même avec FK : il supprime une LEFT JOIN vers une clé unique dont les colonnes ne sont pas lues par la requête, et depuis PG18, la jointure d'une table avec elle-même sur une clé unique. Sans garantie FK et NOT NULL, ne touchez pas à cette jointure : le résultat changera.
Les estimations sont incorrectes et vous ne pouvez pas réécrire la requête : créez des statistiques étendues ou définissez ALTER TABLE … ALTER COLUMN … SET STATISTICS n (par défaut 100).
Mémoire insuffisante (fichiers temporaires, Batches > 1). Augmentez work_mem ou hash_mem_multiplier pour la session ou le rôle (SET, ALTER ROLE … SET), mais pas globalement : work_mem est alloué pour chaque nœud de chaque session. La limite pour le hash est work_mem × hash_mem_multiplier. Par défaut, c'est 4 MO × 2,0 = 8 MO ; avant PG15, le multiplicateur était 1,0. Vérifiez SHOW et calculez la limite finale.

Hypothèse « l'optimiseur s'est trompé »

Vérifiez dans une transaction : BEGIN; SET LOCAL enable_nestloop = off; (ou enable_seqscan), puis EXPLAIN (ANALYZE, BUFFERS), puis ROLLBACK. Comparez par pages — cela montre s'il existe un meilleur plan.
Ne laissez pas enable_* dans la configuration ni dans le code du service. Ils ne font que déconseiller un nœud à l'optimiseur et affectent toutes les requêtes. La documentation les appelle un moyen grossier et temporaire.
Ensuite, deux directions :
- Les estimations de lignes sont incorrectes — corrigez les estimations (voir « Que corriger »).
- Les estimations sont correctes, mais le plan souhaité n'est systématiquement pas choisi pour plusieurs requêtes — modifiez les coûts. Si vous avez un SSD ou que la base est presque entièrement en cache, réduisez random_page_cost de 4,0 à 1,1–2, mais pas en dessous de seq_page_cost (1,0). Dans effective_cache_size, mettez une estimation réelle : shared_buffers plus le cache OS (par défaut 4 Go). Modifiez pour toute la base seulement après vérification sur plusieurs requêtes.

Hors du skill

pg_query_state — nécessite un noyau patché.
plprofiler et plpgsql_check — pour la logique en PL/pgSQL ; ici la logique est dans l'application.
Techniques nécessitant des builds commerciaux de PostgreSQL.

scary-production/claude-plugins

Qu'est-ce que claude-plugins ?

Demander à votre IA préférée

Documentation