Pour diagnostiquer les recherches PostgreSQL répétées, séparez la construction de l’ensemble de candidats de son utilisation. Consultez le nombre d’exécutions du scan interne, vérifiez si la mise en cache évite de répéter le travail, puis comparez la réutilisation après regroupement, l’inlining d’une CTE ou une table temporaire indexée.

Ce guide s’adresse à un développeur travaillant sur Mac qui doit expliquer pourquoi une requête d’articles est lente : sélectionner les articles candidats une seule fois, puis trouver, pour chaque article, l’ID d’article candidat le plus élevé dans son sujet. Des lignes synthétiques sont utilisées tout au long du guide. L’objectif est de repérer où le travail se répète et de choisir une forme de requête qui préserve le résultat.

Configurer une requête reproductible sur les candidats

Utilisez une installation PostgreSQL locale existante dans laquelle psql est disponible. Ouvrez Terminal, exécutez psql avec vos paramètres de connexion habituels et gardez la même session ouverte pour tous les exemples SQL. La documentation PostgreSQL décrit psql comme son terminal interactif permettant de soumettre des requêtes et d’en consulter les résultats. psql

Commencez par vérifier la version du serveur avec SHOW server_version;. La documentation actuelle consultée pour cet article indique que PostgreSQL 18 est la version courante. Les exemples utilisent une syntaxe compatible avec PostgreSQL 12 ou une version ultérieure ; il s’agit d’une limite de compatibilité, et non d’une recommandation d’utiliser une ancienne version. PostgreSQL 12 a introduit l’inlining des CTE et les contrôles MATERIALIZED et NOT MATERIALIZED. Requêtes WITH, Version 12

Exécutez cette préparation une fois dans une nouvelle session. Elle crée 20 000 articles synthétiques et un index pour les recherches par sujet et ID. Le prédicat de candidats utilisé ci-dessous sélectionne les 2 000 premiers articles.

CREATE TEMP TABLE article_demo (
    id integer PRIMARY KEY,
    topic_id integer NOT NULL,
    title text NOT NULL
);

INSERT INTO article_demo (id, topic_id, title)
SELECT g, 1 + (g % 50), 'Article ' || g
FROM generate_series(1, 20000) AS s(g);

CREATE INDEX article_demo_topic_id_idx
    ON article_demo (topic_id, id);

ANALYZE article_demo;

Par défaut, ces tables temporaires durent le temps de la session. Exécutez explicitement ANALYZE après leur chargement : le processus autovacuum de PostgreSQL n’analyse pas les tables temporaires. CREATE TABLE

Capturez maintenant la référence. La sous-requête latérale calcule l’ID candidat le plus élevé pour le sujet de l’article courant. Ici, « ID le plus élevé » correspond simplement à la tâche définie ; cela n’implique pas une date de publication.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
WITH candidate AS MATERIALIZED (
    SELECT id, topic_id, title
    FROM article_demo
    WHERE id <= 2000
)
SELECT c.id, c.title, m.highest_id
FROM candidate AS c
CROSS JOIN LATERAL (
    SELECT max(x.id) AS highest_id
    FROM candidate AS x
    WHERE x.topic_id = c.topic_id
) AS m;

Le MATERIALIZED explicite rend claire la limite de réutilisation dans cette expérience. Une CTE matérialisée peut être construite une fois, tandis que ses consommateurs relisent plusieurs fois les lignes stockées. La documentation PostgreSQL montre qu’une CTE matérialisée référencée plusieurs fois perd l’avantage des index de la table sous-jacente lorsqu’on recherche dans ce résultat intermédiaire. Matérialisation des expressions de table communes

Les exemples SQL ont été examinés, mais n’ont pas été exécutés sur un serveur PostgreSQL pour cet article. Le calcul des nombres de lignes synthétiques a été vérifié séparément ; aucun temps d’exécution ni plan capturé n’est présenté comme mesure.

Lire le plan pour repérer le travail répété

Commencez par la branche qui produit les candidats, puis suivez chaque consommateur. Dans une boucle imbriquée, identifiez l’entrée externe et la recherche interne. PostgreSQL indique loops comme le nombre d’exécutions d’un nœud ; les lignes réelles et les temps du nœud sont des moyennes par exécution. Une version avec sous-requête scalaire corrélée peut plutôt montrer du travail répété sous SubPlan : le diagnostic doit donc suivre les nœuds qui s’exécutent, sans exiger une étiquette particulière. Utiliser EXPLAIN

Relevez quatre observations dans votre plan réel :

  1. Combien de lignes entrent dans la recherche externe ?
  2. Combien de fois le scan interne sous-jacent s’exécute-t-il ?
  3. Ce scan lit-il des candidats avant d’en éliminer la plupart avec un filtre ?
  4. Y a-t-il un nœud Memoize entre les lignes externes et le travail interne coûteux ?

Distinguez les différents niveaux. Une agrégation peut renvoyer un maximum par exécution alors que son nœud enfant parcourt de nombreuses lignes pour le calculer. Le fait que le producteur de candidats ne s’exécute qu’une fois ne prouve donc pas que leur utilisation est peu coûteuse.

Dans cet échantillon précis, 2 000 candidats couvrent 50 sujets, avec 40 articles par sujet. Le calcul ci-dessous décrit des schémas de travail possibles, et non le résultat garanti du plan ou le temps d’exécution :

Schéma d’utilisation Nombre illustratif d’examens de lignes candidates
Parcours complet des candidats pour chaque article externe 2 000 × 2 000 = 4 000 000
Parcours complet une fois par sujet, avec mise en cache des recherches ultérieures 50 × 2 000 = 100 000
Regroupement des candidats une seule fois par sujet 2 000 lignes transmises à l’étape de regroupement, puis travail de jointure ultérieur

Cette distinction compte sur PostgreSQL 14 et versions ultérieures, qui ont introduit la mémorisation des résultats de boucles imbriquées internes. La documentation actuelle décrit Memoize comme un nœud qui met en cache les résultats des scans paramétrés et permet d’ignorer le scan sous-jacent en cas de succès du cache ; les entrées peuvent également être évincées. Examinez la clé de cache ainsi que les éventuels succès, échecs, évictions et dépassements signalés avant d’attribuer des milliers de scans complets aux milliers de lignes externes. Version 14, Planification des requêtes

Comparez également les lignes estimées aux lignes réelles au premier écart important. PostgreSQL recommande de vérifier ces estimations et avertit que les plans obtenus avec de petites tables de test ne s’extrapolent pas nécessairement à des entrées plus volumineuses. Évitez d’additionner les temps des nœuds parents et enfants : ils décrivent des travaux d’exécution qui se chevauchent. Utiliser EXPLAIN

Choisir une réécriture adaptée à la tâche répétée

La question utile est de savoir quelle quantité de travail chaque consommateur doit effectuer sur l’ensemble des candidats. Utilisez cet aide-mémoire pour choisir la première expérience :

Besoin observé Première expérience Condition à préserver
Chaque candidat a besoin du même maximum par sujet Regrouper une fois, puis joindre Une ligne de synthèse par sujet
Chaque consommateur a besoin d’une petite portion indexée de la source Essayer NOT MATERIALIZED Conserver le prédicat complet des candidats
Plusieurs instructions ont besoin d’une relation de candidats explicitement indexée Créer une table temporaire indexée Garder la même session et inclure le coût de préparation
Les paramètres répétés génèrent déjà de nombreux succès du cache Comparer les temps d’exécution totaux avant de modifier la forme Confirmer que les scans sous-jacents sont réellement coûteux

Calculer une seule fois le résultat par sujet

Pour cette tâche, une synthèse regroupée exprime directement la réutilisation. Exécutez ce qui suit après la préparation, dans la même session :

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
WITH candidate AS MATERIALIZED (
    SELECT id, topic_id, title
    FROM article_demo
    WHERE id <= 2000
), topic_max AS (
    SELECT topic_id, max(id) AS highest_id
    FROM candidate
    GROUP BY topic_id
)
SELECT c.id, c.title, m.highest_id
FROM candidate AS c
JOIN topic_max AS m USING (topic_id);

Cette réécriture calcule un maximum par sujet et l’associe à chaque article candidat. PostgreSQL définit max comme la valeur maximale non nulle de l’entrée. Dans cet échantillon, les colonnes sujet et ID ne sont pas nulles, ce qui rend l’équivalence simple : chaque candidat appartient à un groupe qui le contient. Fonctions d’agrégation

Attendez-vous à 2 000 lignes de résultat si vous exécutez la requête sans EXPLAIN. Par exemple, l’article 1 appartient au sujet 2 et reçoit highest_id = 1951 ; le sujet 1 reçoit highest_id = 2000. Ces résultats découlent de façon déterministe du générateur d’échantillon.

Vérifiez le sens du résultat avant de généraliser. Un regroupement sur des colonnes supplémentaires pourrait créer plusieurs lignes de synthèse par sujet et multiplier les lignes de la jointure. Si les ID de sujet peuvent être nuls, il faut également réexaminer la sémantique de l’égalité. Si la tâche exige le titre de l’article ayant l’ID le plus élevé, il faut ajouter une jointure avec le maximum aux lignes candidates ; calculer séparément le titre maximum répondrait à une autre question.

Cette réécriture permet un plan différent, mais n’impose pas de jointure par hachage et ne garantit pas un gain de vitesse. Comparez le travail de regroupement et de jointure mesuré au nombre réel d’exécutions internes de la requête de référence.

Vérifier si les index de la source peuvent remplacer les scans intermédiaires

Pour une CTE admissible, remplacez AS MATERIALIZED dans la requête complète de référence par AS NOT MATERIALIZED, puis relancez-la. Cela permet une optimisation conjointe avec la relation source, ce qui peut remplacer les scans des candidats stockés par des recherches dans la source. Cela peut aussi dupliquer le calcul des candidats. Matérialisation des expressions de table communes

L’index (topic_id, id) de l’échantillon correspond à une égalité sur la première colonne, celle du sujet, et à une restriction sur la colonne ID. C’est donc un index pertinent à tester ; vérifiez si le plan réel l’utilise et combien de lignes il parcourt. PostgreSQL indique que l’égalité sur la première colonne et l’inégalité sur la colonne suivante sont des conditions qui limitent un scan d’arbre B multicolonne. Index multicolonnes

Conservez id <= 2000 dans la définition des candidats. Rechercher le maximum parmi les 20 000 lignes source modifierait le résultat. Respectez également la limite documentée : NOT MATERIALIZED est ignoré pour les CTE récursives et celles qui ne sont pas sans effet de bord. Pour une CTE non admissible, utilisez plutôt une réécriture relationnelle prise en charge ou stockez explicitement son résultat. SELECT

Stocker les candidats dans une relation indexée lorsque la réutilisation s’étend à plusieurs instructions

Une CTE ne s’applique qu’à une seule instruction. Si plusieurs instructions ont besoin des mêmes lignes candidates et d’index explicites, une table temporaire offre une autre durée de vie et une autre option d’indexation. Exécutez cette variante après la préparation initiale :

CREATE TEMP TABLE candidate_demo AS
SELECT id, topic_id, title
FROM article_demo
WHERE id <= 2000;

CREATE INDEX candidate_demo_topic_id_idx
    ON candidate_demo (topic_id, id);

ANALYZE candidate_demo;

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT c.id, c.title, m.highest_id
FROM candidate_demo AS c
CROSS JOIN LATERAL (
    SELECT max(x.id) AS highest_id
    FROM candidate_demo AS x
    WHERE x.topic_id = c.topic_id
) AS m;

Les index des tables temporaires sont eux aussi temporaires, et la relation est limitée à la session. Pour évaluer l’intérêt de cette étape, incluez la création, la construction de l’index et l’analyse. Ne comparez pas uniquement sa recherche finale à l’ensemble de l’opération sans stockage intermédiaire. CREATE TABLE

Capturer la même expérience avec node-postgres sur votre Mac

Pour examiner le chemin de requête de l’application, utilisez une version de Node.js actuellement prise en charge et installez le paquet documenté avec npm install pg. Enregistrez le programme complet ci-dessous sous diagnose.mjs dans ce projet, puis exécutez node diagnose.mjs. Il crée ses propres données synthétiques et ne dépend donc pas de la session psql précédente. node-postgres

Le programme utilise l’environnement de connexion PostgreSQL existant. node-postgres documente les mêmes variables d’environnement de connexion que psql, notamment PGHOST, PGPORT et PGDATABASE. Connexion

import pg from 'pg';
const { Client } = pg;
const client = new Client();

try {
  await client.connect();
  const version = await client.query('SHOW server_version');
  console.log(version.rows[0]);

  await client.query(`
    CREATE TEMP TABLE article_demo (
      id integer PRIMARY KEY,
      topic_id integer NOT NULL,
      title text NOT NULL
    );
    INSERT INTO article_demo (id, topic_id, title)
    SELECT g, 1 + (g % 50), 'Article ' || g
    FROM generate_series(1, 20000) AS s(g);
    CREATE INDEX article_demo_topic_id_idx
      ON article_demo (topic_id, id);
    ANALYZE article_demo;
  `);

  const sql = `
    WITH candidate AS MATERIALIZED (
      SELECT id, topic_id, title
      FROM article_demo
      WHERE id <= $1::integer
    )
    SELECT c.id, c.title, m.highest_id
    FROM candidate AS c
    CROSS JOIN LATERAL (
      SELECT max(x.id) AS highest_id
      FROM candidate AS x
      WHERE x.topic_id = c.topic_id
    ) AS m
  `;

  const result = await client.query({
    text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) ${sql}`,
    values: [2000]
  });

  for (const row of result.rows) {
    console.log(row['QUERY PLAN']);
  }
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  await client.end();
}

La configuration de requête utilise les champs documentés text et values. L’ajout de name crée une instruction préparée sur chaque connexion ; il ne met pas en cache les lignes candidates et ne supprime pas les exécutions internes répétées. Requêtes

Si une application existante utilise un pool, gardez la création et l’utilisation de la table temporaire sur un même client emprunté au pool, puis libérez-le dans finally. Des appels distincts à pool.query peuvent utiliser différents clients. De même, node-postgres exige que toutes les instructions d’une transaction utilisent la même instance de client. Pooling, Transactions

Pour les instructions nommées dont le comportement varie selon les valeurs des paramètres, examinez séparément la planification. PostgreSQL peut choisir des plans génériques ou des plans personnalisés en fonction des paramètres ; la méthode d’inspection documentée consiste à utiliser EXPLAIN EXECUTE sur l’instruction préparée. Une requête sans nom expliquée à la volée peut donc différer d’une exécution préparée établie. PREPARE

Déterminer si le travail répété a réellement diminué

Utilisez le même prédicat de candidats, les mêmes colonnes sélectionnées et les mêmes valeurs d’entrée pour chaque comparaison. Faites une première passe, puis répétez des passes comparables pour observer les variations du cache. Notez la version du serveur, le nombre de candidats, le nombre d’exécutions du scan interne, le comportement du cache et le temps d’exécution total. PostgreSQL 17 a amélioré la planification des CTE à l’aide des statistiques des colonnes et de l’ordre de tri ; conservez donc la version lorsque vous comparez les résultats de différentes machines. Version 17

Pour un second passage de diagnostic, EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) conserve le nombre de lignes et le temps d’exécution de l’instruction tout en évitant les mesures d’horloge pour chaque nœud. Les totaux de tampons des nœuds parents incluent l’activité des nœuds enfants : ne faites donc pas la somme de tout l’arbre. EXPLAIN ANALYZE exécute la requête, mais ne mesure pas la transmission des lignes de résultat à Node.js. EXPLAIN

Considérez les paramètres de planification comme des expériences. Désactiver enable_nestloop décourage les boucles imbriquées, sans pouvoir les éliminer entièrement ; enable_material ne peut pas non plus empêcher toute matérialisation. Aucun de ces paramètres ne prouve que l’utilisation des candidats est devenue efficace. Planification des requêtes

Terminez avec la forme de requête qui préserve les lignes requises et réduit le travail mesuré pour la tâche complète. Pour ce maximum par sujet, la réutilisation après regroupement constitue la première expérience la plus claire. Conservez les recherches indexées lorsqu’elles s’avèrent peu coûteuses et utilisez un stockage explicite lorsque son coût de préparation est justifié par plusieurs consommateurs.

Sources