Comparez les plans PostgreSQL en associant les temps d’exécution répétés aux nombres de tampons du nœud racine. Ce guide pas à pas dans le Terminal de Mac utilise des lignes d’articles synthétiques pour vérifier si un index réduit le travail de la base de données, tout en tenant compte des effets du cache qui peuvent rendre une exécution trompeuse.

Quelles mesures faut-il relever ensemble ?

Votre tâche consiste à comparer la même requête de liste d’articles avant et après l’ajout d’un index. Gardez les colonnes sélectionnées, la catégorie, l’ordre, la limite et les données inchangés. Relevez à la fois le temps d’exécution écoulé et l’activité des tampons nécessaire pour produire le résultat.

Utilisez EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, FORMAT JSON). L’option ANALYZE exécute la requête ; TIMING OFF désactive les mesures d’horloge de chaque nœud tout en conservant le temps d’exécution de l’instruction entière. Un format lisible par machine facilite la collecte de mesures répétées. PostgreSQL : EXPLAIN

Interprétez ces mesures ensemble :

Mesure Ce qu’elle indique
Execution Time Durée d’exécution côté serveur, hors planification
Planning Time Temps passé à générer et à optimiser le plan
Shared Hit Blocks Accès aux tampons servis depuis le cache partagé de PostgreSQL
Shared Read Blocks Blocs chargés dans les tampons partagés
Hits partagés plus lectures Comparaison utile du volume d’accès aux tampons partagés
Temp Read Blocks et Temp Written Blocks Activité des fichiers de travail temporaires, par exemple un tri qui déborde de la mémoire

Les blocs partagés appartiennent aux tables et aux index ordinaires. Les blocs locaux appartiennent aux tables et aux index temporaires ; les blocs des fichiers de travail temporaires constituent une catégorie distincte. Un nœud parent inclut l’activité de tampon de ses enfants : utilisez donc le nœud racine pour les totaux d’exécution et examinez les enfants pour localiser le travail. Ne faites jamais la somme des nombres de tous les nœuds. PostgreSQL : EXPLAIN

Les nombres de tampons représentent des accès non distincts : un bloc revisité peut être compté à nouveau. Par ailleurs, les coûts du planificateur sont exprimés en unités arbitraires, pas en millisecondes. Comparez les temps d’exécution réels aux nombres réels de tampons ; gardez les coûts estimés pour comprendre le choix du planificateur. PostgreSQL : Utilisation de EXPLAIN

Une lecture partagée ne prouve pas que le support de stockage de votre Mac a effectué une lecture physique. Les statistiques d’E/S de PostgreSQL ne permettent pas de distinguer les données récupérées sur le stockage de celles déjà présentes dans le cache de pages du système d’exploitation. Parlez de lectures de tampons partagés, sauf si vous disposez de données distinctes sur le stockage. PostgreSQL : Le système de statistiques cumulatives

Préparer l’expérience sur Mac et vérifier les différences entre versions

Commencez par une base de données PostgreSQL locale en cours d’exécution et Node.js avec npm disponible dans Terminal. Ce guide vise PostgreSQL 18 et utilise Node.js 22 ou 24. La documentation de node-postgres indique que ces versions de Node.js sont compatibles et décrit l’installation avec npm install pg. node-postgres

La commande de comparaison figure également dans la documentation de PostgreSQL 16 et 17. PostgreSQL 18 inclut automatiquement la sortie des tampons avec EXPLAIN ANALYZE ; préciser BUFFERS rend l’intention explicite dans ces versions. Ne supposez pas que les paramètres facultatifs plus récents existent sur les anciens serveurs. PostgreSQL 16 : EXPLAIN, PostgreSQL 17 : EXPLAIN, Notes de version de PostgreSQL 18

Dans un nouveau répertoire de travail, installez le pilote :

mkdir pg-plan-comparison
cd pg-plan-comparison
npm init -y
npm install pg

Utilisez une base de données locale d’entraînement existante à laquelle votre connexion PostgreSQL habituelle peut accéder. Le script utilise new Client(), qui lit les mêmes variables d’environnement de connexion que psql. Par exemple, si cette base de données s’appelle plan_lab, exécutez le script avec PGDATABASE=plan_lab. Ajoutez vos paramètres PGHOST ou PGPORT habituels si nécessaire. node-postgres : Connexion

Le programme crée 100 000 articles synthétiques dans une transaction, compare deux états du schéma, puis annule la transaction. Il utilise une table ordinaire afin que le parcours apparaisse dans les tampons partagés. PostgreSQL précise que ROLLBACK annule les mises à jour de la transaction. Si le nom de la table de démonstration existe déjà, la création échoue au lieu de la remplacer. PostgreSQL : ROLLBACK

Exécuter une comparaison complète avant et après

Enregistrez le programme complet suivant sous le nom compare-plans.mjs. Il génère ses propres données d’entrée, effectue un échauffement non enregistré pour chaque phase, conserve sept exécutions mesurées et affiche le premier plan mesuré de chaque phase. Le générateur de lignes utilise la fonction generate_series documentée par PostgreSQL. PostgreSQL : Fonctions renvoyant des ensembles

import pg from 'pg';

const { Client } = pg;
const client = new Client();
const query = `
  SELECT article_id, title, published_at
  FROM plan_compare_articles_demo
  WHERE category_id = $1
  ORDER BY published_at DESC, article_id DESC
  LIMIT $2
`;
const values = [7, 50];
const explain = `
  EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, FORMAT JSON)
  ${query}
`;
const median = values =>
  [...values].sort((a, b) => a - b)[Math.floor(values.length / 2)];

async function measure(label) {
  await client.query(explain, values);
  const runs = [];
  let firstPlan;
  for (let run = 1; run <= 7; run++) {
    const result = await client.query(explain, values);
    const document = result.rows[0]['QUERY PLAN'][0];
    const root = document.Plan;
    firstPlan ??= document;
    const hits = root['Shared Hit Blocks'] ?? 0;
    const reads = root['Shared Read Blocks'] ?? 0;
    runs.push({
      phase: label,
      run,
      execution_ms: document['Execution Time'],
      planning_ms: document['Planning Time'],
      shared_hit: hits,
      shared_read: reads,
      shared_accesses: hits + reads,
      temp_read: root['Temp Read Blocks'] ?? 0,
      temp_written: root['Temp Written Blocks'] ?? 0
    });
  }
  console.table(runs);
  console.log(label, 'median execution ms:',
    median(runs.map(run => run.execution_ms)));
  console.dir(firstPlan, { depth: null });
}

try {
  await client.connect();
  console.log((await client.query('SHOW server_version')).rows[0]);
  await client.query('BEGIN');
  await client.query(`
    CREATE TABLE plan_compare_articles_demo (
      article_id integer PRIMARY KEY,
      category_id integer NOT NULL,
      title text NOT NULL,
      published_at timestamp NOT NULL,
      body text NOT NULL
    )
  `);
  await client.query(`
    INSERT INTO plan_compare_articles_demo
      (article_id, category_id, title, published_at, body)
    SELECT g, g % 20, 'Article ' || g,
           timestamp '2025-01-01' + g * interval '1 minute',
           repeat('Synthetic article text. ', 12)
    FROM generate_series(1, 100000) AS series(g)
  `);
  await client.query('ANALYZE plan_compare_articles_demo');
  const before = await client.query(query, values);
  await measure('before index');

  await client.query(`
    CREATE INDEX plan_compare_articles_demo_list_idx
    ON plan_compare_articles_demo
      (category_id, published_at DESC, article_id DESC)
  `);
  const after = await client.query(query, values);
  if (JSON.stringify(before.rows) !== JSON.stringify(after.rows)) {
    throw new Error('Result mismatch between schema states');
  }
  await measure('after index');
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  try {
    await client.query('ROLLBACK');
  } finally {
    await client.end();
  }
}

Exécutez-le depuis le même répertoire, en remplaçant par le nom réel de votre base de données d’entraînement :

PGDATABASE=plan_lab node compare-plans.mjs

Le programme transmet séparément les instructions SQL et les valeurs des paramètres via client.query(text, values). Il lit le plan dans la colonne renvoyée nommée QUERY PLAN ; node-postgres associe les colonnes du résultat aux propriétés d’un objet et analyse le JSON de PostgreSQL en objets JavaScript. node-postgres : Requêtes, node-postgres : pg.Result, node-postgres : Types de données

La commande autonome ANALYZE collecte les statistiques de la table pour le planificateur. Elle diffère de l’option ANALYZE qui mesure l’exécution dans EXPLAIN. Ici, la collecte de statistiques après le chargement des lignes synthétiques donne aux deux phases les mêmes informations de départ sur la table. PostgreSQL : ANALYZE

Les calculs de médiane et de total des tampons en JavaScript ont été vérifiés à l’aide d’entrées synthétiques déterministes. L’expérience complète avec la base de données n’a pas été exécutée pendant la préparation, car PostgreSQL n’était pas disponible dans l’environnement de recherche ; l’article ne fournit aucun résultat de benchmark mesuré.

Qu’est-ce qui devrait changer dans les deux plans ?

L’index candidat place d’abord la catégorie filtrée par égalité, puis les colonnes de tri demandées. PostgreSQL précise que les conditions d’égalité sur les colonnes initiales d’un index B-tree limitent la plage d’index parcourue. Cet index constitue donc un candidat raisonné pour la requête. PostgreSQL : Index multicolonnes

Avant l’indexation, recherchez un parcours et un tri, puis un parcours d’index ordonné sous la limite après l’indexation. Ce sont des résultats à vérifier dans votre sortie, pas des formes de plan garanties. Un index B-tree correspondant peut fournir directement des lignes ordonnées, et associer un ordre à une limite peut éviter de traiter les correspondances restantes. PostgreSQL : Index et ORDER BY

Examinez trois éléments dans les plans affichés :

  1. Vérifiez que le nœud racine renvoie 50 lignes dans les deux phases et que le programme ne signale aucune différence dans les résultats.
  2. Repérez les nœuds responsables du parcours et du tri. Comparez leurs estimations de lignes aux lignes réelles et examinez où l’activité des tampons se produit.
  3. Comparez les accès partagés du nœud racine et les temps d’exécution répétés. Un simple changement de nom de nœud ne suffit pas à établir une amélioration utile.

Il s’agit d’une comparaison par phases avec un cache chaud. L’insertion des lignes, les requêtes de vérification, les échauffements et la création de l’index influent tous sur l’état du cache. Gardez la même charge de travail sur le Mac et la même configuration de base de données pendant les deux phases. Conservez les sept observations individuelles afin qu’une exécution anormalement lente reste visible ; une simple médiane peut masquer les variations.

Pour un suivi plus solide, répétez toute l’expérience et testez d’autres catégories et limites. Si vous testez une limite plus élevée, identifiez-la comme une charge de travail distincte. La comparaison initiale permet de savoir si cet index aide pour cette demande précise portant sur 50 articles dans les conditions locales observées.

Comment distinguer une réduction du travail de tampons plus chauds ?

Les chiffres suivants sont des valeurs illustratives, pas des résultats enregistrés. Ils montrent pourquoi il faut considérer ensemble les lectures, les hits et le temps :

Exécution illustrative Exécution Hits partagés Lectures partagées Hits + lectures
A, observation initiale 12 ms 900 100 1 000
A, observation répétée 10 ms 1 000 0 1 000
B, plan candidat 3 ms 80 0 80

Entre les deux observations de A, les lectures disparaissent alors que le total des accès partagés reste à 1 000. Cela correspond à un changement de l’état du cache ; cela ne démontre pas une réduction du volume d’accès aux tampons partagés. B associe une durée plus courte à un nombre d’accès inférieur. Si cette tendance se répète avec des résultats équivalents, elle étaye la conclusion plus restreinte selon laquelle B effectue moins de travail sur les tampons partagés pour cette tâche.

Utilisez cette aide à la décision si vos propres résultats diffèrent :

Tendance observée Interprétation et prochaine étape
Temps réduit et moins d’accès partagés Indice d’amélioration pour la charge de travail testée ; répétez pour vérifier la cohérence
Temps réduit, moins de lectures, total d’accès similaire L’état du cache peut expliquer le changement ; comparez des répétitions avec cache chaud
Moins d’accès, mais temps similaire ou supérieur Examinez le tri, les expressions et les variations de temps avant de choisir
Zéro lecture, mais beaucoup de hits Une exécution depuis le cache effectue tout de même des accès aux tampons ; examinez les nœuds à forte activité
Des lectures ou écritures temporaires apparaissent Examinez l’opération de travail qui les a produites avant de modifier les paramètres

Évitez de faire du pourcentage de hits en cache le critère décisif. Dans l’exemple, A avec cache chaud et B ont tous deux un taux de hits dans les tampons partagés de 100 %, alors que leurs nombres d’accès diffèrent fortement. Gardez les nombres bruts et la taille du résultat à côté des temps.

Quel est le lien entre ces mesures et la vitesse de l’application ?

Le Execution Time du serveur exclut la planification ; EXPLAIN ANALYZE ordinaire omet également la transmission des résultats et, par défaut, la conversion de sortie. L’instrumentation ajoute elle-même une surcharge. Le script compare donc l’exécution instrumentée de la base de données, plutôt que de mesurer le temps total nécessaire à l’affichage d’une liste d’articles. PostgreSQL : Utilisation de EXPLAIN

Pour étudier séparément la conversion de sortie dans PostgreSQL 17 ou 18, ajoutez SERIALIZE TEXT aux mêmes options d’explication. PostgreSQL 16 ne répertorie pas cette option. La sérialisation ne transmet toujours pas les lignes de résultat de la requête et ne permet donc pas de mesurer leur acheminement sur le réseau. Gardez les paramètres de sérialisation identiques entre les exécutions comparées. PostgreSQL 17 : EXPLAIN, PostgreSQL 16 : EXPLAIN

Pour une mesure côté application, chronométrez l’SELECT ordinaire des articles jusqu’à la fin de l’appel du pilote attendu, en ayant déjà établi la connexion. Consignez ce résultat comme une mesure distincte du temps écoulé côté client. Conservez la configuration de requête réelle de l’application : ajouter un name de requête node-postgres active le comportement d’instruction préparée par connexion et modifie les conditions de comparaison. node-postgres : Requêtes

Choisissez le candidat lorsque les résultats équivalents, les temps répétés et les nombres de tampons racontent une histoire cohérente pour la requête d’articles à traiter. Conservez les plans complets, les exécutions individuelles, la version du serveur et les paramètres de requête afin que la conclusion reste spécifique et reproductible.

Sources