LIMIT 10 plafonne le résultat, mais trouver dix valeurs distinctes peut nécessiter des milliers de lignes en entrée. Ce guide explique comment diagnostiquer ce travail et comparer deux approches à l’aide de sujets d’articles synthétiques depuis le Terminal de votre Mac.

Pourquoi dix résultats peuvent nécessiter des milliers de lignes

Supposons qu’une table d’articles contienne 100 000 lignes réparties entre 100 sujets, avec 1 000 articles par sujet. Vous devez récupérer les dix premiers noms de sujet distincts par ordre alphabétique. La requête doit éliminer les noms répétés, conserver l’ordre des sujets et renvoyer dix résultats.

PostgreSQL définit DISTINCT comme l’élimination des lignes de sortie en double et LIMIT comme la limitation du résultat renvoyé. Ces sémantiques n’imposent pas un budget de dix lignes au parcours sous-jacent. Le planificateur tient compte de la limite lorsqu’il choisit un plan ; le travail physique dépend donc de la stratégie d’exécution retenue. SELECT

Considérons un flux ordonné contenant 1 000 occurrences de topic-001, suivies de 1 000 occurrences de topic-002, et ainsi de suite. Une opération de suppression des doublons en flux continu ne voit le dixième sujet distinct qu’après avoir consommé les neuf groupes précédents et la première ligne du dixième groupe :

Lignes d’entrée nécessaires = (9 × 1 000) + 1 = 9 001
Résultats distincts renvoyés = 10

Il s’agit d’un calcul illustratif pour cette stratégie particulière de traitement en flux continu, et non d’un plan PostgreSQL mesuré. Des groupes de tailles inégales modifient le calcul : un groupe très volumineux pour le premier sujet peut représenter l’essentiel du travail, même si les groupes suivants sont petits.

Un index B-tree correspondant peut fournir une entrée triée sans tri explicite. PostgreSQL indique qu’une récupération anticipée est possible avec LIMIT et ORDER BY indexé, mais l’élimination des doublons impose une exigence supplémentaire : suffisamment de valeurs différentes doivent parvenir au nœud limitant le résultat. Un index peut éviter le travail de tri tout en laissant subsister un travail conséquent de traitement des doublons. Indexes and ORDER BY

Lire le plan pour repérer ce qui consomme les lignes

Utilisez EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) avec la requête et les valeurs de paramètres réelles. ANALYZE exécute l’instruction et indique le nombre réel de lignes ; BUFFERS indique l’activité des blocs. L’exemple complet ci-dessous effectue ce diagnostic sur des données synthétiques. EXPLAIN

Deux formes de plan utiles sont présentées ci-dessous. Ce sont des schémas, et non des sorties capturées ou des promesses quant au plan que choisira votre installation.

Parcours en flux ordonné :
Limit
  Unique
    Index Only Scan or Index Scan

Parcours avec agrégation :
Limit
  Sort
    HashAggregate
      Seq Scan

Dans le parcours ordonné, comparez la sortie du parcours avec celles de Unique et Limit. Un écart important indique une entrée comportant beaucoup de doublons. Dans le parcours avec agrégation, l’agrégat consomme son entrée pour établir les groupes avant que le tri et la limitation finaux puissent s’achever. La documentation des plans PostgreSQL explique comment les parcours alimentent les nœuds de tri et d’agrégation. Using EXPLAIN

Examinez le nombre réel de lignes ainsi que loops : lorsque le nœud est exécuté plusieurs fois, les nombres de lignes sont des moyennes par exécution. Multipliez-les par le nombre de boucles pour estimer la sortie totale de ce nœud. Inspectez également l’entrée située sous un tri : le faible nombre de lignes émises par le tri ne signifie pas que son entrée était petite. Using EXPLAIN

Distinguez trois mesures : les lignes émises par un nœud, l’activité des blocs et le temps écoulé. Les nombres de tampons décrivent des blocs, pas des lignes logiques, et les totaux de tampons des nœuds parents incluent ceux de leurs enfants. Les tables temporaires utilisent des tampons locaux ; les fichiers de travail temporaires utilisés par des opérations telles que les tris apparaissent en tant que blocs temporaires. EXPLAIN

Reproduire la comparaison depuis votre Mac

Utilisez une version LTS actuelle de Node.js, npm et une base de données PostgreSQL locale existante. Ce guide prend PostgreSQL 18 comme référence documentaire. Le programme affiche la version de votre serveur afin que vous puissiez interpréter les résultats à l’aide de la documentation correspondant à cette version. node-postgres documente son installation avec npm install pg ainsi que la prise en charge des versions actuelles et LTS de Node.js. node-postgres

Dans Terminal, créez un répertoire de travail et installez le pilote :

mkdir distinct-row-demo
cd distinct-row-demo
npm init -y
npm install pg

Enregistrez le programme complet suivant sous le nom distinct-demo.mjs. Il crée des lignes d’articles synthétiques dans une table temporaire, mesure la requête ordinaire avant et après l’ajout d’un index, puis la compare à une requête explicite de recherche du successeur. Il utilise un seul Client pour que toutes les requêtes s’exécutent dans la même session de base de données.

Les paramètres de connexion proviennent des variables d’environnement PostgreSQL habituelles. Configurez-les pour votre base de données locale existante avant d’exécuter le programme. node-postgres documente les connexions fondées sur l’environnement ainsi que la transmission des valeurs de requête séparément du texte SQL. Connecting, Queries

import pg from 'pg';
import assert from 'node:assert/strict';

const { Client } = pg;
const client = new Client({ connectionTimeoutMillis: 5000 });
const limit = 10;

const distinctSql = `
  SELECT DISTINCT topic FROM demo_articles
  ORDER BY topic LIMIT $1::integer
`;

const seekSql = `
  WITH RECURSIVE topics(topic, step) AS (
    (SELECT topic, 1 FROM demo_articles ORDER BY topic LIMIT 1)
    UNION ALL
    SELECT n.topic, t.step + 1
    FROM topics AS t
    CROSS JOIN LATERAL (
      SELECT a.topic FROM demo_articles AS a
      WHERE a.topic > t.topic ORDER BY a.topic LIMIT 1
    ) AS n
    WHERE t.step < $1::integer
  )
  SELECT topic FROM topics ORDER BY topic
`;

async function showPlan(label, sql) {
  const plan = await client.query(
    `EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) ${sql}`, [limit]
  );
  console.log(`\n${label}`);
  console.log(plan.rows.map(row => row['QUERY PLAN']).join('\n'));
}

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

  await client.query(`
    CREATE TEMP TABLE demo_articles (
      article_id integer NOT NULL,
      topic text COLLATE "C" NOT NULL,
      title text NOT NULL
    )
  `);

  await client.query(`
    INSERT INTO demo_articles (article_id, topic, title)
    SELECT n,
           'topic-' || lpad((((n - 1) / 1000) + 1)::text, 3, '0'),
           'Sample article ' || n::text
    FROM generate_series(1, 100000) AS s(n)
  `);

  await client.query('ANALYZE demo_articles');
  await showPlan('Before index', distinctSql);

  await client.query(
    'CREATE INDEX demo_articles_topic_idx ON demo_articles (topic)'
  );
  await client.query('ANALYZE demo_articles');
  await showPlan('After index', distinctSql);
  await showPlan('Successor seeks', seekSql);

  const ordinary = await client.query(distinctSql, [limit]);
  const seeking = await client.query(seekSql, [limit]);
  assert.deepEqual(seeking.rows, ordinary.rows);
  assert.equal(ordinary.rows.length, limit);
  console.log(ordinary.rows.map(row => row.topic));
} catch (error) {
  console.error(error.message);
  process.exitCode = 1;
} finally {
  await client.end();
}

Le générateur de données produit des entiers de 1 à 100 000 à l’aide de la fonction generate_series documentée par PostgreSQL. Le calcul attribue le même sujet à chaque groupe consécutif de 1 000 articles. ANALYZE fournit les statistiques de la table utilisées par le planificateur pour choisir un plan. Set Returning Functions, ANALYZE

Exécutez le programme en indiquant le nom de votre base de données locale existante. Par exemple, si cette base s’appelle postgres :

PGHOST=localhost PGPORT=5432 PGDATABASE=postgres node distinct-demo.mjs

Les noms de sujet attendus vont de topic-001 à topic-010 ; les assertions vérifient le nombre de résultats et la concordance entre les deux requêtes. La syntaxe JavaScript et le calcul de 9 001 lignes d’entrée pour le traitement en flux continu ont été vérifiés pour cet article. L’exécution sur une base de données n’était pas possible dans l’environnement de recherche ; les durées et les plans PostgreSQL capturés sont donc volontairement omis.

Pourquoi un parcours d’index seul peut encore nécessiter un travail conséquent

Un Index Only Scan signifie que la requête peut obtenir les valeurs de colonne demandées à partir de l’index. Cela ne signifie pas qu’il y a une entrée d’index par résultat distinct. Examinez son nombre réel de lignes, même si la table elle-même est rarement consultée.

L’accès au tas constitue un autre facteur. PostgreSQL vérifie la carte de visibilité et peut encore consulter les tuples du tas lorsque les pages correspondantes ne sont pas marquées « toutes visibles ». Par conséquent, les lignes de démonstration récemment insérées peuvent entraîner des récupérations dans le tas, même avec un plan d’index seul. Vérifiez Heap Fetches en parallèle du nombre de lignes parcourues. Index-Only Scans and Covering Indexes

L’ajout de colonnes de données supplémentaires avec INCLUDE peut rendre d’autres valeurs disponibles pour un parcours d’index seul, mais ne modifie pas le nombre de sujets distincts demandés. Choisissez la couverture de l’index pour répondre à un problème d’accès au tas constaté ; mesurez séparément le traitement des doublons. Index-Only Scans and Covering Indexes

Le parcours par sauts de PostgreSQL 18 règle-t-il automatiquement ce problème ?

PostgreSQL 18 a introduit la prise en charge des parcours par sauts dans les index B-tree. L’objectif documenté est d’améliorer certaines recherches sur des index multicolonnes lorsque des contraintes sur les colonnes ultérieures peuvent guider des recherches répétées parmi les valeurs des colonnes antérieures. Cette fonctionnalité a été introduite dans la version 18 ; ne supposez pas qu’elle existe dans PostgreSQL 17 ou une version antérieure. Release 18, Multicolumn Indexes

Passer automatiquement d’une clé initiale distincte à la suivante est généralement appelé « parcours d’index lâche ». Le wiki du projet PostgreSQL distingue cette opération du parcours par sauts d’index orienté sur les prédicats et documente son émulation en SQL récursif. L’annonce du parcours par sauts de PostgreSQL 18 n’établit donc pas qu’un parcours lâche est automatiquement utilisé pour cette requête à colonne unique DISTINCT. Loose indexscan

La requête de recherche du successeur dans le programme adapte cette émulation : elle trouve le plus petit sujet, puis recherche à chaque étape le plus petit sujet supérieur au précédent. L’étape récursive s’arrête après dix résultats ou lorsqu’il n’y a plus de successeur. Avec un index adapté, chaque recherche peut passer directement au-delà du groupe de doublons précédent. Comparez son plan réel et son temps d’exécution avec ceux de la requête ordinaire, sans supposer qu’elle sera plus rapide. Loose indexscan

Cet exemple exige délibérément des sujets non nuls, un ordre croissant, la collation déclarée C et une limite positive. Avec une limite égale à zéro, la requête renverrait tout de même la ligne d’ancrage récursive ; une implémentation généralisée doit donc traiter le cas zéro séparément. Les sujets pouvant être nuls nécessitent un traitement explicite, car une comparaison « supérieur à » ne les énumérera pas. Si vous ajoutez des filtres sur les articles, appliquez les mêmes conditions à la recherche initiale et à chaque recherche de successeur ; sinon, la requête réécrite peut renvoyer un ensemble différent.

Choisir la prochaine modification à partir des observations

Après avoir recueilli les plans, utilisez ce guide de décision. Il distingue le travail qu’un index de tri peut éliminer de celui qui découle de la répétition des valeurs de sujet.

Résultat observé Prochaine piste à examiner
Un parcours alimente une agrégation et un tri final Comparez le plan existant avec un index B-tree respectant l’ordre des sujets.
Un parcours ordonné émet des milliers de lignes pour dix sujets Mesurez la taille des groupes de doublons et comparez avec des recherches explicites de successeurs.
Un parcours d’index seul indique de nombreuses récupérations dans le tas Examinez séparément la visibilité et les accès au tas, indépendamment du nombre de doublons.
Les estimations diffèrent beaucoup du nombre réel de lignes Actualisez les statistiques avec ANALYZE, puis mesurez à nouveau.
La requête ordinaire indexée est déjà peu coûteuse Conservez la requête la plus simple, sauf si une autre exigence mesurée justifie une réécriture.

Évitez de déplacer LIMIT 10 dans une sous-requête qui sélectionne dix articles bruts avant d’appliquer DISTINCT. Dans l’ordre de l’exemple, ces dix articles appartiennent tous à topic-001 ; la requête réécrite renvoie donc un seul sujet au lieu de dix. Cela modifie la tâche.

Veillez également à définir précisément la clé distincte. Ajouter article_id aux colonnes sélectionnées fait de chaque article une ligne de sortie différente et empêche la déduplication des sujets. Si la tâche consiste à choisir un article représentatif par sujet, DISTINCT ON de PostgreSQL prend en charge cette opération, à condition que ses expressions correspondent aux expressions de tri les plus à gauche. Cette clause ne garantit pas à elle seule une réduction du nombre de lignes parcourues. SELECT

Pour récupérer les dix premiers sujets d’articles distincts, commencez par comparer la sortie réelle du parcours aux dix résultats renvoyés. Testez ensuite l’index correspondant et, lorsque les groupes de doublons dominent, la requête bornée de recherche de successeurs. Préservez l’ensemble de sujets et l’ordre demandés, puis choisissez l’approche dont le travail mesuré convient à vos données.

Sources