PostgreSQL peut utiliser un index pour id < 11 et effectuer un parcours séquentiel pour id > 11, car la première condition correspond à très peu de lignes, tandis que la seconde en correspond à presque toutes celles de la table. Les deux opérateurs prennent en charge les index B-tree ; le planificateur compare leurs coûts estimés.

Ce guide aide un développeur travaillant sur Mac à reproduire ce choix avec des lignes d’articles synthétiques, à lire les plans et à vérifier si cette différence nécessite une intervention. Il s’appuie sur la documentation actuelle de PostgreSQL 18 et sur la documentation actuelle de node-postgres. Vérifiez la version de votre serveur avant de comparer les détails des plans ; les exemples ne garantissent pas des plans identiques d’une version ou d’une machine à l’autre. PostgreSQL : utilisation de EXPLAIN

Pourquoi le même index peut-il conduire à des choix de parcours différents ?

Un index B-tree prend en charge les comparaisons inférieur à et supérieur à. Changer le sens de la comparaison ne rend pas l’index inutilisable. Ce qui change, c’est la plage de valeurs — et donc le nombre de lignes — que le planificateur s’attend à récupérer. PostgreSQL : types d’index

Considérons une table illustrative contenant exactement 100 000 lignes d’articles, avec des ID entiers consécutifs de 1 à 100 000 :

Prédicat Lignes correspondantes Part de la table
id < 11 10 0,01 %
id > 11 99 989 99,989 %
id = 11 1 0,001 %

Ces nombres découlent des données synthétiques, et non de mesures effectuées sur une base de données existante. Aucune des deux plages n’inclut l’ID 11. Un index peut trouver efficacement les dix ID correspondants dans la première plage. Pour la seconde, il identifierait de toute façon presque toutes les lignes.

Un parcours d’index ordinaire dans PostgreSQL récupère des entrées de l’index, puis les lignes correspondantes dans le stockage principal de la table, appelé le tas. Ces lignes du tas peuvent être réparties sur de nombreuses pages. Lorsqu’il faut presque toutes les pages, lire directement la table peut coûter moins cher que de parcourir l’index et de récupérer les lignes auxquelles il renvoie. PostgreSQL : parcours uniquement par index et index couvrants

Le nombre 11 n’a donc aucune signification particulière pour le planificateur. Avec des données différentes, la condition supérieure à pourrait correspondre à la plage étroite. PostgreSQL estime la fraction des lignes correspondantes, appelée sélectivité, à l’aide des statistiques de la table. La valeur numérique de la borne ne suffit pas à déterminer cette fraction. PostgreSQL : statistiques utilisées par le planificateur

Comment reproduire la comparaison sur Mac ?

Vous avez besoin d’une connexion PostgreSQL existante et de la commande psql disponible dans Terminal sur votre Mac. L’installation ne fait pas partie de cet exercice. Ouvrez votre connexion habituelle à la base de données avec psql ; cette commande accepte les paramètres de connexion et fournit une invite SQL interactive. Exécutez les blocs suivants dans une même session neuve. PostgreSQL : psql

La configuration crée une table temporaire contenant uniquement du texte d’article synthétique. Les tables temporaires disparaissent à la fin de la session. La clé primaire entière ordinaire crée automatiquement un index B-tree unique ; un second index sur id est donc inutile. PostgreSQL : CREATE TABLE

SHOW server_version;

CREATE TEMP TABLE article_scan_demo (
    id integer PRIMARY KEY,
    title text NOT NULL,
    body text NOT NULL
);

INSERT INTO article_scan_demo (id, title, body)
SELECT
    g,
    'Article ' || g,
    repeat('Sample article text. ', 20)
FROM generate_series(1, 100000) AS s(g);

ANALYZE article_scan_demo;

SELECT
    count(*) AS total_rows,
    count(*) FILTER (WHERE id < 11) AS below_11,
    count(*) FILTER (WHERE id > 11) AS above_11
FROM article_scan_demo;

La fonction entière generate_series de PostgreSQL inclut les deux bornes avec un pas par défaut de un. La requête de comptage doit donc renvoyer 100 000 lignes au total, 10 en dessous de 11 et 99 989 au-dessus de 11. PostgreSQL : fonctions renvoyant des ensembles

Exécutez ANALYZE après le chargement des lignes afin que le planificateur dispose de statistiques sur la table remplie. Cette commande collecte des statistiques ; elle a une fonction différente de l’option de mesure de l’exécution de EXPLAIN ANALYZE. PostgreSQL : ANALYZE

Ensuite, comparez les deux requêtes qui récupèrent toutes les colonnes :

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM article_scan_demo WHERE id < 11;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM article_scan_demo WHERE id > 11;

Ces commandes exécutent chaque requête et affichent son plan ainsi que le nombre réel de lignes, sans renvoyer toutes les lignes d’articles sélectionnées. BUFFERS ajoute l’activité des blocs ; TIMING OFF désactive la mesure du temps pour chaque nœud. C’est utile pour étudier l’exécution, mais cela ne mesure pas le temps nécessaire pour envoyer toutes les données des articles correspondants à une application de bureau. PostgreSQL : EXPLAIN

Dans des conditions ordinaires, attendez-vous à un plan utilisant un index pour la plage étroite et à un parcours séquentiel pour la plage étendue. Ce ne sont que des attentes : PostgreSQL peut choisir un plan bitmap ou un autre plan applicable en fonction de ses estimations. Le code SQL de cet exemple a été inspecté, mais aucun serveur PostgreSQL n’était disponible pour l’exécuter lors de sa préparation ; le calcul des nombres de lignes a été vérifié séparément.

Quels éléments du plan expliquent le choix ?

Commencez par le nœud de parcours, son rows estimé et son rows réel. Pour la première requête, recherchez Index Scan avec un Index Cond, ou un Bitmap Index Scan sous un Bitmap Heap Scan. Pour la seconde, recherchez Seq Scan avec un Filter. Un plan bitmap utilise toujours un index ; il organise les emplacements des lignes correspondantes avant de récupérer les pages du tas. PostgreSQL : utilisation de EXPLAIN

Vérifiez ensuite les observations suivantes :

Observation dans cet exemple Élément à examiner
Les estimations correspondent à peu près aux 10 et 99 989 lignes réelles La différence du nombre de lignes correspondantes explique les choix différents.
Les lignes estimées diffèrent fortement des lignes réelles Actualisez les statistiques avant de modifier les paramètres du planificateur.
Les deux prédicats utilisent des parcours séquentiels sur un échantillon beaucoup plus petit La taille de la table peut rendre un parcours direct moins coûteux.
Un plan bitmap apparaît Comparez ses estimations de lignes et son travail sur le tas avec cette autre solution utilisant un index.

Les coûts du planificateur sont des estimations exprimées en unités arbitraires, pas en millisecondes. Comparez-les dans le cadre du modèle de PostgreSQL au lieu d’interpréter un coût de 100 comme une durée. Comme l’exemple utilise des tables temporaires, l’activité des tampons apparaît dans la section des blocs locaux ; les tampons des tables ordinaires et des index apparaissent dans celle des blocs partagés. PostgreSQL : EXPLAIN

Si les estimations vous semblent surprenantes, exécutez cette requête de diagnostic dans la même session :

SELECT
    schemaname,
    n_distinct,
    histogram_bounds,
    correlation
FROM pg_stats
WHERE schemaname = (
    SELECT nspname
    FROM pg_namespace
    WHERE oid = pg_my_temp_schema()
)
  AND tablename = 'article_scan_demo'
  AND attname = 'id';

L’histogramme décrit la distribution des valeurs utilisée pour estimer les plages. La corrélation décrit dans quelle mesure l’ordre logique de la colonne suit l’ordre physique des lignes ; des valeurs proches de l’une ou l’autre extrémité peuvent faire paraître moins coûteux l’accès au tas par index. Aucun de ces champs n’établit de pourcentage universel à partir duquel PostgreSQL doit passer à un autre type de parcours. PostgreSQL : pg_stats

Comment comparer les requêtes avec node-postgres ?

node-postgres envoie le texte de la requête et les valeurs des paramètres à PostgreSQL, où la planification a lieu. Son API de requête accepte du SQL suivi d’un tableau de valeurs ; les deux comparaisons peuvent donc utiliser la même borne, 11. node-postgres : requêtes

Pour une reproduction indépendante avec Node.js, utilisez une installation fonctionnelle de Node.js/npm et vos paramètres de connexion PostgreSQL existants. Dans un nouveau répertoire de projet, exécutez npm init -y, puis npm install pg. Enregistrez le programme complet ci-dessous sous compare-scans.mjs et exécutez node compare-scans.mjs. Le client utilise les paramètres d’environnement PostgreSQL tels que PGHOST, PGPORT et PGDATABASE ; utilisez les paramètres de votre base de données locale existante. node-postgres : connexion

import pg from 'pg';

const client = new pg.Client();

try {
  await client.connect();

  await client.query(`
    CREATE TEMP TABLE article_scan_demo (
      id integer PRIMARY KEY,
      title text NOT NULL,
      body text NOT NULL
    )
  `);

  await client.query(`
    INSERT INTO article_scan_demo (id, title, body)
    SELECT
      g,
      'Article ' || g,
      repeat('Sample article text. ', 20)
    FROM generate_series(1, 100000) AS s(g)
  `);

  await client.query('ANALYZE article_scan_demo');

  const queries = [
    'EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM article_scan_demo WHERE id < $1',
    'EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM article_scan_demo WHERE id > $1'
  ];

  for (const text of queries) {
    const result = await client.query(text, [11]);
    console.log(text);
    console.log(
      result.rows.map(row => row['QUERY PLAN']).join('\n')
    );
  }
} finally {
  await client.end();
}

Le programme crée son propre échantillon de table et conserve toutes les opérations sur une seule connexion. Il affiche les deux plans, puis ferme la connexion. Sa syntaxe JavaScript a été vérifiée avec Node.js ; aucune exécution sur une base de données n’a eu lieu lors de la préparation. Si la connexion échoue, résolvez ce problème avant d’interpréter le comportement des parcours.

Des instructions préparées nommées peuvent-elles modifier la comparaison ?

L’ajout d’un name à la configuration d’une requête node-postgres crée une instruction préparée nommée sur chaque connexion. Cela introduit une distinction supplémentaire : PostgreSQL peut utiliser un plan personnalisé fondé sur les valeurs des paramètres ou un plan générique partagé entre les exécutions. node-postgres : requêtes, PostgreSQL : PREPARE

Avec la stratégie automatique par défaut de PostgreSQL 18, les cinq premières exécutions utilisent des plans personnalisés. PostgreSQL compare ensuite le coût estimé d’un plan générique au coût moyen des plans personnalisés ; il ne passe pas automatiquement à un plan générique à la sixième exécution. PostgreSQL : PREPARE

Si le comportement de l’instruction nommée d’une application diffère de celui d’un test en SQL littéral, examinez cette instruction sur la même connexion à l’aide de EXPLAIN EXECUTE. Un plan générique affiche des symboles de paramètres, tandis qu’un plan personnalisé remplace ceux-ci par les valeurs fournies. Conservez les deux opérateurs de comparaison dans des instructions nommées distinctes, car leur texte SQL diffère. PostgreSQL : PREPARE

Quand faut-il modifier la requête ou les paramètres du planificateur ?

Commencez par préserver la tâche réelle de la requête. Remplacer SELECT * par SELECT id modifie les données que PostgreSQL doit récupérer. Lorsque toutes les colonnes nécessaires sont disponibles dans un index approprié, un parcours uniquement par index devient possible. Éviter la récupération de lignes dans le tas dépend aussi de la carte de visibilité ; un plan uniquement par index ne garantit donc pas un accès nul au tas. PostgreSQL : parcours uniquement par index et index couvrants

Si la tâche nécessite tous les articles correspondants, conservez cette exigence lorsque vous comparez les plans. Si elle ne nécessite qu’un petit sous-ensemble ordonné, testez plutôt la véritable requête ORDER BY et LIMIT : le tri et l’arrêt anticipé peuvent modifier le plan préféré. PostgreSQL : utilisation de EXPLAIN

Après avoir vérifié les statistiques, vous pouvez comparer un plan alternatif dans la session SQL existante :

BEGIN;
SET LOCAL enable_seqscan = off;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM article_scan_demo WHERE id > 11;

ROLLBACK;

Ce paramètre décourage les parcours séquentiels sans toutefois pouvoir les supprimer complètement. Utilisez le résultat comme comparaison à des fins de diagnostic. PostgreSQL modélise également l’accès séquentiel et non séquentiel aux pages à l’aide de seq_page_cost et random_page_cost. Modifier leurs valeurs relatives peut changer les choix de plans, mais la documentation recommande d’évaluer les paramètres de coût sur l’ensemble de la charge de requêtes plutôt que sur quelques essais. PostgreSQL : planification des requêtes

Pour cette table synthétique, la comparaison déterminante porte sur 10 lignes correspondantes contre 99 989. Vérifiez la requête exacte, actualisez les statistiques, comparez les lignes estimées et réelles, puis examinez le comportement des plans préparés s’il y a lieu. Ces étapes permettent de déterminer si le parcours séquentiel correspond au travail demandé et fournissent des éléments concrets pour toute modification ultérieure.

Sources