Sélectionner le corps d’un article peut exclure une analyse exclusivement par index : PostgreSQL doit récupérer une colonne que l’index ne contient pas. Ce guide explique comment diagnostiquer ce changement sur un Mac et en mesurer l’effet dans Node.js. Conditions requises pour une analyse exclusivement par index.

Pourquoi le changement des colonnes sélectionnées modifie-t-il le plan ?

Considérons une table d’articles contenant id, category et body, ainsi qu’un index B-tree sur (category, id). Une requête qui sélectionne des identifiants par catégorie peut obtenir de cet index à la fois la valeur de filtrage et le résultat. L’ajout de body rend cet index insuffisant pour une analyse exclusivement par index. Les index PostgreSQL sont distincts du stockage principal de la table, appelé le tas ; une analyse d’index ordinaire récupère aussi les données de la table. Analyses exclusivement par index et index couvrants.

Le planificateur compare alors les moyens de récupérer les lignes requises de la table. Il peut conserver une Index Scan, combiner une Bitmap Index Scan avec une Bitmap Heap Scan, ou choisir une Seq Scan. Les analyses bitmap regroupent les accès au tas selon leur emplacement physique. Lorsque de nombreuses lignes dispersées doivent être récupérées, parcourir la table peut coûter moins cher que de les rechercher séparément. Un plan bitmap utilise toujours un index, même si le nœud d’analyse supérieur mentionne le tas. Utiliser EXPLAIN.

Vérifiez aussi attentivement le plan initial. Une Index Only Scan peut afficher une valeur non nulle pour Heap Fetches : PostgreSQL doit parfois consulter les lignes de la table pour établir leur visibilité. Éviter ces accès dépend de la carte de visibilité de la table, ainsi que de la présence des colonnes requises dans l’index. Vérifications de visibilité pour les analyses exclusivement par index.

Reproduire la comparaison depuis votre Mac

Utilisez une base PostgreSQL locale en cours d’exécution et une version de Node.js installée et actuellement prise en charge, avec npm. L’exemple de référence utilise des fonctionnalités disponibles dans PostgreSQL 14 à 18 ; le manuel PostgreSQL actuel consulté indique la version 18. Le diagnostic de sérialisation supplémentaire ci-dessous nécessite PostgreSQL 17 ou une version ultérieure. La documentation de node-postgres décrit sa compatibilité avec Node.js et sa commande d’installation. Documentation PostgreSQL, Installation et compatibilité de node-postgres.

Ouvrez Terminal et créez un nouveau dossier de travail. Ces commandes créent le projet et installent le pilote de base de données :

mkdir article-plan-lab
cd article-plan-lab
npm init -y
npm install pg

Le programme utilise vos paramètres de connexion locale existants via PGHOST, PGPORT, PGDATABASE et PGUSER. Configurez-les pour une base de données à laquelle vous pouvez déjà vous connecter. node-postgres lit ces variables d’environnement lors de la création d’un Client sans options de connexion explicites. Connexion avec des variables d’environnement.

Enregistrez le programme complet suivant sous article-plan.cjs. Il crée des lignes synthétiques dans une table temporaire, construit un index, affiche les deux plans d’exécution, puis mesure les deux requêtes ordinaires. Les deux comparaisons utilisent la même connexion et la même valeur de catégorie. La configuration de requête fournit le SQL via text et le paramètre via values, conformément à l’API documentée de node-postgres. Configuration de requête.

const { Client } = require('pg');
const { performance } = require('node:perf_hooks');

async function main() {
  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_plan_demo (
      id integer PRIMARY KEY,
      category integer NOT NULL,
      body text NOT NULL
    )`);
    await client.query(`INSERT INTO article_plan_demo
      SELECT g, g % 10, repeat('Synthetic article paragraph. ', 40)
      FROM generate_series(1, 50000) AS s(g)`);
    await client.query(`CREATE INDEX article_plan_demo_category_id
      ON article_plan_demo (category, id)`);
    await client.query('VACUUM (ANALYZE) article_plan_demo');

    const queries = [
      ['IDs', 'SELECT id FROM article_plan_demo WHERE category = $1'],
      ['Bodies', 'SELECT id, body FROM article_plan_demo WHERE category = $1']
    ];

    for (const [label, text] of queries) {
      const plan = await client.query({
        text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE) ${text}`,
        values: [3]
      });
      console.log(label);
      console.log(plan.rows.map(row => row['QUERY PLAN']).join('\n'));

      const start = performance.now();
      const result = await client.query({ text, values: [3] });
      const elapsed = performance.now() - start;
      const bodyBytes = result.rows.reduce((sum, row) =>
        sum + (row.body === undefined ? 0 : Buffer.byteLength(row.body)), 0);
      console.log({
        rows: result.rows.length,
        elapsedMs: elapsed.toFixed(2),
        bodyBytes
      });
    }
  } finally {
    await client.end();
  }
}

main().catch(error => {
  console.error(error.message);
  process.exitCode = 1;
});

Lancez-le depuis le même dossier :

node article-plan.cjs

La configuration envoie VACUUM (ANALYZE) comme commande distincte. Cette commande effectue un nettoyage, puis collecte des statistiques, et ne peut pas être exécutée dans un bloc de transaction. Gardez-la en dehors de tout bloc de transaction que vous ajouteriez à l’exemple. VACUUM.

La syntaxe JavaScript a été vérifiée, et un calcul déterministe distinct a confirmé le nombre de lignes de l’échantillon et le total d’octets des corps. PostgreSQL n’était pas disponible dans l’environnement de recherche ; ces requêtes de base de données ont donc été examinées, mais pas exécutées ici. Aucun plan ni temps d’exécution particulier n’est présenté comme un résultat observé.

Lire les plans dans un ordre utile

Commencez par les noms des analyses, puis comparez les lignes estimées et réelles. Examinez ensuite l’accès à la table et l’activité des tampons. Cette séquence permet de distinguer un changement des méthodes d’accès disponibles d’une estimation inexacte ou du coût élevé du résultat. VERBOSE expose également les colonnes produites par chaque nœud. Utiliser EXPLAIN, Options d’EXPLAIN.

Observation Interprétation Vérification suivante
Les identifiants utilisent Index Only Scan ; les corps utilisent Index Scan La requête sur les corps a besoin des données de la table Comparez les tampons et le temps des requêtes ordinaires
Les corps utilisent Bitmap Heap Scan Examinez le nœud enfant pour vérifier l’utilisation d’un index Comparez les blocs du tas et les lignes renvoyées
Les corps utilisent Seq Scan Le planificateur préfère parcourir la table Comparez les lignes correspondantes estimées et réelles
Les identifiants affichent un nombre important de Heap Fetches L’exécution exclusivement par index consulte tout de même le tas Vérifiez si cela reflète des modifications récentes de la table
Les estimations diffèrent nettement du nombre réel de lignes Les statistiques décrivent peut-être mal les données Collectez des statistiques et comparez à nouveau

ANALYZE collecte les statistiques dont le planificateur se sert pour choisir les plans. Si vous modifiez la distribution synthétique des catégories, collectez de nouveau les statistiques avant d’interpréter la comparaison suivante. L’échantillonnage peut entraîner de légères variations des estimations entre les exécutions. ANALYZE.

Interprétez width comme une estimation du nombre d’octets produits par ligne, et non comme une mesure des octets transférés. De même, les coûts du planificateur sont exprimés en unités relatives, et non en millisecondes. Les accès aux tampons indiquent que des blocs ont été trouvés dans le cache de PostgreSQL ; cela ne signifie pas qu’aucun travail n’a été effectué. Estimations du plan, Rapport sur les tampons.

Pour cet échantillon, les deux requêtes devraient renvoyer 5 000 lignes. Chaque corps synthétique contient 1 160 octets ASCII, donc la requête sur les corps renvoie 5 800 000 octets de contenu, soit environ 5,53 Mio, sans compter les identifiants ni la surcharge du protocole. Il s’agit d’un calcul basé sur les données d’exemple, et non d’une mesure des performances de la base de données. Le champ bodyBytes du programme permet de vérifier cette distinction.

La requête ordinaire s’exécute après la mesure de son plan ; son temps écoulé correspond donc à cette exécution à chaud. Répétez les comparaisons et alternez leur ordre avant de tirer des conclusions à partir de faibles écarts de temps. Une seule exécution est utile pour le diagnostic, mais elle ne permet pas d’établir un rapport de performances fiable.

Pourquoi la récupération des corps peut-elle prendre plus de temps que ne le laisse penser EXPLAIN ?

Les grandes valeurs textuelles peuvent être compressées ou stockées hors de la table principale grâce au mécanisme TOAST de PostgreSQL. Récupérer l’entrée principale d’une ligne et produire son corps complet pour le client peut donc nécessiter des volumes de travail différents. L’échantillon court et répétitif ci-dessus sert à illustrer la couverture des colonnes ; il ne représente pas tous les modes de stockage des corps. Stockage TOAST.

Par défaut, EXPLAIN ANALYZE écarte les résultats de la requête sans les convertir pour leur transmission. Dans PostgreSQL 17 et les versions ultérieures, ajoutez SERIALIZE TEXT pour examiner la conversion de la sortie, notamment la récupération des valeurs stockées hors ligne lorsque nécessaire. Cette option a été introduite dans PostgreSQL 17. Comportement de sérialisation d’EXPLAIN, Notes de version de PostgreSQL 17.

Pour essayer cette option, remplacez la ligne de modèle EXPLAIN existante du programme par ce fragment et relancez le programme entier :

text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SERIALIZE TEXT) ${text}`,

Avec PostgreSQL 14 à 16, conservez la ligne d’origine et utilisez le chronométrage de la requête ordinaire pour mesurer la réception du résultat. SERIALIZE n’envoie toujours pas les données du résultat au client ; il ne peut donc mesurer ni le transfert ni le traitement JavaScript. Limites d’EXPLAIN.

node-postgres rassemble normalement les lignes du résultat dans des objets JavaScript. Son option rowMode: 'array' modifie la représentation du résultat ; elle ne retire pas du résultat SQL les valeurs des corps sélectionnés. Elle ne peut donc pas rétablir la couverture des colonnes perdue lorsque body est ajouté à cette requête. Mode des lignes.

Choisir une modification adaptée à la tâche sur les articles

Si la tâche sur ordinateur nécessite uniquement une liste d’identifiants, limitez la projection. Si elle nécessite le texte intégral des articles, évaluez le temps réellement écoulé pour la requête sur les corps et la taille du résultat. Le diagnostic vise à repérer le travail superflu tout en préservant le résultat demandé.

Pour un écran qui n’affiche que les 50 premiers articles d’une catégorie, limiter le résultat demandé constitue une solution de remplacement pertinente. Dans le tableau queries du programme, remplacez la chaîne de requête sur les corps par ce SQL :

SELECT id, body
FROM article_plan_demo
WHERE category = $1
ORDER BY id
LIMIT 50

Cela modifie délibérément la tâche : elle consiste alors à récupérer une page triée plutôt que tous les articles correspondants. Une fois la catégorie fixée, l’index existant (category, id) offre une voie possible dans l’ordre des identifiants. Un index B-tree correspondant peut rendre ORDER BY avec LIMIT efficace, car l’exécution peut s’arrêter dès que suffisamment de lignes ont été trouvées. Le plan choisi reste à examiner. Index et ORDER BY.

Ne considérez pas INCLUDE (body) comme une solution automatique. Les colonnes incluses dupliquent les données de la table, augmentent la taille de l’index et peuvent faire échouer une insertion si un tuple d’index dépasse sa limite de taille. Les petites colonnes de données peuvent être de bons candidats à des expérimentations mesurées. INCLUDE a été introduit dans PostgreSQL 11 ; il n’est pas disponible dans les versions antérieures. Mises en garde sur les index couvrants, Notes de version de PostgreSQL 11.

Terminez l’analyse avec trois constats concrets : la sélection des corps a-t-elle supprimé l’admissibilité à l’analyse exclusivement par index, la nouvelle analyse offre-t-elle des performances acceptables, et combien de temps faut-il pour que l’intégralité du résultat parvienne à Node.js ? Ces constats vous indiqueront s’il faut conserver le plan, demander moins d’articles ou tester un autre index pour la requête réelle.

Sources